Cohort retention analysis: a step-by-step tutorial with formulas and example data

TL;DR. Cohort retention is the most honest retention metric a business can compute. It groups customers by when they were acquired and tracks what percentage of each group remains active in each subsequent period. The output is a triangular table that reveals whether retention is improving over time — something no average can show. This tutorial walks the exact steps to build one from raw data, how to read it, and the three patterns that should trigger immediate action.

Why averages always lie

If you compute 'retention rate this month' as (active customers this month) ÷ (active customers last month), you get a number that mixes 8-year-old customers with brand-new ones. New customers churn fast; old customers are sticky. As your business grows and adds new customers, the average drops — not because retention got worse, but because the mix changed.

Cohort analysis fixes this by separating the cohorts. Each acquisition month gets its own row. Within each row, you track that group's survival over time. Now you can compare apples to apples: did customers acquired in January retain better at month 6 than customers acquired in February at month 6?

The data you need

You need three columns per transaction:

- Customer ID (a stable identifier across orders/sessions)

- Acquisition date (the date of their FIRST order/signup — not the date of the current transaction)

- Activity date (the date of each transaction or active session)

If your raw data only has transaction dates, derive acquisition date by taking MIN(transaction_date) per customer. Most analytics tools and warehouses make this a one-line SQL query.

Step 1: bucket by acquisition month

Group customers by the month of their first order. '2026-01 cohort' is everyone whose first purchase was in January 2026. '2026-02 cohort' is everyone whose first purchase was in February.

Count the size of each cohort. This becomes the denominator for that row.

Step 2: count active customers per cohort per month

For each cohort, count how many customers from that cohort were active in each subsequent month. 'Active' depends on your business: a subscription company might define it as 'paying'; an ecommerce company as 'placed an order'; a SaaS app as 'logged in'.

Critical: count customers, not transactions. A customer who placed 5 orders in March still counts as 1 active customer that month.

Step 3: divide and build the triangle

For each cohort row, divide the active count for each month by the cohort size. Express as a percentage. You now have a cohort retention table:

Example:

```

Cohort M0 M1 M2 M3 M4 M5

2025-09 100% 62% 48% 41% 38% 36%

2025-10 100% 65% 51% 44% 41% —

2025-11 100% 68% 54% 46% — —

2025-12 100% 71% 57% — — —

2026-01 100% 74% — — — —

2026-02 100% — — — — —

```

M0 is always 100% (cohort definition). The triangle is incomplete on the right because newer cohorts haven't aged yet.

How to read the triangle

Diagonally (top-left to bottom-right): product health. Pick a fixed age — say M3. Read it down the column. In the example, M3 went 41% → 44% → 46% → 47% over four cohorts. Retention is improving cohort over cohort. Something is working: better onboarding, better-fit acquisition, better product.

Vertically (down a column): isolates the impact of changes you made. If you shipped a new onboarding flow in October, compare September's M1 (62%) with October's M1 (65%) and November's M1 (68%). The improvement is real and isolated from cohort-age confusion.

Horizontally (across a row): the customer lifetime curve for that cohort. The shape tells you when churn happens. Steep early drop with shallow tail = product-market fit problem (everyone bounces fast, the survivors love it). Gradual decline = engagement decay. Cliff at a specific month = a structural event (annual renewal date, free trial ending, contract milestone).

Three patterns that demand action

Pattern 1: each new cohort retains worse than the prior. In the example, this would look like M1 going 65% → 62% → 58%. This is the most dangerous pattern. Your acquisition is degrading (worse-fit customers), your product is degrading, or both. Acquisition spend is becoming less efficient. Fix this before any growth investment.

Pattern 2: M1 retention is below 40% in subscription. No CAC payback math will ever work. You're paying to acquire customers who don't come back. Stop acquisition, fix retention.

Pattern 3: long-tail retention plateau is below 20%. Your product doesn't have lasting value. The customers who stay aren't becoming a sustainable base. You're running a one-night-stand business and have to keep refilling the top of the funnel forever.

What to do once you have the triangle

1. Compute it monthly. The trend in the diagonal is the most important signal in your business.

2. Segment cohorts by acquisition channel. Often paid-acquired cohorts retain dramatically worse than organic — you can't see this without segmentation.

3. Use the M3 or M6 column as your North Star for product/onboarding experiments. Don't use M1 — it's too noisy and reflects honeymoon effects.

4. Pair the triangle with revenue cohort analysis: same structure, but using ARR or order value instead of active customers. This reveals whether your retained customers are also spending more.

Once you can read a cohort triangle, you'll never trust an average retention number again. See also: NRR vs GRR explained, customer retention vs acquisition cost, and how to calculate customer retention rate.