Cohort Analysis

Production engineering guide for cohort analysis covering patterns, implementation strategies, and operational best practices.

Cohort Analysis is the practice of grouping users by their initial engagement event—typically sign-up, first purchase, or first feature activation—and tracking their behavior over time to measure retention, engagement, and product success. It matters when product teams must answer: *Which user groups are staying, and why?* It is the go-to method for diagnosing product health, evaluating feature rollouts, and validating user onboarding flows.

Defining Cohorts

A cohort is a group of users who share a common origin point. The origin is defined by a user-level event with a timestamp. The most common origin event is user_signed_up, but it can be first_session_started, first_purchase_completed, or feature_activated. The cohort window—the time interval used to define the cohort—is a fixed duration: daily, weekly, monthly, or custom.

The cohort definition is expressed as a SQL query that identifies the first occurrence of the defining event per user. For example:

-- Define cohorts from first sign-up events
WITH cohort_users AS (
  SELECT
    user_id,
    MIN(event_timestamp) AS cohort_date
  FROM events
  WHERE event_name = 'user_signed_up'
  GROUP BY user_id
)
SELECT
  cohort_date,
  COUNT(*) AS cohort_size
FROM cohort_users
GROUP BY cohort_date
ORDER BY cohort_date;

Each cohort date becomes a row in the output. The cohort size is the number of users who joined on that day.

Computing Retention Across Time

Retention is the primary metric computed per cohort. It measures the fraction of users who perform a target event (e.g., session_started, purchase_completed) at least once in each time interval after their cohort date.

To compute daily retention for users who signed up on a given day, define a retention table:

-- Compute 30-day retention for daily cohorts
WITH cohort_users AS (
  SELECT
    user_id,
    DATE(MIN(event_timestamp)) AS cohort_date
  FROM events
  WHERE event_name = 'user_signed_up'
  GROUP BY user_id
),
retention AS (
  SELECT
    c.user_id,
    c.cohort_date,
    DATE(e.event_timestamp) AS event_date,
    DATEDIFF(DATE(e.event_timestamp), c.cohort_date) AS days_since_cohort
  FROM events e
  INNER JOIN cohort_users c ON e.user_id = c.user_id
  WHERE e.event_name = 'session_started'
)
SELECT
  cohort_date,
  COUNT(*) AS total_users,
  SUM(CASE WHEN days_since_cohort = 0 THEN 1 ELSE 0 END) AS day_0,
  SUM(CASE WHEN days_since_cohort = 1 THEN 1 ELSE 0 END) AS day_1,
  SUM(CASE WHEN days_since_cohort = 7 THEN 1 ELSE 0 END) AS day_7,
  SUM(CASE WHEN days_since_cohort = 30 THEN 1 ELSE 0 END) AS day_30
FROM retention
GROUP BY cohort_date
ORDER BY cohort_date;

This query yields one row per cohort date with retention counts for key days. The output can be pivoted into a matrix where rows are cohort dates and columns are days since cohort.

Aggregating Cohort Tables for Visualization

To generate a cohort table suitable for visualization (e.g., a heatmap), aggregate retention into a matrix format:

-- Pivot retention data into a cohort matrix
WITH cohort_users AS (
  SELECT
    user_id,
    DATE(MIN(event_timestamp)) AS cohort_date
  FROM events
  WHERE event_name = 'user_signed_up'
  GROUP BY user_id
),
retention AS (
  SELECT
    c.user_id,
    c.cohort_date,
    DATE(e.event_timestamp) AS event_date,
    DATEDIFF(DATE(e.event_timestamp), c.cohort_date) AS days_since_cohort
  FROM events e
  INNER JOIN cohort_users c ON e.user_id = c.user_id
  WHERE e.event_name = 'session_started'
),
retention_by_day AS (
  SELECT
    cohort_date,
    days_since_cohort,
    COUNT(*) AS active_users
  FROM retention
  GROUP BY cohort_date, days_since_cohort
),
cohort_matrix AS (
  SELECT
    cohort_date,
    COALESCE(SUM(CASE WHEN days_since_cohort = 0 THEN active_users END), 0) AS d0,
    COALESCE(SUM(CASE WHEN days_since_cohort = 1 THEN active_users END), 0) AS d1,
    COALESCE(SUM(CASE WHEN days_since_cohort = 2 THEN active_users END), 0) AS d2,
    COALESCE(SUM(CASE WHEN days_since_cohort = 7 THEN active_users END), 0) AS d7,
    COALESCE(SUM(CASE WHEN days_since_cohort = 14 THEN active_users END), 0) AS d14,
    COALESCE(SUM(CASE WHEN days_since_cohort = 30 THEN active_users END), 0) AS d30
  FROM retention_by_day
  GROUP BY cohort_date
)
SELECT
  cohort_date,
  d0,
  d1,
  d2,
  d7,
  d14,
  d30
FROM cohort_matrix
ORDER BY cohort_date;

The resulting table is ready for ingestion into a visualization tool like Grafana, Metabase, or a custom dashboard.

Handling Time Zone and Data Latency

Cohort analysis is sensitive to time zone and data latency. The cohort date is defined by event_timestamp, but this timestamp may be in the user’s local time zone. To ensure consistency, standardize all timestamps to UTC before cohorting.

When events are processed in batches (e.g., daily ETL jobs), cohort dates are computed from data that arrived up to 24 hours after the event. This leads to cohorting lag: users who signed up on 2023-10-05 may be assigned to the cohort on 2023-10-06 if the cohorting job runs nightly.

To correct for this, use a cohorting window with a “cut-off” time. For example, cohort users based on user_signed_up events received by 2023-10-05 23:59:59 UTC. This ensures that the cohort table reflects all users who signed up up to that point, even if the event was recorded at 2023-10-05 10:00:00 UTC.

In the data pipeline, the cohorting job should:

  1. Read all user_signed_up events from the last 48 hours.
  2. Compute the MIN(event_timestamp) per user.
  3. Assign cohort dates based on event_timestamp in UTC.
  4. Write the cohort data to a table with a cohorted_at timestamp.

Failure modes:

Defining Cohorts with Multiple Events

Not all cohorts are based on a single event. Some require a sequence of actions to define a cohort.

For example, to define a cohort of users who completed onboarding, define a cohort as users who:

The cohort date is the date of the first user_signed_up event, and the cohort membership is determined by whether the user completed the sequence within 7 days.

-- Define onboarding cohort: signed up, created project, added task within 7 days
WITH user_events AS (
  SELECT
    user_id,
    event_name,
    event_timestamp,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS event_order
  FROM events
  WHERE event_name IN ('user_signed_up', 'project_created', 'task_created')
),
onboarding_sequence AS (
  SELECT
    u1.user_id,
    u1.event_timestamp AS sign_up_time,
    u2.event_timestamp AS project_created_time,
    u3.event_timestamp AS task_created_time,
    DATEDIFF(u3.event_timestamp, u1.event_timestamp) AS days_to_complete
  FROM user_events u1
  INNER JOIN user_events u2 ON u1.user_id = u2.user_id AND u2.event_order = 2
  INNER JOIN user_events u3 ON u1.user_id = u3.user_id AND u3.event_order = 3
  WHERE u1.event_name = 'user_signed_up'
    AND u2.event_name = 'project_created'
    AND u3.event_name = 'task_created'
    AND DATEDIFF(u3.event_timestamp, u1.event_timestamp) <= 7
)
SELECT
  DATE(sign_up_time) AS cohort_date,
  COUNT(*) AS cohort_size
FROM onboarding_sequence
GROUP BY cohort_date
ORDER BY cohort_date;

This approach allows tracking of cohort-specific retention and engagement, such as how users who completed onboarding differ from those who did not.

Cohort Analysis in Production

In production, cohort analysis is typically automated. A scheduled job runs nightly to:

A typical cron job runs:


0 4 * * * /usr/local/bin/cohort-analysis --input=/data/events --output=/data/cohort-matrix --event=user_signed_up --target=session_started --days=0,1,7,14,30

The cohort-analysis binary is a command-line tool that:

The tool fails silently when:

The tool must handle:

Common Pitfalls and Sharp Edges

The choice affects cohort shapes and comparisons.

Advanced Patterns

Cohort analysis is not just a report—it is a diagnostic tool. When retention flattens or drops, the cohort matrix reveals whether the issue is due to early drop-off (low day_0), poor mid-term engagement (declining day_7), or long-term attrition (failing day_30). The shape of the cohort table tells a story the team can act on.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.