How to Build a Cohort Analysis That Actually Tells You Something
Cohort analysis is the most powerful tool for understanding whether a business is actually retaining and growing customers over time. A cohort is a group of customers who started at the same time --- and tracking their revenue, activity, or retention together reveals patterns that aggregate metrics hide. Most early-stage cohort analyses show either raw retention data without context or aggregate metrics that make retention look better than it is. A cohort analysis that actually tells you something shows how revenue from each cohort evolves over time, whether retention is improving across cohorts, and what the LTV trajectory looks like as cohorts mature.
Author: Yanni Papoutsi · Fractional VP of Finance and Strategy for early-stage startups · Author, *Raise Ready*
Published: 2025-03-08 · Last updated: 2025-03-08
Reading time: \~7 min
What Cohort Analysis Is and Why It Beats Aggregate Metrics
An aggregate retention metric --- "we retain 85% of customers annually" --- describes the current cross-section of the customer base. It does not tell you whether retention is improving or deteriorating, whether early cohorts behave differently from recent ones, or whether the 15% who churn do so in month one or month eighteen.
Cohort analysis tracks the same group of customers from their start date forward. This reveals:
When churn happens (is it front-loaded or distributed across the
lifetime?)
Whether newer cohorts retain better or worse than older ones How revenue per cohort evolves (does expansion offset churn? At what
point?)
What the actual LTV trajectory looks like from observed data rather
than assumptions
The Structure of a Revenue Cohort Analysis
The standard revenue cohort table shows, for each acquisition cohort, the revenue generated in each subsequent period as a percentage of the revenue generated in the first period.
Example structure (simplified):
Jan 2024 | 100% | 92% | 88% | 85% | 90% Feb 2024 | 100% | 90% | 86% | 84% | 89% Mar 2024 | 100% | 91% | 87% | 85% | ---
What a Good vs. Problematic Cohort Chart Looks Like
Healthy patterns:
Revenue curve flattens after initial churn and stabilises ---
cohorts reach steady-state retention
Later months show values above 100% --- expansion revenue drives NRR
above 100%
Newer cohorts show similar or better retention curves than older
ones --- product-market fit is consistent
Warning patterns:
Revenue continues declining through month 12+ without stabilising
--- ongoing churn with no floor
Newer cohorts show worse retention than older ones --- potential
product-market fit erosion or quality of acquisition declining
All growth is in month 0 with rapid decay --- new customer
acquisition driving the top line, not retention
Only a few cohorts are large enough to read --- data concentration
risk Key insight: Investors reviewing cohort data are specifically looking for the "smile curve" --- a revenue cohort that dips in early months due to initial churn, then recovers and climbs above 100% as expansion revenue kicks in. Businesses with smile curves on their cohort data have materially stronger unit economics conversations than those without.
The Three Cohort Analyses Every Startup Should Have
1. Revenue retention cohort
Tracks revenue from each cohort over time, as a percentage of first-period revenue. This is the primary cohort analysis for any subscription or recurring revenue business. Shows NRR, GRR, and the expansion pattern all in one view.
2. Customer count cohort (logo retention)
Tracks the number of customers from each cohort that are still active in subsequent periods. This is different from revenue retention --- a cohort might show improving revenue retention but declining logo retention if a few large customers are growing while many small ones churn. Both dimensions tell part of the story.
3. Usage or engagement cohort
For PLG businesses or products with usage-based pricing, cohort analysis on product usage (active sessions, feature adoption, API calls) is often the leading indicator of future revenue retention. Declining engagement cohorts predict revenue churn before it appears in the financials.
How to Build Cohort Analysis in Excel
The structure:
Rows: acquisition cohorts (each row is a group of customers who
started in the same month)
Columns: periods since acquisition (month 0, month 1, month 2\...) Values: revenue (or customers) from that cohort in that period The calculation for each cell is: revenue from cohort X in period N ÷ revenue from cohort X in period 0.
Important: normalise to cohort start revenue, not absolute revenue. Absolute revenue comparisons across cohorts are misleading because later cohorts are typically larger. Percentage of starting revenue puts all cohorts on the same scale.
For startups with fewer than 12 months of data, the cohort table will have a triangular shape --- early cohorts have more periods of data than recent ones. This is normal. Plot what you have and fill in the rest as data accumulates.
What Investors Ask When They See Cohort Data
"Show me the cohort where retention stabilises."
This asks for the month at which the decay curve flattens. Businesses where retention never stabilises have a churn problem that compounds over time. The stabilisation point also determines the appropriate LTV calculation window.
"Are your newer cohorts better or worse than your older ones?" Improving cohorts signal that retention is getting better as the product matures. Deteriorating cohorts signal that quality of acquisition may be declining (less targeted growth) or that the product is regressing. **"What drives the expansion in cohorts that show revenue above 100%?"**
Investors want to understand whether expansion is from organic usage growth, from proactive upselling, from seat additions, or from price increases. Each has different implications for sustainability and scalability.
Frequently Asked Questions
How many customers do you need for cohort analysis to be meaningful?
As a rule of thumb, a cohort with fewer than 10 customers is too small for statistical significance. A cohort with 20-50 customers gives directional signals. Cohorts of 100+ customers give reliable data. For early-stage startups with small cohorts, present the data honestly and flag the small sample size.
Should cohorts be monthly or quarterly?
Monthly cohorts give more granular data and are better for identifying early-month churn patterns. Quarterly cohorts are more readable for investor presentations when there are many periods of data. Build monthly cohorts internally; present quarterly cohorts externally if monthly data is too granular for the audience.
How do you handle free trial conversions in cohort analysis?
Define the cohort start date as the first paid period, not the trial start. Free trials that convert in month one are in the same cohort as those that convert in month three if both are measured from their paid start date. If you want to analyse conversion from trial to paid, build a separate funnel analysis rather than including it in the retention cohort.
Summary
Cohort analysis reveals what aggregate metrics hide: when churn happens, whether newer cohorts retain better than older ones, and how revenue evolves over the lifetime of a customer relationship. The revenue retention cohort table --- showing each cohort's revenue as a percentage of its first-period revenue over time --- is the primary tool. A healthy cohort chart shows a decay that stabilises and then recovers above 100% (the smile curve). Build all three cohort analyses (revenue, logo, engagement), keep the data monthly internally, and present it in investor materials as evidence of the retention dynamics the financial model is based on.
Get the complete guide with all 16 chapters, exercises, and model templates.
Get Raise Ready - $9.99