【PostgreSQL数据分析实战：从数据清洗到可视化全流程】电商数据分析案例-9.2 流量转化漏斗分析

👉 点击关注不迷路
👉 点击关注不迷路
👉 点击关注不迷路

---

9.2 流量转化漏斗分析：从数据清洗到可视化全流程实战

一、背景与目标

在电商运营中，流量转化漏斗分析是评估用户行为路径健康度的核心手段。

• 通过分析用户从进入网站到完成购买的全流程转化率，能够精准定位流失瓶颈，优化运营策略。
• 本章将结合PostgreSQL数据库，详细演示如何构建电商流量转化漏斗模型，涵盖数据清洗、指标计算、可视化展示及优化建议。

二、数据准备与清洗

2.1 数据来源与字段说明

本案例使用某电商平台2023年Q3的用户行为数据，包含以下核心字段：

• user_id：用户唯一标识
• event_type：行为类型（pv-页面浏览、cart-加入购物车、order-提交订单、pay-支付成功）
• event_time：行为发生时间
• product_id：商品ID
• category_id：商品类目ID

2.2 数据清洗步骤

2.2.1 去除无效数据

-- 创建原始用户行为表 raw_user_behavior
CREATE TABLE IF NOT EXISTS raw_user_behavior (id SERIAL PRIMARY KEY,  -- 自增主键用于唯一标识记录user_id BIGINT NOT NULL,  -- 用户唯一标识（假设用户ID为大整数）event_type VARCHAR(10) NOT NULL CHECK (event_type IN ('pv', 'cart', 'order', 'pay')),  -- 行为类型约束event_time TIMESTAMP NOT NULL,  -- 行为发生时间（带时间戳）product_id BIGINT NOT NULL,  -- 商品ID（假设商品ID为大整数）category_id BIGINT NOT NULL  -- 商品类目ID（假设类目ID为大整数）
);-- 插入100条测试数据（模拟2023年Q3电商用户行为）
INSERT INTO raw_user_behavior (user_id, event_type, event_time, product_id, category_id)
SELECT -- 生成1-20的随机用户ID（模拟20个活跃用户）floor(random() * 20 + 1)::BIGINT AS user_id,-- 按比例生成行为类型（pv:60%, cart:20%, order:10%, pay:10%）CASE WHEN random() < 0.6 THEN 'pv'WHEN random() < 0.8 THEN 'cart'WHEN random() < 0.9 THEN 'order'ELSE 'pay'END AS event_type,-- 生成2023-07-01到2023-09-30之间的随机时间'2023-07-01'::TIMESTAMP + random() * ('2023-09-30'::TIMESTAMP - '2023-07-01'::TIMESTAMP) AS event_time,-- 生成1-50的随机商品ID（模拟50款商品）floor(random() * 50 + 1)::BIGINT AS product_id,-- 生成1-10的随机类目ID（模拟10个商品类目）floor(random() * 10 + 1)::BIGINT AS category_id
FROM generate_series(1, 100);  -- 生成100条记录-- 过滤异常行为类型（保留有效行为）
CREATE TABLE clean_user_behavior AS
SELECT user_id, event_type, event_time, product_id, category_id
FROM raw_user_behavior
WHERE event_type IN ('pv', 'cart', 'order', 'pay');

2.2.2 处理时间格式

-- 将时间戳转换为日期时间类型
-- 步骤1：将错误的TIMESTAMP类型转回BIGINT（恢复原始毫秒时间戳）
ALTER TABLE clean_user_behavior
ALTER COLUMN event_time 
TYPE BIGINT 
USING EXTRACT(EPOCH FROM event_time)::BIGINT * 1000;  -- 秒级时间戳转毫秒（*1000）-- 步骤2：重新转换为正确的TIMESTAMP类型
ALTER TABLE clean_user_behavior
ALTER COLUMN event_time 
TYPE TIMESTAMP 
USING TO_TIMESTAMP(event_time::DOUBLE PRECISION / 1000);  -- 毫秒转秒（/1000）

2.2.3 剔除重复记录

-- 按用户ID、行为类型、时间去重
CREATE TABLE unique_user_behavior AS
SELECT DISTINCT ON (user_id, event_type, event_time) *
FROM clean_user_behavior
ORDER BY user_id, event_type, event_time;

三、漏斗模型构建

3.1 定义转化路径

本案例分析用户从浏览商品→加入购物车→提交订单→支付成功的核心转化路径，各阶段定义如下：

• 1. 浏览（pv）：用户访问商品详情页
• 2. 加购（cart）：用户将商品加入购物车
• 3. 下单（order）：用户提交订单
• 4. 支付（pay）：用户完成支付

3.2 计算各阶段转化率

3.2.1 基础指标统计

-- 统计各行为总数
SELECT event_type,COUNT(DISTINCT user_id) AS uv,COUNT(*) AS pv
FROM unique_user_behavior
GROUP BY event_type;

3.2.2 漏斗转化率计算

-- 使用窗口函数计算各阶段转化率
WITH funnel_base AS (SELECT user_id,MAX(CASE WHEN event_type = 'pv' THEN 1 ELSE 0 END) AS has_pv,MAX(CASE WHEN event_type = 'cart' THEN 1 ELSE 0 END) AS has_cart,MAX(CASE WHEN event_type = 'order' THEN 1 ELSE 0 END) AS has_order,MAX(CASE WHEN event_type = 'pay' THEN 1 ELSE 0 END) AS has_payFROM unique_user_behaviorGROUP BY user_id
)
SELECT '浏览' AS stage,COUNT(*) AS uv,NULL AS conversion_rate
FROM funnel_base
WHERE has_pv = 1UNION ALLSELECT '加购' AS stage,COUNT(*) AS uv,ROUND(COUNT(*)::NUMERIC / (SELECT COUNT(*) FROM funnel_base WHERE has_pv = 1), 4) AS conversion_rate
FROM funnel_base
WHERE has_cart = 1UNION ALLSELECT '下单' AS stage,COUNT(*) AS uv,ROUND(COUNT(*)::NUMERIC / (SELECT COUNT(*) FROM funnel_base WHERE has_cart = 1), 4) AS conversion_rate
FROM funnel_base
WHERE has_order = 1UNION ALLSELECT '支付' AS stage,COUNT(*) AS uv,ROUND(COUNT(*)::NUMERIC / (SELECT COUNT(*) FROM funnel_base WHERE has_order = 1), 4) AS conversion_rate
FROM funnel_base
WHERE has_pay = 1;

3.3 结果展示

四、多维度分析

4.1 按商品类目分析

-- 创建商品类目表（存储类目ID与名称的映射）
CREATE TABLE IF NOT EXISTS product_category (category_id BIGINT PRIMARY KEY,  -- 类目ID（与unique_user_behavior的category_id关联）category_name VARCHAR(50) NOT NULL  -- 类目名称（如"电子产品"、"服装"等）
);-- 插入10个模拟类目（假设unique_user_behavior的category_id范围是1-10）
INSERT INTO product_category (category_id, category_name)
VALUES(1, '电子产品'),(2, '服装鞋包'),(3, '家居用品'),(4, '美妆个护'),(5, '母婴玩具'),(6, '食品饮料'),(7, '图书音像'),(8, '运动户外'),(9, '汽车用品'),(10, '办公用品');-- 统计各商品类目漏斗转化率
WITH category_funnel AS (SELECT category_id,user_id,MAX(CASE WHEN event_type = 'pv' THEN 1 ELSE 0 END) AS has_pv,MAX(CASE WHEN event_type = 'cart' THEN 1 ELSE 0 END) AS has_cart,MAX(CASE WHEN event_type = 'order' THEN 1 ELSE 0 END) AS has_order,MAX(CASE WHEN event_type = 'pay' THEN 1 ELSE 0 END) AS has_payFROM unique_user_behaviorGROUP BY category_id, user_id
),
-- 子查询：先计算各阶段UV（生成别名）
funnel_metrics AS (SELECT c.category_name,COUNT(DISTINCT CASE WHEN has_pv = 1 THEN user_id END) AS pv_uv,COUNT(DISTINCT CASE WHEN has_cart = 1 THEN user_id END) AS cart_uv,COUNT(DISTINCT CASE WHEN has_order = 1 THEN user_id END) AS order_uv,COUNT(DISTINCT CASE WHEN has_pay = 1 THEN user_id END) AS pay_uvFROM category_funnel cfJOIN product_category c ON cf.category_id = c.category_idGROUP BY c.category_name
)
-- 外部查询：使用子查询的别名计算转化率
SELECT category_name,pv_uv,cart_uv,order_uv,pay_uv,ROUND(cart_uv::NUMERIC / NULLIF(pv_uv, 0), 4) AS cart_conversion,ROUND(order_uv::NUMERIC / NULLIF(cart_uv, 0), 4) AS order_conversion,ROUND(pay_uv::NUMERIC / NULLIF(order_uv, 0), 4) AS pay_conversion
FROM funnel_metrics
ORDER BY category_name;

4.2 按用户类型分析

-- 区分新老用户漏斗转化率
WITH user_type_funnel AS (SELECT u.user_id,u.user_type,MAX(CASE WHEN event_type = 'pv' THEN 1 ELSE 0 END) AS has_pv,MAX(CASE WHEN event_type = 'cart' THEN 1 ELSE 0 END) AS has_cart,MAX(CASE WHEN event_type = 'order' THEN 1 ELSE 0 END) AS has_order,MAX(CASE WHEN event_type = 'pay' THEN 1 ELSE 0 END) AS has_payFROM unique_user_behavior ubJOIN user_profile u ON ub.user_id = u.user_idGROUP BY u.user_id, u.user_type
),
-- 子查询：先计算各阶段UV（生成别名）
funnel_metrics AS (SELECT user_type,COUNT(DISTINCT CASE WHEN has_pv = 1 THEN user_id END) AS pv_uv,COUNT(DISTINCT CASE WHEN has_cart = 1 THEN user_id END) AS cart_uv,COUNT(DISTINCT CASE WHEN has_order = 1 THEN user_id END) AS order_uv,COUNT(DISTINCT CASE WHEN has_pay = 1 THEN user_id END) AS pay_uvFROM user_type_funnelGROUP BY user_type
)
-- 外部查询：使用子查询的别名计算转化率
SELECT user_type,pv_uv,cart_uv,order_uv,pay_uv,ROUND(cart_uv::NUMERIC / NULLIF(pv_uv, 0), 4) AS cart_conversion,  -- 防除零错误ROUND(order_uv::NUMERIC / NULLIF(cart_uv, 0), 4) AS order_conversion,ROUND(pay_uv::NUMERIC / NULLIF(order_uv, 0), 4) AS pay_conversion
FROM funnel_metrics
ORDER BY user_type;

五、可视化展示

5.1 漏斗图

使用Apache Superset生成漏斗图，直观展示各阶段转化率：

-- 生成漏斗图数据
-- 定义funnel_base CTE（统计用户各阶段行为标记）
WITH funnel_base AS (SELECT user_id,MAX(CASE WHEN event_type = 'pv' THEN 1 ELSE 0 END) AS has_pv,  -- 标记是否浏览MAX(CASE WHEN event_type = 'cart' THEN 1 ELSE 0 END) AS has_cart,  -- 标记是否加购MAX(CASE WHEN event_type = 'order' THEN 1 ELSE 0 END) AS has_order,  -- 标记是否下单MAX(CASE WHEN event_type = 'pay' THEN 1 ELSE 0 END) AS has_pay  -- 标记是否支付FROM unique_user_behavior  -- 依赖的用户行为表（需提前创建）GROUP BY user_id  -- 按用户分组统计
)
-- 主查询：生成漏斗阶段UV数据
SELECT '浏览' AS stage,COUNT(DISTINCT CASE WHEN has_pv = 1 THEN user_id END) AS uv
FROM funnel_baseUNION ALL
SELECT '加购' AS stage,COUNT(DISTINCT CASE WHEN has_cart = 1 THEN user_id END) AS uv
FROM funnel_baseUNION ALL
SELECT '下单' AS stage,COUNT(DISTINCT CASE WHEN has_order = 1 THEN user_id END) AS uv
FROM funnel_baseUNION ALL
SELECT '支付' AS stage,COUNT(DISTINCT CASE WHEN has_pay = 1 THEN user_id END) AS uv
FROM funnel_base;

5.2 转化率趋势图

通过折线图展示各阶段转化率随时间的变化：

-- 按天统计转化率
-- 使用公共表表达式 (CTE) 先计算每日各环节的独立用户数（UV）
WITH daily_uv AS (SELECT -- 按天截断事件时间，作为统计日期（格式：年-月-日 00:00:00）DATE_TRUNC('day', event_time) AS event_date,-- 计算「浏览」行为的独立用户数：当事件类型为 'pv' 时，统计去重的用户IDCOUNT(DISTINCT CASE WHEN event_type = 'pv' THEN user_id END) AS pv_uv,-- 计算「加购」行为的独立用户数：当事件类型为 'cart' 时，统计去重的用户IDCOUNT(DISTINCT CASE WHEN event_type = 'cart' THEN user_id END) AS cart_uv,-- 计算「下单」行为的独立用户数：当事件类型为 'order' 时，统计去重的用户IDCOUNT(DISTINCT CASE WHEN event_type = 'order' THEN user_id END) AS order_uv,-- 计算「支付」行为的独立用户数：当事件类型为 'pay' 时，统计去重的用户IDCOUNT(DISTINCT CASE WHEN event_type = 'pay' THEN user_id END) AS pay_uvFROM unique_user_behavior  -- 从用户行为明细表取数-- 按统计日期分组（必须与 DATE_TRUNC 结果一致）GROUP BY DATE_TRUNC('day', event_time)
)
-- 主查询：计算各环节转化率
SELECT event_date,  -- 统计日期pv_uv,       -- 浏览环节独立用户数cart_uv,     -- 加购环节独立用户数order_uv,    -- 下单环节独立用户数pay_uv,      -- 支付环节独立用户数-- 加购转化率：加购UV / 浏览UV（保留4位小数，避免除零错误）ROUND(cart_uv::NUMERIC / NULLIF(pv_uv, 0), 4) AS cart_conversion,-- 下单转化率：下单UV / 加购UV（保留4位小数，避免除零错误）ROUND(order_uv::NUMERIC / NULLIF(cart_uv, 0), 4) AS order_conversion,-- 支付转化率：支付UV / 下单UV（保留4位小数，避免除零错误）ROUND(pay_uv::NUMERIC / NULLIF(order_uv, 0), 4) AS pay_conversion
FROM daily_uv  -- 从CTE结果中取数
ORDER BY event_date;  -- 按日期升序排列，便于观察时间趋势

六、瓶颈识别与优化建议

6.1 瓶颈分析

• 加购到下单转化率低（53.33%）：可能原因包括结算流程复杂、价格敏感、库存不足
• 新用户转化率显著低于老用户：需优化新用户引导流程

6.2 优化策略

• 1. 简化结算流程：减少必填字段，增加一键支付功能
• 2. 价格促销：对加购未下单用户推送限时优惠券
• 3. 库存预警：实时显示库存状态，避免超卖
• 4. 新用户激励：提供新人专享折扣，优化首单体验

七、性能优化与工具整合

7.1 索引优化

-- 创建时间字段索引
CREATE INDEX idx_event_time ON unique_user_behavior (event_time);-- 创建用户ID索引
CREATE INDEX idx_user_id ON unique_user_behavior (user_id);

7.2 工具整合

• 1. 数据清洗：使用Python的pandas库进行初步清洗
• 2. 可视化：通过Apache Superset或Tableau生成动态报表
  • 说明： Apache Superset针对该工具分析，其他专栏做专项分享（后续排期ing）
• 3. 自动化：利用Airflow调度定时分析任务

八、总结

通过PostgreSQL构建的流量转化漏斗分析模型，

• 能够高效处理大规模用户行为数据，结合多维度分析和可视化工具，精准定位业务瓶颈。
• 企业可根据分析结果针对性优化运营策略，提升整体转化率和用户价值。