如何使用Snowflake SQL实现Cohort群组用户留存分析？

完整可运行Snowflake留存分析SQL #

WITH 
-- 统计每个注册 cohort 的初始用户总量
cohort_size AS (
    SELECT 
        registered_month AS acctCreated,
        COUNT(DISTINCT user_id) AS original_size
    FROM registration
    GROUP BY registered_month
),
-- 活跃用户按月去重，同时过滤超出统计截止时间的记录
active_user_dedup AS (
    SELECT DISTINCT 
        active_month,
        user_id
    FROM activity
    WHERE active_month <= '2021-09-01'
),
-- 关联注册信息和活跃信息，匹配用户所属的注册 cohort
user_cohort_active AS (
    SELECT
        r.registered_month AS acctCreated,
        a.active_month AS Evaluation_Month,
        DATEDIFF(month, r.registered_month, a.active_month) AS months_passed,
        r.user_id
    FROM registration r
    INNER JOIN active_user_dedup a
        ON r.user_id = a.user_id
        AND a.active_month >= r.registered_month
)
-- 最终聚合计算留存指标
SELECT
    u.acctCreated,
    u.Evaluation_Month,
    u.months_passed,
    c.original_size,
    COUNT(DISTINCT u.user_id) AS remaining,
    ROUND(COUNT(DISTINCT u.user_id) * 100.0 / c.original_size, 2) AS retention
FROM user_cohort_active u
INNER JOIN cohort_size c
    ON u.acctCreated = c.acctCreated
GROUP BY 1,2,3,4
ORDER BY 1,2;

逻辑说明 #

• cohort_size 模块：按注册月份分组，计算每个月新注册用户的总量，对应输出字段的original_size
• active_user_dedup 模块：对活跃表做按月去重，避免同一用户当月多次活跃被重复统计，同时按要求过滤掉2021年9月之后的活跃记录
• user_cohort_active 模块：匹配每个用户的注册信息和活跃记录，只保留活跃时间晚于等于注册时间的关联结果，同时计算注册后经过的月数months_passed
• 最终聚合层：按注册月份、统计月份分组，计算对应月份的留存用户数和留存率，留存率默认保留两位小数

原有代码问题修正点 #

1. 补全了DATEDIFF函数的时间单位参数，Snowflake中该函数必须指定month/day等时间单位才能正确计算间隔
2. 补充了注册 cohort 初始用户数的统计逻辑，补全了要求输出的original_size字段
3. 增加了活跃数据去重逻辑，避免重复计数符合业务规则要求
4. 调整了关联逻辑，用内连接替代原有的左连接+非空过滤，执行效率更高
5. 补全了留存率retention的计算逻辑，输出格式符合分析需求

内容的提问来源于stack exchange，提问作者Zhongyi Zhang