| 首日集合 | 首次行为日 cohort |
|---|---|
| 活跃明细 | 每日活跃用户 |
| 口径 | 活跃行为定义 |
次日留存
次日留存
WITH first_day AS (
SELECT user_id, min(dt) AS cohort_dt
FROM dwd_active_user GROUP BY user_id
)
SELECT f.cohort_dt, count(DISTINCT a.user_id) / count(DISTINCT f.user_id) AS d1_retention
FROM first_day f
LEFT JOIN dwd_active_user a
ON a.user_id = f.user_id AND a.dt = date_add(f.cohort_dt, 1)
GROUP BY f.cohort_dt;「活跃」定义（登录/下单/浏览）不同，留存数字差异大，跨团队对比先对齐定义。