Cohort retention in SQL, without the spreadsheet

Retention usually arrives as a spreadsheet. Someone exports a user table, someone else writes a month formula down column C, and the triangle in the middle of the sheet becomes the number the company plans against. Then the export changes shape, the formula silently stops filling down, and nobody can say which definition produced last quarter’s chart. Retention belongs in SQL, where the definition is written down once and the numbers rebuild themselves.

The examples below are Postgres, and they run unchanged in DuckDB against the sample data — an events table of 40,000 rows you can query straight from the web. BigQuery needs DATE_TRUNC(d, MONTH) and DATE_DIFF(a, b, MONTH) instead of the Postgres date and time functions used here, but the shape does not change.

Pin every user to exactly one cohort

A cohort is a group of users who started at the same time. “Started” has to mean one specific event — first order, first paid invoice, first login after signup — and the choice changes the whole table. Signup-date cohorts measure your marketing. First-order cohorts measure your product. Pick one, write it in the query, and say which one you picked whenever you show the chart.

with first_seen as (
  select
    customer_id,
    date_trunc('month', min(occurred_at))::date as cohort_month
  from events
  where event_type = 'login'
  group by customer_id
),
customer_periods as (
  select distinct
    e.customer_id,
    f.cohort_month,
    (date_part('year',  age(date_trunc('month', e.occurred_at),
                            f.cohort_month)) * 12
     + date_part('month', age(date_trunc('month', e.occurred_at),
                              f.cohort_month)))::int as period
  from events e
  join first_seen f using (customer_id)
  where e.event_type = 'login'
)
select * from customer_periods;

Two details do real work here. The cohort comes from min(occurred_at) over the customer’s whole history, not from a signed_up_at column that someone may have backfilled. And the period is a whole number of months between two truncated months, not occurred_at - first_seen_at in days divided by 30 — day arithmetic puts a customer who acted on the 1st and the 29th into different periods for no reason anyone can explain.

Generate the period spine

The natural next step is group by cohort_month, period, and it is wrong. A group-by only produces rows for combinations that exist in the data. A cohort that went dark in month 3 and came back in month 5 produces no row for month 4 at all, so the chart draws a straight line across the gap instead of a dip to zero. Worse, the reader cannot tell a missing month from a month nobody has reached yet.

Build the grid first, then join the counts onto it.

-- first_seen and customer_periods repeated from the block above, so this
-- query runs on its own.
with first_seen as (
  select customer_id,
         date_trunc('month', min(occurred_at))::date as cohort_month
  from events where event_type = 'login' group by customer_id
),
customer_periods as (
  select distinct e.customer_id, f.cohort_month,
    (date_part('year',  age(date_trunc('month', e.occurred_at),
                            f.cohort_month)) * 12
     + date_part('month', age(date_trunc('month', e.occurred_at),
                              f.cohort_month)))::int as period
  from events e join first_seen f using (customer_id)
  where e.event_type = 'login'
),
cohort_sizes as (
  select cohort_month, count(*) as cohort_customers
  from first_seen
  group by cohort_month
),
counts as (
  select cohort_month, period, count(*) as active_customers
  from customer_periods
  group by cohort_month, period
),
spine as (
  select c.cohort_month, c.cohort_customers, p.period
  from cohort_sizes c
  cross join lateral generate_series(
    0,
    (date_part('year',  age(date_trunc('month', current_date),
                            c.cohort_month)) * 12
     + date_part('month', age(date_trunc('month', current_date),
                              c.cohort_month)))::int
  ) as p(period)
)
select
  s.cohort_month,
  s.period,
  s.cohort_customers,
  coalesce(x.active_customers, 0) as active_customers,
  round(coalesce(x.active_customers, 0)::numeric
          / s.cohort_customers, 3) as retention
from spine s
left join counts x using (cohort_month, period)
order by s.cohort_month, s.period;

generate_series per cohort gives each cohort exactly as many period rows as it has had time to live. The left join plus coalesce turns silence into a zero, which is what a dead month actually is.

Active in this period, or still active

The query above answers “did this customer do the thing in month N”. That is the standard definition and it bounces: a customer who logs in during months 0, 1, 3 is counted as churned in month 2 and resurrected in month 3. For a subscription business that is misleading, because the subscription never stopped.

The other definition — still active as of month N, meaning the customer has any activity in month N or later — needs their last period, which is one aggregate away, or one line of the window functions worth knowing if you want it alongside the raw rows.

-- Preamble repeated again, plus the spine without the cohort sizes this
-- query does not need.
with first_seen as (
  select customer_id,
         date_trunc('month', min(occurred_at))::date as cohort_month
  from events where event_type = 'login' group by customer_id
),
customer_periods as (
  select distinct e.customer_id, f.cohort_month,
    (date_part('year',  age(date_trunc('month', e.occurred_at),
                            f.cohort_month)) * 12
     + date_part('month', age(date_trunc('month', e.occurred_at),
                              f.cohort_month)))::int as period
  from events e join first_seen f using (customer_id)
  where e.event_type = 'login'
),
spine as (
  select c.cohort_month, p.period
  from (select distinct cohort_month from first_seen) c
  cross join lateral generate_series(
    0,
    (date_part('year',  age(date_trunc('month', current_date),
                            c.cohort_month)) * 12
     + date_part('month', age(date_trunc('month', current_date),
                              c.cohort_month)))::int
  ) as p(period)
),
last_period as (
  select customer_id, cohort_month, max(period) as last_period
  from customer_periods
  group by customer_id, cohort_month
)
select
  s.cohort_month,
  s.period,
  count(l.customer_id) filter (where l.last_period >= s.period)
    as still_active
from spine s
left join last_period l using (cohort_month)
group by s.cohort_month, s.period
order by 1, 2;

This curve only ever goes down. The per-period curve wobbles. Neither is more correct; they answer different questions, and a chart that does not say which one it is showing will be read as the flattering one.

Recent cohorts have not had time to churn

Here is the bug that survives review. Plot retention by period, average across all cohorts, and the recent cohorts look better than the old ones. Somebody concludes the product improved. It did not.

A cohort born last month has one observed period. It contributes a full-looking number to period 1 and nothing to period 12. Every cell in the lower-right of the triangle is computed from a shrinking, older, more self-selected set of cohorts. The average at period 1 covers everyone; the average at period 12 covers only cohorts old enough to have reached it. Comparing them compares different populations.

Two fixes, and you want both. Cut every cell the calendar has not finished — the test is one comparison, and these are the only cells worth plotting:

-- The spine on its own, filtered to the cells the calendar has finished.
with first_seen as (
  select customer_id,
         date_trunc('month', min(occurred_at))::date as cohort_month
  from events where event_type = 'login' group by customer_id
),
spine as (
  select c.cohort_month, p.period
  from (select distinct cohort_month from first_seen) c
  cross join lateral generate_series(
    0,
    (date_part('year',  age(date_trunc('month', current_date),
                            c.cohort_month)) * 12
     + date_part('month', age(date_trunc('month', current_date),
                              c.cohort_month)))::int
  ) as p(period)
)
select s.cohort_month, s.period
from spine s
where (s.cohort_month + ((s.period + 1) || ' months')::interval)
        <= date_trunc('month', current_date)
order by 1, 2;

That where clause goes on the final select of the retention query, and it drops the current partial month, which otherwise shows a half-collected count as if it were a full one. Then, when comparing cohorts, compare them at the same period — cohort A at month 3 against cohort B at month 3 — and only include cohorts that have actually reached that period. A retention table with a ragged right edge is honest. One that is a neat rectangle has been padded somewhere.

One practical note about where that filter lives: keep it in the base view, not in the BI tool. A maturity condition written into a dashboard tile survives until someone builds a second tile from the same table, copies the select and not the where, and the neat rectangle is back — this time on a slide nobody will re-derive.