customer churn analysis tutorial with sample data
most solopreneurs find out about churn the same way: they run a Stripe report at the end of the month, MRR went down, and they have no idea why. they cancel a few subscriptions, the numbers move, but they cannot tell you which segment is leaving, when it leaves, or what the typical lifetime is. without that, every retention experiment is a guess and pricing decisions are luck.
a real churn analysis takes one afternoon. you list every customer, mark whether they churned, segment by signup month and plan, and calculate the monthly churn rate for each cohort. that single table tells you whether retention is improving or decaying, which plan is hemorrhaging customers, and what your real average customer lifespan is.
this tutorial walks the entire process with a realistic 20-row sample dataset of customers from the last six months. by the end, you will have a working cohort retention table, a churn-by-segment pivot, and a clear answer to “should I focus on acquisition or retention next.” no amplitude, no chartmogul, just spreadsheets.
the sample dataset
below is the dataset we will use. paste into Google Sheets, save as churn-analysis-2026, and follow along.
| customer_id | signup_month | plan | mrr | churned | churn_month | reason |
|---|---|---|---|---|---|---|
| C001 | 2025-11 | starter | 19 | yes | 2026-01 | price |
| C002 | 2025-11 | pro | 49 | no | ||
| C003 | 2025-11 | starter | 19 | yes | 2025-12 | not used |
| C004 | 2025-11 | pro | 49 | no | ||
| C005 | 2025-11 | starter | 19 | yes | 2026-02 | found alternative |
| C006 | 2025-12 | pro | 49 | no | ||
| C007 | 2025-12 | starter | 19 | yes | 2026-01 | not used |
| C008 | 2025-12 | starter | 19 | yes | 2026-03 | price |
| C009 | 2025-12 | pro | 49 | no | ||
| C010 | 2025-12 | enterprise | 199 | no | ||
| C011 | 2026-01 | pro | 49 | no | ||
| C012 | 2026-01 | starter | 19 | yes | 2026-02 | not used |
| C013 | 2026-01 | starter | 19 | no | ||
| C014 | 2026-01 | enterprise | 199 | no | ||
| C015 | 2026-02 | pro | 49 | yes | 2026-04 | switched tools |
| C016 | 2026-02 | starter | 19 | yes | 2026-03 | not used |
| C017 | 2026-02 | pro | 49 | no | ||
| C018 | 2026-03 | enterprise | 199 | no | ||
| C019 | 2026-03 | starter | 19 | no | ||
| C020 | 2026-03 | pro | 49 | no |
a customer churn analysis is a structured measurement of which customers leave, when, why, and at what monthly rate. the standard solopreneur build is three views: monthly churn rate, churn by plan tier, and a cohort retention table by signup month. healthy SaaS churn is under 5% monthly for SMB and under 1% for mid-market. the analysis takes 30 minutes in Sheets and consistently surfaces a single segment-level fix worth a 20-40% retention lift before any new acquisition spend.
step 1: structure the master sheet
paste the data into A1:G21. add data validation to columns C (plan) and E (churned).
lock plan dropdown
select column C → Data → Data validation → list: starter, pro, enterprise.
lock churned dropdown
select column E → list: yes, no.
add helper column: months_active
formula in H2:
=IF(E2="no", DATEDIF(DATEVALUE(B2&"-01"), TODAY(), "M"), DATEDIF(DATEVALUE(B2&"-01"), DATEVALUE(F2&"-01"), "M"))
expected output for C001 (signed up 2025-11, churned 2026-01): 2. for C002 (still active): months from 2025-11 to today.
drag down to row 21.
common mistake: most solopreneurs calculate churn as “customers who left this month divided by customers at start of month” but forget that new signups during the month do not belong in the denominator. fix this by using the “customers at start of month” cohort only, not all active during the month.
step 2: calculate overall monthly churn
build a summary table below the data.
customers at start of each month
| month | active_at_start |
|---|---|
| 2025-12 | 5 |
| 2026-01 | 8 |
| 2026-02 | 11 |
| 2026-03 | 12 |
| 2026-04 | 14 |
formula approach:
=COUNTIFS(B$2:B$21, "<="&start_of_month, OR(F$2:F$21="", F$2:F$21>start_of_month))
simpler manual count works fine for 20 rows.
churned customers by month
| month | churned |
|---|---|
| 2025-12 | 1 |
| 2026-01 | 2 |
| 2026-02 | 2 |
| 2026-03 | 2 |
| 2026-04 | 1 |
formula:
=COUNTIF(F$2:F$21, "2026-01") for January.
monthly churn rate
| month | active_at_start | churned | churn_rate |
|---|---|---|---|
| 2025-12 | 5 | 1 | 20.0% |
| 2026-01 | 8 | 2 | 25.0% |
| 2026-02 | 11 | 2 | 18.2% |
| 2026-03 | 12 | 2 | 16.7% |
| 2026-04 | 14 | 1 | 7.1% |
20-25% monthly churn is catastrophic. this dataset is small enough that one customer leaves moves the rate by 8%. the trend is the signal: april is much better than january, suggesting whatever is driving churn is fading or being addressed.
step 3: segment churn by plan
build a pivot table.
- Rows:
plan - Values:
customer_id(COUNTA, total) - Values:
churnedfiltered to “yes” (COUNTIF) - Calculated field:
churn_rate= churned / total
expected output:
| plan | total | churned | churn_rate |
|---|---|---|---|
| starter | 9 | 6 | 66.7% |
| pro | 8 | 1 | 12.5% |
| enterprise | 3 | 0 | 0.0% |
starter customers churn at 66.7% over the observation window. pro at 12.5%. enterprise at 0%. this is the segment-level insight that gut feel misses. starter is not a tier, it is a leak.
three options follow from this:
1. raise the starter price to filter out customers who never use it
2. add an activation flow for starter that mirrors pro
3. drop the starter tier entirely
step 4: analyze churn reasons
pivot on the reason column.
| reason | count |
|---|---|
| not used | 4 |
| price | 2 |
| found alternative | 1 |
| switched tools | 1 |
“not used” dominates churn reasons. these are activation failures, not pricing or competition. the fix is onboarding, not discounts.
| churn type | what to fix |
|---|---|
| not used | onboarding, activation flow, first-week emails |
| price | pricing, packaging, value communication |
| found alternative | feature gap, positioning |
| switched tools | feature gap, integration |
| other | manual review |
half your retention work is making sure new customers reach value within their first 7 days.
step 5: build the cohort retention table
this is the hardest part and the highest-value output.
set up a table with signup_month as rows, months_since_signup as columns.
| signup_month | M0 | M1 | M2 | M3 | M4 | M5 |
|---|---|---|---|---|---|---|
| 2025-11 | 5 | 5 | 4 | 3 | 2 | 2 |
| 2025-12 | 5 | 5 | 4 | 4 | 3 | |
| 2026-01 | 4 | 4 | 3 | 3 | ||
| 2026-02 | 3 | 3 | 2 | |||
| 2026-03 | 3 | 3 |
each cell shows how many of that signup_month cohort were still active N months later.
convert to retention percent:
| signup_month | M0 | M1 | M2 | M3 | M4 | M5 |
|---|---|---|---|---|---|---|
| 2025-11 | 100% | 100% | 80% | 60% | 40% | 40% |
| 2025-12 | 100% | 100% | 80% | 80% | 60% | |
| 2026-01 | 100% | 100% | 75% | 75% | ||
| 2026-02 | 100% | 100% | 67% | |||
| 2026-03 | 100% | 100% |
read the table: retention typically holds at month 1, drops at month 2, then stabilizes. the M2 retention is your single best predictor of long-term LTV.
newer cohorts (2026-01, 02, 03) hold M2 retention better than the oldest cohort (2025-11). this means whatever you changed between November and January is working.
our cohort analysis for SaaS founders tutorial covers the cohort math in more depth.
step 6: calculate average customer lifespan
simple version:
average_lifespan_months = 1 / monthly_churn_rate
with overall churn at 17% (averaged across observation window):
average_lifespan = 1 / 0.17 = 5.9 months
at 5.9 months average lifespan and $40 average MRR, your average customer LTV is $236.
if you can move churn from 17% to 10%, lifespan jumps to 10 months and LTV becomes $400. that is a 70% LTV improvement from a 7-point churn improvement.
our customer lifetime value (LTV) calculation tutorial walks the LTV math in more detail.
comparing churn benchmarks by business type
| business type | healthy monthly churn | warning sign | red zone |
|---|---|---|---|
| SaaS SMB | 3-5% | 5-7% | 7%+ |
| SaaS mid-market | 1-2% | 2-3% | 3%+ |
| SaaS enterprise | 0.5-1% | 1-2% | 2%+ |
| consumer subscription | 5-10% | 10-15% | 15%+ |
| ecommerce subscription | 7-12% | 12-18% | 18%+ |
| info product subscription | 8-15% | 15-20% | 20%+ |
your benchmark depends on the segment, not just the product type. compare your starter tier to consumer benchmarks (5-10%), not enterprise.
frequently asked questions
what is the difference between customer churn and revenue churn?
customer churn counts logos. revenue churn counts dollars. enterprise customers leaving look small in customer churn but huge in revenue churn. always track both. revenue churn is more economically meaningful, customer churn is more directionally meaningful.
how many cohorts do I need before the table is useful?
minimum 6 cohorts (6 signup months) before the cohort table reveals trends. with fewer cohorts you can only spot extreme outliers.
should I include free trial users?
no. trial users have not committed yet. measure trial-to-paid conversion separately, then start the churn clock at first payment.
what counts as a churn?
cancellation, payment failure not recovered after 30 days, downgrade to free tier. some teams treat downgrades separately (“contraction”) which is a more sophisticated metric. start with cancellations only.
how often should I run this?
monthly for the rate calculation, quarterly for the cohort retention table. cohorts need at least 90 days of data to be meaningful.
conclusion: build the cohort table this week
churn analysis is one of the few SaaS workflows where the analysis itself usually surfaces the answer. you build the cohort retention table, you see month-2 is the cliff, and you immediately know the next 30 days of work is in onboarding.
start this week. dump every customer from your Stripe or billing system, mark churned vs active, signup month, plan, and reason if known. paste into the schema above. build the three pivots. the answer will be one of: starter is leaking, onboarding is the cliff, or a specific signup cohort is decaying. all three are fixable. all three pay for themselves inside one month of focused work.
for context on what to do with the answer, our marketing funnel analysis tutorial covers the activation step that often drives “not used” churn, and our customer lifetime value calculation tutorial connects retention to long-term economics. fix activation, then prove it with the next cohort.