基于月活跃度的客户生命周期状态分析SQL实现问询

基础信息 #

• 数据库：Microsoft SQL Server 2019，通过Azure Data Studio连接
• 涉及表：
  • CALENDAR：包含CALENDAR_DATE、CALENDAR_YEAR等日期维度字段
  • REVENUE ANALYSIS：记录用户投注活动，BANK_TYPE_ID=0代表真实资金投注，含ACTIVITY_DATE、MEMBER_ID等字段
• 核心定义：
  • 活跃：用户当月至少完成1次真实资金投注
  • 生命周期状态规则：
    • NEW：首次进行真实资金投注
    • RETAINED：上月及当月均活跃
    • UNRETAINED：上月活跃但当月不活跃
    • REACTIVATED：上月不活跃但当月活跃
    • LAPSED：上月及当月均不活跃
• 需求：构建视图，包含MEMBER_ID、CALENDAR_YEAR_MONTH、MEMBER_LIFECYCLE_STATUS、LAPSED_MONTHS字段，每个用户从首次投注月起每月生成一行记录，流失状态下显示距上次活跃的累计月数

现有代码问题分析 #

原CTE仅基于当前/上月的硬编码逻辑判断状态，存在两大核心问题：

1. 未生成用户从首次投注到当前的全量月度行，仅包含有投注活动的月份，不符合"每月一行"的需求
2. UNRETAINED和REACTIVATED的判断逻辑错误，未基于用户完整的历史月度活跃状态进行对比

解决方案代码 #

WITH user_monthly_activity AS (
    -- 1. 聚合每个用户每月的真实资金投注活跃状态
    SELECT 
        ra.MEMBER_ID,
        FORMAT(c.CALENDAR_DATE, 'yyyy-MM') AS CALENDAR_YEAR_MONTH,
        DATEFROMPARTS(c.CALENDAR_YEAR, c.CALENDAR_MONTH_NUMBER, 1) AS MONTH_START_DATE,
        CASE WHEN COUNT(ra.MEMBER_ID) > 0 THEN 1 ELSE 0 END AS IS_ACTIVE
    FROM CALENDAR c
    LEFT JOIN [dbo].[REVENUE_ANALYSIS] ra 
        ON c.CALENDAR_DATE = ra.ACTIVITY_DATE 
        AND ra.BANK_TYPE_ID = 0
    GROUP BY ra.MEMBER_ID, c.CALENDAR_YEAR, c.CALENDAR_MONTH_NUMBER, FORMAT(c.CALENDAR_DATE, 'yyyy-MM'), DATEFROMPARTS(c.CALENDAR_YEAR, c.CALENDAR_MONTH_NUMBER, 1)
),
user_monthly_series AS (
    -- 2. 生成每个用户从首次投注月到当前的全量月度序列
    SELECT 
        u.MEMBER_ID,
        FORMAT(DATEADD(MONTH, n.n, u.FIRST_ACTIVITY_MONTH), 'yyyy-MM') AS CALENDAR_YEAR_MONTH,
        DATEADD(MONTH, n.n, u.FIRST_ACTIVITY_MONTH) AS MONTH_START_DATE
    FROM (
        -- 获取每个用户的首次投注月份
        SELECT 
            MEMBER_ID,
            MIN(DATEFROMPARTS(c.CALENDAR_YEAR, c.CALENDAR_MONTH_NUMBER, 1)) AS FIRST_ACTIVITY_MONTH
        FROM [dbo].[REVENUE_ANALYSIS] ra
        JOIN CALENDAR c ON ra.ACTIVITY_DATE = c.CALENDAR_DATE
        WHERE ra.BANK_TYPE_ID = 0
        GROUP BY MEMBER_ID
    ) u
    -- 生成月度序列（此处取100个月，可根据业务需求调整上限）
    JOIN (
        SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.all_columns
    ) n ON DATEADD(MONTH, n.n, u.FIRST_ACTIVITY_MONTH) <= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
),
user_lifecycle_base AS (
    -- 3. 关联活跃状态，计算上月活跃标记、首次投注标记及上次活跃月
    SELECT 
        ums.MEMBER_ID,
        ums.CALENDAR_YEAR_MONTH,
        COALESCE(uma.IS_ACTIVE, 0) AS IS_CURRENT_ACTIVE,
        LAG(COALESCE(uma.IS_ACTIVE, 0)) OVER(PARTITION BY ums.MEMBER_ID ORDER BY ums.MONTH_START_DATE) AS IS_PREVIOUS_ACTIVE,
        CASE WHEN ums.MONTH_START_DATE = u.FIRST_ACTIVITY_MONTH AND COALESCE(uma.IS_ACTIVE, 0) = 1 THEN 1 ELSE 0 END AS IS_FIRST_ACTIVITY,
        MAX(CASE WHEN COALESCE(uma.IS_ACTIVE, 0) = 1 THEN ums.MONTH_START_DATE END) OVER(PARTITION BY ums.MEMBER_ID ORDER BY ums.MONTH_START_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LAST_ACTIVE_MONTH
    FROM user_monthly_series ums
    LEFT JOIN user_monthly_activity uma 
        ON ums.MEMBER_ID = uma.MEMBER_ID 
        AND ums.MONTH_START_DATE = uma.MONTH_START_DATE
    JOIN (
        SELECT MEMBER_ID, MIN(DATEFROMPARTS(c.CALENDAR_YEAR, c.CALENDAR_MONTH_NUMBER, 1)) AS FIRST_ACTIVITY_MONTH
        FROM [dbo].[REVENUE_ANALYSIS] ra
        JOIN CALENDAR c ON ra.ACTIVITY_DATE = c.CALENDAR_DATE
        WHERE ra.BANK_TYPE_ID = 0
        GROUP BY MEMBER_ID
    ) u ON ums.MEMBER_ID = u.MEMBER_ID
)
-- 4. 匹配生命周期状态，计算流失月数
SELECT 
    MEMBER_ID,
    CALENDAR_YEAR_MONTH,
    CASE
        WHEN IS_FIRST_ACTIVITY = 1 THEN 'NEW'
        WHEN IS_CURRENT_ACTIVE = 1 AND IS_PREVIOUS_ACTIVE = 1 THEN 'RETAINED'
        WHEN IS_CURRENT_ACTIVE = 0 AND IS_PREVIOUS_ACTIVE = 1 THEN 'UNRETAINED'
        WHEN IS_CURRENT_ACTIVE = 1 AND IS_PREVIOUS_ACTIVE = 0 THEN 'REACTIVATED'
        ELSE 'LAPSED'
    END AS MEMBER_LIFECYCLE_STATUS,
    CASE
        WHEN IS_CURRENT_ACTIVE = 1 THEN 0
        ELSE DATEDIFF(MONTH, LAST_ACTIVE_MONTH, MONTH_START_DATE)
    END AS LAPSED_MONTHS
FROM user_lifecycle_base
ORDER BY MEMBER_ID, CALENDAR_YEAR_MONTH;

代码逻辑说明 #

1. user_monthly_activity：按用户和月份聚合，标记当月是否有真实资金投注活跃行为
2. user_monthly_series：生成每个用户从首次投注月到当前月的全量月度行，确保满足"每月一行"的需求
3. user_lifecycle_base：通过LAG()函数获取上月活跃状态，标记首次投注月份，记录用户最近一次活跃的月份
4. 最终查询：根据当前月与上月的活跃状态组合，匹配对应的生命周期规则，同时计算流失状态下的累计月数

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