Building a Cohort Retention Table in SQL

The query is only as good as the tables under it. You need three things: a customer table with a reliable signup date, an invoice or charge table with one row per billed period, and a consistent way to identify the same customer across both.

The signup date problem. Your customers.created_at column may be account creation, not first payment. Plenty of trials never convert. If you cohort by account creation, some cohorts include people who never paid, which deflates retention for reasons that have nothing to do with product quality. The cleaner definition is the month of the customer's first successful charge. Derive it from billing rather than reading it off the customer row.

The invoice problem. Decide up front whether you are measuring gross revenue (invoiced) or net revenue (collected after refunds and credits). Both are defensible, but they are different metrics and you must not mix them. If you measure net, refunds and credit notes need to be subtracted in the same query, not in a separate report.

The identity problem. A customer who upgrades, downgrades, or reactivates may appear under several subscription records. Cohort identity must be stable at the customer level, not the subscription level, or one person can land in two cohorts.

A worked example. Suppose the January cohort was 40 customers who paid a combined 12,000 dollars in month zero. In month one they paid 10,800. In month two, 9,600. Revenue retention is 90% then 80% of the original base. That is a different question from "how many customers paid", which might read 36 and 33. Both views matter, and the grid below carries both.

Start with a CTE that gives every customer exactly one cohort month and one first payment date. Deriving the cohort from the first paid invoice, rather than joining a separate customers table, removes a whole class of mismatch bugs.

WITH first_payment AS (
    SELECT
        customer_id,
        MIN(paid_at) AS first_paid_at
    FROM invoices
    WHERE status = 'paid'
    GROUP BY customer_id
),
cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', first_paid_at)::date AS cohort_month
    FROM first_payment
)

If you have customers who paid but have no row in a customers table, or customers in the customers table who never paid, this pattern silently drops them. That is usually what you want for revenue retention, but log the counts so you know how many records you excluded and why.

The StrataScratch guide on retention makes the case for cohorts in one sentence: newer customers churn more, so an aggregate retention rate mixes old and new and hides the fact that the product is failing new customers the StrataScratch retention guide. Cohorting by first payment is exactly the fix.

The second CTE joins invoices back to cohorts and computes how many months after the cohort month each invoice falls. Integer month arithmetic, rather than day counts divided by 30, keeps the buckets clean across short months.

monthly_revenue AS (
    SELECT
        c.cohort_month,
        i.customer_id,
        (DATE_PART('year', i.paid_at) - DATE_PART('year', c.cohort_month)) * 12
          + (DATE_PART('month', i.paid_at) - DATE_PART('month', c.cohort_month)) AS month_number,
        SUM(i.amount) AS revenue
    FROM invoices i
    JOIN cohorts c ON c.customer_id = i.customer_id
    WHERE i.status = 'paid'
    GROUP BY 1, 2, 3
)

Note the GROUP BY includes the customer. Without it you cannot count distinct paying customers in the same pass, and you would need a second scan of the invoice table. Grouping at (cohort, customer, month) lets the next layer roll up both revenue and customer counts.

One caveat on DATE_PART: in PostgreSQL it returns a double precision number, so cast to integer before using it as a column key. In MySQL the equivalent is TIMESTAMPDIFF(MONTH, cohort_month, paid_at), and the tutorial scripts in this cohort analysis video walk through that variant, including how they truncate dates so the month difference does not return null the cohort analysis SQL walkthrough.

The classic mistake is to aggregate and pivot in one step. If a cohort had no revenue in month four, that cell simply does not exist, and a chart joins month three straight to month five. The fix is a generated grid of cohort months crossed with month numbers, left-joined to the aggregates. This is the same structure the Holistics query uses when it left-joins activity counts back to cohort sizes and divides to get a percentage the Holistics cohort guide.

SELECT
    g.cohort_month,
    g.month_number,
    COALESCE(r.revenue, 0) AS revenue,
    COALESCE(r.paying_customers, 0) AS paying_customers
FROM (SELECT DISTINCT cohort_month FROM cohorts) g
CROSS JOIN generate_series(0, 24) AS month_number
LEFT JOIN (
    SELECT cohort_month, month_number,
           SUM(revenue) AS revenue,
           COUNT(DISTINCT customer_id) AS paying_customers
    FROM monthly_revenue
    GROUP BY 1, 2
) r
  ON r.cohort_month = g.cohort_month
 AND r.month_number = g.month_number
ORDER BY g.cohort_month, g.month_number

generate_series is PostgreSQL specific. On a warehouse without it, a small numbers table with 25 rows works just as well and is portable. Cap the month numbers at a horizon you actually have data for: a cohort that is four months old should not show a 24-month row of zeros, because zeros read as churn. Add a months_elapsed column so consumers can filter out months that have not happened yet.

Retention percentages come from dividing each cell by month zero for that cohort. Compute month zero in a separate CTE rather than in a window function over the whole grid, so the denominator is unambiguous.

SELECT
    g.cohort_month,
    g.month_number,
    g.revenue,
    g.revenue / NULLIF(base.base_revenue, 0) AS revenue_retention,
    g.paying_customers,
    g.paying_customers::numeric / NULLIF(base.base_customers, 0) AS logo_retention
FROM grid g
JOIN (
    SELECT cohort_month,
           SUM(CASE WHEN month_number = 0 THEN revenue END) AS base_revenue,
           SUM(CASE WHEN month_number = 0 THEN paying_customers END) AS base_customers
    FROM grid
    GROUP BY 1
) base USING (cohort_month)

NULLIF protects against divide-by-zero for any cohort whose month zero is empty. If that happens, it is a data problem, not a query problem, and you want the null to surface rather than a silent zero.

For presentation, most teams pivot months into columns. A FILTER clause with conditional aggregation does it without a dedicated pivot function:

SELECT
    cohort_month,
    MAX(revenue) FILTER (WHERE month_number = 0) AS m0,
    MAX(revenue) FILTER (WHERE month_number = 1) AS m1,
    MAX(revenue) FILTER (WHERE month_number = 2) AS m2
FROM grid
GROUP BY cohort_month
ORDER BY cohort_month

Wide tables are convenient in a BI tool and awkward at 24 columns. Keep the long format as the source of truth in your warehouse and let the presentation layer pivot it.

Two metrics fall out of the same query and they answer different questions. Revenue retention tells you whether the money is holding up, and it can exceed 100% when a cohort expands. Logo retention tells you whether the people are holding up, and it never exceeds 100%.

The SaaS benchmarks make this distinction explicit: net revenue retention is calculated by adding expansion ARR and subtracting contraction and churned ARR from the starting base, and a number above 100% means existing customers are expanding faster than they shrink the SaaS Academy metrics guide. Gross revenue retention strips out expansion entirely and cannot exceed 100%, which makes it the honest measure of leakage. Klipfolio reports that the average annual net revenue retention rate sits between 85% and 135%, with more than half of the companies in the OpenView survey falling between 95% and 115% the Klipfolio SaaS benchmark list. Report both, side by side, and never let an expansion-heavy cohort hide a gross retention problem.

This is also where the cohort table pays for itself in cash planning. CAC payback is defined as acquisition cost divided by the gross margin dollars the customer generates, and it does not account for churn or the time value of money, which is precisely why measuring it on a real cohort, month by month, beats the one-customer formula the CAC payback guide. Practitioners who run the cohort version commonly find payback is longer than the simple formula suggested, because churned customers stop contributing before the cost is recovered the cohort payback walkthrough. Stripe's worked example, 50,000 dollars of spend acquiring 500 customers at 100 dollars of monthly revenue and 20 dollars of cost each, gives a 100 dollar CAC and a 1.25 month payback, which is the kind of number that only looks that good because churn is absent from the arithmetic the Stripe payback explainer.

If you would rather not hand-roll the reporting layer, a consulting call to scope the model before you build it is often cheaper than rebuilding it twice.

A retention table that disagrees with the billing system is worse than no table, because people will quote it. Run these checks every time the query changes.

First, reconcile revenue. Sum every cell in the grid across all cohorts and month numbers, and compare it to the total of paid invoices in the same window. Those numbers must match exactly. If they do not, you either excluded invoices (refunded, disputed, or with a status outside your filter) or double-counted customers with more than one record.

Second, check the month-zero diagonal. Month zero should equal the cohort's total first-month paid revenue, which you can pull directly from the billing system's cohort view for a single month. Start with the oldest cohort and the newest, since they are the two most likely to be affected by cutoff differences.

Third, spot-check individual customers. Pick five customers at random from one cohort and trace their invoice history by hand. Confirm they land in the right cohort month and that their revenue appears in the right month numbers. This catches timezone errors in paid_at, which are common when your warehouse stores UTC and your billing system bills on local calendar days. A payment at 23:30 on the last day of a month in one timezone is the first day of the next in another.

Fourth, check perpendicular totals. The sum of all cohort sizes should equal the count of distinct customers with at least one paid invoice. The sum of all paying customers in month one across all cohorts should equal the number of distinct customers who paid in month one after first paying in month zero. Both are quick and both catch join fan-out.

Finally, pin the query behind a test. A simple assertion that the grand total matches the invoice table total, run on a schedule, turns a one-time validation into a guardrail.