一. 认识数据
数据来自天池推荐算法大赛，点击获取数据。
数据集包含了：2014年11月18日-2014年12月18日期间，用户在该电商平台的购物行为数据。
数据集有6个字段，说明如下图：
二. 分析目的
从以下4个维度的核心指标了解、认识用户在该电商平台的购物行为情况。
1.流量访问情况
核心指标：PV、UV、跳失率、一天二十四小时的活跃情况、一周七天的活跃情况、一个月每天的活跃情况、点击/收藏/加购最多的商品id和商品品类。
2.用户购买情况
总购买行为数、总购买用户数和人均购买行为数、复购率、用户购买次数前十的商品和品类。
3.用户行为转化情况
点击/收藏/加购/购买四种行为的占比、成交转化率、收藏转化率、加购转化率。
4.用户留存情况
第一周新增用户，在接下来三周内的留存曲线。
三. 用Pandas清洗数据
导入数据
import pandas as pd
import numpy as np
df = pd.read_csv('user_behavior.csv')
df.head()
本次分析不会用到字段user_geohash，将其删掉。
del df['user_geohash']
发现字段time是字符而非datetime类型，将其改掉。
df['time'] = pd.to_datetime(df['time'], format='%Y/%m/%d %H:%M:%S')
df.head()
检查各字段下是否有缺失值
print([len(df),df['user_id'].count(),
df['item_id'].count(),
df['behavior_type'].count(),
df['item_category'].count(),
df['time'].count()])
数据总行数和各字段数据数量相同，没有缺失值。
数据集共有12256906条数据，导入MySQL中，计算速度会比较慢，因此取十分之一的数据。注意，此时不能直接在整体数据上随机取，为保证用户行为数据的完整性，这里要随机抽取十分之一用户的行为数据。先在所有用户中随机取十分之一。
total_user = df['user_id'].drop_duplicates() # user_id 去重
sample_user = total_user.sample(n=10%*total_user.count(), random_state=1) # 用户随机取样十分之一
在完整的数据集中，将样本用户的行为数据取出。
sample_behavior = df[df['user_id'].isin(sample_user)]
len(sample_behavior)
样本用户的行为数据有1214056条。
把样本数据存入sample_behavior.csv文件，待导入MySQL中进行分析。
sample_behavior.to_csv('sample_behavior.csv', header=0, index=0)
四. 用MySQL制作指标模型
接下来，把样本数据导入MySQL中的表，开始计算指标数值。
-- 建表
create table user_behavior_sample (
user_id varchar(16) not null,
item_id varchar(16) not null,
behavior_type tinyint not null,
item_category varchar(8) not null,
time datetime not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 载入数据
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/sample_behavior.csv'
into table user_behavior_sample character set utf8mb4
fields terminated by ',';
看一下样本数据的时间范围。
select min(time),max(time) from user_behavior_sample;
发现样本数据时间范围是：2014-11-18 00:00:00至2014-12-18 23:00:00。为取整月30天的数据，去除2014-12-18的数据。
delete from user_behavior_sample where date(time) = '2014-12-18';
select min(time),max(time) from user_behavior_sample;
看一下，现在样本的数据量和样本中的用户数。
select count(*) as 样本数据量,count(distinct user_id) as 样本用户数
from user_behavior_sample;
开始计算各项指标。
1. 流量访问情况
计算样本PV
select count(*) as 'PV'
from user_behavior_sample
where behavior_type = 1;
计算样本UV，就是样本用户数998。
样本跳失率（只有点击行为，而没有收藏、加购、购买行为的用户比例）
select concat(round(count(user_id)/(select count(distinct user_id) from user_behavior_sample)*100,2),'%') as 跳失率 # 即跳失的用户数/UV
from (select user_id
from user_behavior_sample
group by user_id
having avg(behavior_type)=1)as 跳失名单;
样本用户每天不同时段的行为情况
select count(behavior_type) as 总行为数,
(case when hour(time)=0 then 0
when hour(time)=1 then 1
when hour(time)=2 then 2
when hour(time)=3 then 3
when hour(time)=4 then 4
when hour(time)=5 then 5
when hour(time)=6 then 6
when hour(time)=7 then 7
when hour(time)=8 then 8
when hour(time)=9 then 9
when hour(time)=10 then 10
when hour(time)=11 then 11
when hour(time)=12 then 12
when hour(time)=13 then 13
when hour(time)=14 then 14
when hour(time)=15 then 15
when hour(time)=16 then 16
when hour(time)=17 then 17
when hour(time)=18 then 18
when hour(time)=19 then 19
when hour(time)=20 then 20
when hour(time)=21 then 21
when hour(time)=22 then 22
when hour(time)=23 then 23
else '其他' end) as hours,
sum(case when behavior_type=1 then 1 else 0 end) as 点击数,
sum(case when behavior_type=2 then 1 else 0 end) as 收藏数,
sum(case when behavior_type=3 then 1 else 0 end) as 加购数,
sum(case when behavior_type=4 then 1 else 0 end) as 购买数
from user_behavior_sample
group by hours
order by hours;
可以看出，白天9-17点期间四类行为数量平稳波动，晚上19-23点期间点击、收藏、加购行为数量较白天增长明显，但购买行为数量增长非常小。对电商平台来说，晚上19-23点适合向用户种草。
样本用户每周不同日子的行为情况
select count(behavior_type) as 总行为数,
(case when dayofweek(time)=1 then 7
when dayofweek(time)=2 then 1
when dayofweek(time)=3 then 2
when dayofweek(time)=4 then 3
when dayofweek(time)=5 then 4
when dayofweek(time)=6 then 5
when dayofweek(time)=7 then 6
else '其他' end) as 星期,
sum(case when behavior_type=1 then 1 else 0 end) as 点击数,
sum(case when behavior_type=2 then 1 else 0 end) as 收藏数,
sum(case when behavior_type=3 then 1 else 0 end) as 加购数,
sum(case when behavior_type=4 then 1 else 0 end) as 购买数
from user_behavior_sample
group by 星期
order by 星期;
可以看出，用户在周六、周日、周一的四类行为都不如周二、周三、周四、周五活跃，用户在工作日逛网店的需求强于周末。另外，周五出现了加购和购买的高峰，是因为统计周期内有“双12活动”，活动当天是周五，它提升了整个周期内周五的活跃表现。
样本用户在30天统计周期内不同日子的行为情况
select count(behavior_type) as 总行为数,
(case when day(time)=1 then 1
when day(time)=2 then 2
when day(time)=3 then 3
when day(time)=4 then 4
when day(time)=5 then 5
when day(time)=6 then 6
when day(time)=7 then 7
when day(time)=8 then 8
when day(time)=9 then 9
when day(time)=10 then 10
when day(time)=11 then 11
when day(time)=12 then 12
when day(time)=13 then 13
when day(time)=14 then 14
when day(time)=15 then 15
when day(time)=16 then 16
when day(time)=17 then 17
when day(time)=18 then 18
when day(time)=19 then 19
when day(time)=20 then 20
when day(time)=21 then 21
when day(time)=22 then 22
when day(time)=23 then 23
when day(time)=24 then 24
when day(time)=25 then 25
when day(time)=26 then 26
when day(time)=27 then 27
when day(time)=28 then 28
when day(time)=29 then 29
when day(time)=30 then 30
else '其他' end) as 日期,
sum(case when behavior_type=1 then 1 else 0 end) as 点击数,
sum(case when behavior_type=2 then 1 else 0 end) as 收藏数,
sum(case when behavior_type=3 then 1 else 0 end) as 加购数,
sum(case when behavior_type=4 then 1 else 0 end) as 购买数
from user_behavior_sample
group by 日期
order by 日期;
可以看出，“双12”活动促进了各类用户行为的增长，点击、加购、和购买行为增长极为明显。平台可以根据12月9日-11日期间用户的点击和加购行为，判断哪些商品可能会大卖，哪些商品曝光力度不够，进而为活动大卖做更充足的准备。
以下分别是关注热度最高的10个商品item_id和关注热度最高的10个品类item_category（注：关注热度=点击数+收藏数+加购数）。这些商品和品类可能会成为爆款和热销品，要重点做好在运营、商品管理、库存管理等方面的工作。
关注热度最高的10个商品item_id。
select item_id,item_category,
sum(case when b_t in (1,2,3) then 1 else 0 end) as 关注热度
from user_behavior_sample
group by item_id
order by 关注热度 desc
limit 0,10;
关注热度最高的10个品类item_category。
select item_category,
sum(case when behavior_type in (1,2,3) then 1 else 0 end) as 关注热度
from user_behavior_sample
group by item_category
order by 关注热度 desc
limit 0,10;
2. 购买情况
select count(behavior_type) as 总购买行为数,
count(distinct user_id) as 总购买用户数,
count(behavior_type)/count(distinct user_id) as 人均购买行为数
from user_behavior_sample
where behavior_type=4;
计算复购率
select concat(round(count(*)/ (select count(distinct user_id) as 有购买行为的用户数 from user_behavior_sample where behavior_type=4)*100,2),'%') as 复购率
from (select user_id
from user_behavior_sample
where behavior_type=4
group by user_id
having count(distinct date(time))>=2) as 复购名单;
受到“双12”活动影响，复购率非常高。
以下分别是购买热度最高的10个商品item_id和购买热度最高的10个品类item_category。这些商品和品类是平台的爆款和热销品，起到引流、提升销售额和销量的作用。
购买次数最多的10个商品
select item_id,
sum(case when behavior_type =4 then 1 else 0 end) as 购买行为
from user_behavior_sample
group by item_id
order by 购买行为 desc
limit 0,10;
购买次数最多的10个品类
select item_category,
sum(case when behavior_type =4 then 1 else 0 end) as 购买行为
from user_behavior_sample
group by item_category
order by 购买行为 desc
limit 0,10;
3. 用户行为转化情况
计算点击/收藏/加购/购买四种行为的占比分布
select behavior_type,count(behavior_type) as 行为数量,
concat(round(count(behavior_type)/(select count(behavior_type) from ubs)*100,2),'%') as 行为占比
from user_behavior_sample
group by behavior_type
order by count(behavior_type) desc;
计算成交转化率
select concat(round(sum(case when behavior_type=4 then 1 else 0 end)/sum(case when behavior_type=1 then 1 else 0 end)*100,2),'%') as 成交转化率
from user_behavior_sample;
计算收藏转化率
select concat(round(count(*)/(select count(*) from ubs where behavior_type=2)*100,2),'%') as 收藏转化率
from (select user_id,item_id,behavior_type,date(time)
from user_behavior_sample
group by user_id,item_id,date(time)
having sum(case when behavior_type=2 then 1 else 0 end)>0 and sum(case when behavior_type=4 then 1 else 0 end)>0) as 收藏购买事件;
计算加购转化率
select concat(round(count(*)/(select count(*) from ubs where behavior_type=3)*100,2),'%') as 加购转化率
from (select user_id,item_id,behavior_type,date(time)
from user_behavior_sample
group by user_id,item_id,date(time)
having sum(case when behavior_type=3 then 1 else 0 end)>0 and sum(case when behavior_type=4 then 1 else 0 end)>0) as 加购购买事件;
成交转化率是0.99%、收藏转化率2.45%、加购转化率15.41%。
4. 用户留存情况
第一周（'2014-11-18' - '2014-11-24'）新增用户在接下来连续三周内的留存曲线。
select (case when date(time) between '2014-11-18' and '2014-11-24' then '1第一周'
when date(time) between '2014-11-25' and '2014-12-01' then '2第二周'
when date(time) between '2014-12-02' and '2014-12-08' then '3第三周'
when date(time) between '2014-12-09' and '2014-12-15' then '4第四周'
else '其他' end) as 星期,
count(distinct user_id) as 留存人数
from user_behavior_sample
where behavior_type = 4 and user_id in (select distinct user_id from ubs where behavior_type=4 and date(time) between '2014-11-18' and '2014-11-24')
group by 星期
order by 星期;
第二周和第三周的留存数、留存率在以更慢的速度降低。但在“双12”活动影响下，第四周的留存数、留存率明显高于第二周和第三周。
五. 总结
1.本次分析取原数据集十分之一用户的全部行为数据做样本。
2.从数据指标中，可以从流量、购买、转化和留存四个维度，了解该电商平台的基本情况。也可以看到，“双12”活动对一些指标产生了明显的影响。
3.数据集中time字段精确到“时”，这样无法判断数据集中的重复值是真实的用户重复性行为，还是系统产生的脏数据，如果精确到“秒”即可判断。
4.本人对电商领域关键指标值的业界水平认知还有不足，并且数据维度比较少，没有做出更深入的解读。以后通过工作与学习，会继续迭代分析内容。
