用户行为漏斗分析：从曝光到下单的转化追踪
业务背景
促销活动上线后，运营团队最关心的问题往往不是"卖了多少"，而是"哪个环节在漏人"。100 个用户看到了商品，最终只有 3 个下单——是点击率太低，还是加购后放弃了，还是下单页面出了问题？
漏斗分析把用户行为拆解成有序的步骤序列（曝光 → 点击 → 加购 → 下单），在每个步骤统计到达的用户数和转化率，精确定位流失最严重的环节。
典型应用场景：
- 促销活动复盘：活动结束后，对比各商品的漏斗差异，找出表现异常的 SKU
- 页面优化决策：点击率高但加购率低，说明详情页说服力不足；加购率高但下单率低，说明结算流程有阻力
- 实时监控：活动进行中，每分钟刷新漏斗数据，发现异常立即介入
适用场景
| 场景 | 说明 |
|---|
| 电商促销漏斗 | 曝光 → 点击 → 加购 → 下单，找出流失最大的步骤 |
| 注册转化漏斗 | 访问落地页 → 填写表单 → 验证手机 → 完成注册 |
| 内容消费漏斗 | 推送曝光 → 打开 → 阅读完成 → 分享 |
| A/B 测试对比 | 对比实验组和对照组在各步骤的转化差异 |
涉及的 SQL 命令
| 命令 / 函数 | 用途 |
|---|
COUNT(DISTINCT user_id)
COUNT(DISTINCT user_id) | 按步骤统计去重用户数（UV） |
LAG()
LAG() | 获取上一步骤的 UV，计算环比转化率 |
FIRST_VALUE()
FIRST_VALUE() | 获取漏斗第一步的 UV，计算整体转化率 |
PARTITION BY
PARTITION BY | 按商品、时间段等维度分组计算各自的漏斗 |
CREATE DYNAMIC TABLE
CREATE DYNAMIC TABLE | 将漏斗查询封装为自动刷新的实时看板 |
数据架构
外部数据源（埋点 SDK / 日志系统 / Kafka / ...）
│ 实时写入
▼
doc_user_events（用户行为事件表）
│
│ 窗口函数聚合
▼
doc_ads_funnel_overview（Dynamic Table，1 分钟自动刷新）
│
▼
BI 看板 / 运营大屏
事件数据如何实时写入
本文用
INSERT INTO
INSERT INTO
模拟用户行为事件，方便你在测试环境快速复现。生产环境中，用户行为数据通常来自客户端埋点或服务端日志，Lakehouse 提供多种方式持续写入：
| 数据来源 | 推荐方式 | 说明 | 参考文档 |
|---|
| Kafka 消息队列（埋点实时上报） | Pipe 持续导入 | 适合客户端 SDK 将事件发到 Kafka 的场景，毫秒级延迟 | 借助 read_kafka 函数持续导入 |
| 对象存储（日志文件落盘） | Pipe 持续导入 | 适合服务端日志定期写入 OSS / COS / S3 的场景 | 使用 Pipe 持续导入对象存储数据 |
| 业务数据库（订单、点击记录） | Studio 实时同步任务（CDC） | 捕获源库 binlog，毫秒级延迟同步到 Lakehouse | 实时同步任务 |
底表有新事件写入后，Dynamic Table 在下一个刷新周期自动感知并增量计算，漏斗数据无需手动触发更新。
前置准备
建用户行为事件表
CREATE TABLE IF NOT EXISTS doc_user_events (
event_id STRING,
user_id STRING,
session_id STRING,
event_type STRING,
product_id STRING,
event_time TIMESTAMP
);
event_type
event_type
取值为
expose
expose
（曝光）、
click
click
（点击）、
cart
cart
（加购）、
order
order
（下单），代表漏斗的四个步骤。
插入测试事件数据
INSERT INTO doc_user_events VALUES
('E001','U101','S001','expose','P001', CAST('2026-05-28 10:00:00' AS TIMESTAMP)),
('E002','U101','S001','click', 'P001', CAST('2026-05-28 10:01:00' AS TIMESTAMP)),
('E003','U101','S001','cart', 'P001', CAST('2026-05-28 10:03:00' AS TIMESTAMP)),
('E004','U101','S001','order', 'P001', CAST('2026-05-28 10:05:00' AS TIMESTAMP)),
('E005','U102','S002','expose','P001', CAST('2026-05-28 10:10:00' AS TIMESTAMP)),
('E006','U102','S002','click', 'P001', CAST('2026-05-28 10:11:00' AS TIMESTAMP)),
('E007','U102','S002','cart', 'P001', CAST('2026-05-28 10:15:00' AS TIMESTAMP)),
('E008','U103','S003','expose','P002', CAST('2026-05-28 10:20:00' AS TIMESTAMP)),
('E009','U103','S003','click', 'P002', CAST('2026-05-28 10:21:00' AS TIMESTAMP)),
('E010','U104','S004','expose','P001', CAST('2026-05-28 10:30:00' AS TIMESTAMP)),
('E011','U105','S005','expose','P002', CAST('2026-05-28 10:35:00' AS TIMESTAMP)),
('E012','U105','S005','click', 'P002', CAST('2026-05-28 10:36:00' AS TIMESTAMP)),
('E013','U105','S005','order', 'P002', CAST('2026-05-28 10:40:00' AS TIMESTAMP)),
('E014','U106','S006','expose','P003', CAST('2026-05-28 11:00:00' AS TIMESTAMP)),
('E015','U106','S006','click', 'P003', CAST('2026-05-28 11:02:00' AS TIMESTAMP)),
('E016','U106','S006','cart', 'P003', CAST('2026-05-28 11:05:00' AS TIMESTAMP)),
('E017','U106','S006','order', 'P003', CAST('2026-05-28 11:08:00' AS TIMESTAMP)),
('E018','U107','S007','expose','P003', CAST('2026-05-28 11:10:00' AS TIMESTAMP)),
('E019','U108','S008','expose','P001', CAST('2026-05-28 11:15:00' AS TIMESTAMP)),
('E020','U108','S008','click', 'P001', CAST('2026-05-28 11:16:00' AS TIMESTAMP));
场景一：整体漏斗转化率
统计全量用户在各步骤的 UV，计算环比转化率（每步相对上一步）和整体转化率（每步相对曝光）。
WITH funnel AS (
SELECT
event_type,
COUNT(DISTINCT user_id) AS uv,
CASE event_type
WHEN 'expose' THEN 1
WHEN 'click' THEN 2
WHEN 'cart' THEN 3
WHEN 'order' THEN 4
END AS step
FROM doc_user_events
GROUP BY event_type
)
SELECT
step,
event_type,
uv,
LAG(uv) OVER (ORDER BY step) AS prev_uv,
CAST(uv * 100.0 / LAG(uv) OVER (ORDER BY step) AS DECIMAL(5,1)) AS step_cvr,
CAST(uv * 100.0 / FIRST_VALUE(uv) OVER (ORDER BY step) AS DECIMAL(5,1)) AS total_cvr
FROM funnel
ORDER BY step;
+----+----------+--+-------+---------+---------+
|step|event_type|uv|prev_uv|step_cvr |total_cvr|
+----+----------+--+-------+---------+---------+
|1 |expose |8 |NULL |NULL |100.0 |
|2 |click |6 |8 |75.0 |75.0 |
|3 |cart |3 |6 |50.0 |37.5 |
|4 |order |3 |3 |100.0 |37.5 |
+----+----------+--+-------+---------+---------+
结果解读：
- 曝光 → 点击：75% 的用户点击了商品，点击率正常
- 点击 → 加购：只有 50% 的用户加购，是流失最严重的环节——详情页可能说服力不足
- 加购 → 下单：100% 的加购用户完成了下单，结算流程没有阻力
- 整体转化率：8 个曝光用户中有 3 个下单，整体转化率 37.5%
LAG(uv) OVER (ORDER BY step)
LAG(uv) OVER (ORDER BY step)
取上一步的 UV；第一步（expose）没有上一步，
prev_uv
prev_uv
和
step_cvr
step_cvr
为 NULL，属于正常结果。
场景二：按商品分组的漏斗对比
找出各商品的转化差异，定位表现异常的 SKU。
WITH funnel AS (
SELECT
product_id,
event_type,
COUNT(DISTINCT user_id) AS uv,
CASE event_type
WHEN 'expose' THEN 1
WHEN 'click' THEN 2
WHEN 'cart' THEN 3
WHEN 'order' THEN 4
END AS step
FROM doc_user_events
GROUP BY product_id, event_type
)
SELECT
product_id,
step,
event_type,
uv,
CAST(uv * 100.0 / FIRST_VALUE(uv) OVER (PARTITION BY product_id ORDER BY step) AS DECIMAL(5,1)) AS total_cvr
FROM funnel
ORDER BY product_id, step;
+----------+----+----------+--+---------+
|product_id|step|event_type|uv|total_cvr|
+----------+----+----------+--+---------+
|P001 |1 |expose |4 |100.0 |
|P001 |2 |click |3 |75.0 |
|P001 |3 |cart |2 |50.0 |
|P001 |4 |order |1 |25.0 |
|P002 |1 |expose |2 |100.0 |
|P002 |2 |click |2 |100.0 |
|P002 |4 |order |1 |50.0 |
|P003 |1 |expose |2 |100.0 |
|P003 |2 |click |1 |50.0 |
|P003 |3 |cart |1 |50.0 |
|P003 |4 |order |1 |50.0 |
+----------+----+----------+--+---------+
结果解读：
- P001：点击率 75%，但加购到下单只有 25% 整体转化，加购后流失明显
- P002：点击率 100%，且没有加购步骤（用户直接从点击跳到下单）——这是正常现象，说明该商品的购买决策路径较短，或者加购事件未被埋点覆盖
- P003：点击率只有 50%，是三个商品中最低的，详情页吸引力有待提升
PARTITION BY product_id
PARTITION BY product_id
让每个商品独立计算
FIRST_VALUE
FIRST_VALUE
（各自的曝光 UV），转化率不会互相干扰。
场景三：用 Dynamic Table 构建实时漏斗看板
将整体漏斗查询封装为 Dynamic Table，每分钟自动刷新，运营大屏直接查询结果表即可。
CREATE OR REPLACE DYNAMIC TABLE doc_ads_funnel_overview
REFRESH INTERVAL '1' MINUTE VCLUSTER default
AS
WITH funnel AS (
SELECT
event_type,
COUNT(DISTINCT user_id) AS uv,
CASE event_type
WHEN 'expose' THEN 1
WHEN 'click' THEN 2
WHEN 'cart' THEN 3
WHEN 'order' THEN 4
END AS step
FROM doc_user_events
GROUP BY event_type
)
SELECT
step,
event_type,
uv,
CAST(uv * 100.0 / FIRST_VALUE(uv) OVER (ORDER BY step) AS DECIMAL(5,1)) AS total_cvr
FROM funnel;
等待约 1 分钟后查询：
SELECT * FROM doc_ads_funnel_overview ORDER BY step;
+----+----------+--+---------+
|step|event_type|uv|total_cvr|
+----+----------+--+---------+
|1 |expose |8 |100.0 |
|2 |click |6 |75.0 |
|3 |cart |3 |37.5 |
|4 |order |3 |37.5 |
+----+----------+--+---------+
新事件写入后，Dynamic Table 在下一个刷新周期自动更新。模拟新一批用户行为：
INSERT INTO doc_user_events VALUES
('E021','U109','S009','expose','P001', CAST('2026-05-28 12:00:00' AS TIMESTAMP)),
('E022','U109','S009','click', 'P001', CAST('2026-05-28 12:01:00' AS TIMESTAMP)),
('E023','U109','S009','cart', 'P001', CAST('2026-05-28 12:03:00' AS TIMESTAMP)),
('E024','U109','S009','order', 'P001', CAST('2026-05-28 12:05:00' AS TIMESTAMP)),
('E025','U110','S010','expose','P002', CAST('2026-05-28 12:10:00' AS TIMESTAMP)),
('E026','U110','S010','click', 'P002', CAST('2026-05-28 12:11:00' AS TIMESTAMP));
等待约 1 分钟，漏斗自动更新：
+----+----------+--+---------+
|step|event_type|uv|total_cvr|
+----+----------+--+---------+
|1 |expose |10|100.0 |
|2 |click |8 |80.0 |
|3 |cart |4 |40.0 |
|4 |order |4 |40.0 |
+----+----------+--+---------+
曝光 UV 从 8 增至 10，点击率从 75% 升至 80%，整体转化率从 37.5% 升至 40%——新一批用户的转化质量更高。
场景四：按时间段对比漏斗
对比上午和下午的转化差异，判断不同时段的用户质量。
WITH funnel AS (
SELECT
CASE WHEN HOUR(event_time) < 12 THEN '上午(10-12点)' ELSE '下午(12点+)' END AS time_slot,
event_type,
COUNT(DISTINCT user_id) AS uv,
CASE event_type
WHEN 'expose' THEN 1
WHEN 'click' THEN 2
WHEN 'cart' THEN 3
WHEN 'order' THEN 4
END AS step
FROM doc_user_events
GROUP BY time_slot, event_type
)
SELECT
time_slot,
step,
event_type,
uv,
CAST(uv * 100.0 / FIRST_VALUE(uv) OVER (PARTITION BY time_slot ORDER BY step) AS DECIMAL(5,1)) AS total_cvr
FROM funnel
ORDER BY time_slot, step;
+-------------+----+----------+--+---------+
|time_slot |step|event_type|uv|total_cvr|
+-------------+----+----------+--+---------+
|上午(10-12点) |1 |expose |8 |100.0 |
|上午(10-12点) |2 |click |6 |75.0 |
|上午(10-12点) |3 |cart |3 |37.5 |
|上午(10-12点) |4 |order |3 |37.5 |
|下午(12点+) |1 |expose |2 |100.0 |
|下午(12点+) |2 |click |2 |100.0 |
|下午(12点+) |3 |cart |1 |50.0 |
|下午(12点+) |4 |order |1 |50.0 |
+-------------+----+----------+--+---------+
结果解读：下午时段的点击率（100%）和整体转化率（50%）均高于上午（75% / 37.5%），说明下午用户的购买意图更强，适合在下午时段加大推送力度。
PARTITION BY time_slot
PARTITION BY time_slot
让每个时间段独立计算转化率，
FIRST_VALUE(uv) OVER (PARTITION BY time_slot ORDER BY step)
FIRST_VALUE(uv) OVER (PARTITION BY time_slot ORDER BY step)
取各时间段自己的曝光 UV 作为分母。
清理资源
DROP DYNAMIC TABLE IF EXISTS doc_ads_funnel_overview;
DROP TABLE IF EXISTS doc_user_events;
关键点总结
COUNT(DISTINCT user_id)
COUNT(DISTINCT user_id)
是漏斗的基础：每个步骤统计去重用户数（UV），同一用户在同一步骤的多次行为只计一次FIRST_VALUE
FIRST_VALUE
计算整体转化率，LAG
LAG
计算环比转化率：两者配合使用，既能看每步流失，也能看从曝光到当前步骤的累计转化PARTITION BY
PARTITION BY
支持多维度漏斗：按商品、时间段、渠道等维度分组，每个分组独立计算转化率，不互相干扰- 步骤缺失是正常现象：部分用户可能跳过某个步骤（如直接从点击到下单），结果中该步骤的行不存在，不影响其他步骤的计算
- Dynamic Table 实现实时漏斗：将漏斗 SQL 封装为 Dynamic Table，新事件写入后自动增量刷新，无需定时任务
相关文档
