Cohort Analysis Explained: A Worked Customer Retention Example
Cohort Analysis Explained: A Worked Customer Retention Example
Cohort analysis groups customers by a shared starting event, usually the month they signed up or first bought, and tracks how each group behaves over time. Instead of one blended retention number, you get a table showing how every cohort retains in month 1, month 2, month 3 and beyond. That reveals whether retention is genuinely improving or just being masked by growth.
Cohort analysis is one of the most useful techniques a business or product analyst can learn. It answers questions that a single dashboard number can't: Are customers who joined after the redesign sticking around longer? Did the discount campaign bring in loyal buyers or one-time bargain hunters? Is churn getting better, or are we just acquiring more people?
Why Cohorts Beat a Single Retention Number
Imagine your app reports "48% of users are active one month after signing up." That sounds stable. But that number blends together users from very different periods. If newer users retain much better than older ones, or much worse, the average hides it.
Cohort analysis fixes this by comparing like with like: users who started in the same period, measured at the same age.
Question
Single metric
Cohort view
Is retention improving?
Can't tell: mixes old and new users
Compare month-1 retention across cohorts
Did a product change help?
Diluted by everyone else
Look at cohorts that started after the change
When do most users drop off?
Hidden
Visible as the steepest step in each row
Is growth hiding churn?
Yes, often
No: each cohort is tracked on its own
The Worked Example
A subscription app tracks how many users from each monthly signup cohort are still active in later months.
Step 1: Count active users by cohort and month since signup.
Signup cohort
Users
Month 0
Month 1
Month 2
Month 3
Month 4
January
1,000
1,000
420
350
310
290
February
1,200
1,200
540
450
400
March
900
900
450
396
April
1,100
1,100
605
Step 2: Convert to retention rates by dividing each cell by the cohort's starting size.
Signup cohort
Month 0
Month 1
Month 2
Month 3
Month 4
January
100%
42.0%
35.0%
31.0%
29.0%
February
100%
45.0%
37.5%
33.3%
March
100%
50.0%
44.0%
April
100%
55.0%
This triangular table is the classic cohort retention table. Newer cohorts have fewer columns simply because less time has passed.
Step 3: Read it in three directions.
Across a row: how one cohort decays over time. January loses 58% of users in the first month, then the curve flattens. The users who survive month 1 are much more likely to stay. That flattening is a sign of a real, loyal core.
Down a column: how cohorts compare at the same age. Month-1 retention rose steadily: 42% → 45% → 50% → 55%. Something improved for newer users, perhaps onboarding, product quality or acquisition channels.
Along a diagonal: cells on the same diagonal happened in the same calendar month. If every cohort dips on one diagonal, look for a calendar event such as an outage, a price change or a holiday.
Step 4: Compare with the blended number. Across all four cohorts, month-1 retention is (420 + 540 + 450 + 605) ÷ (1,000 + 1,200 + 900 + 1,100) ≈ 48%. That single number hides the most important finding in the table: retention is improving month after month.
How to Build a Cohort Table
You need just two pieces of information per customer: the cohort date (first purchase or signup) and the activity dates after that.
In SQL, the pattern is:
Find each customer's first activity month, often with MIN() or a window function.
Join that back to their activity and calculate months since cohort start.
Count distinct customers by cohort and month offset.
Divide by the cohort's month-0 count.
In pandas, the same logic is a groupby on cohort month and month offset, followed by pivot_table and division by the first column. See Pandas for Beginners for the core operations.
In Excel or BI tools, a PivotTable with cohort month in rows, month offset in columns and distinct customers as values gets you there, followed by a calculated percentage.
Choosing the Right Cohort and Metric
Cohort analysis is flexible. The design choices matter more than the tool.
Choice
Common options
Tip
Cohort definition
Signup month, first purchase, first use of a feature, acquisition channel
Match it to the decision. Evaluating a campaign? Cohort by campaign.
Time grain
Day, week, month
Daily for apps, monthly for subscriptions, quarterly for B2B
Metric
Active users, retained subscribers, revenue, orders
Revenue cohorts show whether customers grow their spending over time
"Active" definition
Logged in, purchased, used a core feature
Use the action that reflects real value, not just opening the app
Revenue cohorts deserve a special mention. Because some customers expand their spending, revenue retention can exceed 100%. That's the idea behind net revenue retention, a key SaaS metric.
Common Mistakes
Comparing cohorts at different ages. January at month 4 isn't comparable with April at month 1. Compare down columns, not across the whole table.
Trusting tiny cohorts. A cohort of 40 users can swing wildly. Show cohort sizes next to the percentages.
A vague definition of "active". If one team counts logins and another counts purchases, the same table tells two different stories. Write the definition down, as you would for any KPI.
Reading causation into a column trend. Rising month-1 retention shows that something changed, not what. A new channel, seasonality or a pricing change could all explain it. Treat it as a hypothesis, as discussed in Correlation vs Causation.
Ignoring the right edge. The most recent cohorts have the least data. Don't declare victory from one strong month.
Where Cohort Analysis Shows Up in Interviews
Analyst and product-analytics interviews often include a case such as "Retention dropped last quarter. How would you investigate?" A strong answer mentions cohort analysis early: split users by signup period, compare retention at the same age, then segment by channel or platform. You may also be asked to write the SQL. Practice the first-activity-month pattern until it's second nature. A cohort analysis on a public e-commerce dataset also makes an excellent portfolio project.
What is the difference between cohort analysis and segmentation?
Segmentation groups customers by attributes such as region, plan or age. Cohort analysis groups them by when a shared event happened and tracks them over time. You can combine both, for example monthly cohorts split by acquisition channel.
What is a good retention rate?
It depends heavily on the industry and product. Consumer apps often lose most users in the first weeks, while B2B software retains far more. Benchmarks are less useful than your own trend across cohorts.
How many cohorts do I need?
Enough to see a pattern: usually at least six to twelve periods. Make sure each cohort is large enough that one or two customers don't swing the percentages.
Can I do cohort analysis in Google Analytics or Power BI?
Yes. Google Analytics 4 includes a cohort exploration report, and Power BI or Tableau can build cohort tables from transaction data with a cohort column and a month-offset column.
Bottom Line
A single retention number blends very different customers together. Cohort analysis separates them by start date and compares them at the same age, so you can see whether retention is really improving, when users drop off and which changes made a difference. Build the table, read it across rows, down columns and along diagonals, and treat what you find as a hypothesis to test.
