MKT 626 ยท Forecasting churn and CLV: Tool 4
Two roads to E(CLV)
Start from one survivor curve, S(t). Road 1 weights each lifetime value by how many customers have that lifetime. Road 2 weights the cash so far by how many customers survive. Same inputs, same answer, and the 0/1 matrix shows why: one road adds up rows, the other adds up columns.
Timing of cash flows and churn
Cash flow arrives at the start of the month. The churn decision happens at the end of the month. A customer who churns at the end of month t has lifetime L = t and pays t times, at t = 0, 1, ..., t-1. Every payment is the same cash amount, m, but a dollar later is worth less today, so month t's payment is worth m / (1+d)t today. CAC is paid once, at t = 0.
How many people have each lifetime?
S(t) is the share of the acquired cohort still alive through t, so it is still paying at the start of month t. S(0) = 1: everyone makes the first payment. Both roads are built from this one curve.
| t | S(t) | r(t) = S(t) / S(t-1) | P(L = t) = S(t-1) - S(t) |
|---|
Survivor curve: % still here, S(t)
How many will have each lifetime? P(L = t) = S(t-1) - S(t)
Road 1: weight each lifetime value by how many have that lifetime
Every possible lifetime has its own CLV. How many customers get it? P(L = t) = S(t-1) - S(t). E(CLV) is the probability-weighted average. Everyone still alive at n* sits in the spike at max(CLV), with height P(L ≥ n*) = S(n*-1). n* is set far enough out that every longer lifetime has the same CLV to the cent. The bar heights are exactly the "how many have each lifetime" bars from step 2; only the x-axis changes, from lifetime L to CLV(L).
| Lifetime L | P(L) | month L adds m / (1+d)^(L-1) | CLV(L) | P(L) x value |
|---|
Road 2: weight the cash so far by how many survive
Road 2 never asks how long any one customer lasts. It goes month by month. Month t's cash, m / (1+d)t, only comes from customers who are still here, and on average that is a share S(t) of the cohort. So the expected cash in month t is m S(t) / (1+d)t. Add those up, month after month, and the cash so far climbs to E(CLV). The survivor bars on the left are the same S(t) from step 2; on the right, each one is multiplied by that month's discounted cash.
| Month t | S(t) | 1 / (1+d)^t | m S(t) / (1+d)^t | Cash so far |
|---|
Why the roads are equal: one grid, two ways to add it up
Each row is a type of customer: the ones who churn at the end of month 1, month 2, and so on, plus everyone still here at the horizon. Each column is a month. A cell is m x 1 if that customer is still paying that month and m x 0 if they are gone. Road 1 adds across each row first (sum over time: that row's CLV), then averages the rows using P(L). Road 2 adds down each column first (sum over customers: the shares still paying add up to % surviving, S(t)), then adds across months.
Where the roads meet: pick a horizon big enough
Adding up to infinity is possible, but the formula is inconvenient. Road 1 already runs out to n*, the first month where every longer lifetime is worth max(CLV) to the cent. Road 2 needs you to pick how many months to add: that is E(CnV), the value of the first n months. Drag n down in the bar above and watch Road 2 fall short; at n = n* the two roads agree. (Stop Road 1 at the same n and they would agree at every n: the solid and dashed lines are the same curve.)
Match your Excel
Uses the same m, d, CAC and churn inputs as above. The Class 7 workbook's BG CLV tab runs 586 months (rows 9 to 594).
Formulas, conventions, and sources
Timing. Cash at the start of month t, churn at the end. The first payment is at t = 0, undiscounted, and everyone makes it: S(0) = 1. Lifetime L = t means payments at t = 0, ..., t-1.
Road 1. E(CLV) = sum over L = 1..n-1 of P(L) CLV(L) + S(n-1) CLV(n), where CLV(L) = -CAC + sum over tau = 0..L-1 of m / (1+d)^tau and P(L) = S(L-1) - S(L). The last term is the spike: everyone alive at n* = n gets the value of an n-month lifetime.
Road 2. E(CLV) = -CAC + sum over t = 0..n-1 of m S(t) / (1+d)^t. This is E(CnV); it approaches E(CLV) as n grows.
Churn. BG: S(t) = S(t-1) (b + t - 1) / (a + b + t - 1). Constant: S(t) = r^t. Defaults are the Class 7 workbook (Blue Apron): a = 0.6746, b = 1.2336, m = $26.82 (AOF 1.8 x AOV $57.30 x 26% margin), d = 1.53% monthly, CAC = $100. The constant-r default 0.815 is 1 minus the one-segment geometric fit (0.185).
Source. Built from Eric's handwritten notes, "CLV Distribution Plots and Two Approaches to E(CLV)".