SQL cohort and retention analysis: measuring whether users come back

Retention starts with a shared starting point

A retention question usually asks: after users start using a product, how many come back later? But users do not all start at the same time. A customer who joined in January has had much more time to return than one who joined in March, so comparing them directly can be misleading. Cohort analysis solves this by grouping users according to a shared starting point (a January cohort, a February cohort, a March cohort), then asks what percentage of each cohort returned one month later, two months later, and whether newer cohorts retain better. Consider these customers and their activity:

For this article, a customer is retained in a month if they have at least one activity event during that month. The exact definition of “active” depends on the product (logged in, placed an order, completed an assessment, used a core feature), and the SQL comes after that business definition.

Assign each user to a cohort

We will use the customer’s signup month as the cohort:

SELECT
    customer_id,
    customer,
    DATE_TRUNC('month', signup_at)::date AS cohort_month
FROM customers
ORDER BY customer_id;

Now we have three cohorts of two customers each: January (Asha, Ben), February (Carla, Dev), March (Esha, Farah). The important question is what event defines cohort membership. Here it is signup, but for another analysis it could be first purchase, first assessment, or subscription start, and that choice changes the meaning of the entire retention analysis.

Convert events into user activity periods

The source contains individual events, and Asha has two February events (February 8, February 20). But the question “was Asha active in February?” should answer yes, not twice. So before calculating retention, convert to one row per customer per active month:

SELECT DISTINCT
    customer_id,
    DATE_TRUNC('month', event_at)::date AS activity_month
FROM activity_events
ORDER BY
    customer_id,
    activity_month;

Repeated activity inside the same month has been collapsed to one customer-month. This establishes the correct grain for monthly retention.

Counting events instead of retained users

Suppose we want January cohort retention in Month 1. The January cohort is Asha and Ben, so the cohort size is 2. In February, Asha has two events and Ben has none. Someone counts event rows:

SELECT COUNT(*) AS retained
FROM activity_events
WHERE event_at >= DATE '2026-02-01'
  AND event_at <  DATE '2026-03-01'
  AND customer_id IN (1, 2);

-- Cohort: Asha, Ben  (size 2)
-- February events: Asha x2, Ben x0
-- COUNT(*) returns 2

-- A: 0%    B: 50%    C: 100%    D: 200%

The fix: match the grain to the metric

For monthly customer retention, one retained unit is one customer active during one month. So either use COUNT(DISTINCT customer_id) or normalize to one row per customer-month first (SELECT DISTINCT customer_id, DATE_TRUNC('month', event_at)). The second approach is often easier to reason about.

Measure activity relative to the cohort

Calendar months alone are not enough. For January users, January is Month 0, February is Month 1, March is Month 2. For February users, February is Month 0, March is Month 1, April is Month 2. So retention is measured in relative periods, computed from the difference between the activity month and the cohort month:

WITH cohort_users AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', signup_at)::date AS cohort_month
    FROM customers
),
user_activity AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', event_at)::date AS activity_month
    FROM activity_events
)
SELECT
    c.customer_id,
    c.cohort_month,
    a.activity_month,
    (
        EXTRACT(YEAR  FROM AGE(a.activity_month, c.cohort_month)) * 12
        +
        EXTRACT(MONTH FROM AGE(a.activity_month, c.cohort_month))
    )::int AS month_number
FROM cohort_users AS c
JOIN user_activity AS a
    ON a.customer_id = c.customer_id
WHERE a.activity_month >= c.cohort_month
ORDER BY
    c.cohort_month,
    c.customer_id,
    a.activity_month;

For monthly cohorts, Month 0 is the cohort’s starting month, Month 1 is one month later, and Month 2 is two months later. For Asha (signup January), January activity is Month 0, February is Month 1, March is Month 2. For Carla (signup February), February is Month 0, March is Month 1, April is Month 2. The calendar dates differ, but the customer lifecycle position is the same. That is what makes cohort comparison useful.

Cohort size, retained users, and retention rate

The denominator for retention is usually the number of users who originally entered the cohort (COUNT(*) of cohort_users grouped by cohort_month), which here is 2 for each cohort. This denominator should not quietly change from month to month: Month 1 retention is active-in-Month-1 over 2, and Month 2 retention is also active-in-Month-2 over 2. Counting distinct active users per relative month gives the numerator:

The full query combines retained users with cohort size and divides:

WITH cohort_users AS (
    SELECT customer_id, DATE_TRUNC('month', signup_at)::date AS cohort_month
    FROM customers
),
user_activity AS (
    SELECT DISTINCT customer_id, DATE_TRUNC('month', event_at)::date AS activity_month
    FROM activity_events
),
cohort_activity AS (
    SELECT
        c.customer_id,
        c.cohort_month,
        (
            EXTRACT(YEAR  FROM AGE(a.activity_month, c.cohort_month)) * 12
            + EXTRACT(MONTH FROM AGE(a.activity_month, c.cohort_month))
        )::int AS month_number
    FROM cohort_users AS c
    JOIN user_activity AS a ON a.customer_id = c.customer_id
    WHERE a.activity_month >= c.cohort_month
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(*) AS cohort_size
    FROM cohort_users
    GROUP BY cohort_month
),
retained AS (
    SELECT cohort_month, month_number, COUNT(DISTINCT customer_id) AS retained_users
    FROM cohort_activity
    GROUP BY cohort_month, month_number
)
SELECT
    r.cohort_month,
    r.month_number,
    c.cohort_size,
    r.retained_users,
    ROUND(100.0 * r.retained_users / c.cohort_size, 1) AS retention_pct
FROM retained AS r
JOIN cohort_sizes AS c ON c.cohort_month = r.cohort_month
ORDER BY r.cohort_month, r.month_number;

That is different from continuous retention (“active in every month since signup”), under which Ben would not qualify in Month 2 because he missed Month 1. Period retention (“active in Month 2?” Ben: yes) and continuous retention (“active in Month 1 and Month 2?” Ben: no) should not share a metric name without clarification.

Building a retention matrix

Retention is often displayed as a matrix, which makes it easy to compare cohorts at the same lifecycle stage:

Because we already learned pivoting, reshape the long retention result with conditional aggregation, one column per relative month:

SELECT
    cohort_month,
    MAX(CASE WHEN month_number = 0 THEN retention_pct END) AS month_0,
    MAX(CASE WHEN month_number = 1 THEN retention_pct END) AS month_1,
    MAX(CASE WHEN month_number = 2 THEN retention_pct END) AS month_2
FROM retention_by_month
GROUP BY cohort_month
ORDER BY cohort_month;

The long form (cohort, month_number, retention_pct) is often better for analysis; the wide matrix is often better for presentation.

The denominator trap

Missing future periods are not zero retention

If a cohort signed up this month, there has not yet been enough time to observe Month 2. A retention table should not show Month 2 = 0%. Zero means the period occurred and no users returned; an unobservable future period means we do not know yet. In a cohort matrix, future periods are represented as NULL, blank, or “not yet observed” rather than zero, which matters especially when comparing recent cohorts with older ones.

Definition choices change the metric

Signup month is only one cohort definition; users might instead be grouped by first purchase, subscription start, or first use of a core feature, and a signup cohort and a purchase cohort measure different lifecycle starting points. The retention event matters just as much: activity defined as login usually retains higher than lesson_completed, which retains higher than an assessment, with identical SQL but different meaning. The same structure works at daily, weekly, or monthly grain; choose a cadence matching expected product behaviour. And distinguish calendar buckets (signup month, next calendar month) from elapsed-time windows (Day 0 to 29, Day 30 to 59). Finally, segment by attributes whose meaning is stable, for example “signup channel” rather than a “current channel” that can later change.

How to spot cohort and retention problems

Think about cohort and retention analysis when the requirement says users who came back, Day 1 or Week 4 or monthly retention, repeat users, customer or signup or first-purchase cohorts, retention curves, or comparing newer and older customers.

Watch for these common mistakes:

• Events are counted instead of users.
• Repeated activity in one period counts a user several times.
• Cohort membership is not defined clearly.
• Activity is compared by calendar month without converting it into relative cohort periods.
• The denominator changes from period to period.
• Future unobservable periods are shown as zero.
• “Retained” is undefined.
• Calendar-month retention is confused with elapsed-day retention.
• Period retention is assumed to be continuous retention.

Answer six questions before writing retention SQL

Assign cohort
      v
Normalize activity to the retention grain
      v
Calculate activity period relative to cohort
      v
Count distinct returning entities
      v
Divide by original cohort size
      v
Compare retention across cohorts

One more prediction

The January cohort contains Asha and Ben, both active in January. In February, Asha has 5 activity events and Ben has 0. How many users are retained in Month 1?

Cohort: Asha, Ben (both active in January)
February: Asha 5 events, Ben 0 events

-- A: 0    B: 1    C: 2    D: 5

Summary

Cohort analysis aligns users according to a shared lifecycle starting point. Instead of comparing January, February, and March activity directly, it converts activity into Month 0, Month 1, Month 2 relative to each user’s cohort. A typical monthly workflow is: assign each user to a cohort month, reduce activity to one user-month, calculate the month offset from the cohort, count distinct active users, and divide by the original cohort size. The core calculation is retention = retained cohort users in period / original cohort users.