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

1. 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.
2. 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.