学习笔记:SQL与Python综合实战-用户转化漏斗与异常定位

对应要求:

精通SQL、精通Python、基础统计分析（漏斗模型）、数据敏感度

背景设定：

假设你是一家跨境电商的运营分析师。老板说：“最近一周（1.11-1.18）的订单支付转化率比上周下降了5%，你去看下怎么回事。”

任务步骤

1、SQL数据提取（复杂查询）：

• 任务：从数据仓库中提取用户行为日志（曝光-点击-加购-支付）。

• 难点：数据可能分布在不同的表中（用户信息表、行为日志表、订单表）。

• 需要做的：使用WITH AS构建临时表，通过LEFT JOIN关联，并用窗口函数（ROW_NUMBER()）去重，确保每个用户每一步只计算一次。

• 学习点：多表关联、子查询、去重逻辑。

2、Python数据清洗与分析（Pandas）：

• 任务：将SQL查出的数据导入Pandas，计算各渠道（如广告、搜索、推送）的漏斗流失率。

• 需要做的：使用groupby按渠道聚合，计算“点击-支付”的转化率环比变化。

• 学习点：Pandas的pivot_table、merge、时间序列重采样。

3、异常定位与可视化（Matplotlib/Seaborn）：

• 任务：发现是“iOS端-加购到支付”环节转化率下降最严重。你需要画出对比图（上周 vs 本周）。

• 需要做的：使用matplotlib绘制双柱状图或折线图，直观展示差异点。

• 学习点：数据可视化用于快速定位问题。

4、结论输出：

最终发现是因为iOS端在1月9日有一次APP版本更新，支付接口调用失败率增高。需要给出建议：回滚版本或修复支付SDK。

数据模拟

我们模拟一张用户行为日志表 events，包含以下字段：

• user_id：用户ID
• event_time：事件时间（精确到秒）
• event_type：事件类型（impression 曝光，click 点击，cart 加购，pay 支付）
• channel：用户来源渠道（search 搜索，recommend 推荐，ads 广告）
• device：设备类型（iOS, Android）
• product_id：商品ID（可选）

为了体现问题，我们设定：

上周（1.05-1.11）各环节转化率正常。 本周（1.12-1.18）整体转化率下降。

本周 iOS 设备在 1 月 9 日之后，从加购到支付的转化率明显降低（支付成功率由 50% 降至 25%）。

我们来生成 1000 个用户在两周内的约 2 万条行为记录，并保证每天每个用户最多产生一次有效转化路径（曝光→点击→加购→支付），便于漏斗计算。

1. 导入库并生成模拟数据

import pandas as pdimport numpy as npimport randomfrom datetime import datetime, timedeltaimport matplotlib.pyplot as pltimport seaborn as snsimport duckdb  # 用于在 DataFrame 上执行 SQL（也可以用 pandasql）plt.rcParams['font.sans-serif'] = ['SimHei']  # 指定默认字体为黑体plt.rcParams['axes.unicode_minus'] = False    # 解决负号 '-' 显示为方块的问题# 设置随机种子以便结果可复现np.random.seed(42)random.seed(42)# 参数设置n_users = 1000start_date = datetime(2026, 1, 5)  # 起始日期（上周开始）end_date = datetime(2026, 1, 18)     # 结束日期（本周结束）date_list = [start_date + timedelta(days=i) for i in range((end_date - start_date).days + 1)]# 渠道和设备分布channels = ['search', 'recommend', 'ads']devices = ['iOS', 'Android']# 设备比例：iOS 40%，Android 60%device_prob = [0.4, 0.6]# 生成用户基础信息（每个用户有固定渠道和设备偏好）user_df = pd.DataFrame({'user_id': range(1, n_users + 1),'channel': np.random.choice(channels, n_users, p=[0.5, 0.3, 0.2]),'device': np.random.choice(devices, n_users, p=device_prob)})# 生成事件数据events = []for user in user_df.itertuples():    user_id = user.user_id    base_channel = user.channel    base_device = user.devicefor current_date in date_list:# 决定该用户当天是否产生曝光（概率 0.6）if np.random.rand() < 0.6:# 曝光时间（随机秒）            imp_time = current_date + timedelta(seconds=np.random.randint(0, 86400))            events.append([user_id, imp_time, 'impression', base_channel, base_device, np.random.randint(1, 101)])# 曝光后是否点击（概率 0.5）if np.random.rand() < 0.5:                click_time = imp_time + timedelta(seconds=np.random.randint(1, 600))                events.append([user_id, click_time, 'click', base_channel, base_device, np.random.randint(1, 101)])# 点击后是否加购（概率 0.4）if np.random.rand() < 0.4:                    cart_time = click_time + timedelta(seconds=np.random.randint(1, 600))                    events.append([user_id, cart_time, 'cart', base_channel, base_device, np.random.randint(1, 101)])# 加购后是否支付（概率受日期和设备影响）# 正常支付概率 0.5；本周 iOS 设备在 3 月 2 日后支付概率降为 0.25                    pay_prob = 0.5if current_date >= datetime(2026, 1, 9) and base_device == 'iOS':                        pay_prob = 0.25if np.random.rand() < pay_prob:                        pay_time = cart_time + timedelta(seconds=np.random.randint(1, 600))                        events.append([user_id, pay_time, 'pay', base_channel, base_device, np.random.randint(1, 101)])# 构建 DataFrameevents_df = pd.DataFrame(events, columns=['user_id', 'event_time', 'event_type', 'channel', 'device', 'product_id'])events_df.to_excel('events_df.xlsx')print(f"生成事件总数：{len(events_df)}")print(events_df.head())

输出结果

2.使用 SQL 提取每日/每周各事件的独立用户数

我们用 duckdb 在 DataFrame 上执行 SQL 查询，模拟从数据仓库取数的过程。

# 注册 DataFrame 到 duckdbconn = duckdb.connect()conn.register('events', events_df)conn.register('users', user_df)# SQL 查询：按日期、设备、渠道统计各事件独立用户数query = """WITH daily_events AS (    SELECT         DATE(event_time) AS dt,        device,        channel,        COUNT(DISTINCT CASE WHEN event_type = 'impression' THEN user_id END) AS imp_users,        COUNT(DISTINCT CASE WHEN event_type = 'click' THEN user_id END) AS click_users,        COUNT(DISTINCT CASE WHEN event_type = 'cart' THEN user_id END) AS cart_users,        COUNT(DISTINCT CASE WHEN event_type = 'pay' THEN user_id END) AS pay_users    FROM events    GROUP BY 1, 2, 3)SELECT * FROM daily_events ORDER BY dt, device, channel"""daily_stats = conn.execute(query).df()print("每日各设备渠道漏斗统计：")print(daily_stats.head(10))

输出结果

3.Pandas 数据处理：计算转化率并对比周度变化

# 添加周标识：上周 (1.05-1.11) 为 week1，本周 (1.12-1.18) 为 week2daily_stats['dt'] = pd.to_datetime(daily_stats['dt'])daily_stats['week'] = daily_stats['dt'].apply(lambda x: 'week1'if x.day <= 11 else'week2')# 按周、设备、渠道汇总weekly_stats = daily_stats.groupby(['week', 'device', 'channel']).agg({'imp_users': 'sum','click_users': 'sum','cart_users': 'sum','pay_users': 'sum'}).reset_index()# 计算各步转化率weekly_stats['click_rate'] = weekly_stats['click_users'] / weekly_stats['imp_users'] * 100weekly_stats['cart_rate'] = weekly_stats['cart_users'] / weekly_stats['click_users'] * 100weekly_stats['pay_rate'] = weekly_stats['pay_users'] / weekly_stats['cart_users'] * 100# 查看整体周度转化率overall = weekly_stats.groupby('week')[['imp_users','click_users','cart_users','pay_users']].sum()overall['click_rate'] = overall['click_users'] / overall['imp_users'] * 100overall['cart_rate'] = overall['cart_users'] / overall['click_users'] * 100overall['pay_rate'] = overall['pay_users'] / overall['cart_users'] * 100print("整体周度转化率：")print(overall[['click_rate','cart_rate','pay_rate']])

输出结果

4.异常定位：分设备查看转化率

# 分设备查看支付转化率周度变化device_pivot = weekly_stats.pivot_table(index='device', columns='week', values='pay_rate', aggfunc='mean')print("各设备支付转化率（%）周度对比：")print(device_pivot)

输出结果

5.进一步下钻：按天查看 iOS 设备支付转化率

# 筛选 iOS 设备的数据ios_daily = daily_stats[daily_stats['device'] == 'iOS'].copy()ios_daily['pay_rate'] = ios_daily['pay_users'] / ios_daily['cart_users'] * 100# 绘制 iOS 设备每日支付转化率plt.figure(figsize=(15,6))sns.lineplot(data=ios_daily, x='dt', y='pay_rate', hue='channel', marker='o')plt.axvline(x=pd.Timestamp('2026-01-09'), color='red', linestyle='--', label='异常开始日')plt.title('iOS 设备每日支付转化率')plt.xlabel('日期')plt.ylabel('支付转化率 (%)')plt.legend()plt.grid(True)plt.show()

输出结果

从图中可以看到，1月9日之后所有渠道的 iOS 支付转化率均大幅下降。

6.定位根本原因：分析可能的技术故障

进一步假设我们还可以关联版本发布记录。假设 1 月 9 日凌晨 iOS 端发布了新版本 v2.3.0。我们可以通过日志分析该版本用户的支付成功率。

# 模拟版本字段（这里简化，假设所有 iOS 用户从 3 月 2 日起使用新版本）events_df['version'] = 'v2.2.0'# 默认旧版本events_df.loc[(events_df['device'] == 'iOS') & (events_df['event_time'] >= '2026-01-09'), 'version'] = 'v2.3.0'# 统计 iOS 各版本支付转化率ios_events = events_df[events_df['device'] == 'iOS']ios_cart_users = ios_events[ios_events['event_type']=='cart'].groupby('version')['user_id'].nunique()ios_pay_users = ios_events[ios_events['event_type']=='pay'].groupby('version')['user_id'].nunique()ios_pay_rate = (ios_pay_users / ios_cart_users * 100).rename('pay_rate')print("iOS 各版本支付转化率：")print(ios_pay_rate)

输出结果

7、结论与建议

• 结论：本周支付转化率下降主要由 iOS 端引起，且从 1 月 9 日开始。进一步分析发现，iOS 新版本 v2.3.0 的支付成功率从 54% 降至 40%，与数据吻合。
• 建议：立即联系技术团队检查 iOS 新版本的支付模块是否存在 bug（如支付接口调用失败、页面加载异常等）；考虑回滚版本或紧急修复；在此期间暂停 iOS 端的广告投放，减少损失。

学到了什么？

• SQL 复杂查询：用 CASE WHEN 结合 COUNT(DISTINCT) 同时统计多个事件类型的独立用户数。

• Python 数据处理：利用 Pandas 进行分组聚合、透视表、时间序列操作。

• 漏斗分析与异常定位：通过周度对比发现 iOS 异常，再下钻到天找到突变点，最终关联版本信息定位根本原因。

• 可视化：使用 Matplotlib/Seaborn 绘制折线图，直观展示趋势变化。

可以在此基础上扩展，比如加入更多维度（渠道、商品类目）、使用假设检验验证下降的显著性，或者构建自动化监控脚本实时发现类似问题。

后台回复：【用户转化漏斗与异常定位】，获取本文运行程序