DIY Hack: How to Calculate Customer Lifetime Value
Customer Lifetime Value Calculation
Customer lifetime value is a key metric that every marketer should track, as it usually reflects the core health of customer-centric businesses.
There are various formulas floating around the Web aimed at helping marketers quickly calculate customer lifetime value. Using these straightforward equations may be appealing, but when components of the formula are based on averages (as they must be), the results are usually quite crude.
Instead, you might want to use the following simple method. This approach uses Excel, but it does not require any complex formulas or macros. It’s not as accurate as building a customer model based on predictive micro-segmentation (which is the basis of Optimove’s retention automation software), but it is faster, more cost-effective and more accurate than many other alternatives.
Track the Spending of a Cohort of New Customers
Start by taking a cohort of new customers and follow their cumulative spending over time. This approach gives you an accurate basis, both because cohorts are closed groups of actual customers, and because cohorts already include all the parameters that you would otherwise have to insert into your formulas (for example, churn is built in because the cohort’s value drops from period to period due to customer churn).
Tally the cohort’s total cumulative revenues (or any other value metric you choose) in Excel for each period, starting with their first period. Divide this figure by the number of customers in the cohort to get an average per-customer figure. In this example, I am looking at the cumulative revenue generated by an average customer within one particular cohort during their first six months:
Create a simple Excel line chart for this data (instructions here).
Plot a Logarithmic Trendline of the Data
Next, use Excel’s built-in Trendline function to discover the logarithmic formula that best fits the cohort’s value trend over time. You can then use that formula to predict the value of this cohort’s average customer into the future. (The reason I suggest using a logarithmic trendline is because it is usually the best fit for any pattern that exhibits diminishing periodic increases over time, which is usually the case when looking at the cumulative value of any given customer cohort.)
Here is an example, where I’ve plotted the six months of cumulative spending data above for an average customer within our cohort, and then generated a predictive trendline, an additional six periods into the future:
To get this type of graph in Excel, and reveal the logarithmic formula as shown, add a trendline to your line chart, by clicking Trendline and then More Trendline Options:
Select Logarithmic, enter the number periods you want to see in your forecast, and select the Display Equation checkbox:
You can now use the logarithmic formula determined by Excel to tally CLV as far into the future as you consider reasonable.
Two Tips for Better Results
- Instead of selecting one particular cohort, consider using averages of the per-period figures of multiple cohorts. This will smooth the statistical variance that might appear in any one individual cohort, which could potentially have very different behavior than an average cohort.
- Perform the above process for three or four high-level customer segments, such as acquisition source or primary geographic location. The choice of segmentation basis depends on what makes the most sense for any particular business. If you skip this step and look at all your customers as one large group, the results will be less accurate because the variance across your entire customer base is typically too great to yield useful information. Also, it can be valuable to compare the lifetime value figures of different segments.
Use an Advanced Solution to Calculate Customer Lifetime Value
If you’re looking for more advanced customer data analysis, and automated targeting and personalization of customer marketing campaigns, check out our Optimove’s Science-First Relationship Marketing Hub.
Comments (8)
Hi Pini, This is a very useful article to help with calculating CLV. I have been researching on the topic recently. Good job here.
Thanks, Sylviya
This is a misleading post. When you fit a model, specially for predictive analytics, you need to validate it. The author takes a deterministic view and ignores variability. Unfortunately life is more complex and data shows variability, which therefore needs to be accounted for. The models shown here cannot be generalized and therefore should be used with caution.
Thanks for the feedback, Ron! Creating a predictive model involves myriad considerations, among them the ones you mention. However, they are beyond the scope of this blog post, which aims to equip marketers having no prior knowledge in analytics with a basic and practical technique for estimating LTV.
Hi Pini,
Thank you for this blog post.
In your example the customer bought more each month, but if the customer’s purchase amount changes in an irregular order – the first month is high, the second month is low, third again high. How will LTV look?
Asaf
Hi Asaf,
Thanks for your question.
The customer in this example actually didn’t buy more each month. The values presented in the table are the customer’s *cumulative* purchases. Thus, it doesn’t matter if the customer buys more or less in the second or third month. The cumulative metric would increase or remain the same. For instance: 1st month $100, 2nd month $0, 3rd month $20 – the cumulative figures would be $100, $100 and $120 accordingly.
Cheers,
Pini
Thanks for good input on this topic! A question: Shouldn’t LCV be calculated as total PROFITS from a customer, not just revenue.
Say a cohort of customers actually were producing a loss, then we would want to mimimize selling to these customers etc.
So a LCV must somehow take into account the profit from customers. This is the “value” we provide to the shareholders (minus fixed costs).
Could you comment on this?
Thanks
Hi David,
Great question. Each company chooses the metric they use for customer lifetime value, such as revenue, profit or any other value that makes the most sense for each particular business. Towards the beginning of the article, I referred to this as, “Tally the cohort’s total cumulative revenues (or any other value metric you choose)…”
Cheers,
Pini