Getting to grips with the Real Customer Lifetime Value

Getting to grips with the Real Customer Lifetime Value – Part 3

The full skinny on how to build your own customer lifetime model

In Parts 1 & 2 of this mini-course I went through the essentials of why knowing your customer lifetime value is vital to your business growth and what’s involved with establishing what it is for your business. In this third and last part I want to show you how to build your own customer lifetime model.

Download Your Master Files

Please download the Lifetime Value zip file (right-click and choose Save as) and extract the contents to your computer. Inside is a PDF (containing these 3 articles) and two Excel spreadsheets; one is a ‘master template’ that does most of the work for you.

Not all, but most! It isn’t complete until you input your own data. The hard work is done by the spreadsheet and the formulas it contains.

The other spreadsheet is a working example for you to study.

STOP!

Before you do anything else, create a template-master file of the spreadsheet then use it to open a second file to create your ‘what if’ or personal working file. Only enter your own data into your working file. That way you always have the template-master to go back to.

Please open your work file. Inside you will find two worksheets labelled ‘Detail’ and ‘Model’.

The Detail Spreadsheet – Assembling the Information

Here you have a basic checklist of the factors that you may want to include in your own LTV model. Where these factors have a cost, the more precise your definition is, the more accurate your LTV model will be. You can add or delete data rows above the ‘Total’ without affecting the addition formula.

In my experience it is also the area where direct marketers get things wrong. Despite their interest in getting their metrics right, they often seem to run away when they see the £ or $ signs. Of course they are different, but they’re not difficult. Plus you’ll be able to defend your results when others ask questions.

The important idea to remember is to be consistent in the elements you include so that all versions can be comparative. For example, if you have a telephone response option then including those costs in one version and not in another must affect the end result and any comparison between those results is not viable.

You also don’t need to go overboard in the costs you include. You’ll have set your product prices to cover your overheads so there’s no need to consider them.

What must be included are the extra costs associated with the specific segment you are modelling. For example, the costs of recruitment, subsequent costs of communication and handling promotional costs – except discounts as they are already accounted for in the sales value.

There’s one factor that is a bit more of a challenge to deal with. You’ll remember from Part 1 that I wrote about needing to account for inflation. This is done by applying a discount rate but, where do you get this from? Some will try to tell you that the discount rate is equivalent to the prevailing interest rate. It’s not!

Sure, if you had to borrow the money you’d pay a bank’s lending rate, but that’s only a start. You also need to allow for risk and more which I would need another course to describe.

So use these ‘rules of experience’ – if the bank rate is:
° less than 5% use a discount rate of 10%
° from 5- 10% double it to establish the discount rate
° over 10% add another 10%
° if it is over 10%… then god help us!

The Model Spreadsheet – Making The Maths Simple

The major formula in the spreadsheet is shown below and it’s not that simple to understand! That’s why the model worksheet takes care of the mathematics for you.

Real lifetime value formula

But there are two issues with this formula of much greater relevance:

1. You want tools to help you make better decisions. You don’t want to become a mathematician. This LTV model is one of the most useful marketing tools you can use.

2. The formula gives you a single figure result. In the example I’ve given the Customer LTV is £98.22 based on net cumulative sales over 5 years. When we refine the calculation based on profit earned over 5 years the Customer LTV is £39.29. Of course those are results we set out to establish but they hide too much essential information.

When you look at the example spreadsheet you’ll see the impact of the retention rate on the decline in the customer numbers year on year. This may be a factor that you may not have appreciated in hard numbers before. And there’s much more to review besides.

Run spreadsheet tests to examine what you would need to do to increase Customer Lifetime Values. If you use realistic alternatives and the results are positive then you have removed some of the risk before you organise a live test.

Please go through the spreadsheets to familiarise yourself with the information they reveal. There’ll be a surprise or two for you I’m sure

Questions? I will do my best to answer any questions left in the comments below.

If this mini-course has been useful, and you think that another article at a later date to build on how to get the best out of your customer LTV would be helpful, please let us know by adding a comment below or sending an email to carol@carolbentley.com.

Terry Savage has been advising, training and mentoring direct marketers successfully for more than 35 years. From individual addresses on metal to highly complex online applications he’s managed them all and helped develop a few of them too.

His consultancy continues to work with major direct marketing companies in building new online strategies and resources. Now he’s distilling his skills and experiences via online products and services for everyone.

People seem to think he knows what he’s talking about (I certainly do!)

Terry Savage: BA, DipM, FCIM, FIDM, FISMM, AMRS, Cert Accy

Be Sociable, Share!