Cohort analysis

Definition

Cohort analysis groups users by a shared starting event, usually their install date, and measures each group separately over time so that changes in behaviour can be separated from changes in acquisition mix.

It exists to solve one problem: aggregate metrics move for two completely different reasons, and you cannot tell which from the aggregate. A drop in daily active users can mean the product got worse or that you simply acquired fewer users last month. Cohorts separate those, because each row follows a fixed group of people whose composition never changes.

Reading a cohort table

The canonical output is triangular: one row per cohort, one column per period since that cohort started, and progressively fewer filled cells as you move down toward cohorts that have not lived long enough to reach the later columns.

Read down a column to compare cohorts at the same age: this is the comparison that tells you whether the product or the traffic changed. Read across a row to see one group's decay curve. The two readings answer different questions and mixing them is the most common misinterpretation of the format.

The table above contains one obvious event. The week of 16 June is 60% larger than its neighbours and retains at roughly two-thirds of their rate at every column, then everything returns to normal the following week. That is a volume spike of low-quality traffic — a burst campaign, an incentivised placement, or something worth checking against click spamming. Crucially, the blended monthly retention figure for June would show a mild decline and hide the cause entirely.

What to cohort on

Install date is the default, not the only option. The starting event should be whatever moment you are trying to reason about, and changing it changes which question the table answers.

Building the table

-- One row per cohort week, one column per day index.
-- Cells that have not matured are NULL, never 0 — a zero would be
-- read as churn and a NULL is read as "not yet known".
WITH cohort AS (
  SELECT
    user_id,
    DATE_TRUNC(DATE(installed_at), WEEK(MONDAY)) AS cohort_week,
    DATE(installed_at)                           AS install_date
  FROM installs
),
activity AS (
  SELECT DISTINCT
    c.user_id,
    c.cohort_week,
    DATE_DIFF(DATE(s.started_at), c.install_date, DAY) AS day_n
  FROM cohort c
  JOIN sessions s USING (user_id)
  WHERE s.is_foreground
),
sizes AS (
  SELECT cohort_week, COUNT(*) AS cohort_size FROM cohort GROUP BY cohort_week
)
SELECT
  z.cohort_week,
  z.cohort_size,
  ROUND(100.0 * COUNT(DISTINCT IF(a.day_n = 1,  a.user_id, NULL)) / z.cohort_size, 1) AS d1,
  ROUND(100.0 * COUNT(DISTINCT IF(a.day_n = 7,  a.user_id, NULL)) / z.cohort_size, 1) AS d7,
  IF(z.cohort_week <= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY),
     ROUND(100.0 * COUNT(DISTINCT IF(a.day_n = 30, a.user_id, NULL)) / z.cohort_size, 1),
     NULL) AS d30
FROM sizes z
LEFT JOIN activity a USING (cohort_week)
GROUP BY z.cohort_week, z.cohort_size
ORDER BY z.cohort_week DESC;

The IF wrapper on the D30 column is the pattern to copy for every column. Rendering an immature cell as 0.0% produces a cohort table whose bottom-right corner always looks like a catastrophe, and teams learn to ignore the corner — which is where a real regression would first appear.

The four mistakes that make a cohort table lie

1. Zero instead of null for immature cells. Invents churn that has not happened.
2. Cohorts too small to read. A 200-user cohort at 6% is twelve people; weekly or monthly grouping is usually the fix.
3. Blending sources in one row. A shifting acquisition mix moves the row for reasons that have nothing to do with the product.
4. Changing the activity definition mid-history. Every row before the change becomes incomparable, and nothing in the table records that it happened.

The third is worth restating because it is the most expensive. A cohort table split only by date, for an app whose channel mix changed, measures channel mix. Adding a source dimension turns the same data into a decision: which channels to keep buying — and paired with spend, it is also where effective CPI becomes a payback question rather than a cost question.

Finally, decide the retention definition before building the table and write it into the title. A cohort table is a grid of retention numbers, so every ambiguity in the underlying definition is reproduced in every one of its cells.