How do we build a cohort analysis dashboard that shows which customer vintages are most profitable and which will churn?
Cohort dashboard tracks ARR, expansion rate, and churn risk by acquisition year. Build it as a waterfall: each cohort row shows entry, expansion, churn, and ending value. Profitable vintage predicts hiring and GTM scaling.
The Cohort Table Architecture
Instead of aggregate metrics ("34% growth"), show every year-of-acquisition as a separate P&L:
| Cohort | Customers | Entry ARR | Yr 1 Expansion | Yr 1 Churn | Yr 1 Ending ARR | Expansion % | Churn % | Lifetime Value (Projected) |
|---|---|---|---|---|---|---|---|---|
| 2022 | 142 | $1,800k | $540k | −$150k | $2,190k | 30% | 8% | $6.2M |
| 2023 | 189 | $2,100k | $620k | −$210k | $2,510k | 30% | 10% | $6.8M |
| 2024 | 223 | $2,400k | $480k | −$120k | $2,760k | 20% | 5% | $7.1M |
| 2025 | 156 | $1,700k | $120k | −$30k | $1,790k | 7% | 1.8% | TBD |
Key Insights from Above:
- 2022 cohort: Growing (30% expansion/yr), but churn accelerating (8% ARR loss = customers leaving). Year 4–5 risk.
- 2023 cohort: Stable (30% expansion), but churn trending up (10% vs. 2022's 8%). Watch next year.
- 2024 cohort: Strong (20% expansion, only 5% churn). Healthy vintage.
- 2025 cohort: Early (month 1–3). Expansion low because new. Churn will tell the story in months 9–12.
Why Cohorts Matter to Profitability
Cohort analysis separates time value from customer quality:
- 2022 cohort may have high expansion because they're in year 3 (seat growth, use-case depth happens later).
- 2025 cohort has low expansion because they're brand-new (no time for expansion yet).
- If 2024 expansion is half of 2023, either your product got worse, your pricing changed, or the 2024 cohort is lower-quality (weaker fit).
Three Dashboards, One Data Source
1. Cohort Waterfall (Finance View)
`` 2022: Start $1.8M → + Expansion $540k → − Churn $150k → End $2.19M 2023: Start $2.1M → + Expansion $620k → − Churn $210k → End $2.51M 2024: Start $2.4M → + Expansion $480k → − Churn $120k → End $2.76M ``
This shows: are older cohorts dying faster (churn %) or expanding slower (maturation)?
2. Profitability by Cohort (Unit Econ View)
| Cohort | Customers | Avg CAC | Payback (Months) | 3-Yr LTV | LTV:CAC |
|---|---|---|---|---|---|
| 2022 | 142 | $2,800 | 16 | $18,200 | 6.5x |
| 2023 | 189 | $2,650 | 17 | $19,100 | 7.2x |
| 2024 | 223 | $2,400 | 14 | $20,300 | 8.5x |
Trend: Newer cohorts have lower CAC (better sourcing or market shift) and faster payback. Good sign.
3. Churn Risk Heatmap (Ops View)
`` 2022: ▓▓░░░ (8% churn—medium risk) 2023: ▓▓▓░░ (10% churn—watch) 2024: ▓░░░░ (5% churn—healthy) 2025: ░░░░░ (1.8% churn—new, pending) ``
How to Identify Profitable vs. At-Risk Cohorts
Profitable (Green Flags):
- Expansion rate stays 25%+ in years 2–3.
- Churn <6% ARR annually.
- Payback <16 months.
- LTV:CAC >5x.
At-Risk (Red Flags):
- Expansion drops >30% from year 1 to year 2 (maturation curve broken).
- Churn >12% ARR annually (customer fit issue).
- Payback >20 months (CAC too high or expansion too low).
- LTV:CAC <3x (money-losing cohort).
Implementation Steps:
- Tag every customer at creation: acquisition date, source (SDR, AE, inbound, partner), ACV, segment.
- Monthly cohort pull: For each vintage (2022, 2023, etc.), sum current ARR, YTD expansion, YTD churn.
- Dashboard: Use Tableau, Looker, or SQL-based Redash. Plot cohort as rows; columns = vintage, entry ARR, expansion $, churn $, ending ARR, payback.
- Refresh cadence: Monthly (weekly is noise; annual is too late).
Red Flag to Investigate: If 2024 cohort has 50% higher expansion rate than 2023, either:
- You hired better AEs (expansion talent).
- Your product improved (more use cases).
- Your 2024 cohort is not comparable (e.g., more enterprise, vs. SMB in 2023).
Trace back: segment, ACV, buyer profile. If segments are the same, you have a talent or product win. If different, your cohorts aren't apples-to-apples.
TAGS: cohort-analysis,revenue-reporting,ltv,churn,profitability,unit-econ