Customer Value versus Customer Lifetime Value
I came across a wonderful table that nicely create cohorts based on when a customer was acquired. Each value represent a running total spent per customer. As you would expect, these values are higher for customers with older acquisition dates. This does NOT represent the Customer Lifetime Value (CLV), as most would define it, is a projection as to how much a customer or cohort will spend during their lifetime. CLV calculations are very complex and best suited for machine learning. I created a simple CLV calculation based on the most recent purchases. Most CLV results from consumer experience suggest recent purchases are the best indicators of future purchases.
This table show the running total per customer.
This table is a projection of Customer Lifetime Value - along with a List of all customers - displaying rolling sales and projected CLV.
This CLV calculation is NOT realistic and is used as an example. The calculation of CLV is simply 2 times the amount spent in the last four quaters. Certainly, not a good calculation. The best CLV projections use machine learning. To do this we would need to use a program to calculate the CLV for each customer and then we can aggregate them in a tool like Tableau.
Known in Marketing is a Buy Till You Die model - used for many decades. The basics of this came about in the research of sales data and analysts noted that there is a high chance that if a customer does not purchase within, let's say a year, they will probably not purchase again (therefore dead to the merchant, and maybe literally dead). Modeling this behavior and projecting into the future is an area of financial mathematics. Consumer behavior is not linear and machine learning is best suited for analysis. Below is a reference of an implementation of the BTYD model in Excel with reference to the author.