Approach: compute 30-day rolling retention per cohort (cohort = signup_date), measuring for each cohort the fraction of users active on each day 1..30 after signup. Fit a simple linear slope across days 1..30 per cohort and return top 5 cohorts with largest positive slope. For large scale use pre-aggregations (daily_active_by_user), partitioned/clustering tables, and dbt materializations.BigQuery SQL (assumes tables: users(user_id, signup_date) and events(user_id, event_date); dates are DATE):sql
-- 1) Pre-aggregate: daily active users per user (run as a scheduled job / materialized view)
CREATE OR REPLACE TABLE project.dataset.daily_user_active AS
SELECT user_id, DATE(event_timestamp) AS event_date
FROM project.dataset.events
GROUP BY user_id, event_date;
-- 2) Compute cohort retention days 1..30 and slope
WITH cohorts AS (
SELECT user_id, signup_date
FROM project.dataset.users
),
user_activity AS (
SELECT d.user_id, d.event_date, c.signup_date,
DATE_DIFF(d.event_date, c.signup_date, DAY) AS day_since_signup
FROM project.dataset.daily_user_active d
JOIN cohorts c USING(user_id)
WHERE d.event_date BETWEEN c.signup_date AND DATE_ADD(c.signup_date, INTERVAL 30 DAY)
),
cohort_day AS (
SELECT signup_date AS cohort_date,
day_since_signup,
COUNT(DISTINCT user_id) AS active_users
FROM user_activity
GROUP BY cohort_date, day_since_signup
),
cohort_sizes AS (
SELECT signup_date AS cohort_date, COUNT(DISTINCT user_id) AS cohort_size
FROM cohorts
GROUP BY signup_date
),
retention AS (
SELECT cd.cohort_date,
cd.day_since_signup,
SAFE_DIVIDE(cd.active_users, cs.cohort_size) AS retention_rate
FROM cohort_day cd
JOIN cohort_sizes cs USING(cohort_date)
WHERE cd.day_since_signup BETWEEN 1 AND 30
),
slope_per_cohort AS (
-- linear regression slope: cov(x,y)/var(x)
SELECT cohort_date,
(SUM((x - avg_x) * (y - avg_y)) / NULLIF(SUM((x - avg_x)*(x - avg_x)),0)) AS slope
FROM (
SELECT cohort_date,
day_since_signup AS x,
retention_rate AS y,
AVG(day_since_signup) OVER (PARTITION BY cohort_date) AS avg_x,
AVG(retention_rate) OVER (PARTITION BY cohort_date) AS avg_y
FROM retention
)
GROUP BY cohort_date
)
SELECT cohort_date, slope
FROM slope_per_cohort
ORDER BY slope DESC
LIMIT 5;
