A monthly churn rate can hide the reason customers leave. A SaaS churn cohort analysis shows whether customers acquired in the same month keep paying, cancel early, downgrade, or return later.
That detail matters when a new onboarding change, pricing update, or acquisition channel shifts retention. Build the analysis around clear subscription rules first, then let SQL calculate the cohort matrix from the same definitions every month.
What a SaaS churn cohort analysis should measure
A cohort groups customers by their first paid month. Each later column shows their tenure, such as month 0, month 1, and month 2. This separates a bad January cohort from a generally weak retention trend.
Your definitions drive the result. If a customer pauses for two months and resumes, their outcome changes depending on whether a pause counts as churn.
Logo churn and revenue churn have different denominators
Logo churn measures lost customer accounts. For a monthly period, divide customers active at the start of the month who are no longer active next month by all customers active at the start of that month. Maxio’s logo churn definition uses the same account-level view.
Revenue churn measures recurring revenue lost from those accounts. Its denominator is beginning MRR, not the number of customers. Gross revenue churn includes lost MRR and downgrades, while net revenue churn subtracts expansion MRR.
A $50 customer and a $5,000 customer each count as one lost logo. Their effect on revenue churn is very different.
Pick an observation window before you calculate anything
Only label churn for fully observed months. If your latest complete month is July, calculate July churn by comparing MRR at July 1 with MRR at August 1.
A cohort row is incomplete until the next monthly boundary exists. Treating the current partial month as churn will inflate losses.
This guide uses calendar months and PostgreSQL. PostgreSQL’s date and time functions provide date_trunc, age, and generate_series, which make monthly cohort work manageable.
Set subscription rules before you query
Use a normalized table named subscription_periods. It should contain one contiguous paid-service interval per subscription and price change.
| Field | Meaning |
|---|---|
customer_id | A stable billing account identifier used for logo churn |
subscription_id | The individual subscription, useful when accounts hold several products |
started_at | Timestamp when paid access starts |
ended_at | Timestamp when paid access ends, exclusive of that moment |
mrr | Monthly recurring revenue for that interval |
is_paid | True only for revenue-bearing service intervals |
Convert timestamps to one business time zone before truncating to months. A customer who starts at 11:30 PM Pacific time can otherwise land in the wrong cohort.
Treat trials, cancellations, and pauses consistently
This query excludes free trials. A customer enters a cohort when their first paid interval begins. If you want trial-to-paid conversion reporting, build a separate trial cohort based on trial_started_at.
Set ended_at to the actual service end date, often current_period_end, rather than the timestamp when a customer clicks Cancel. Stripe’s subscription lifecycle documentation distinguishes subscription updates, cancellations, invoices, and payment collection.
For a true service pause, end the paid interval at the pause start and open a new interval when service resumes. However, if customers retain product access while invoice collection pauses, keep the interval active. Stripe’s pause behavior is a useful example of why billing status alone may not match access status.
Normalize paid intervals with modular CTEs
Put these CTEs after one WITH keyword, separated by commas. Replace the example end month with your latest fully observed month.
Start with a fixed reporting cutoff
Use params AS (SELECT DATE '2026-07-01' AS analysis_end_month). Keeping the cutoff in one place makes reruns reproducible.
Then filter to usable paid service intervals:
normalized_periods AS (SELECT customer_id, subscription_id, started_at, ended_at, mrr FROM subscription_periods CROSS JOIN params WHERE is_paid = true AND started_at < analysis_end_month + INTERVAL '1 month' AND (ended_at IS NULL OR ended_at > started_at))
An open ended_at means active only when the subscription itself is still active. Do not leave ended_at blank for a known cancellation. Put records with a canceled status and no reliable service-end date into a data-quality report until you can repair them.
Define the first paid cohort month
Create a customer-level acquisition table:
first_paid AS (SELECT customer_id, date_trunc('month', MIN(started_at))::date AS cohort_month FROM normalized_periods GROUP BY customer_id)
This preserves the original acquisition cohort after a reactivation. A customer who cancels in March and returns in June stays in their first paid cohort. If your business treats reactivation as a new acquisition, create a separate resurrection cohort instead of overwriting the original date.
Create a customer-by-month tenure grid
A cohort table needs an explicit row for every customer and every observed month. Otherwise, a missing row could mean either churn or a query gap.
Generate tenure periods for every cohort member
Build the monthly spine through one additional month. That extra boundary lets the final complete month calculate churn.
cohort_grid AS (SELECT f.customer_id, f.cohort_month, g.month_start::date AS month_start, (EXTRACT(YEAR FROM age(g.month_start, f.cohort_month)) * 12 + EXTRACT(MONTH FROM age(g.month_start, f.cohort_month)))::int AS tenure_month FROM first_paid f CROSS JOIN params p CROSS JOIN LATERAL generate_series(f.cohort_month, p.analysis_end_month + INTERVAL '1 month', INTERVAL '1 month') AS g(month_start))
Tenure month 0 is the cohort month. Tenure month 1 is the following calendar month, even when the first payment happened mid-month.
Calculate monthly activity and opening MRR
Join intervals to the grid. active_in_month means the customer had paid access at any point in the month. opening_mrr measures MRR at the first instant of that month.
customer_month AS (SELECT g.customer_id, g.cohort_month, g.month_start, g.tenure_month, MAX(CASE WHEN n.subscription_id IS NOT NULL THEN 1 ELSE 0 END) AS active_in_month, MAX(CASE WHEN n.started_at <= g.month_start AND (n.ended_at IS NULL OR n.ended_at > g.month_start) THEN 1 ELSE 0 END) AS opening_logo, COALESCE(SUM(CASE WHEN n.started_at <= g.month_start AND (n.ended_at IS NULL OR n.ended_at > g.month_start) THEN n.mrr ELSE 0 END), 0) AS opening_mrr FROM cohort_grid g LEFT JOIN normalized_periods n ON n.customer_id = g.customer_id AND n.started_at < g.month_start + INTERVAL '1 month' AND (n.ended_at IS NULL OR n.ended_at > g.month_start) GROUP BY 1,2,3,4)
The account-level grouping avoids double-counting a customer with several subscriptions. It also adds MRR across active products at the start of each month.
Calculate retention, logo churn, and revenue churn
The next CTE compares each monthly opening balance with the next month’s opening balance.
Flag churn without losing reactivation history
Use:
period_flags AS (SELECT *, LEAD(opening_logo) OVER (PARTITION BY customer_id ORDER BY month_start) AS next_opening_logo, LEAD(opening_mrr) OVER (PARTITION BY customer_id ORDER BY month_start) AS next_opening_mrr, MIN(active_in_month) OVER (PARTITION BY customer_id ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS continuously_retained FROM customer_month)
A logo churn event occurs when opening_logo = 1 and next_opening_logo = 0. A customer can later reactivate, but the original churn event remains recorded.
For revenue, calculate lost_mrr when opening MRR is positive and next-month MRR is zero. Calculate contraction_mrr when next-month MRR remains positive but is lower. Expansion is the reverse difference.
Aggregate the cohort table
Your cohort_metrics CTE should group by cohort_month and tenure_month. Calculate the following fields:
| Metric | Numerator | Denominator |
|---|---|---|
| Point retention | SUM(active_in_month) | Original cohort size |
| Survival retention | SUM(continuously_retained) | Original cohort size |
| Logo churn rate | Churned opening logos | SUM(opening_logo) |
| Gross revenue churn | Lost MRR plus contraction MRR | SUM(opening_mrr) |
| Net revenue churn | Gross churn less expansion MRR | SUM(opening_mrr) |
For the final output, filter to month_start <= analysis_end_month. Then calculate rates with NULLIF to avoid divide-by-zero errors.
A wide matrix is easy to create after aggregation. For example, use MAX(point_retention) FILTER (WHERE tenure_month = 0) AS m0, then repeat for m1, m2, and later columns. Keep the long-format table as your source of truth because BI tools and spreadsheets can pivot it without changing the calculation.
Validate the cohort output and handle edge cases
Run checks before sharing a retention chart. A clean-looking matrix can still be wrong if subscription intervals overlap or canceled accounts have null end dates.
Reconcile the data before trusting churn rates
Use these checks during development:
- Find overlapping intervals with
SELECT a.subscription_id, a.started_at, b.started_at FROM normalized_periods a JOIN normalized_periods b ON a.subscription_id = b.subscription_id AND a.started_at < b.started_at AND COALESCE(a.ended_at, 'infinity'::timestamptz) > b.started_at; - Confirm every cohort begins with paid activity using
SELECT * FROM customer_month WHERE tenure_month = 0 AND active_in_month = 0; - Reconcile opening MRR with your billing snapshot using
SELECT month_start, SUM(opening_mrr) FROM customer_month GROUP BY month_start ORDER BY month_start;
Investigate differences by customer, not only by total. One subscription migration can offset another in aggregate totals.
Decide how edge cases affect your definition
An upgrade or downgrade within a month appears in the next monthly opening snapshot. This prevents double-counting two price intervals in the same month. If finance needs daily MRR movement, use a daily spine instead.
A customer who starts and cancels within their acquisition month appears as active in month 0. Flag these same-month cancellations separately if short-lived subscriptions distort onboarding analysis. Partial first months also make month 0 less comparable, so many teams focus on month 1 retention.
Customers with multiple subscriptions belong to one logo cohort because the query groups by customer_id. If each workspace or seat is a separate customer, replace that key with the identifier that matches how your company defines a logo.
Snowflake users can retain the same CTE design, then swap PostgreSQL date expressions for Snowflake’s DATEDIFF and date functions. Cohort results still depend on your churn definition, time zone, subscription grain, and observation window.
Put the cohort query to work
A reliable SaaS churn cohort analysis starts with a stable customer identifier and paid-service intervals that reflect actual access dates. The SQL then turns those intervals into transparent monthly retention, logo churn, and MRR churn metrics.
Create a view from these CTEs in your warehouse, set analysis_end_month to the last completed month, and reconcile its opening MRR against one billing snapshot before publishing the first cohort matrix.
