BigQuery中SQL实现时间范围内月度用户留存计算方案咨询

核心思路 #

不用SQL循环，通过用户首次活跃标记+月份维度关联的集合操作实现，效率更高且逻辑清晰。核心是先确定每个用户的「新用户归属月」，再跟踪其后续各月的活跃状态，从而统计留存、流失、忠诚用户数。

1. 标记用户首次活跃月份 #

从全量交易/行为数据中，提取每个用户的首次活跃时间并按月份聚合，确定新用户归属的月份：

WITH user_first_active AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(activity_time)) AS first_active_month
    FROM
        user_behavior  -- 替换为你的交易/行为数据表名
    WHERE
        activity_time BETWEEN '2016-01-01' AND '2018-06-01'
    GROUP BY
        user_id
),

2. 生成统计周期内的所有月份 #

生成2016年1月至2018年6月的所有月份，作为统计的时间维度：

all_stat_months AS (
    SELECT
        GENERATE_SERIES(
            '2016-01-01'::DATE,
            '2018-06-01'::DATE,
            '1 month'::INTERVAL
        ) AS stat_month
),

数据库适配说明：

• MySQL：用递归CTE生成月份，替换GENERATE_SERIES
• Oracle：用SELECT ADD_MONTHS('2016-01-01', LEVEL-1) FROM DUAL CONNECT BY LEVEL <= 30（30为总月数）

3. 统计每月活跃用户 #

提取每个月有交易/行为记录的用户列表：

monthly_active_users AS (
    SELECT
        user_id,
        DATE_TRUNC('month', activity_time) AS active_month
    FROM
        user_behavior
    WHERE
        activity_time BETWEEN '2016-01-01' AND '2018-06-01'
    GROUP BY
        user_id, DATE_TRUNC('month', activity_time)
)

4. 计算留存、流失与忠诚用户数 #

关联上述CTE，统计每个月新用户在后续各月的相关指标：

SELECT
    ufa.first_active_month AS new_user_month,
    am.stat_month,
    COUNT(DISTINCT ufa.user_id) AS total_new_users,  -- 当月新用户总数
    COUNT(DISTINCT mau.user_id) AS retained_users,   -- 留存用户：新用户在统计月仍活跃
    -- 流失用户：新用户在统计月的前一个月无活跃（可根据业务调整流失判定规则）
    COUNT(DISTINCT CASE 
        WHEN NOT EXISTS (
            SELECT 1 FROM monthly_active_users mau2 
            WHERE mau2.user_id = ufa.user_id 
            AND mau2.active_month = am.stat_month - INTERVAL '1 month'
        ) THEN ufa.user_id 
    END) AS churned_users,
    -- 忠诚用户：连续3个月保持活跃（可调整连续活跃的月数）
    COUNT(DISTINCT CASE 
        WHEN EXISTS (
            SELECT 1 FROM monthly_active_users mau2 
            WHERE mau2.user_id = ufa.user_id 
            AND mau2.active_month BETWEEN am.stat_month - INTERVAL '2 months' AND am.stat_month
        ) THEN ufa.user_id 
    END) AS loyal_users
FROM
    user_first_active ufa
CROSS JOIN
    all_stat_months am
LEFT JOIN
    monthly_active_users mau ON ufa.user_id = mau.user_id AND mau.active_month = am.stat_month
WHERE
    am.stat_month >= ufa.first_active_month
    AND am.stat_month <= '2018-06-01'::DATE
GROUP BY
    ufa.first_active_month, am.stat_month
ORDER BY
    ufa.first_active_month, am.stat_month;

关键调整点 #

• 流失规则：示例中以上月无活跃为流失标准，若业务要求连续N个月无活跃才算流失，可修改CASE中的条件。
• 忠诚用户定义：可调整连续活跃的月数（比如把INTERVAL '2 months'改成INTERVAL '5 months'，即连续6个月活跃）。
• 数据库语法：不同数据库的日期处理函数略有差异，比如MySQL用DATE_FORMAT(activity_time, '%Y-%m-01')替代DATE_TRUNC。

内容的提问来源于stack exchange，提问作者Erika Tran