cohort retention curve tutorial: real walkthrough
most solopreneurs know they should be running cohort retention but think it requires a paid analytics tool. they look at Mixpanel, see the cohort builder, panic at the data engineering, and go back to total user counts. meanwhile, the question they actually need answered (does retention improve over time as the product matures) sits in their billing data, unanswered.
a retention curve is one of the highest-leverage charts in any subscription business. it is also one of the easiest to build. you list every customer, mark which signup month they joined, count how many were still active 1, 2, 3, 4 months later, and divide. that gives you a curve per cohort. comparing curves answers the only question that matters: is the new cohort retaining better than the old one.
this tutorial walks the entire build with a realistic 22-row sample dataset of customers from the last 7 months. by the end, you will have a working cohort table, a stacked retention curve chart, and a clear answer about whether your product is improving. no Mixpanel, no Amplitude, just spreadsheets and 30 minutes.
the sample dataset
below is the dataset we will use. paste into Google Sheets, save as cohort-retention-2026, and follow along.
| customer_id | signup_month | last_active_month | churned |
|---|---|---|---|
| C001 | 2025-10 | 2026-01 | yes |
| C002 | 2025-10 | 2026-04 | no |
| C003 | 2025-10 | 2025-11 | yes |
| C004 | 2025-10 | 2026-04 | no |
| C005 | 2025-10 | 2026-02 | yes |
| C006 | 2025-11 | 2026-04 | no |
| C007 | 2025-11 | 2025-12 | yes |
| C008 | 2025-11 | 2026-04 | no |
| C009 | 2025-11 | 2026-04 | no |
| C010 | 2025-12 | 2026-04 | no |
| C011 | 2025-12 | 2026-02 | yes |
| C012 | 2025-12 | 2026-04 | no |
| C013 | 2025-12 | 2026-04 | no |
| C014 | 2026-01 | 2026-04 | no |
| C015 | 2026-01 | 2026-04 | no |
| C016 | 2026-01 | 2026-02 | yes |
| C017 | 2026-01 | 2026-04 | no |
| C018 | 2026-02 | 2026-04 | no |
| C019 | 2026-02 | 2026-04 | no |
| C020 | 2026-03 | 2026-04 | no |
| C021 | 2026-03 | 2026-04 | no |
| C022 | 2026-04 | 2026-04 | no |
a cohort retention curve plots how many customers from each signup month are still active N months later. it is the canonical chart for subscription businesses because it separates “we are growing” from “we are retaining.” healthy SaaS curves flatten after month 3; declining curves indicate a structural product problem. the standard build takes 30 minutes in Sheets, requires only signup_month and last_active_month per customer, and consistently surfaces whether product changes are improving long-term retention before total revenue numbers reveal it.
step 1: structure the data
paste into A1:D23. add data validation on column D (churned): list yes, no.
add helper column: months_active
formula in column E:
=DATEDIF(DATEVALUE(B2&"-01"), DATEVALUE(C2&"-01"), "M")+1
expected output for C001 (joined 2025-10, last active 2026-01): 4. each customer has been active for 4 months.
drag down to row 23.
common mistake: most solopreneurs build the cohort table by counting “active customers” without normalizing for cohort size. the result looks like all cohorts are the same size, hiding which is actually retaining. always express cohort retention as a percentage of the cohort’s M0 size.
step 2: build the cohort count table
list signup months in column G. list months 0 through 6 across row 1 (G1:N1).
populate cohort counts
for each cell, count customers from that signup_month who were still active N months later.
formula for cell H2 (cohort 2025-10, M0):
=COUNTIFS(B$2:B$23, "2025-10", E$2:E$23, ">=1")
expected output: 5 (5 customers signed up in October).
formula for I2 (cohort 2025-10, M1):
=COUNTIFS(B$2:B$23, "2025-10", E$2:E$23, ">=2")
expected output: 5 (all 5 still active 1 month later).
continue for M2, M3, M4, M5, M6:
| cohort | M0 | M1 | M2 | M3 | M4 | M5 | M6 |
|---|---|---|---|---|---|---|---|
| 2025-10 | 5 | 5 | 4 | 3 | 2 | 2 | 2 |
| 2025-11 | 4 | 4 | 3 | 3 | 3 | 3 | |
| 2025-12 | 4 | 4 | 4 | 3 | 3 | ||
| 2026-01 | 4 | 4 | 3 | 3 | |||
| 2026-02 | 2 | 2 | 2 | ||||
| 2026-03 | 2 | 2 | |||||
| 2026-04 | 1 |
empty cells are cohorts that have not aged that long yet. do not interpret blanks as zero.
step 3: convert to retention percentages
create a parallel table below.
formula for retention cell:
=I2/H2
format as percent.
| cohort | M0 | M1 | M2 | M3 | M4 | M5 | M6 |
|---|---|---|---|---|---|---|---|
| 2025-10 | 100% | 100% | 80% | 60% | 40% | 40% | 40% |
| 2025-11 | 100% | 100% | 75% | 75% | 75% | 75% | |
| 2025-12 | 100% | 100% | 100% | 75% | 75% | ||
| 2026-01 | 100% | 100% | 75% | 75% | |||
| 2026-02 | 100% | 100% | 100% | ||||
| 2026-03 | 100% | 100% | |||||
| 2026-04 | 100% |
now patterns are visible. the october 2025 cohort dropped to 40% by month 4. the december 2025 cohort still holds 75% at month 4. that is a 35-point retention improvement.
something changed between october and december. that is a finding you can investigate.
step 4: calculate the average retention curve
below the cohort table, average each column.
formula for M0 average:
=AVERAGE(IF(H2:H8>0, H2:H8/H2:H8)) (entered as array)
simpler version: average the percentage row directly.
| month | avg_retention |
|---|---|
| M0 | 100% |
| M1 | 100% |
| M2 | 86% |
| M3 | 71% |
| M4 | 63% |
| M5 | 58% |
| M6 | 40% |
this is your single retention curve, averaged across cohorts. for a quick LTV estimate, sum the averages × ARPU.
step 5: chart the cohort curves
select the percentage table → Insert → Chart → line chart.
- X axis: M0 through M6
- one line per cohort
expected result: 7 lines, all starting at 100% in M0, fanning out by month 4.
look at the shape:
– if newer cohorts (2026-01, 02, 03) are higher than older cohorts at the same month, retention is improving
– if newer cohorts are lower, retention is decaying
– if all cohorts overlap, you are at steady state
in our sample data, the newer cohorts (2025-12 and later) sit above the october cohort at month 3 and 4. that is the visual evidence of retention improvement.
step 6: identify the cliff
the cliff is the month where the largest single drop happens. find it by row.
| transition | avg_drop |
|---|---|
| M0 to M1 | 0 pts |
| M1 to M2 | 14 pts |
| M2 to M3 | 15 pts |
| M3 to M4 | 8 pts |
| M4 to M5 | 5 pts |
| M5 to M6 | 18 pts |
biggest drops are M1 to M2 and M5 to M6. the M1 to M2 cliff is typical SaaS behavior (post-honeymoon churn). the M5 to M6 cliff is unusual and worth investigating; often indicates an annual contract expiration or a recurring billing failure.
step 7: project forward (LTV input)
retention curves let you estimate LTV without waiting for the full 12-month observation.
simplest projection method: assume retention stabilizes at the latest observed value.
if M6 retention is 40%, project M7 through M24 at 40% (or apply a small monthly decay like 2%).
| month | projected_retention | revenue_contribution (at $50 ARPU) |
|---|---|---|
| M0 | 100% | $50 |
| M1 | 100% | $50 |
| M2 | 86% | $43 |
| M3 | 71% | $36 |
| M4 | 63% | $32 |
| M5 | 58% | $29 |
| M6 | 40% | $20 |
| M7 to M24 | 40% (flat) | $20 × 18 = $360 |
| total LTV | $620 |
this LTV is more conservative than the marketing-blog formula and far more accurate. our customer lifetime value calculation tutorial covers the full LTV math.
comparing cohort analysis approaches
| method | what it shows | best for |
|---|---|---|
| customer count cohort | absolute headcount per cohort | detecting cohort size changes |
| customer percent cohort | retention rate per cohort | comparing cohorts apples-to-apples |
| revenue cohort | total revenue from each cohort | financial planning |
| revenue percent cohort | net revenue retention rate | expansion analysis |
solopreneurs should use percent cohort first. add revenue cohort once you have 6+ months of data.
our customer churn analysis tutorial covers the churn-rate side of the same retention question, and our cohort analysis for SaaS founders tutorial walks deeper into the math.
frequently asked questions
how many cohorts do I need before the curve is meaningful?
minimum 4 cohorts with at least 3 months observation each. 6+ cohorts with 6+ months of observation is ideal. fewer than 4 cohorts and you are reading noise.
what counts as “active”?
depends on your product. for SaaS, “logged in this month” or “paid this month.” for content, “engaged with email this month.” for ecommerce subscriptions, “received and did not refund this month’s box.” pick one definition and stick to it.
should I use weekly or monthly cohorts?
monthly for businesses below 500 customers. weekly for businesses above 1,000 customers. weekly cohorts are noisier and require more data to interpret.
what about expansion revenue?
build a parallel revenue cohort table. shows whether customers grow or shrink in the cohort over time. expansion-positive cohorts are gold.
how often should I run this?
monthly. each new month adds one column to existing cohorts and one new cohort row. 5-minute refresh once the template exists.
the M0 retention is always 100%. is that wrong?
no. M0 is by definition all customers in the cohort. the curve only tells you something at M1 and beyond.
conclusion: build the curve this week
cohort retention is one of the few analyses where the chart itself answers the question. you do not need to interpret it, you just need to build it. once it is on the page, the trend is obvious within 30 seconds.
start this week. dump every customer’s signup_month and last_active_month from your billing or product analytics tool. paste into the schema above. build the cohort count table, convert to percentages, chart the curves. you will see one of three things: improving retention (keep going), decaying retention (something is broken), or steady state (you have plateaued).
the answer changes what you work on next month. that is the highest-leverage 30 minutes of analysis a subscription solopreneur can run.
for next steps, our customer lifetime value calculation tutorial plugs the curve into LTV math, and our marketing funnel analysis tutorial connects the top of funnel to retention metrics. measure the curve, then improve M2.