Day-N retention
Retention shows whether people come back, which often says more than raw traffic does. It depends on comparing every later day with a fixed starting group.
How it works
Fix the day-0 cohort and its size once. For each later day, find the distinct active users who are in that same cohort and divide by the original cohort size.
- Build the cohort from the qualifying day-0 event, and
SELECT DISTINCTthe user identity. - Build one set of distinct active users for each observation day.
SAFE_DIVIDE(returning cohort users, original cohort users)gives the retention for that day.
Common mistake
If you divide by each day’s active users, you measure how the audience is made up. Calculating beyond the last available shard reports numbers for days that were never exported.
Worked example
WITH cohort AS (SELECT 'a' AS user_id UNION ALL SELECT 'b' UNION ALL SELECT 'c'),
day2 AS (SELECT 'a' AS user_id UNION ALL SELECT 'c' UNION ALL SELECT 'outside')
SELECT SAFE_DIVIDE(
(SELECT COUNT(*) FROM cohort WHERE user_id IN (SELECT user_id FROM day2)),
(SELECT COUNT(*) FROM cohort)
) AS day2_retentionThe denominator stays at the three users in the original cohort.
Two cohort users return on day 2 and the outside user does not count, so retention is two thirds.