Computing Customer Lifetime Value

By Pooja Ranganathan

A few days ago, Avinash Kaushik and David Hughes published a post on Customer Lifetime Value. David was kind enough to provide a downloadable Excel template to get readers started on their own LTV analysis. The post and the worksheet provided a perfect opportunity for us at Medill IMC to vet our techniques against those used by industry practitioners. Last quarter, Professor Ed Malthouse taught several models and approaches to computing customer lifetime value at Medill.

The basic premise in both techniques is the same:

1. Segment your customers. On this note, David’s idea of segmenting by acquisition channel for optimal allocation of your marketing budget is excellent. He also suggests segmenting by purchase behavior, using differences in average order value, frequency of purchases, retention periods, etc.
2. For each segment, compute retention rates. What percentage of the original segment do you retain year-on-year?
3. Estimate the net profit of customers retained each year. This is a function of frequency of purchase, average order amount, and costs.
4. Discount each year’s net profit to get its net present value, less acquisition costs. The sum of all NPVs less acquisition costs is the customer lifetime value.

I won’t cover this process in more detail as it has been wonderfully explained in the original post. It’s almost exactly the same as the technique taught at Medill, with a few differences:

• David’s technique accounts for different average order amounts, as he uses it as a basis for segmentation (“best” vs. “average” customers).
• The technique taught at Medill accounts for “recency,” which is the period in which the customer last made a purchase. Your “active” customers in the current period would therefore include the customers that you retained from the immediately preceding period, as well as those you lost earlier that renewed their relationship with you in this period (after an absence of a few periods). The retention rate for the latter would vary based on recency as the longer a customer is inactive, the lower his probability of renewing the relationship in a later period.
• By accounting for different recencies, we can vary our marketing expenditure accordingly, and spend less on customers that have been “inactive” for a longer time.

A happy union of David’s technique and the approaches taught at Medill is a small step away. One way of doing it is to segment customers based on revenue or average order amount (deciles are a common way of doing so), and replicate the spreadsheet for each of the top deciles.

How would you go about estimating LTV? Are there any further tweaks you would recommend?

***

Pooja Ranganathan is a blogger at Vitamin IMC and a student in the Masters in Integrated Marketing Communications program at Northwestern University’s Medill School. She can be reached at PoojaRanganathan2010@u.northwestern.edu