顺子の杂货铺
生命不息，折腾不止，且行且珍惜~
        找回密码
当前位置：順子の杂货铺 > BigData > 正文

24-漏斗分析

2026-01-18 分类：BigData / BLOG / Database / SQL 阅读(2) 评论(0)
DMIT VPS

漏斗分析

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

📊 支持情况

数据库COUNTSUMCOUNT DISTINCTWINDOW FUNCTION备注
MySQL✅✅✅✅ 8.0+完整支持
Oracle✅✅✅✅完整支持
ClickHouse✅✅✅✅完整支持
Hologres✅✅✅✅完整支持
MaxCompute✅✅✅✅完整支持
Hive✅✅✅✅完整支持

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;
赞(0)
未经允许不得转载：順子の杂货铺 » 24-漏斗分析
标签：
Avatar photo

shunzi

一半忧伤，一半明媚！喜欢看书，听歌，写字，打游戏，交朋友！
搬瓦工VPS

相关推荐

评论 抢沙发

Avatar photo

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
吐血推荐

New

热门文章

热门标签

sql (48)mysql (22)oracle (21)python (15)Hive (13)MaxCompute (13)Hologres (13)ClickHouse (12)rm (6)password (5)port (5)install (4)linux (4)log (4)join (3)pos (3)google (3)vi (3)tab (3)redis (3)deepin (3)htaccess (2)master (2)centos (2)kangle (2)easypanel (2)list (2)django (2)plsql (2)docker (2)

网站统计

  • 日志总数：88
  • 评论总数：55
  • 标签总数：125
  • 最后更新：2026-01-18

分享创造快乐

联系我们联系我们