漏斗分析

漏斗分析是用户行为分析的核心方法，用于追踪用户在多个步骤中的转化情况。

📊 支持情况

1. 基础漏斗模型

用户行为漏斗

-- MySQL - 计算各步骤用户数
SELECT
    COUNT(DISTINCT user_id) AS total_users,
    SUM(CASE WHEN event = 'view' THEN 1 ELSE 0 END) AS step1_view,
    SUM(CASE WHEN event = 'add_cart' THEN 1 ELSE 0 END) AS step2_add_cart,
    SUM(CASE WHEN event = 'checkout' THEN 1 ELSE 0 END) AS step3_checkout,
    SUM(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS step4_purchase
FROM user_events
WHERE event_time >= '2024-01-01';

按步骤独立计算

-- MySQL - 各步骤独立用户数
SELECT
    '访问' AS step, COUNT(DISTINCT user_id) AS users
    FROM user_events WHERE event = 'view'
UNION ALL
SELECT '加购', COUNT(DISTINCT user_id)
    FROM user_events WHERE event = 'add_cart'
UNION ALL
SELECT '下单', COUNT(DISTINCT user_id)
    FROM user_events WHERE event = 'checkout'
UNION ALL
SELECT '支付', COUNT(DISTINCT user_id)
    FROM user_events WHERE event = 'purchase';

2. 完整漏斗转化

单用户多步骤漏斗

-- MySQL - 完整漏斗查询
WITH funnel_steps AS (
    SELECT
        user_id,
        MAX(CASE WHEN event = 'view' THEN 1 ELSE 0 END) AS step1,
        MAX(CASE WHEN event = 'add_cart' THEN 1 ELSE 0 END) AS step2,
        MAX(CASE WHEN event = 'checkout' THEN 1 ELSE 0 END) AS step3,
        MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS step4
    FROM user_events
    WHERE event_time >= '2024-01-01'
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_users,
    SUM(step1) AS view_users,
    SUM(step2) AS add_cart_users,
    SUM(step3) AS checkout_users,
    SUM(step4) AS purchase_users,
    ROUND(SUM(step1) / COUNT(*) * 100, 2) AS view_rate,
    ROUND(SUM(step2) / SUM(step1) * 100, 2) AS view_to_cart_rate,
    ROUND(SUM(step3) / SUM(step2) * 100, 2) AS cart_to_checkout_rate,
    ROUND(SUM(step4) / SUM(step3) * 100, 2) AS checkout_to_purchase_rate
FROM funnel_steps;

按日期分时段漏斗

-- MySQL - 每日漏斗
SELECT
    DATE(event_time) AS day,
    COUNT(DISTINCT user_id) AS total_users,
    COUNT(DISTINCT CASE WHEN event = 'view' THEN user_id END) AS view_users,
    COUNT(DISTINCT CASE WHEN event = 'add_cart' THEN user_id END) AS add_cart_users,
    COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END) AS purchase_users
FROM user_events
WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(event_time)
ORDER BY day;

3. 时间窗口漏斗

首次行为漏斗

-- MySQL - 用户首次访问后的转化
WITH first_action AS (
    SELECT
        user_id,
        MIN(event_time) AS first_time
    FROM user_events
    GROUP BY user_id
),
user_funnel AS (
    SELECT
        f.user_id,
        f.first_time,
        MAX(CASE WHEN e.event = 'view' THEN 1 ELSE 0 END) AS did_view,
        MAX(CASE WHEN e.event = 'add_cart' THEN 1 ELSE 0 END) AS did_add_cart,
        MAX(CASE WHEN e.event = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
    FROM first_action f
    JOIN user_events e ON f.user_id = e.user_id
        AND e.event_time >= f.first_time
        AND e.event_time <= DATE_ADD(f.first_time, INTERVAL 7 DAY)
    GROUP BY f.user_id, f.first_time
)
SELECT
    COUNT(*) AS users,
    SUM(did_view) AS viewed,
    SUM(did_add_cart) AS added_cart,
    SUM(did_purchase) AS purchased,
    ROUND(SUM(did_view) / COUNT(*) * 100, 2) AS view_rate,
    ROUND(SUM(did_add_cart) / SUM(did_view) * 100, 2) AS cart_rate,
    ROUND(SUM(did_purchase) / SUM(did_add_cart) * 100, 2) AS purchase_rate
FROM user_funnel;

连续步骤漏斗

-- MySQL - 用户必须完成前一步才能进入下一步
WITH step1 AS (
    SELECT DISTINCT user_id
    FROM user_events
    WHERE event = 'view' AND event_time >= '2024-01-01'
),
step2 AS (
    SELECT DISTINCT user_id
    FROM user_events e
    WHERE event = 'add_cart'
      AND EXISTS (SELECT 1 FROM step1 s WHERE s.user_id = e.user_id)
),
step3 AS (
    SELECT DISTINCT user_id
    FROM user_events e
    WHERE event = 'purchase'
      AND EXISTS (SELECT 1 FROM step2 s WHERE s.user_id = e.user_id)
)
SELECT
    (SELECT COUNT(*) FROM step1) AS step1_users,
    (SELECT COUNT(*) FROM step2) AS step2_users,
    (SELECT COUNT(*) FROM step3) AS step3_users;

4. 实战案例

场景1：电商购买漏斗

-- MySQL
SELECT
    '浏览商品' AS stage,
    COUNT(DISTINCT user_id) AS users,
    100.00 AS pct
FROM user_events WHERE event = 'view' AND event_time >= '2024-01-01'
UNION ALL
SELECT
    '加入购物车',
    COUNT(DISTINCT CASE WHEN event = 'add_cart' THEN user_id END),
    ROUND(COUNT(DISTINCT CASE WHEN event = 'add_cart' THEN user_id END) * 100.0 /
          NULLIF(COUNT(DISTINCT CASE WHEN event = 'view' THEN user_id END), 0), 2)
FROM user_events WHERE event_time >= '2024-01-01'
UNION ALL
SELECT
    '提交订单',
    COUNT(DISTINCT CASE WHEN event = 'checkout' THEN user_id END),
    ROUND(COUNT(DISTINCT CASE WHEN event = 'checkout' THEN user_id END) * 100.0 /
          NULLIF(COUNT(DISTINCT CASE WHEN event = 'add_cart' THEN user_id END), 0), 2)
FROM user_events WHERE event_time >= '2024-01-01'
UNION ALL
SELECT
    '完成支付',
    COUNT(DISTINCT CASE WHEN event = 'pay' THEN user_id END),
    ROUND(COUNT(DISTINCT CASE WHEN event = 'pay' THEN user_id END) * 100.0 /
          NULLIF(COUNT(DISTINCT CASE WHEN event = 'checkout' THEN user_id END), 0), 2)
FROM user_events WHERE event_time >= '2024-01-01';

场景2：注册转化漏斗

-- Hive/MaxCompute
SELECT
    '访问首页' AS stage,
    COUNT(DISTINCT user_id) AS users
FROM user_behavior WHERE event = 'visit'
UNION ALL
SELECT
    '开始注册',
    COUNT(DISTINCT user_id)
FROM user_behavior WHERE event = 'register_start'
UNION ALL
SELECT
    '完成注册',
    COUNT(DISTINCT user_id)
FROM user_behavior WHERE event = 'register_complete'
UNION ALL
SELECT
    '首次下单',
    COUNT(DISTINCT user_id)
FROM user_behavior WHERE event = 'first_order';

场景3：分渠道漏斗

-- MySQL - 按来源渠道分析漏斗
SELECT
    source,
    COUNT(DISTINCT CASE WHEN event = 'view' THEN user_id END) AS view_users,
    COUNT(DISTINCT CASE WHEN event = 'add_cart' THEN user_id END) AS cart_users,
    COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END) AS purchase_users,
    ROUND(COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END) * 100.0 /
          NULLIF(COUNT(DISTINCT CASE WHEN event = 'view' THEN user_id END), 0), 2) AS conversion_rate
FROM user_events
WHERE event_time >= '2024-01-01'
GROUP BY source
ORDER BY conversion_rate DESC;

场景4：流失分析

-- MySQL - 各步骤流失用户
WITH funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event = 'view' THEN 1 ELSE 0 END) AS step1,
        MAX(CASE WHEN event = 'add_cart' THEN 1 ELSE 0 END) AS step2,
        MAX(CASE WHEN event = 'checkout' THEN 1 ELSE 0 END) AS step3,
        MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS step4
    FROM user_events
    GROUP BY user_id
)
SELECT
    COUNT(*) AS total_users,
    SUM(step1) AS reached_step1,
    SUM(CASE WHEN step1 = 1 AND step2 = 0 THEN 1 ELSE 0 END) AS lost_at_step2,
    SUM(CASE WHEN step2 = 1 AND step3 = 0 THEN 1 ELSE 0 END) AS lost_at_step3,
    SUM(CASE WHEN step3 = 1 AND step4 = 0 THEN 1 ELSE 0 END) AS lost_at_step4
FROM funnel;

5. 留存率计算

基础留存率

-- MySQL
SELECT
    DATEDIFF(event_date, install_date) AS days,
    COUNT(*) AS users,
    ROUND(COUNT(*) * 100.0 / FIRST_VALUE(COUNT(*)) OVER (), 2) AS retention_pct
FROM (
    SELECT
        user_id,
        MIN(DATE(event_time)) AS install_date,
        DATE(event_time) AS event_date
    FROM user_events
    WHERE event = 'install'
    GROUP BY user_id, DATE(event_time)
    HAVING DATEDIFF(DATE(event_time), MIN(DATE(event_time))) <= 7
) t
GROUP BY days
ORDER BY days;

多日留存矩阵

-- MySQL
WITH install_cohort AS (
    SELECT user_id, MIN(DATE(install_time)) AS install_date
    FROM user_actions
    GROUP BY user_id
),
user_activity AS (
    SELECT
        i.user_id,
        i.install_date,
        DATEDIFF(DATE(a.action_time), i.install_date) AS days
    FROM install_cohort i
    JOIN user_actions a ON i.user_id = a.user_id
    WHERE DATEDIFF(DATE(a.action_time), i.install_date) BETWEEN 0 AND 6
)
SELECT
    install_date AS cohort,
    SUM(CASE WHEN days = 0 THEN 1 ELSE 0 END) AS day0,
    SUM(CASE WHEN days = 1 THEN 1 ELSE 0 END) AS day1,
    SUM(CASE WHEN days = 2 THEN 1 ELSE 0 END) AS day2,
    SUM(CASE WHEN days = 3 THEN 1 ELSE 0 END) AS day3,
    SUM(CASE WHEN days = 4 THEN 1 ELSE 0 END) AS day4,
    SUM(CASE WHEN days = 5 THEN 1 ELSE 0 END) AS day5,
    SUM(CASE WHEN days = 6 THEN 1 ELSE 0 END) AS day6
FROM user_activity
GROUP BY install_date
ORDER BY install_date;

⚠️ 注意事项

1. 去重逻辑

-- 确保每个用户只计算一次
COUNT(DISTINCT user_id)  -- ✅ 正确
COUNT(*)                 -- ❌ 错误（会重复计数）

2. 时间范围

-- 漏斗分析需要明确时间范围
WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01'

3. 步骤顺序

-- 确保步骤是按时间顺序的
-- 使用窗口函数或自连接验证步骤顺序

📝 练习题

-- 建表
CREATE TABLE user_funnel_events (
    event_id INT,
    user_id INT,
    event_type STRING,
    event_time DATETIME
);

INSERT INTO user_funnel_events VALUES
(1, 101, 'view', '2024-01-14 10:00:00'),
(2, 101, 'add_cart', '2024-01-14 10:05:00'),
(3, 101, 'purchase', '2024-01-14 10:10:00'),
(4, 102, 'view', '2024-01-14 11:00:00'),
(5, 102, 'add_cart', '2024-01-14 11:10:00'),
(6, 103, 'view', '2024-01-14 12:00:00'),
(7, 104, 'view', '2024-01-14 13:00:00'),
(8, 104, 'add_cart', '2024-01-14 13:05:00');

要求：

1. 计算各步骤用户数
2. 计算各步骤转化率
3. 计算整体转化率
4. 分析流失用户

-- 参考答案（MySQL）
-- 1. 各步骤用户数
SELECT
    COUNT(DISTINCT CASE WHEN event_type = 'view' THEN user_id END) AS view_users,
    COUNT(DISTINCT CASE WHEN event_type = 'add_cart' THEN user_id END) AS cart_users,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS purchase_users
FROM user_funnel_events;

-- 2. 各步骤转化率
SELECT
    'view' AS stage, COUNT(DISTINCT user_id) AS users, 100.00 AS pct
FROM user_funnel_events WHERE event_type = 'view'
UNION ALL
SELECT
    'add_cart',
    COUNT(DISTINCT user_id),
    ROUND(COUNT(DISTINCT user_id) * 100.0 / 4, 2)
FROM user_funnel_events WHERE event_type = 'add_cart'
UNION ALL
SELECT
    'purchase',
    COUNT(DISTINCT user_id),
    ROUND(COUNT(DISTINCT user_id) * 100.0 / 4, 2)
FROM user_funnel_events WHERE event_type = 'purchase';

-- 3. 整体转化率
WITH funnel AS (
    SELECT user_id,
           MAX(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS step1,
           MAX(CASE WHEN event_type = 'add_cart' THEN 1 ELSE 0 END) AS step2,
           MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS step3
    FROM user_funnel_events GROUP BY user_id
)
SELECT
    COUNT(*) AS total,
    SUM(step3) / COUNT(*) * 100 AS overall_conversion_pct
FROM funnel;

-- 4. 流失分析
SELECT
    SUM(CASE WHEN step1 = 1 AND step2 = 0 THEN 1 ELSE 0 END) AS lost_at_cart,
    SUM(CASE WHEN step2 = 1 AND step3 = 0 THEN 1 ELSE 0 END) AS lost_at_purchase
FROM (
    SELECT user_id,
           MAX(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS step1,
           MAX(CASE WHEN event_type = 'add_cart' THEN 1 ELSE 0 END) AS step2,
           MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS step3
    FROM user_funnel_events GROUP BY user_id
) t;