Cohort retention triangle

You meet it when#

Anyone asks whether retention is improving. The chart everyone wants, and one most people build with an off-by-one somewhere.

The pattern#

with cohorts as (
    select account_id,
           date_trunc('month', signed_up_on) as cohort_month,
           churned_at
    from saas_subscriptions
),
months as (
    select distinct date_trunc('month', signed_up_on) as month_start
    from saas_subscriptions
),
activity as (
    select c.cohort_month,
           c.account_id,
           datediff('month', c.cohort_month, m.month_start) as month_number
    from cohorts c
    cross join months m
    where m.month_start >= c.cohort_month
      and (c.churned_at is null
           or m.month_start < date_trunc('month', c.churned_at))
)
select cohort_month,
       month_number,
       count(distinct account_id) as accounts
from activity
group by 1, 2
order by 1, 2;

The two bugs#

The churn comparison. An account that churned on 14 March was active in March, so the condition is < on the truncated month, not <=. One character, and it shifts every retention number by a month.

Censoring. A cohort three months old has no twelve-month retention — that cell does not exist yet. It is not zero. Comparing a young cohort's late columns against an old one's is comparing "not yet observed" to "observed and left", and the triangle always looks best in its bottom-right corner because those cells are simply young.

Leave unobservable cells blank, never zero. If your BI tool insists on filling them, compute the maximum observable month_number per cohort and null out the rest:

where month_number <= datediff('month', cohort_month, current_date)

Reading it honestly#

Compare down the columns, not across the rows. Column 3 across cohorts tells you whether month-3 retention is improving over time — that is the question people mean. Reading across a row just describes one cohort ageing.