Lesson 8 of 11 · 24 min
Customer lifetime value
By the end of this lesson you can compute historical customer value at the customer grain, compare cohorts over an equal time window, estimate subscription lifetime value from ARPA and churn, and state the assumptions behind each figure.
Customer lifetime value (CLV or LTV) is the value a customer brings over their whole relationship with the business. There are three practical flavours, from most to least certain:
- Historical CLV: what each customer has actually spent so far. Exact, but it favours old customers simply because they have had more time.
- Cohort CLV over a fixed window: average value per customer in the first N months after their first purchase, compared across acquisition cohorts. Fair between cohorts, but only for cohorts old enough to have completed the window.
- Predictive CLV: an estimate of the future, e.g. for a subscription business ARPA × gross margin ÷ churn rate. Useful for planning, but only as good as its assumptions.
Whichever you use, write down: revenue or margin (margin is what pays for acquisition), gross or net of returns, which customers (guests? test accounts? customers who never bought?), and the time window.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons