高活跃用户累计活跃天数分布
dau3_cumsum = dau3_df.groupby(‘user_id’)[‘date’].count()
复制代码
SQL
SELECT user_id, COUNT(date)
FROM
(SELECT date, user_id, COUNT(type)
FROM behavior_sql
GROUP BY date, user_id
HAVING COUNT(type) >= 3) dau3
GROUP BY user_id;
复制代码
fig, ax = plt.subplots(figsize=[16,6])
ax.set_yscale(“log”)
sns.countplot(dau3_cumsum.values,palette=‘Set1’)
for p in ax.patches:
ax.annotate(‘{:.2f}%’.format(100*p.get_height()/len(dau3_cumsum.values)), (p.get_x() + 0.2, p.get_height() + 100))
plt.title(‘高活跃用户累计活跃天数分布’)
复制代码
统计周期内,大部分高活跃用户累计活跃天数在六天以下,但也存在高达十六天的超级活跃用户数量,对累计天数较高的用户要推出连续登录奖励等继续维持其对平台的黏性,对累计天数较低的用户要适当进行推送活动消息等对其进行召回。
#每日浏览量
pv_daily = behavior[behavior[‘type’] == ‘pv’].groupby(‘date’)[‘user_id’].count()
#每日访客数
uv_daily = behavior.groupby(‘date’)[‘user_id’].nunique()
复制代码
SQL
#每日浏览量
SELECT date, COUNT(type) pv_daily FROM behavior_sql
WHERE type = ‘pv’
GROUP BY date;
#每日访客数
SELECT date, COUNT(DISTINCT user_id) uv_daily FROM behavior_sql
GROUP BY date;
复制代码
每日浏览量可视化
fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(pv_daily.index, pv_daily.values,markers=‘D’, linestyles=‘–’,color=‘dodgerblue’)
x=list(range(0,16))
for a,b in zip(x,pv_daily.values):
plt.text(a+0.1, b + 2000 , ‘%i’ % b, ha=‘center’, va= ‘bottom’,fontsize=14)
plt.title(‘每日浏览量’)
复制代码
每日访客数可视化
fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(uv_daily.index, uv_daily.values, markers=‘H’, linestyles=‘–’,color=‘m’)
x=list(range(0,16))
for a,b in zip(x,uv_daily.values):
plt.text(a+0.1, b + 500 , ‘%i’ % b, ha=‘center’, va= ‘bottom’,fontsize=14)
plt.title(‘每日访客数’)
复制代码
浏览量和访客数每日变化趋势大致相同,2018-04-04日前后用户数量变化波动较大,4月4日为清明节假日前一天,各数据量在当天均有明显下降,但之后逐步回升,推测应为节假日营销活动或推广拉新活动带来的影响。
#每时浏览量
pv_hourly = behavior[behavior[‘type’] == ‘pv’].groupby(‘hour’)[‘user_id’].count()
#每时访客数
uv_hourly = behavior.groupby(‘hour’)[‘user_id’].nunique()
复制代码
SQL
每时浏览量
SELECT date, COUNT(type) pv_daily FROM behavior_sql
WHERE type = ‘pv’
GROUP BY hour;
每时访客数
SELECT date, COUNT(DISTINCT user_id) uv_daily FROM behavior_sql
GROUP BY hour;
复制代码
浏览量随小时变化可视化
fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(pv_hourly.index, pv_hourly.values, markers=‘H’, linestyles=‘–’,color=‘dodgerblue’)
for a,b in zip(pv_hourly.index,pv_hourly.values):
plt.text(a, b + 10000 , ‘%i’ % b, ha=‘center’, va= ‘bottom’,fontsize=12)
plt.title(‘浏览量随小时变化’)
复制代码
访客数随小时变化可视化
fig, ax = plt.subplots(figsize=[16,6])
sns.pointplot(uv_hourly.index, uv_hourly.values, markers=‘H’, linestyles=‘–’,color=‘m’)
for a,b in zip(uv_hourly.index,uv_hourly.values):
plt.text(a, b + 1000 , ‘%i’ % b, ha=‘center’, va= ‘bottom’,fontsize=12)
plt.title(‘访客数随小时变化’)
复制代码
浏览量及访客数随小时变化趋势一致,在凌晨1点到凌晨5点之间,大部分用户正在休息,整体活跃度较低。凌晨5点到10点用户开始起床工作,活跃度逐渐增加,之后趋于平稳,下午6点之后大部分人恢复空闲,浏览量及访客数迎来了第二波攀升,在晚上8点中到达高峰,随后逐渐下降。可以考虑在上午9点及晚上8点增大商品推广力度,加大营销活动投入,可取的较好的收益,1点到5点之间适合做系统维护。
用户各操作随小时变化
type_detail_hour = pd.pivot_table(columns = ‘type’,index = ‘hour’, data = behavior,aggfunc=np.size,values = ‘user_id’)
用户各操作随星期变化
type_detail_weekday = pd.pivot_table(columns = ‘type’,index = ‘weekday’, data = behavior,aggfunc=np.size,values = ‘user_id’)
type_detail_weekday = type_detail_weekday.reindex([‘Monday’,‘Tuesday’,‘Wednesday’,‘Thursday’,‘Friday’,‘Saturday’,‘Sunday’])
复制代码
SQL
用户各操作随小时变化
SELECT hour,
SUM(CASE WHEN behavior=‘pv’ THEN 1 ELSE 0 END)AS ‘pv’,
SUM(CASE WHEN behavior=‘fav’ THEN 1 ELSE 0 END)AS ‘fav’,
SUM(CASE WHEN behavior=‘cart’ THEN 1 ELSE 0 END)AS ‘cart’,
SUM(CASE WHEN behavior=‘pay’ THEN 1 ELSE 0 END)AS ‘pay’
FROM behavior_sql
GROUP BY hour
ORDER BY hour
用户各操作随星期变化
SELECT weekday,
SUM(CASE WHEN behavior=‘pv’ THEN 1 ELSE 0 END)AS ‘pv’,
SUM(CASE WHEN behavior=‘fav’ THEN 1 ELSE 0 END)AS ‘fav’,
SUM(CASE WHEN behavior=‘cart’ THEN 1 ELSE 0 END)AS ‘cart’,
SUM(CASE WHEN behavior=‘pay’ THEN 1 ELSE 0 END)AS ‘pay’
FROM behavior_sql
GROUP BY weekday
ORDER BY weekday
复制代码
tdh_line = type_detail_hour.stack().reset_index().rename(columns={0: ‘Value’})
tdw_l