如何用SQL实现带30天延迟的注册-购买转化漏斗计算

你的SQL无法得到正确结果，存在以下几个硬伤：

• 字段名和表结构不匹配：语句中引用的signups_month、purchase_month和实际表字段month_signup、month_purchase命名不一致，直接执行会报字段不存在错误
• 未做跨月匹配：当前逻辑是在同一行数据里判断时间范围，本质还是计算同月注册同月购买的转化率，完全没实现「下一个月购买数对应当月注册数」的延迟关联逻辑
• 类型不匹配：你的月份字段是两位数字/字符串格式（比如01代表1月），不是标准日期类型，直接调用DATE_ADD做日期间隔计算会触发类型错误，也不符合自然月偏移的业务规则

你的业务逻辑本质是注册月M的转化率 = M+1月的购买用户数 / M月的注册用户数 * 100，只需要把购买月份向前偏移1个月，和注册月份做等值关联即可计算，不需要做复杂的时间区间判断。
针对你给出的两位数字月份存储格式，可使用如下SQL：

WITH monthly_signup AS (
  -- 聚合去重，避免同注册月对应多条购买记录时注册数被重复统计
  SELECT
    month_signup,
    nb_signups
  FROM 你的业务表名
  GROUP BY month_signup, nb_signups
),
offset_purchase AS (
  -- 购买月减1，对齐对应的注册月
  SELECT
    LPAD(CAST(month_purchase - 1 AS STRING), 2, '0') AS signup_match_month,
    nb_purchases
  FROM 你的业务表名
)
SELECT
  s.month_signup,
  SAFE_DIVIDE(p.nb_purchases, s.nb_signups) * 100 AS sign_up_to_purchase_ratio
FROM monthly_signup s
LEFT JOIN offset_purchase p
  ON s.month_signup = p.signup_match_month
ORDER BY s.month_signup

如果你的月份字段是标准DATE类型或者yyyy-MM格式的字符串，只需要把购买月偏移逻辑替换为DATE_SUB(month_purchase, INTERVAL 1 MONTH)后格式化对齐到注册月的格式即可，不需要做数值减1的计算。

用你给出的3条测试数据运行上述SQL，得到的结果完全符合预期：

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