Cohort Retention Analysis: A Step-by-Step Guide

Cohort retention analysis groups customers by when they started, usually the month of their first purchase or sign-up, and tracks what share of each group is still active in every following period. Laid out as a table, it shows you when customers drop off (in the first month, after a trial, after a year) and whether customers who joined recently stay longer than those who joined earlier. That makes it the best way to see whether changes to onboarding, pricing or service are actually working.

This guide shows you how to build a cohort table in a spreadsheet step by step, how to read it, and what to do with what you find.

Why a single churn rate is not enough

A churn rate gives you one number for all customers in a period. It is useful, but it blends very different groups together: customers in their first month, who often leave at higher rates, and loyal customers of several years, who rarely leave.

Imagine a business whose overall monthly churn has been flat at 4% for a year. A cohort table might reveal that:

• Customers who joined recently are actually staying longer than before, thanks to better onboarding.
• But a large cohort from a discount campaign is leaving quickly, pulling the average up.

The single number hides both stories. The cohort table shows them.

Step 1: Choose what "active" means

Decide what counts as being active in a period:

Business type	Active in a period means…
Subscription	Paid subscription during the month
Software	Logged in or used a key feature during the month
Online shop	Placed at least one order in the period (consider quarterly periods if customers buy infrequently)
Services	Had a project or invoice in the period

For shops with irregular purchases, monthly "active" will look jumpy, because customers who buy every few months appear and disappear. Quarterly periods, or a definition such as "ordered in the last 90 days", give a smoother picture.

Step 2: Export the raw data

You need one row per activity (order, payment or login) with at least:

customer_id	activity_date
C-1001	2026-01-14
C-1001	2026-02-20
C-1002	2026-01-22
C-1003	2026-02-03

Most shop, billing and CRM systems can export this as CSV. Remove test accounts and internal users before you start.

Step 3: Add the helper columns

In your spreadsheet, add three columns:

1. Activity month: the month of each activity, for example with a formula that turns the date into the first day of its month.
2. Cohort month: the earliest activity month for that customer. In most spreadsheet tools you can use a minimum-if formula over the customer ID column.
3. Period number: the number of months between cohort month and activity month. The first month is period 0.

After this step, each row tells you: this customer, who started in cohort month X, was active in period N.

Step 4: Build the cohort table

Create a pivot table:

• Rows: cohort month
• Columns: period number
• Values: count of unique customer IDs

Use unique counts. A customer with three orders in one month should count once.

The result is a triangle of counts:

Cohort	Period 0	1	2	3	4	5
Jan	200	150	128	116	108	102
Feb	180	140	122	112	105
Mar	220	176	158	147
Apr	210	172	156
May	240	202
Jun	230

The triangle shape is normal: newer cohorts have had fewer periods to be observed.

Step 5: Convert counts to percentages

Divide every cell by the cohort's period 0 value. This makes cohorts of different sizes comparable.

Cohort	Period 0	1	2	3	4	5
Jan	100%	75.0%	64.0%	58.0%	54.0%	51.0%
Feb	100%	77.8%	67.8%	62.2%	58.3%
Mar	100%	80.0%	71.8%	66.8%
Apr	100%	81.9%	74.3%
May	100%	84.2%
Jun	100%

If you have the counts for one cohort, the churn rate calculator has a cohort mode: paste the numbers and it returns retention per period and period-over-period churn.

Step 6: Read the table

Look in three directions.

Along a row: how one cohort decays

The January cohort loses 25% of customers between period 0 and 1, then 14.7% of the remaining customers between periods 1 and 2 (from 150 to 128), and smaller shares after that. This shape, a steep early drop followed by flattening, is typical. The point where the curve flattens shows when customers who stay have "settled in".

Down a column: are newer cohorts better?

Period 1 retention rises from 75.0% (Jan) to 84.2% (May). If you changed your onboarding in March, this is the evidence that it worked. Read each column from top to bottom to compare cohorts at the same age.

Along the diagonals: calendar effects

Cells on the same diagonal fall in the same calendar month. If one diagonal is unusually weak across all cohorts, something happened in that month that affected everyone: a price change, a delivery problem, a seasonal effect.

Step 7: Turn patterns into actions

Pattern	Likely cause	What to try
Steep drop in period 1	Customers do not reach value quickly	Improve onboarding; see the customer onboarding checklist
One cohort much worse	Acquisition source or offer attracted poor-fit customers	Check where that cohort came from; adjust targeting or offer
Retention keeps falling, never flattens	Ongoing value problem	Talk to long-term customers who left; review product or service
Sudden dip on a diagonal	Event affecting all customers	Check what happened that month
Newer cohorts better at the same age	Recent changes are working	Keep going; document what changed

Worked example: measuring an onboarding change

Say a subscription business introduced a welcome call for new customers starting in March. Before the change, period 1 retention averaged (75.0 + 77.8) ÷ 2 ≈ 76.4% for January and February. After the change, it averaged (80.0 + 81.9 + 84.2) ÷ 3 ≈ 82.0% for March to May.

That is about 5.6 percentage points more customers still active after one month. For a cohort of 220 customers, that is roughly 12 extra customers per month who stay past the first month. Multiply by their customer lifetime value to see what the welcome calls are worth.

A caution: cohorts this size can fluctuate by a few points for random reasons, and other things may have changed at the same time. Treat a consistent improvement over several cohorts as stronger evidence than a single good month.

Common mistakes

• Counting orders, not customers. Use unique customer counts per period.
• Uneven period lengths. Calendar months vary slightly in length; that is usually fine. Mixing weeks and months is not.
• Cohorts too small. If a cohort has 15 customers, one departure moves retention by almost 7 points. Use quarterly cohorts if needed.
• Forgetting reactivations. In shops, a customer can be inactive in period 2 and active again in period 3. That is fine; just be aware that retention can rise along a row.
• Analysing once and never again. A cohort table is most useful when updated monthly, so each new cohort can be compared.

Your next step

Export twelve months of orders or payments with customer ID and date, build the cohort table following steps 3 to 5, and look at the period 1 column. If it is the steepest drop in your table, early experience is where your next retention effort belongs. For more ideas on what to do then, see our customer retention strategies.