埋点数据分析：路径漏斗与留存看板

python

df = df.drop_duplicates(subset=['user_id','event_name','timestamp_ms']) df = df[~df['user_id'].str.startswith('test_')] df['timestamp'] = pd.to_datetime(df['timestamp_ms'], unit='ms') df = df.sort_values(['user_id','timestamp'])

二、路径漏斗分析实战

2.1 漏斗的定义与步骤设计

路径漏斗是一系列有序的事件步骤，用户必须按顺序完成前一步才能计入下一步。例如电商转化漏斗：

浏览商品页 → 加入购物车 → 发起结算 → 支付成功

每一步都对应一个特定的埋点事件。设计漏斗时需注意：

• 步骤之间一般有先后顺序约束，但可设定时间窗口（如用户需在30分钟内完成全流程）。
• 避免步骤颗粒度过细导致漏斗断裂，也避免过粗失去洞察。

2.2 计算漏斗转化率

假设已获得每个用户的完整行为序列，计算整体及分步转化率：

整体转化率 = 完成最后一步的用户数 / 完成第一步的用户数
分步转化率 = 完成当前步骤的用户数 / 完成上一步的用户数

示例数据（用户数为独立用户计数）：

直观发现：“浏览→加购”流失最大，仅35%用户点击了加购，是首要优化环节。

2.3 时间窗口与多路径分析

真实场景中用户可能跳出后返回，因此需要引入时间窗口。例如：设定用户开始第一步后24小时内完成后续步骤都算有效转化。超过窗口则视为流失。

实现方法：找到每个用户第一次触发第一步事件的时间T0，然后扫描在T0至T0 + 窗口期内是否按顺序触发后续事件。

若产品存在多条转化路径（如社交媒体可通过搜索或推荐进入），建议分别建立漏斗，对比不同渠道质量。

2.4 使用 SQL 快速构建漏斗

以标准事件表events (user_id, event, timestamp)为例，计算当日“浏览→加购”的24小时窗口漏斗：

WITH step1 AS (
    SELECT user_id, MIN(timestamp) as t1
    FROM events
    WHERE event = 'page_view_product'
      AND DATE(timestamp) = '2025-01-01'
    GROUP BY user_id
),
step2 AS (
    SELECT DISTINCT s1.user_id
    FROM step1 s1
    JOIN events e2 ON s1.user_id = e2.user_id
    WHERE e2.event = 'add_to_cart'
      AND e2.timestamp BETWEEN s1.t1 AND s1.t1 + INTERVAL '24' HOUR
)
SELECT
    COUNT(DISTINCT s1.user_id) AS step1_users,
    COUNT(DISTINCT s2.user_id) AS step2_users
FROM step1 s1
LEFT JOIN step2 s2 ON s1.user_id = s2.user_id;

三、留存看板构建与解读

3.1 留存的三种定义

• 新用户留存：以首次使用产品（新增）为起点，观察第N日后仍有活跃的用户比例。
• 活跃用户留存：以某日活跃为起点，看后续持续活跃情况。常用于衡量功能粘性。
• 自定义事件留存：例如完成“首次发布内容”后，有多少用户在7天内再次发布内容。

本教程以新用户留存为主，其经典指标为次日留存、7日留存、30日留存。

3.2 留存表的准备

需要两张基础表：用户新增表new_users (user_id, first_active_date) 和用户每日活跃表active_days (user_id, active_date)。活跃可根据核心事件（打开App、浏览页面）定义。

计算逻辑：对于新增日期为D0的用户，检查在D0+N是否出现在活跃表中。公式：

N日留存率 = (在D0新增且在D0+N活跃的用户数) / D0新增总用户数

3.3 用 SQL 计算次日留存矩阵

以下SQL产出每个新增日期的次日、3日、7日留存率：

SELECT
    n.first_active_date,
    COUNT(DISTINCT n.user_id) AS new_users,
    COUNT(DISTINCT a2.user_id) * 100.0 / COUNT(DISTINCT n.user_id) AS retention_day2,
    COUNT(DISTINCT a3.user_id) * 100.0 / COUNT(DISTINCT n.user_id) AS retention_day3,
    COUNT(DISTINCT a7.user_id) * 100.0 / COUNT(DISTINCT n.user_id) AS retention_day7
FROM new_users n
LEFT JOIN active_days a2 ON n.user_id = a2.user_id
    AND a2.active_date = DATE_ADD(n.first_active_date, INTERVAL 2 DAY)
LEFT JOIN active_days a3 ON n.user_id = a3.user_id
    AND a3.active_date = DATE_ADD(n.first_active_date, INTERVAL 3 DAY)
LEFT JOIN active_days a7 ON n.user_id = a7.user_id
    AND a7.active_date = DATE_ADD(n.first_active_date, INTERVAL 7 DAY)
GROUP BY n.first_active_date
ORDER BY n.first_active_date;

对应可绘制留存曲线，观察长期衰减趋势。

3.4 留存看板可视化与监控

推荐用折线图呈现不同批次新增用户的留存率变化（同期群分析）。横轴为距首次活跃天数（Day0 ～ Day30），纵轴为留存率，每条线代表一个新增日期群组。

异常监控：

• 若某日新增的次日留存突然下跌，排查当日渠道质量、是否有严重bug、新手引导是否改动。
• 群组曲线后期翘尾可能意味着产品功能被重新发现或推送活动影响，需要区分自然留存与运营刺激。

四、从分析到行动：驱动优化

4.1 漏斗与留存联合诊断

将漏斗关键步骤的完成用户单独划分群组，比较其留存。例如对比“完成注册”和“未完成注册”的7日留存率，可量化注册墙对长期留存的影响，从而决定是否简化注册流程。

4.2 常见优化思路

• 高流失步骤强化引导：如加购率低，可在商品页增加动态购物车按钮、显示限时优惠。
• 缩短转化时间窗口：通过智能推荐让用户更快进入核心体验。
• 新手期关键行为激励：促使新用户完成首次核心事件，能显著提升留存。
• 流失用户召回：针对漏斗步骤中途放弃的用户，推送个性化消息或礼品，通过埋点标识其停滞节点。

4.3 进阶：用 Python 自动化分析

可构建自动化脚本，定时生成漏斗与留存报告。示例利用 Pandas 快速计算新用户次日留存：

import pandas as pd

# 读取活跃表
active = pd.read_csv('active_days.csv')
active['active_date'] = pd.to_datetime(active['active_date'])

# 新增用户表
new = pd.read_csv('new_users.csv')
new['first_active_date'] = pd.to_datetime(new['first_active_date'])

# 合并次日活跃
merged = new.merge(active, on='user_id')
merged['day_diff'] = (merged['active_date'] - merged['first_active_date']).dt.days
retention = merged[merged['day_diff'] == 1].groupby('first_active_date')['user_id'].nunique()
base = new.groupby('first_active_date')['user_id'].nunique()
retention_rate = (retention / base * 100).fillna(0)