Building a Cohort Retention Query in PostgreSQL

Retention is the metric most often quoted and most often computed wrongly, and the error is almost always the same shape: an inner join. Accounts that churned disappear from the result instead of contributing zero, so the query reports retention among the customers who stayed — a number that is always flattering and never meaningful. This guide sits under Billing Analytics & Revenue Metrics and covers the queries themselves, written against a movement log.

The second most common error is cohorting by subscription rather than by account. A customer who cancels one plan and starts a larger one has expanded; a subscription-keyed query records a churn and a new sale, and the cohort’s retention drops for a customer who spent more. Since almost every meaningful retention question is about customers, the cohort key is the account.

Getting these two right is most of the work. The rest is deciding which of several defensible retention definitions you are reporting and saying so on the chart.

Trade-offs

Report both. A business at 115% net and 80% gross is growing through a handful of large expansions while losing a fifth of its revenue base every year, which is a materially different story from 115% net on 96% gross. The pair is far more informative than either alone.

Step-by-Step Implementation

1. Define the cohort

-- Each account's cohort: the first month it contributed recurring revenue.
CREATE OR REPLACE VIEW account_cohorts AS
SELECT account_id,
       min(month) FILTER (WHERE current_minor > 0) AS cohort_month
  FROM mrr_movements
 GROUP BY account_id;

Using the first paying month rather than the signup month matters for products with long trials or a free tier: cohorting on signup mixes accounts that started paying months apart and makes the early periods of every cohort look artificially weak.

2. Compute cohort revenue by month

CREATE OR REPLACE VIEW cohort_revenue AS
SELECT c.cohort_month,
       m.month,
       (extract(year FROM age(m.month, c.cohort_month)) * 12
        + extract(month FROM age(m.month, c.cohort_month)))::int AS period_index,
       count(DISTINCT m.account_id) FILTER (WHERE m.current_minor > 0) AS active_accounts,
       sum(m.current_minor)                                           AS revenue_minor
  FROM mrr_movements m
  JOIN account_cohorts c USING (account_id)
 GROUP BY c.cohort_month, m.month;

period_index — months since cohort start — is what makes cohorts comparable. Charting against calendar months compares a six-month-old cohort with a three-year-old one and shows only that the older one is bigger.

3. Left join so churn counts as zero

-- Net revenue retention for a cohort at a given period offset.
WITH base AS (
  SELECT account_id, sum(current_minor) AS start_minor
    FROM mrr_movements
   WHERE month = $1 AND current_minor > 0
   GROUP BY account_id
), later AS (
  SELECT account_id, sum(current_minor) AS end_minor
    FROM mrr_movements
   WHERE month = $2
   GROUP BY account_id
)
SELECT round(100.0 * sum(coalesce(l.end_minor, 0)) / nullif(sum(b.start_minor), 0), 1) AS nrr_pct
  FROM base b
  LEFT JOIN later l USING (account_id);

The coalesce is doing the real work. Without it a NULL from the left join propagates and the account is excluded from the numerator while remaining in the denominator only by accident of how sum treats nulls — which is exactly the kind of subtlety that produces a number nobody can explain.

4. Separate gross from net

-- Gross retention caps each account's contribution at its starting amount.
SELECT round(100.0 * sum(least(coalesce(l.end_minor, 0), b.start_minor))
             / nullif(sum(b.start_minor), 0), 1) AS grr_pct
  FROM base b
  LEFT JOIN later l USING (account_id);

least(end, start) per account is the whole difference. Capping at the aggregate level instead — computing net and then clamping the total at 100% — gives a different and wrong answer, because it lets one account’s expansion offset another’s contraction.

5. Materialise once it is slow

CREATE MATERIALIZED VIEW cohort_matrix AS
SELECT cohort_month, period_index,
       sum(revenue_minor) AS revenue_minor,
       sum(active_accounts) AS accounts
  FROM cohort_revenue
 GROUP BY cohort_month, period_index;

CREATE UNIQUE INDEX ON cohort_matrix (cohort_month, period_index);
-- Refresh nightly; CONCURRENTLY needs the unique index above.

Reading the Matrix Without Fooling Yourself

A cohort matrix invites over-interpretation, and three habits keep the reading honest.

Watch the cohort sizes. A cohort of eleven accounts will show retention figures that swing twenty points on one departure, and putting it on the same chart as a cohort of nine hundred implies a comparability that does not exist. Show the base count next to every percentage, and grey out or exclude cohorts below a size threshold.

Distinguish young cohorts from complete ones. The most recent cohorts have only two or three periods of data, and their early numbers are systematically better than older cohorts at the same age simply because the population that would have churned has not had time to. Truncate the matrix so every cohort is compared only at ages all of them have reached, or state the incompleteness explicitly.

Look along the diagonal as well as the rows. A row is one cohort ageing; a diagonal is one calendar month affecting every cohort at once. A bad diagonal — every cohort dipping in the same month — points at something that happened to the whole business, such as a pricing change, an outage, or a billing bug that churned accounts involuntarily. That is a completely different investigation from one cohort ageing badly, and only the diagonal view distinguishes them.

Finally, resist averaging cohorts into a single retention curve without weighting. An unweighted average treats a fifteen-account cohort and a nine-hundred-account cohort as equals, which produces a curve that describes no actual population. Weight by starting revenue if the metric is revenue retention, by account count if it is logo retention.

Segmenting Cohorts Without Losing the Denominator

Retention becomes actionable when it is split — by plan, by acquisition channel, by company size, by country — and splitting is where the denominator quietly breaks.

The rule is that a segment must be a property of the account fixed at cohort entry, not a property that can change afterwards. Segmenting by current plan is a trap: an account that started on Starter and upgraded to Business appears in the Business cohort’s denominator without ever having been part of it, which inflates Business retention and deflates Starter’s. Segment by the plan at cohort entry and the numbers describe something real — how well each entry point retains — while upgrades show up as expansion inside their original cohort.

The same applies to geography, company size, and anything else derived from a mutable field. Snapshot the segment value when the account enters its cohort and store it on the cohort row, so a later change to the account record cannot retroactively move it between segments and change a published figure.

Watch the sample size after splitting. A base of a thousand accounts divided across four plans, three regions, and two channels leaves cells with a handful of accounts each, and a retention percentage computed on six accounts is noise presented as a measurement. Split on one dimension at a time, and set a minimum cell size below which the figure is suppressed rather than shown.

ALTER TABLE account_cohort_attributes
  ADD COLUMN entry_plan_key   TEXT,     -- snapshot at cohort entry, never updated
  ADD COLUMN entry_country    CHAR(2),
  ADD COLUMN entry_channel    TEXT;

Finally, be explicit about which segmentation the headline number uses. “Net revenue retention was 112%” means something different for all accounts than for accounts above a spend threshold, and the version quoted externally is almost always the latter. Label it on the chart rather than in a footnote nobody reads.

Verification & Testing

Build a fixture population with hand-computed answers: ten accounts, one churning entirely, one halving, one doubling, seven unchanged. Assert the exact gross and net figures rather than a range — the numbers are small enough to work out by hand, and an assertion on an exact value catches join errors that a tolerance would let through.

Test the join explicitly by asserting that removing a churned account’s later row does not change the result. If it does, an inner join has crept back in. This is worth a dedicated test because the regression is easy to introduce during a query rewrite and invisible in review.

Test the gross cap at the account level with a fixture where one account doubles and another halves by the same absolute amount. Net retention should be exactly 100%; gross should be below it. A query that reports both as 100% is capping in the wrong place.

Gotchas & Production Pitfalls

• Inner join instead of left join. Reports retention among survivors. The single most common defect in this entire query family.
• Cohorting by subscription. Turns plan switches into churn plus new, and understates retention for exactly the customers who upgraded.
• Signup month as the cohort key. Mixes accounts that began paying at very different times, flattening the early periods of every cohort.
• Capping gross retention on the aggregate. Lets one account’s expansion offset another’s loss, which is precisely what gross retention exists to exclude.
• Comparing incomplete cohorts. Recent cohorts always look better at the same nominal age; truncate or label them.

Frequently Asked Questions

Should retention be monthly or annual? Report annual net revenue retention as the headline — it is what the number is understood to mean externally — and keep monthly views for operational use. Annualising a monthly figure by exponentiation overstates the effect of a single unusual month.

How do I handle accounts that churn and return? They contribute zero in the months they were gone and their actual amount when they return. A cohort that recovers revenue in later periods is showing genuine win-back, which is worth seeing rather than smoothing away.

Does the denominator ever change? No. Once a cohort’s base is fixed at its starting revenue, it stays fixed for every subsequent period. A denominator that drifts is the same error as the inner join wearing different clothes.

Where do new customers fit? They do not — they form their own cohort. Retention measures existing customers only, and mixing acquisition into it produces a metric that rises whenever sales has a good month regardless of whether anyone stayed.

Related

• Billing Analytics & Revenue Metrics
• Calculating MRR Movements from Subscription Data
• Reconciling MRR Against Recognised Revenue
• Designing a Win-Back Flow After Involuntary Churn
• Subscription Lifecycle States