lesson 53 Advanced product analysis · advanced

Cohorts & exact-day retention

Measure whether users return on a specific day after joining.

the idea

A cohort groups people by signup date. Exact-day D1 retention asks whether they return on signup +1 day, not anytime later. Count people, include non-returners, and exclude cohorts that have not matured by the observation cutoff.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

viewers4 columns · 8 rows

idinteger · key	countrytext	signup_attext	referred_byinteger
1	US	2025-01-01	NULL
2	US	2025-01-02	1
3	GB	2025-01-02	1
4	IN	2025-01-03	2
5	GB	2025-01-04	NULL

showing 5 of 8 rows

watch_events5 columns · 26 rows

idinteger · key	viewer_idinteger	video_idinteger	watched_attext	watch_secondsinteger
1	1	1	2025-01-01 10:00:00	300
2	1	2	2025-01-01 11:00:00	450
3	2	1	2025-01-02 12:00:00	600
4	3	3	2025-01-02 13:00:00	120
5	1	4	2025-01-02 14:00:00	700

showing 5 of 26 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

-- D1 retention; observation cutoff is 2025-01-15

SELECT
  v.signup_at AS cohort_day,
  COUNT(*) AS cohort_size,
  SUM(CASE
    WHEN EXISTS(
      SELECT 1
      FROM watch_events w
      WHERE w.viewer_id=v.id
        AND date(w.watched_at)=date(v.signup_at,'+1 days')
    ) THEN 1
    ELSE 0
  END) AS retained_viewers,
  ROUND(100.0*SUM(CASE
    WHEN EXISTS(
      SELECT 1
      FROM watch_events w
      WHERE w.viewer_id=v.id
        AND date(w.watched_at)=date(v.signup_at,'+1 days')
    ) THEN 1
    ELSE 0
  END)/COUNT(*),2) AS retention_pct
FROM viewers v
WHERE v.signup_at<=date('2025-01-15','-1 days')
GROUP BY v.signup_at

Watch for: A seven-day window and exact-day D7 retention are different metrics.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

Define mature cohorts. Each row is one viewer in a cohort that can have a D7 observation.

step-1.sql

SELECT id,signup_at,date(signup_at,'+7 days') AS return_day
FROM viewers
WHERE signup_at<=date('2025-01-15','-7 days');

Mark exact-day returns. EXISTS counts each person at most once, even if several events occur on the return date.

step-2.sql

SELECT
  v.id,
  v.signup_at,
  CASE
    WHEN EXISTS(
      SELECT 1
      FROM watch_events w
      WHERE w.viewer_id=v.id
        AND date(w.watched_at)=date(v.signup_at,'+7 days')
    ) THEN 1
    ELSE 0
  END AS retained
FROM viewers v
WHERE v.signup_at<=date('2025-01-15','-7 days');

Report D7 retention. Retained people divided by all mature cohort members.

step-3.sql

SELECT
  v.signup_at AS cohort_day,
  COUNT(*) AS cohort_size,
  SUM(CASE
    WHEN EXISTS(
      SELECT 1
      FROM watch_events w
      WHERE w.viewer_id=v.id
        AND date(w.watched_at)=date(v.signup_at,'+7 days')
    ) THEN 1
    ELSE 0
  END) AS retained_viewers,
  ROUND(100.0*SUM(CASE
    WHEN EXISTS(
      SELECT 1
      FROM watch_events w
      WHERE w.viewer_id=v.id
        AND date(w.watched_at)=date(v.signup_at,'+7 days')
    ) THEN 1
    ELSE 0
  END)/COUNT(*),2) AS retention_pct
FROM viewers v
WHERE v.signup_at<=date('2025-01-15','-7 days')
GROUP BY v.signup_at