PROJECT 05进阶

游戏玩家行为序列与留存漏斗分析

Gaming Player Funnel Analysis

通过时间序列处理和漏斗分析，追踪玩家从注册到付费的转化路径。

时间序列rolling 窗口shift 操作漏斗转化留存率计算

项目背景

某手游上线后 DAU 增长放缓。产品团队需要分析玩家从注册 -> 新手引导 -> 首局游戏 -> 达到 5 级 -> 首次付费的完整转化漏斗，找出流失关键节点。

模拟数据集

player_id,event_type,event_time,level,session_duration_min,is_paying
P001,register,2024-06-01 10:00:00,0,0,False
P001,tutorial_start,2024-06-01 10:02:00,0,2,False
P001,tutorial_complete,2024-06-01 10:15:00,1,13,False
P001,first_match,2024-06-01 10:20:00,1,18,False
P001,reach_level5,2024-06-03 14:30:00,5,120,False
P002,register,2024-06-01 11:00:00,0,0,False
P002,tutorial_start,2024-06-01 11:02:00,0,2,False
P002,quit,2024-06-01 11:05:00,0,5,False
P003,register,2024-06-02 09:00:00,0,0,False
P003,first_purchase,2024-06-02 09:30:00,3,30,True

代码练习区

在下方编辑器中编写你的 Pandas 代码。可记录笔记、编写伪代码，参考答案在下方。

pandas_exercise.py

参考答案

reference_solution.py

import pandas as pd

df = pd.read_csv('player_events.csv')
df['event_time'] = pd.to_datetime(df['event_time'])

# 1. 定义漏斗步骤
funnel_steps = ['register', 'tutorial_start', 'tutorial_complete', 'first_match', 'reach_level5', 'first_purchase']

# 2. 计算各步骤人数
funnel_counts = {}
for step in funnel_steps:
    funnel_counts[step] = df[df['event_type'] == step]['player_id'].nunique()

funnel_df = pd.DataFrame(list(funnel_counts.items()), columns=['step', 'users'])
funnel_df['conversion_rate'] = funnel_df['users'] / funnel_df['users'].iloc[0] * 100

# 3. 计算步骤间转化率
funnel_df['step_conversion'] = funnel_df['users'].pct_change() * 100

# 4. 次日留存率
df['event_date'] = df['event_time'].dt.date
register_dates = df[df['event_type'] == 'register'][['player_id', 'event_date']].rename(columns={'event_date': 'reg_date'})
retention = df[df['event_type'] == 'first_match'].merge(register_dates, on='player_id')
retention['days_diff'] = (retention['event_date'] - retention['reg_date']).dt.days
retention_rate = (retention['days_diff'] == 1).sum() / len(register_dates) * 100

# 5. 滚动平均 DAU
daily_players = df.groupby(df['event_time'].dt.date)['player_id'].nunique().reset_index()
daily_players['dau_ma7'] = daily_players['player_id'].rolling(window=7).mean()

业务解读

漏斗分析揭示转化瓶颈。若 '新手引导完成 -> 首局游戏' 转化率低于 60%，说明引导设计有问题。若 '达到 5 级 -> 首次付费' 是最大流失点，可考虑在该节点推送限时优惠。留存率比 DAU 更能反映产品健康度。

player_id,event_type,event_time,level,session_duration_min,is_paying P001,register,2024-06-01 10:00:00,0,0,False P001,tutorial_start,2024-06-01 10:02:00,0,2,False P001,tutorial_complete,2024-06-01 10:15:00,1,13,False P001,first_match,2024-06-01 10:20:00,1,18,False P001,reach_level5,2024-06-03 14:30:00,5,120,False P002,register,2024-06-01 11:00:00,0,0,False P002,tutorial_start,2024-06-01 11:02:00,0,2,False P002,quit,2024-06-01 11:05:00,0,5,False P003,register,2024-06-02 09:00:00,0,0,False P003,first_purchase,2024-06-02 09:30:00,3,30,True

import pandas as pd df = pd.read_csv('player_events.csv') df['event_time'] = pd.to_datetime(df['event_time']) # 1. 定义漏斗步骤 funnel_steps = ['register', 'tutorial_start', 'tutorial_complete', 'first_match', 'reach_level5', 'first_purchase'] # 2. 计算各步骤人数 funnel_counts = {} for step in funnel_steps: funnel_counts[step] = df[df['event_type'] == step]['player_id'].nunique() funnel_df = pd.DataFrame(list(funnel_counts.items()), columns=['step', 'users']) funnel_df['conversion_rate'] = funnel_df['users'] / funnel_df['users'].iloc[0] * 100 # 3. 计算步骤间转化率 funnel_df['step_conversion'] = funnel_df['users'].pct_change() * 100 # 4. 次日留存率 df['event_date'] = df['event_time'].dt.date register_dates = df[df['event_type'] == 'register'][['player_id', 'event_date']].rename(columns={'event_date': 'reg_date'}) retention = df[df['event_type'] == 'first_match'].merge(register_dates, on='player_id') retention['days_diff'] = (retention['event_date'] - retention['reg_date']).dt.days retention_rate = (retention['days_diff'] == 1).sum() / len(register_dates) * 100 # 5. 滚动平均 DAU daily_players = df.groupby(df['event_time'].dt.date)['player_id'].nunique().reset_index() daily_players['dau_ma7'] = daily_players['player_id'].rolling(window=7).mean()