Build a monthly customer cohort table in SQL

A monthly customer cohort groups customers by a shared starting month, then measures their activity in later months. The cohort denominator stays tied to the original group. New customers acquired later should not silently enter that denominator.

For a purchase-based cohort, define the starting event and the activity event explicitly. In this example, the start is the customer's first observed purchase, and activity means at least one purchase during a calendar month. That is not necessarily first-ever acquisition: the available history may be incomplete.

The advanced SQL dataset contains five synthetic customers across January–April 2026. The source is treated as complete through April 30, with an observation cutoff of May 1.

Inspect the cohort membership

SELECT customer_id,
       MIN(purchased_at) AS first_observed_purchase,
       SUBSTR(MIN(purchased_at), 1, 7) AS cohort_month
FROM purchases
GROUP BY customer_id
ORDER BY customer_id;

A and B belong to January, C to February, D to March and E to April. Cohort sizes are therefore two, one, one and one.

This first table is worth reviewing before building a heatmap. If the acquisition dates are wrong, a beautifully formatted retention matrix simply spreads the error across more cells.

Count each customer once per activity month

A customer can make several purchases in a month but should count once in a customer-retention measure. B has two January purchases; counting order rows would overstate January cohort activity.

Create distinct customer-month pairs, then connect them to fixed cohort membership. A calendar grid provides explicit rows for months with zero activity. Without the grid, an absent aggregate row can be confused with missing data or an unobserved future period.

WITH cohorts AS (
    SELECT customer_id,
           SUBSTR(MIN(purchased_at), 1, 7) AS cohort_month
    FROM purchases
    GROUP BY customer_id
), sizes AS (
    SELECT cohort_month, COUNT(*) AS cohort_size
    FROM cohorts
    GROUP BY cohort_month
), activity AS (
    SELECT DISTINCT customer_id,
           SUBSTR(purchased_at, 1, 7) AS activity_month
    FROM purchases
), active_counts AS (
    SELECT c.cohort_month, a.activity_month, COUNT(*) AS active_customers
    FROM cohorts AS c
    JOIN activity AS a ON a.customer_id = c.customer_id
    GROUP BY c.cohort_month, a.activity_month
), months AS (
    SELECT DISTINCT SUBSTR(day, 1, 7) AS activity_month
    FROM calendar
)
SELECT s.cohort_month, m.activity_month, s.cohort_size,
       COALESCE(a.active_customers, 0) AS active_customers,
       100.0 * COALESCE(a.active_customers, 0) / s.cohort_size
           AS active_pct
FROM sizes AS s
JOIN months AS m ON m.activity_month >= s.cohort_month
LEFT JOIN active_counts AS a
    ON a.cohort_month = s.cohort_month
   AND a.activity_month = m.activity_month
ORDER BY s.cohort_month, m.activity_month;

The calendar contains only January–April, so the query does not create unobserved future cells. Its zero filling is justified by the exercise's source-completeness assumption, not by the calendar's existence alone.

Verify the expected matrix

The dash means the month precedes acquisition. It is not zero retention. April for the February cohort is a real zero under the stated observation assumption: C has no April purchase.

The January cohort's three later 50% cells do not all represent the same customer. A buys in February and April; B buys in March. This is calendar-period activity, not a measure of uninterrupted monthly retention.

That distinction should appear in the chart caption. A stakeholder could otherwise read three 50% values as evidence that the same half of customers stayed continuously active.

Separate cohort age from calendar month

A heatmap often uses month zero, month one and month two instead of calendar labels. Calculate the difference between year/month indices rather than subtracting month numbers alone; December to January crosses a year boundary.

Keep the underlying calendar month in the data even when displaying relative age. It helps diagnose seasonality, outages and campaigns that affect several cohorts at once.

SQLite's date and string functions support this example, but other engines provide different month-truncation and date-difference functions. SQLite date/time reference.

Avoid three interpretation traps

First, small cohorts are unstable. A one-person cohort can only show 0% or 100%; it is not a reliable estimate of broad customer behaviour. Always show cohort size.

Second, incomplete history misclassifies established customers as new. Call the start “first observed” unless you can establish complete acquisition history.

Third, an ongoing month has had less opportunity to accumulate activity. Mark it partial or exclude it from like-for-like comparisons. Do not fill future cells with zero and call the result churn.

Exercise: add a May purchase by C, extend the calendar through a fully observed May, and explain the February cohort's return after an inactive April. The result should show reactivation; it should not rewrite April's zero.

NeuraPath's Data Analytics with Generative AI programme covers the SQL and statistical reasoning needed to turn a cohort table into a defensible business interpretation. The table is useful only when the event, denominator and observation window remain clear.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in LAG and LEAD for repeat-purchase intervals.
• Continue with Retention in SQL: distinguish active users from returning users.