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

发布于：2025-05-09 ⋅ 阅读:(14) ⋅ 点赞:(0)

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

文章大纲

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）：用户访问商品详情页
    1. 加购（cart）：用户将商品加入购物车
    1. 下单（order）：用户提交订单
    1. 支付（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_pay
  FROM unique_user_behavior
  GROUP BY user_id
)
SELECT 
  '浏览' AS stage,
  COUNT(*) AS uv,
  NULL AS conversion_rate
FROM funnel_base
WHERE has_pv = 1

UNION ALL

SELECT 
  '加购' 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 = 1

UNION ALL

SELECT 
  '下单' 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 = 1

UNION ALL

SELECT 
  '支付' 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_pay
    FROM unique_user_behavior
    GROUP 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_uv
    FROM category_funnel cf
    JOIN product_category c ON cf.category_id = c.category_id
    GROUP 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_pay
    FROM unique_user_behavior ub
    JOIN user_profile u ON ub.user_id = u.user_id
    GROUP 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_uv
    FROM user_type_funnel
    GROUP 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_base

UNION ALL
SELECT 
    '加购' AS stage,
    COUNT(DISTINCT CASE WHEN has_cart = 1 THEN user_id END) AS uv
FROM funnel_base

UNION ALL
SELECT 
    '下单' AS stage,
    COUNT(DISTINCT CASE WHEN has_order = 1 THEN user_id END) AS uv
FROM funnel_base

UNION 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' 时，统计去重的用户ID
    COUNT(DISTINCT CASE WHEN event_type = 'pv' THEN user_id END) AS pv_uv,
    -- 计算「加购」行为的独立用户数：当事件类型为 'cart' 时，统计去重的用户ID
    COUNT(DISTINCT CASE WHEN event_type = 'cart' THEN user_id END) AS cart_uv,
    -- 计算「下单」行为的独立用户数：当事件类型为 'order' 时，统计去重的用户ID
    COUNT(DISTINCT CASE WHEN event_type = 'order' THEN user_id END) AS order_uv,
    -- 计算「支付」行为的独立用户数：当事件类型为 'pay' 时，统计去重的用户ID
    COUNT(DISTINCT CASE WHEN event_type = 'pay' THEN user_id END) AS pay_uv
  FROM 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. 简化结算流程：减少必填字段，增加一键支付功能
    1. 价格促销：对加购未下单用户推送限时优惠券
    1. 库存预警：实时显示库存状态，避免超卖
    1. 新用户激励：提供新人专享折扣，优化首单体验

七、性能优化与工具整合

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库进行初步清洗
    1. 可视化：通过Apache Superset或Tableau生成动态报表
    • 说明： Apache Superset针对该工具分析，其他专栏做专项分享（后续排期ing）
    1. 自动化：利用Airflow调度定时分析任务

八、总结

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

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

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布