下面给出一份可直接运行并易于调整的留存分析 SQL。它实现以下要求：
- 指标：7日留存（+1 至 +7 天任意一天发生 app_open 视为留存）
- 新增定义：用户的“首日 signup”为其全生命周期第一次 signup 的日期
- 时间范围：最近 30 天的新增用户，且保证7日观察窗完整（即 signup_date 在 [今天-30天, 今天-7天]）
- 过滤条件：platform='ios' AND country='CN'
- AB 实验：experiment_id='onboarding_v2'，按 variant 分组
- 产出：按 cohort_date（新增日期）与 variant 粒度输出新增人数、7日留存人数与留存率；同时输出 D1~D7 的逐日留存（去重用户数与占比），便于诊断
注意：以下 SQL 假设
- 事件表 app_events(user_id, event_name, event_timestamp, platform, country)
- 实验分配表 user_experiments(user_id, experiment_id, variant)
- 使用支持 DATEADD、DATEDIFF 函数的数仓（如 Snowflake/Redshift）。若为 BigQuery/Presto，请将日期函数替换为对应语法。
SQL
WITH params AS (
SELECT
DATEADD(day, -30, CURRENT_DATE) AS cohort_start_date,
DATEADD(day, -7, CURRENT_DATE) AS cohort_end_date
),
-- 仅保留 iOS/CN 的 signup 事件
signups AS (
SELECT
e.user_id,
CAST(e.event_timestamp AS DATE) AS signup_date
FROM app_events e
WHERE e.event_name = 'signup'
AND e.platform = 'ios'
AND e.country = 'CN'
),
-- 取用户全生命周期的首日 signup
first_signup AS (
SELECT
user_id,
MIN(signup_date) AS first_signup_date
FROM signups
GROUP BY user_id
),
-- 取最近30天且可完整观察7天留存的 cohort（[今天-30, 今天-7]）
cohort_users AS (
SELECT
fs.user_id,
fs.first_signup_date AS cohort_date
FROM first_signup fs
JOIN params p
ON fs.first_signup_date BETWEEN p.cohort_start_date AND p.cohort_end_date
),
-- 限定在指定实验中的用户，并按 variant 分组
cohort_with_variant AS (
SELECT
cu.user_id,
cu.cohort_date,
ue.variant
FROM cohort_users cu
JOIN user_experiments ue
ON ue.user_id = cu.user_id
AND ue.experiment_id = 'onboarding_v2'
),
-- 仅保留 iOS/CN 的 app_open 事件
app_opens AS (
SELECT
e.user_id,
CAST(e.event_timestamp AS DATE) AS open_date
FROM app_events e
WHERE e.event_name = 'app_open'
AND e.platform = 'ios'
AND e.country = 'CN'
),
-- 连接 +1~+7 天内的 app_open，计算相对首日的天数
user_open_1_7 AS (
SELECT
cv.cohort_date,
cv.variant,
cv.user_id,
DATEDIFF(day, cv.cohort_date, ao.open_date) AS days_since_signup
FROM cohort_with_variant cv
LEFT JOIN app_opens ao
ON ao.user_id = cv.user_id
AND ao.open_date BETWEEN DATEADD(day, 1, cv.cohort_date) AND DATEADD(day, 7, cv.cohort_date)
),
-- 汇总：新增、7日留存，以及 D1~D7 逐日留存（均为去重用户数）
cohort_agg AS (
SELECT
cohort_date,
variant,
COUNT(DISTINCT user_id) AS new_users,
COUNT(DISTINCT CASE WHEN days_since_signup BETWEEN 1 AND 7 THEN user_id END) AS retained_7d_users,
COUNT(DISTINCT CASE WHEN days_since_signup = 1 THEN user_id END) AS d1_users,
COUNT(DISTINCT CASE WHEN days_since_signup = 2 THEN user_id END) AS d2_users,
COUNT(DISTINCT CASE WHEN days_since_signup = 3 THEN user_id END) AS d3_users,
COUNT(DISTINCT CASE WHEN days_since_signup = 4 THEN user_id END) AS d4_users,
COUNT(DISTINCT CASE WHEN days_since_signup = 5 THEN user_id END) AS d5_users,
COUNT(DISTINCT CASE WHEN days_since_signup = 6 THEN user_id END) AS d6_users,
COUNT(DISTINCT CASE WHEN days_since_signup = 7 THEN user_id END) AS d7_users
FROM user_open_1_7
GROUP BY cohort_date, variant
)
SELECT
cohort_date,
variant,
new_users,
retained_7d_users,
CASE WHEN new_users > 0 THEN retained_7d_users::float / new_users ELSE 0 END AS retained_7d_rate,
-- 逐日留存占比（用于诊断，可选）
d1_users,
CASE WHEN new_users > 0 THEN d1_users::float / new_users ELSE 0 END AS d1_rate,
d2_users,
CASE WHEN new_users > 0 THEN d2_users::float / new_users ELSE 0 END AS d2_rate,
d3_users,
CASE WHEN new_users > 0 THEN d3_users::float / new_users ELSE 0 END AS d3_rate,
d4_users,
CASE WHEN new_users > 0 THEN d4_users::float / new_users ELSE 0 END AS d4_rate,
d5_users,
CASE WHEN new_users > 0 THEN d5_users::float / new_users ELSE 0 END AS d5_rate,
d6_users,
CASE WHEN new_users > 0 THEN d6_users::float / new_users ELSE 0 END AS d6_rate,
d7_users,
CASE WHEN new_users > 0 THEN d7_users::float / new_users ELSE 0 END AS d7_rate
FROM cohort_agg
ORDER BY cohort_date, variant;
说明与可调项
- 观察窗完整性：为避免右删截导致的低估，cohort_end_date 设为今天-7天。若需查看更近 cohort，可放宽到今天，但要注明结果受右删截影响。
- 新增口径：使用全生命周期最早的 signup 作为新增日，确保“新增=首日 signup”。若库内 signup 可能重复或有历史缺失，请先构建“首签约表”以保证一致性。
- 实验分配：假设每个用户在该实验下只有一个 variant。若存在多次分配且带有时间戳，需要在 user_experiments 中先用窗口函数挑选 signup 当时生效的一条记录。
- 性能建议：为 app_events 建立分区/聚簇（按 event_date、event_name）并尽量在 CTE 中先筛选 event_name、平台与国家，以减少扫描量。
