Level 6 · A cohort retention grid

The month-1 retention you computed in the window functions boss is one column of a bigger picture. A retention grid shows, for each cohort, how many customers were active 0, 1, 2, 3 months after their first order. Reading down a column compares cohorts; reading across a row shows how one cohort decays.

Month offsets

The grid’s key column is the offset: how many calendar months separate an active month from the cohort month. DATEDIFF('month', start, end) counts month boundaries crossed, so DATEDIFF('month', DATE '2025-01-01', DATE '2025-03-01') is 2. Because both dates are first-of-month, that is exactly “two months later”.

DATEDIFF('month', cohort_month, order_month) AS month_offset

Offset 0 is the cohort month itself. Every cohort customer is active there by definition, which makes the offset-0 count the cohort size.

Long format first

SQL is good at long tables (one row per cohort and offset) and awkward at wide ones. Produce the long grid, then pivot it in a spreadsheet or BI tool. If you must pivot in SQL, conditional aggregation does it: COUNT(*) FILTER (WHERE month_offset = 1) AS m1.

Worked example: the first two 2025 cohorts

cohort_month	month_offset	active_customers	retention_pct
2025-01-01	0	37	100.0
2025-01-01	1	4	10.8
2025-01-01	2	3	8.1
2025-01-01	3	4	10.8
2025-02-01	0	28	100.0
2025-02-01	3	7	25.0

Two things to notice. Retention can go up between offsets: this grid asks “active in that month”, not “came back at least once by then”, so a customer can skip month 2 and return in month 3. And these cohorts are small (28 to 52 customers in the first half of 2025, because most Harbor & Pine customers placed their first order earlier), so one customer moves a cell by 2 to 4 points.

Choosing the cohort range

The June 2025 cohort’s offset 3 is September 2025, which is complete. A cohort from November 2025 has no month 3 yet; including it would show empty or misleadingly low cells. Only show offsets that have fully happened.

Common mistakes

• Counting orders, not customers. Deduplicate to customer-month first.
• Filtering before the cohort window. Find the first order on all history, then filter cohorts.
• Missing zeros. A cohort with no active customers at some offset has no row at all. For a full grid, cross join cohorts with offsets 0 to 3 and LEFT JOIN the counts.

In SQL Server

DATEDIFF(month, cohort_month, order_month) counts month boundaries exactly like DuckDB. Build month starts with DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1), and next month with DATEADD(month, 1, cohort_month).