02_funnel_analysis.sql

-- ============================================================
-- 查询02：活动漏斗转化分析
-- 业务问题：一次push活动中，从收到推送到最终下单，每一步流失了多少人？
--           哪个环节是最大的流失点？
-- ============================================================

SELECT
    -- 第一层：收到推送的人数（活动覆盖人数）
    COUNT(DISTINCT CASE WHEN event_type = 'push_received'  THEN user_id END) AS 收到推送,

    -- 第二层：点击推送的人数
    COUNT(DISTINCT CASE WHEN event_type = 'push_clicked'   THEN user_id END) AS 点击推送,

    -- 第三层：进入活动页的人数
    COUNT(DISTINCT CASE WHEN event_type = 'page_viewed'    THEN user_id END) AS 进入活动页,

    -- 第四层：领取优惠券的人数
    COUNT(DISTINCT CASE WHEN event_type = 'coupon_claimed' THEN user_id END) AS 领取优惠券,

    -- 第五层：完成下单的人数
    COUNT(DISTINCT CASE WHEN event_type = 'order_placed'   THEN user_id END) AS 完成下单,

    -- 整体转化率：从收到推送到最终下单
    ROUND(
        COUNT(DISTINCT CASE WHEN event_type = 'order_placed'  THEN user_id END) * 100.0
        / NULLIF(COUNT(DISTINCT CASE WHEN event_type = 'push_received' THEN user_id END), 0),
        1
    ) AS 整体转化率百分比

FROM events
WHERE event_time >= '2025-03-10 00:00:00'
  AND event_time <  '2025-03-11 00:00:00';  -- 只看这次活动当天的数据

-- ============================================================
-- 说明：
-- COUNT(DISTINCT user_id) 而不是 COUNT(*) 的原因：
--   同一个用户可能触发多次同类事件，用DISTINCT确保每人只算一次
--
-- NULLIF(..., 0) 的原因：
--   防止分母为0时报错
--
-- 结果解读示例：
-- 收到推送5人 → 点击4人（流失20%）→ 进入活动页3人（流失25%）
-- → 领券2人（流失33%）→ 下单1人（流失50%）
--
-- 如果"领券→下单"流失最严重 → 优惠券门槛可能太高，或有效期太短
-- 如果"点击→进入页面"流失严重 → 落地页加载太慢，或内容与push描述不符
-- ============================================================