Why churn by tenure month
A calendar churn rate, such as "5% of subscribers cancelled in May", mixes customers in their first month with customers in their third year. Survival analysis lines customers up by tenure instead: month 1 is everyone's first month, whenever they joined. That shows when customers leave, which is what retention work needs to know.
Step 1: the customers at risk
Take one sign-up cohort and count the cancellations in each tenure month. The risk set n(t) is everyone still active at the start of the month: =B2-C2, the previous month's n minus its cancellations. Month 1 starts with 1,000 customers and loses 180, so month 2 starts with 820, month 3 with 700, and so on down to 290 at the start of month 12.
Step 2: the hazard rate per month
The hazard is =C2/B2: 180 ÷ 1,000 = 18.0% in month 1, 120 ÷ 820 = 14.6% in month 2, then steadily lower until it levels off around 7.6% in months 10 to 12. Do not divide by the original 1,000. That gives the share of the whole cohort lost in a month, which falls just because fewer customers are left and hides whether loyal customers really become safer.
Pool months by dividing total cancellations by total customer-months at risk, never by averaging the rates. Over months 7 to 12 the pooled hazard is 7.98%, so the month-1 hazard is 2.26 times as high. Most subscription businesses look like this: the first box, the end of a trial or a failed card payment cause most of the losses.
Step 3: the survival curve
To still be active after month t, a customer must survive every month before it, so the survival rates multiply: S(t) = S(t−1) × (1 − h(t)), in the sheet =E2*(1-D3). S(1) is 82.0%, S(2) 70.0%, S(4) 54.0% and S(12) 26.8%. Do not subtract the hazards from 100%: each one applies only to the customers still there, so subtracting them overstates the losses.
Worked example: one cohort of 1,000 subscribers
Column C holds the cancellations per tenure month of a fictional subscription cohort. Columns B, D and E are formulas, rows 15 to 20 summarize the curve.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Tenure month | Active at start (n) | Cancelled (d) | Hazard h = d ÷ n | Survival S(t) |
| 2 | 1 | 1,000 | 180 | 18.0% | 82.0% |
| 3 | 2 | 820 | 120 | 14.6% | 70.0% |
| 4 | 3 | 700 | 90 | 12.9% | 61.0% |
| 5 | 4 | 610 | 70 | 11.5% | 54.0% |
| 6 | 5 | 540 | 55 | 10.2% | 48.5% |
| 7 | 6 | 485 | 45 | 9.3% | 44.0% |
| 8 | 7 | 440 | 38 | 8.6% | 40.2% |
| 9 | 8 | 402 | 33 | 8.2% | 36.9% |
| 10 | 9 | 369 | 29 | 7.9% | 34.0% |
| 11 | 10 | 340 | 26 | 7.6% | 31.4% |
| 12 | 11 | 314 | 24 | 7.6% | 29.0% |
| 13 | 12 | 290 | 22 | 7.6% | 26.8% |
| 14 | Measure | Result | |||
| 15 | Median lifetime (first month S ≤ 50%) | 5 | |||
| 16 | Pooled monthly hazard, months 1–12 | 11.60% | |||
| 17 | Median if churn were constant at that rate | 5.6 | |||
| 18 | Month-1 hazard ÷ hazard of months 7–12 | 2.26 | |||
| 19 | Still active after 12 months | 26.8% | |||
| 20 | Active after 12 months at that constant rate | 22.8% |
- B3
=B2-C2Customers at risk at the start of the month - D3
=C3/B3Hazard of tenure month 2 - E3
=E2*(1-D3)Survival: multiply, do not subtract - B15
=MINIFS(A2:A13,E2:E13,"<=0.5")Median lifetime - B16
=SUM(C2:C13)/SUM(B2:B13)Pooled hazard, not an average of rates
Fictional data for illustration, not business or financial advice.
Step 4: the median customer lifetime
The median lifetime is the first month in which S(t) falls to 50% or below. =MINIFS(A2:A13,E2:E13,"<=0.5") finds it: S(5) = 48.5%, so the median is 5 months. Half of the customers who sign up are gone within five months, even though those who stay a year become fairly loyal.
The median is more robust than an average lifetime, because it does not depend on how long the most loyal customers will eventually stay, which a young business cannot know yet.
What a constant churn rate gets wrong
Many LTV models assume one constant monthly churn rate. The pooled hazard of months 1 to 12 is 11.60%. At that constant rate, the median would be =LN(0.5)/LN(1-B16) = 5.6 months and only 22.8% would remain after a year. The real curve loses customers faster at the start and slower later, so 26.8% remain. For a business that earns most of its profit from long-term customers, that difference matters. The LTV to CAC ratio is only as good as the lifetime behind it.
Customers who have not had the chance to churn
This example follows a cohort that was observed for the full 12 months. In a real customer list, recent sign-ups are still active at the cut-off date: they are censored. They must stay in the risk set for the months you observed them and then drop out without counting as churn. The Kaplan-Meier curve in Excel handles exactly that, with the same multiplication of survival rates.
Common mistakes
- Dividing by the starting cohort. Cancellations ÷ 1,000 is not a hazard. Divide by the customers still active at the start of the month.
- Averaging monthly hazards. A simple average gives the small late months as much weight as month 1. Pool: total cancellations ÷ total customers at risk.
- Subtracting hazards to get survival. 1 − h1 − h2 − … overstates the losses and can go below zero. Multiply the survival rates.
- Dropping recent sign-ups. Customers who are still active are censored, not missing. Leaving them out makes churn look worse than it is.
- Using one constant churn rate for everyone. With front-loaded churn, a constant rate gets both the early losses and the loyal tail wrong.
- Mixing cohorts with different prices or plans. A price rise or a new plan changes the hazard. Compare cohorts and segments before pooling them.
Frequently asked questions
What is the difference between churn rate and hazard rate?
A churn rate is usually measured per calendar period across all customers. A hazard rate is measured per tenure month and conditional on survival so far: the share of customers in their month t who cancel in that month.
How do you calculate median customer lifetime?
Build the survival curve S(t) = S(t-1) x (1 - h(t)) from the monthly hazards and find the first month in which S(t) is 50% or less. In Excel, MINIFS on the month and survival columns does it in one formula.
Is customer lifetime 1 divided by the churn rate?
Only if the churn rate is constant every month. Then the average lifetime is 1 / churn. Most subscription businesses lose many customers early and few later, so the survival curve gives a more realistic picture.
What is a good churn hazard in the first month?
It depends on the product and price. What matters more is the shape: if month 1 is far above later months, onboarding and the first experience are where retention work pays off most.