milestone 3/4 Cohorts & exact-day retention
Measure whether users return on a specific day after joining.
Video Product Analytics · Move from watch events to daily engagement, rolling trends, and signup-cohort retention.
approach
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.
worked-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_atdata for this milestone
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