Cohort Analysis: Read Retention Triangles Like a Pro

Why this matters

Averages lie about retention. Suppose your overall monthly churn is 5% and stable. That can hide two opposite stories. Old customers may be churning at 1% while every new cohort churns at 12%, and the base only looks stable because the old customers are a large share of it. When growth adds more new customers, churn will jump and nobody will understand why.

A cohort is a group of customers who started in the same period: signed up in March, first ordered in the Diwali week, converted to paid in Q2. Cohort analysis follows each group over time, so you compare customers at the same age. It is the only way to answer the questions that matter:

• Is retention improving for the customers we acquire now, compared with a year ago?
• Does our retention curve ever flatten, or does every cohort slowly go to zero?
• Is revenue from a cohort growing through expansion or repeat orders even as some customers leave?
• Did that pricing change, outage or channel shift hurt one group or everyone?

The LTV and payback numbers from the previous lesson depend on a churn or repeat assumption. Cohorts are how you check that assumption against reality.

The framework

The cohort table, or retention triangle

Each row is a cohort. Each column is the cohort’s age (month 0, month 1 and so on). Each cell is the share of the cohort still active at that age. Recent cohorts have fewer columns filled in, which gives the table its triangle shape.

Here is a sample logo retention table for a hypothetical B2B SaaS product. Month 0 is the month the customer started paying.

Read it in three directions

Rows (one cohort ageing). The January cohort drops from 100% to 61% in month 1, then 51%, 46%, 44%, 43%. The month-to-month loss shrinks from 39 points to 10, then 5, 2 and 1. The curve is flattening around the low 40s. That flattening point is your core of retained customers, and it is the strongest sign of product-market fit for that segment.

Columns (cohorts at the same age). Month-1 retention goes 61%, 60%, 58%, 52%, 49%. Something changed for the April and May cohorts. They are also the largest, which usually means a newly scaled channel is bringing in lower-intent customers. Old cohorts look fine, so a blended churn number would miss this for months.

Diagonals (same calendar month). Cells on a diagonal fall in the same calendar month for different cohorts. If April shows a dip in every cohort (January at M3, February at M2, March at M1), something happened in April: an outage, a price rise, a competitor launch. In the table above the diagonals look normal, which points the finger at acquisition rather than the product.

Logo cohorts vs revenue cohorts

Logo (or user) cohorts count people or accounts. They answer whether customers keep using or buying. They can only go down or stay flat, unless you count resurrected customers.

Revenue cohorts count money from the same group. They can go up over time, because staying customers add seats, move to bigger plans or order more. When a revenue cohort dips and then rises above its starting level, the line forms a smile curve.

Here is a hypothetical SaaS revenue cohort with seat-based pricing:

Early churn pulls revenue down to 92% by month 6. Then expansion in the surviving accounts lifts it to 104% by month 12. Logos may still be falling the whole time. That is why you always show logo and revenue cohorts together.

D2C brands see the same pattern as cumulative revenue. Warby Parker’s 2021 S-1 describes a Sales Retention Rate: a cohort’s later sales as a percentage of its first-purchase sales. For customers acquired between 2015 and 2019, the filing reports roughly 50% within 24 months of first purchase and nearly 100% over 48 months. In other words, a typical cohort’s repeat purchases over four years roughly matched what it spent on its first order.

See how the curve shape drives the base

Use the visualiser below. Start with 6% monthly churn and no floor, then set a floor of 35% and see how the customer base changes. A flattening curve turns acquisition into a growing base. A curve that never flattens caps the base, however much you spend.

Step-by-step playbook

1. Pick the cohort event and period. SaaS: first paid month. D2C: first delivered order month. Use monthly cohorts unless volume is very high (weekly) or very low (quarterly). Aim for at least 100 customers per cohort so percentages are stable.
2. Pick the activity definition. Logo retention for SaaS: still paying at month end. D2C: placed at least one order in that month, or cumulative repeat rate. Write the definition down.
3. Build the table. Use the spreadsheet method below or a SQL query. Grey out cells that are not complete yet.
4. Add revenue cohorts. Same rows and columns, but sum revenue instead of counting customers. For D2C, show cumulative revenue or contribution per original customer.
5. Read rows, columns and diagonals. Note where the curve flattens, whether newer cohorts are better or worse at the same age, and any calendar-month dips.
6. Segment. Repeat the table by acquisition channel, plan, first-order discount, and COD versus prepaid. The bottleneck is usually in one segment.
7. Feed the result back. Replace the churn assumption in your LTV model with mature-cohort churn. Set a retention target for a specific age, such as month-3 logo retention of 50% for new cohorts.

Worked example: build it in a spreadsheet

Start with an export of orders or payments: one row per transaction with customer ID, date and amount. Put customer ID in column A, date in column B and amount in column C.

1. Cohort month (column D). The first transaction month for that customer. =EOMONTH(MINIFS($B:$B, $A:$A, A2), -1) + 1
2. Activity month (column E). The month of this transaction. =EOMONTH(B2, -1) + 1
3. Age in months (column F). Months between cohort and activity. =DATEDIF(D2, E2, "M")
4. Pivot table. Rows = cohort month. Columns = age. Values = count of unique customer IDs (COUNTUNIQUE in Google Sheets pivot tables, or Distinct Count in Excel when you add the data to the data model). Make a second pivot with the sum of amount for revenue cohorts.
5. Convert to percentages. In a new grid, divide each cell by its month 0 cell, locking the column: =C5/$C5 copied across and down.
6. Add a heatmap. Apply a colour scale to the grid. The column and diagonal patterns jump out immediately.

Now read the result. Say the January cohort has 400 customers and 172 are active in month 5. 172 ÷ 400 = 43%. The May cohort has 700 customers and 343 active in month 1, which is 49%, against 61% for January at the same age. If May’s curve flattens at the same relative level as January’s, it settles around 43% × (49 ÷ 61), about 35%. That means roughly 245 long-term customers instead of 301 from the same 700 people (700 × 43%). Those 56 lost customers are what the channel shift is costing you, before you even count CAC.

B2B SaaS vs D2C: what changes

B2B SaaS. Retention is contractual, so logo churn shows up at renewal dates. Monthly plans churn smoothly, while annual plans show cliffs at months 12 and 24. Separate monthly and annual customers in different tables. Revenue cohorts matter as much as logo cohorts, because seat and usage expansion can produce net revenue retention above 100%.

D2C. Retention is non-contractual. A customer who does not order this month has not necessarily churned. Use cumulative repeat rate (share of the cohort with a second order by month N) and cumulative revenue or contribution per original customer. Align windows to the product’s natural repurchase cycle: 30 to 45 days for consumables like skincare or coffee, much longer for durables. Cut cohorts by first-order discount and by COD versus prepaid, since both change repeat behaviour.

How to measure it

Rules for clean cohorts:

• Exclude internal, test and free accounts from paid cohorts.
• Use the same timezone and month boundaries everywhere.
• Treat reactivated customers consistently. Either count them as retained or track them separately, but never mix the two between reports.
• Never compare incomplete cells. A cohort that started on 25 March has not had a full month 0.

Templates

Cohort review checklist for the monthly business review:

• Logo and revenue cohort tables are refreshed, with incomplete cells greyed out.
• Month-1 and month-3 retention for the three newest cohorts are compared with the same ages last year.
• The flattening point for mature cohorts is noted, and the LTV model uses it.
• Any diagonal dip is explained with a dated event.
• Retention is split by top two acquisition channels and, for D2C, by COD versus prepaid.
• One action is assigned for the weakest segment.