1 项目背景和思路
项目以淘宝平台2017年双十二活动数据为数据集，通过各项指标和模型对用户行为数据探索式分析，把握用户的行为习惯、商品偏好、用户价值，为平台及商家提供数据支撑，保证平台及商家的精细化运营。同时也为精准营销提供方向。
首先通过Python对原始数据集进行清洗，然后将处理好的数据导入到SQL Server中，通过SQL Server进行指标计算和分析，并通过Tableau将分析结果可视化。分析思路和框架如下：
2 数据清洗
2.1 数据来源
数据集来源于淘宝用户购物行为数据集_数据集-阿里云天池。数据集包含了2017年11月25日至2017年12月3日之间，约一百万随机用户的所有行为，每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成。关于数据集每一列的具体描述如下：
用户行为类型共有四种，分别是：
2.2 数据清洗
将数据集导入Python中，对数据进行缺失值处理、格式内容处理、去重、去除非需求数据等处理。
2.2.1 数据导入
原始数据集没有列名，需自行添加列名。另外，原始数据集包含一亿多行数据，此处使用get_chunk()函数截取前100万行数据用于分析。
import pandas as pd
import datetime
columns = ['user_id','item_id','category_id','behavior_type', 'timestamp']
reader = pd.read_csv('E:\\UserBehavior.csv', names=columns, iterator=True)
chunkSize = 1000000
data = reader.get_chunk(chunkSize)导入后先查看数据信息和数据类型。
data.info()
data.describe()
2.2.2 重复值清洗
查看数据集中的重复记录的数量并去除。
#查看重复值
data.duplicated().sum()
#删除重复值
data.drop_duplicates(inplace=True)2.2.3 格式内容处理
将数值类型的时间戳转化为日期时间类型数据，并分别提取日期、时间、小时作为单独一列。
#将时间戳转化为日期时间数据。pd.to_datetime默认使用UTC时间，需要转换为北京时间
data['timestamp'] = pd.to_datetime(data.timestamp, unit='s') + datetime.timedelta(hours = 8)
#分别提取日期、时间、小时
data['date'] = data['timestamp'].dt.date
data['date'] = pd.to_datetime(data['date'])
data['time'] = data['timestamp'].dt.time
data['hour'] = data['timestamp'].dt.hour
2.2.4 缺失值清洗
查看是否有缺失值，该数据集无缺失值。
#查看缺失值
data.isnull().sum()
2.2.5 异常值清洗
数据集的时间范围为2017年11月25日至2017年12月3日，不在该时间范围内的为异常数据，需要删除。
#查看是否有日期不在20171125-20171204范围内的数据
data[data['date']<'2017-11-25'].shape
data[data['date']>'2017-12-04'].shape
日期为2017年11月25日之前的数据有469行，没有日期为2017年12月4日之后的数据。删除日期范围异常的469行数据。
#删除时间范围异常数据
data = data[(data['timestamp'] >= '2017-11-25') & (data['timestamp'] < '2017-12-4')]2.2.6 数据排序和保存
将处理好的数据重新排序，并保存到csv文件中。
# 重新排序、索引
data = data.sort_values(by = ['timestamp','user_id'], ascending=True)
data = data.reset_index(drop=True)
#保存到csv文件
data.to_csv('data.csv')3 用户行为分析
用户行为分析分为以下三个方向，分别是：用户行为习惯分析、用户商品偏好分析以及用户价值分析。通过计算各类指标，借助不同的模型，对用户行为进行定性和定量分析。
3.1 用户行为习惯分析
用户行为分析指标可以分为渠道类指标、访问类指标、转化类指标、留存类指标和社交类指标五大类，原始数据集不包含渠道数据和社交数据，因此本部分着重分析访问类指标、转化类指标、留存类指标，并结合漏斗模型和用户路径模型进行描述性分析。
3.1.1 用户行为整体情况
- 从用户行为种类来看，浏览、加购、收藏、购买四种用户性的的变动趋势基本保持一致。四种行为中，浏览数远高于其他行为数，加购数高于收藏和购买数，收藏数略高于购买数，说明用户在选择商品时更倾向于精挑细选，细细对比和甄别。进一步挖掘用户喜好、完善推荐主页、推送喜好商品，可以更好的实现流量转化。
- 从日期来看，11月25日至12月1日用户行为每日变化趋势并不大，在12月2日出现较大增幅，背后的业务逻辑可能是，平台在该天进行双十二预热活动，吸引到 更多的流量，用户更活跃
- 从小时来看，0-8点时间段用户行为数最低，19-22点最为活跃，与用户的工作休息时间相符。可针对用户的上网时段偏好制定相应策略，例如在访问量最高时段进行集中推广，在下单高峰时段设计优惠活动，促进消费。
3.1.2 用户访问情况
3.1.2.1 每日新增用户数量（DNU)
select 日期,count(distinct user_id) as 当日新增用户
from (select user_id,min(date) as 日期
from Userbehavior
group by user_id)t
group by 日期
order by 日期
- 日新增用户数呈逐日减少趋势
- 日新增用户结合日活跃用户可以反映平台的用户留存情况和用户粘性，日活跃用户变动不明显或增加，而日新增用户减少，可以说明平台的用户留存情况较好，活跃用户中老用户占比大
- 日新增用户结合渠道数据可以用于反映渠道获客效果
3.1.2.2 每日pv、uv、pv/uv
pv：平台访问量或点击量
uv：平台独立访客数
pv/uv：用户在一次访问中访问平台的不同页面的数量，即访问深度
select date,
count(distinct user_id) as uv,
sum(case behavior_type when 'pv' then 1 else 0 end) as pv,
sum(case behavior_type when 'pv' then 1 else 0 end)/count(distinct user_id) as 访问深度
from Userbehavior
group by date
order by date
- uv和pv的变动趋势基本一致，在11月25日至12月1日变动较小，12月2日增长较大；访问深度维持在12-14之间浮动，变动幅度较小，说明用户每日平均浏览页面的次数比较稳定
- 访问深度维持在12-14，用户偏向于多次浏览，说明平台的内容符合用户需求，且平台具有较好的用户粘性
3.1.2.3 跳失率
跳失率是指只浏览了一个页面就离开的访问次数占总的访问次数的百分比，即只浏览了一个页面的访客数/ 总访客数。跳失率用于评价流量质量。跳失率越高，说明页面的跳转性越低，页面的吸引力不足，最终会影响转化率。
--日跳失率
select date,
sum(case when 用户行为总数=1 then 1 else 0 end) as 跳失访客数,
count(distinct user_id) as 总访客数,
concat(cast(sum(case when 用户行为总数=1 then 1 else 0 end)*100.00/count(distinct user_id) as decimal(18,2)),'%') as 日跳失率
from(select date,user_id,count(behavior_type) as 用户行为总数
from Userbehavior
group by date,user_id)a
group by date
order by date;
--总体跳失率
select sum(case when 用户行为总数=1 then 1 else 0 end) as 跳失访客数,
count(distinct user_id) as 总访客数,
concat(cast(sum(case when 用户行为总数=1 then 1 else 0 end)*100.00/count(distinct user_id) as decimal(18,2)),'%') as 总体跳失率
from(select user_id,count(behavior_type) as 用户行为总数
from Userbehavior
group by user_id)b;
- 日跳失率在11%-14%之间波动，11月25日至12月3日总体的跳失率为0，说明平台对用户的吸引力较好，无需采取额外措施降低跳失率
3.1.3 用户转化情况
在业务场景中，用户达成完成某一特定动作或是达到某一状态水平，称之为一次转化，转化率分布在各类转化节点，即完成转化用户占总用户的比例。转化率非常重要，提高转化率就是提高了销售额。此外，监控用户在各个环节的转化率可针对性的提升运营策用户行为案例分析略，优化短板，提升用户体验。本文采用漏斗模型和桑基图分析用户的转化情况。
3.1.3.1 漏斗模型
AARRR模型，包括用户获取、用户激活、用户留存、用户产生收入等用户的生命阶段，是分析用户增长和生命周期最常用的漏斗模型。从用户增长各阶段入手，判断用户流失大致处于哪个阶段，进而对问题阶段的用户进行细分，精细化运营，完成用户向成熟用户和付费用户的引导，实现用户增长。
此处按照浏览-加购-收藏-购买的流程分析用户的行为。
--创建视图查询用户行为
create view user_behavior_view as
select distinct user_id,
sum(case when behavior_type = 'pv' then 1 else 0 end) if_pv,
sum(case when behavior_type = 'fav' then 1 else 0 end) if_fav,
sum(case when behavior_type = 'cart' then 1 else 0 end) if_cart,
sum(case when behavior_type = 'buy' then 1 else 0 end) if_buy
from Userbehavior
group by user_id;
--基于漏斗模型
create view aarrr_model as
select count(user_id) as '浏览用户数',
sum(case when if_pv>0 and if_cart >0 then 1 else 0 end) as '浏览加购用户数',
sum(case when if_pv>0 and if_cart >0 and if_fav >0 then 1 else 0 end) as '浏览加购收藏用户数',
sum(case when if_pv>0 and if_cart >0 and if_fav >0 and if_buy >0 then 1 else 0 end) as '浏览加购收藏购买用户数'
from user_behavior_view;
select * from aarrr_model;
select concat(cast(浏览用户数*100.00/浏览用户数 as decimal(18,2)),'%') as 浏览,
concat(cast(浏览加购用户数*100.00/浏览用户数 as decimal(18,2)),'%') as 浏览加购转化率,
concat(cast(浏览加购收藏用户数*100.00/浏览加购用户数 as decimal(18,2)),'%') as 浏览加购转化率,
concat(cast(浏览加购收藏购买用户数*100.00/浏览加购收藏用户数 as decimal(18,2)),'%') as 浏览加购转化率
from aarrr_model
- 浏览-加购的转化率为74.89%，说明用户的加购意愿比较强烈，在这一环节的用户体验是比较良好的。
- 在加购-收藏阶段的转化率为37.19%，明显偏低。加购和收藏的功能存在重叠，有些用户选择性使用其中一种，因此导致这一阶段的转化率偏低。
- 对于浏览加购并收藏的用户，有76.78%会转化为付费用户，说明有同时有加购和收藏行为的用户的购买意愿是比较高的，因此可以考虑增加活动促进用户进行收藏加购，如收藏加购送小礼品、商品打折等
3.1.3.2 桑基图
对于上述漏斗模型反映出的用户有多种路径实现购买的问题，这里通过桑基图对产生购买行为的用户的路径进行进一步分析。
对于用户产生的购买行为，统计并计算其路径的种类和占比，具体如下：
create view user_path as
select count(*) as 总购买数,
sum(case when if_pv > 0 and if_fav = 0 and if_cart = 0 then 1 else 0 end) as '浏览后购买' ,
sum(case when if_pv > 0 and if_cart = 0 and if_fav > 0 then 1 else 0 end) as '浏览收藏后购买' ,
sum(case when if_pv > 0 and if_fav = 0 and if_cart > 0 then 1 else 0 end) as '浏览加购后购买' ,
sum(case when if_pv > 0 and if_fav > 0 and if_cart > 0 then 1 else 0 end) as '浏览加购收藏后购买',
sum(case when if_pv = 0 and if_cart = 0 and if_fav > 0 then 1 else 0 end) as '收藏后购买' ,
sum(case when if_pv = 0 and if_fav = 0 and if_cart > 0 then 1 else 0 end) as '加购后购买' ,
sum(case when if_pv = 0 and if_fav > 0 and if_cart > 0 then 1 else 0 end) as '加购收藏后购买',
sum(case when if_pv = 0 and if_fav = 0 and if_cart = 0 then 1 else 0 end) as '直接购买'
from user_behavior_view
where if_buy > 0
select * from user_path;
select concat(cast(总购买数*100.00/总购买数 as decimal(18,2)),'%') as '购买',
concat(cast(浏览后购买*100.00/总购买数 as decimal(18,2)),'%') as '浏览后购买',
concat(cast(浏览收藏后购买*100.00/总购买数 as decimal(18,2)),'%') as '浏览收藏后购买',
concat(cast(浏览加购后购买*100.00/总购买数 as decimal(18,2)),'%') as '浏览加购后购买',
concat(cast(浏览加购收藏后购买*100.00/总购买数 as decimal(18,2)),'%') as '浏览加购收藏后购买',
concat(cast(收藏后购买*100.00/总购买数 as decimal(18,2)),'%') as '收藏后购买',
concat(cast(加购后购买*100.00/总购买数 as decimal(18,2)),'%') as '加购后购买',
concat(cast(加购收藏后购买*100.00/总购买数 as decimal(18,2)),'%') as '加购收藏后购买',
concat(cast(直接购买*100.00/总购买数 as decimal(18,2)),'%') as '直接购买'
from user_path
- 部分用户没有浏览行为，之间加购或收藏或购买，这部分数据的产生的原因是本数据集是一个区间数据集，一些用 户11月25日之前已经点击浏览过商品，后续在11月 25 至12月3日的观测期间直接付款购买，所以没监测到购前行为。
- 在产生购买行为的路径中，占比最高的流程是浏览加购，说明加购后购买的可能性比较高，也可以看出用户对加购功能比较感兴趣
- 执行了加购和收藏的用户持续走到购买环节的可能性比未执行加购和收藏的用户要高，但收藏行为只占到了58.32%，有较大上升空间，可以考虑加强收藏功能的价值曝光
3.1.4 用户留存情况
3.1.4.1 用户留存率
留存是指用户在起始新增之后一段时间再次回到产品。留存率是指再次回到产品的用户数量与初始用户数量的比率，即 T 日新增用户中，在第 n 日（即 T+n 日）再次活跃的用户，占 T 日新增用户的比例。
select 日期,
当日新增用户,
concat(cast((次日留存人数*1.0/当日新增用户)*100 as decimal(18,2)),'%') as 次日留存率,
concat(cast((三日留存人数*1.0/当日新增用户)*100 as decimal(18,2)),'%') as 三日留存率,
concat(cast((五日留存人数*1.0/当日新增用户)*100 as decimal(18,2)),'%') as 五日留存率,
concat(cast((七日留存人数*1.0/当日新增用户)*100 as decimal(18,2)),'%') as 七日留存率
from (select register_date as 日期,
count(distinct u1.user_id) as 当日新增用户,
sum(case when datediff(day,register_date,login_date) = 1 then 1 else 0 end) as 次日留存人数,
sum(case when datediff(day,register_date,login_date) = 3 then 1 else 0 end) as 三日留存人数,
sum(case when datediff(day,register_date,login_date) = 5 then 1 else 0 end) as 五日留存人数,
sum(case when datediff(day,register_date,login_date) = 7 then 1 else 0 end) as 七日留存人数
from (select distinct user_id,min(date) as register_date
from Userbehavior
group by user_id)u1
left join (select distinct user_id,date as login_date
from Userbehavior)u2
on register_date <= login_date and u1.user_id = u2.user_id
group by register_date)t1
order by 日期
- 平台的用户留存率整体呈现出波动上升的趋势，越接近双十二活动预热期（12月1日后），用户再次打开应用的意愿越是强烈，在12月2日和12月3日，留存率上升到90%以上，反映出平台的营销效果比较好，和用户的需求贴合
- 平台整体的用户粘性比较高
- 在分析留存率时，进一步结合用户群组、渠道来源等分析，可以进一步提高平台个性化程度和渠道质量等，采取更具体的措施提高留存率
3.1.4.2 复购率
复购率即重复购买率，是指产生重复购买行为的客户数量占样本客户数量的比率。
select concat(cast(sum(case when if_buy >=2 then 1 else 0 end)*100.0/sum(case when if_buy > 0 then 1 else 0 end) as decimal(18,2)),'%') as 用户复购率
from user_behavior_view
- 平台的整体复购率比较高，可以通过推出复购优惠等方式，进一步提高复购率
3.2 用户商品偏好分析
3.2.1 商品四象限分析
计算每个商品的总浏览量和购买量，以所有商品的平均浏览量和平均购买量为标准，将商品划分到四个不同的象限中。
create view item_caculate as
select item_id,
sum(case when behavior_type = 'pv' then 1 else 0 end) as 商品浏览量,
sum(case when behavior_type = 'buy' then 1 else 0 end) as 商品购买量
from Userbehavior
group by item_id;
select cast(avg(商品浏览量*1.0) as decimal(18,2)) as 浏览量均值,
cast(avg(商品购买量*1.0) as decimal(18,2)) as 购买量均值
from item_caculate
where 商品购买量>0
create view item as
select item_id,
case when 商品浏览量>5.67 and 商品购买量>1.16 then '高浏览高购买'
when 商品浏览量<5.67 and 商品购买量>1.16 then '低浏览高购买'
when 商品浏览量>5.67 and 商品购买量<1.16 then '高浏览低购买'
when 商品浏览量<5.67 and 商品购买量<1.16 then '低浏览低购买'
end as 商品分类
from item_caculate
where 商品购买量>0;
select distinct 商品分类,
count(*) over(partition by 商品分类) as item_num,
count(*) over() as item_total,
concat(cast(count(*) over(partition by 商品分类)*100.00/count(*) over() as decimal(18,2)),'%') as item_percent
from item
order by 商品分类
- 低浏览低购买的人数明显多于其他三类商品，其次多的是高浏览低购买的商品
- 用户购买的商品集中在浏览量偏低的商品中，相比之下，浏览量偏高的商品的购买量比较少，反映了互联网经济下的长尾效应。那些产品流行度和销量小的产品，因其品种繁多，服务量巨大，累积产生的总效应超过了主流的产品。这意味着，对于平台而言，引入和覆盖更多小众品种的商品，带来的效益是非常可观的
3.2.2 品类四象限分析
SQL代码与商品四象限分析类似，将item_id替换为category_id，此处不再赘述。
- 低浏览低购买的品类占比最高
- 从品类四象限图中同样可以看出，购买行为集中在浏览量偏低的品类中，同样反映出长尾效应
3.3 用户价值分析
RFM模型用于衡量客户价值和客户创造利益的能力。其中，R指最近一次消费时间，F指一定时间内的消费频率，M指一定时间内的消费金额。此处只分析用户的R值和F值，并根据平均值，将用户分为四大类。
--创建视图：查询每位用户的最近购买日期和购买次数
create view rf_calculate as
select user_id,
max(date) as 最近购买日期,
count(behavior_type) as 购买次数
from Userbehavior
where behavior_type = 'buy'
group by user_id;
--创建视图：计算每位用户的R值
create view r_score as
select user_id,
最近购买日期,
case when datediff(day,最近购买日期,'2017-12-04') between 0 and 2 then 4
when datediff(day,最近购买日期,'2017-12-04') between 3 and 4 then 3
when datediff(day,最近购买日期,'2017-12-04') between 5 and 6 then 2
else 1 end as R值
from rf_calculate
----创建视图：计算每位用户的F值
create view f_score as
select user_id,
购买次数,
case when 购买次数 between 1 and 5 then 1
when 购买次数 between 6 and 9 then 2
when 购买次数 between 10 and 13 then 3
else 4 end as F值
from rf_calculate
--计算R值均值和F值均值
select cast(avg(R值*1.0) as decimal(18,2)) as r_avg from r_score;
select cast(avg(F值*1.0) as decimal(18,2)) as f_avg from f_score;
--创建视图：根据R值和F值求客户类型
create view rfm_model as
select r.user_id,最近购买日期,购买次数,R值,F值,
case when R值 >= 2.99 and F值 >= 1.17 then '重要价值客户'
when R值 < 2.99 and F值 >= 1.17 then '重要保持客户'
when R值 >= 2.99 and F值 < 1.17 then '重要发展客户'
when R值 < 2.99 and F值 < 1.17 then '重要挽留客户'
else null end as 客户类型
from r_score r inner join f_score f on r.user_id = f.user_id
--统计客户类型数量和占比
select distinct 客户类型,
count(*) over(partition by 客户类型) as 客户数量,
count(*) over() as 客户总数,
concat(cast(count(*) over(partition by 客户类型)*100.00/count(*) over() as decimal(18,2)),'%') as 占比
from rfm_model
order by 客户类型
- 重要发展客户是近期客户，但其忠诚度不高。对此类用户可以提供会员或忠诚度计划或推荐相关产品以实现向上销售，将其转化为拥护者和高价值客户
- 重要价值客户是最佳客户，他们是那些最新购买且最常购买的消费者。向重要价值客户提供VIP服务和个性化服务，鼓励他们可以成为新产品的早期采用者，有助于提高效益
- 重要保持客户是经常购买，但最近没有购买的客户。向他们发送个性化的重新激活活动，并提供续订和有用的产品以可以有效鼓励其再次购买
- 重要挽回客户是曾经光顾过且购买频率低，但最近没有光顾的顾客。可以通过设计召回策略和相关的促销活动带回他们，并进行调查以找出问题所在，避免将其输给竞争对手
4 结论
本文基于淘宝的用户行为数据集，对用户行为的规律和特质进行探究。对数据进行清洗后，通过用户行为习惯分析，揭露了平台流量分布特点及规律、平台用户粘性，展示了各个转化节点的转化率，反映了用户对页面的兴趣程度，展现了不同用户的购物特点及偏好。通过商品偏好分析，探究了产品的浏览量和购买量之间的关系。通过RFM模型，实现客户价值等级细分，针对整个用户行为数据（包含收藏、加购等行为）上综合分析客户潜在价值。同时，在三个用户行为分析角度，根据具体的数据，在平台运营问题上提出了积极的思考，对平台的运营提供参考价值。
5 参考
[1]程丽珍. 电商平台用户的行为分析和商品推荐[D].东华大学,2024.DOI:10.27012/d.cnki.gdhuu.2023.000155.
[2]案例：如何用SQL分析电商用户行为数据 | 人人都是产品经理
扫一扫
4121
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
