| 要素 | 步骤序列、时间窗、去重主体 |
|---|---|
| 实现 | 每步各取集合，再按主体关联 |
| 输出 | 到达人数、转化率、流失率 |
| 注意 | 时间窗要覆盖完整决策周期 |
两步漏斗
两步漏斗
WITH v AS (
SELECT user_id, MIN(dt) AS view_dt FROM dwd_page_view_di WHERE page = 'detail' GROUP BY user_id
), o AS (
SELECT user_id, MIN(dt) AS order_dt FROM dwd_trade_order_di GROUP BY user_id
)
SELECT COUNT(1) AS view_users,
COUNT(CASE WHEN order_dt >= view_dt AND order_dt <= DATE_ADD(view_dt, 7) THEN 1 END) AS ordered_users
FROM v LEFT JOIN o ON v.user_id = o.user_id;漏斗步骤顺序与时间窗要写进口径文档：同一份数据用不同窗口能得到差别很大的转化率。