ModeldrillCohort analysis
A cohort analysis groups customers by the period they joined and tracks each group across the following periods: customers retained, revenue, and cumulative contribution against the acquisition cost. Retention in month n = customers active in month n / customers at start; payback is the first month where cumulative contribution exceeds CAC.
Syntax
Retention_n = Active_n / Cohort size; Cumulative contribution_n = sum of (Active_t x ARPU x gross margin) for t = 1..n| Part | What it is |
|---|---|
Cohort size | Customers acquired in the start month |
Active_n | Customers from that cohort still paying in month n |
ARPU x gross margin | Contribution per active customer per month |
Examples
Cohort of 100; active in months 1 to 3: 100, 90, 81
=81 / 100Result: Month 3 retention 81%
Contribution 24 per active customer per month
Monthly: 2,400, 2,160, 1,944; cumulativeResult: 2,400, 4,560, 6,504
CAC 100 per customer = 10,000 for the cohort; cumulative reaches 9,828.24 in month 5 and 11,245.42 in month 6
=MATCH(TRUE, cumulative>=10000, 0)Result: Payback in month 6
Enter as an array formula in older Excel.
Common mistakes
- Comparing cohorts by calendar month instead of by months since joining, which hides whether retention is improving.
- Reading the bottom-right of a cohort triangle as data: young cohorts have fewer months, so blank cells mean not yet observed, not zero.
- Using revenue rather than contribution for payback, which makes payback look faster than it is.
Practice Cohort analysis
These drills check your answer on live data, free, no account needed.
- Cohort payback by monthFollow one customer over a year. Fill the share still active (row 7: one less churn, raised to months since the start), the expected gross profit (row 8), the cumulative gross profit (row 9) and, in B11, the first month in which the cumulative total reaches the acquisition cost in B4.
- Twelve months of subscribersBuild a monthly subscription forecast. Each month, the customers at the end equal the prior month less churn, plus new customers. Fill row 8 (January works from the starting customers in B2) and row 9 (revenue is month-end customers times price).
- CAC payback and lifetime valueFor each acquisition channel, fill the cost to acquire a customer (column G), the months of gross profit needed to earn it back (column H), the lifetime value as monthly gross profit divided by churn (column I) and the ratio of lifetime value to acquisition cost (column J).
Questions
How do I lay out a cohort table in Excel?
One row per acquisition month, one column per month since acquisition (0, 1, 2, ...), values from a pivot or SUMIFS on start month and age. Format as a heatmap to read it quickly.
What retention curve is normal?
Consumer apps often keep 20% to 40% after three months; B2B SaaS keeps 85% to 95% per year. The shape matters: a curve that flattens is healthy, one that keeps falling is not.
Related
- CAC and LTVCAC = sales and marketing spend / new customers; LTV = ARPU x gross margin / churn rate. Examples giving LTV to CAC of 4.8 and a 4.2 month payback, errors.
- Payback periodPayback period = years before full recovery + unrecovered amount / cash flow in the recovery year. Simple and discounted versions, exact examples, formula.
- Revenue growth and CAGRCAGR = (end value / start value)^(1 / years) - 1, or =RRI(years, start, end) in Excel. Year over year growth, exact examples and the off-by-one trap in n.
- Gross marginGross margin = (revenue - COGS) / revenue. The formula, worked examples with exact percentages, the difference between margin and markup and common mistakes.