MySQL用户行为分析
一、背景介绍
在互联网整体人口红利消失的背景下，电商平台流量成本不断提升，逐渐由流量思维开始向用户思维转变。本文希望通过对用户在平台上留下的的日常操作数据，进一步来分析用户的消费行为特点。
二、理解数据
1.数据来源
阿里巴巴天池
本数据集(UserBehavior.csv)包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、收藏），数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。
2.字段含义
userid：用户ID，字符串类型，用户ID
itemid：商品ID，字符串类型，商品ID
categoryid：商品类目ID，字符串类型，商品所属类目ID
behavior：行为类型，字符串，枚举类型，包括('pv', 'buy', 'cart', 'fav')
timestamps：时间戳，行为发生的时间戳
注意到，用户行为类型共有四种，它们分别是
pv：商品详情页pv，等价于点击
buy ：商品购买
cart ：将商品加入购物车
fav：收藏商品
由于数据集的数据量较大，本文侧重于分析过程的学习，因此选取原数据集中的10万条数据导入本地MySQL数据库表userbehavior中，用于后续的用户行为分析。
三、提出问题及分析思路
1、用户整体购物情况怎样？
统计数据集中总的用户数，商品数，商品类别数，用户行为数。
2、用户粘性如何？
分析用户整体的跳出率、复购率，运用群组分析方法分析新增用户留存率。
3、用户的消费行为习惯怎样？
运用对比分析方法，分析用户随时间的活动规律。
4、用户在哪个环节流失的？
运用AARRR漏斗分析方法，分析用户行为路径及各个环节的转化率，运用假设检验分析方法找出导致用户流失的原因。
5、哪些是最有价值的用户？
运用RFM分析方法，对用户按价值进行分类，对于不同价值的用户进行精细化运营。
四、数据清洗
1、选择子集，这里导入10万条数据，均为有用子集
2、列重命名
因原表的列名为第一行数据，这里修改为对应列名
3、删除重复值
这里的每一条数据都是记录用户的行为数据，均有意义无需去重
4、缺失值处理
查询每列是否有空值
SELECT
count( userid ),
count( itemid ),
count( categoryid ),
count( behavior ),
count( timestamps )
userbehavior
WHERE
userid IS NULL
OR itemid IS NULL
OR categoryid IS NULL
OR behavior IS NULL
OR timestamps IS NULL;
从查询结果可以看出，不存在缺失值
5、一致化处理
由于timestamps列导入数据时数据类型为字符型，这里需要转换为日期格式，并创建date列和hour列分别存放对应的日期和小时
# 将timestamps列转换为日期格式
UPDATE userbehavior SET timestamps=FROM_UNIXTIME(timestamps,'%Y-%m-%d %H:%i:%s');
# 新建date列,从timestamp列截取日期
ALTER TABLE userbehavior add date varchar(10);
UPDATE userbehavior SET date=SUBSTRING(timestamps,1,10);
# 新建hour列,从timestamp列截取小时
ALTER TABLE userbehavior add hours varchar(10);
UPDATE userbehavior SET hours=SUBSTRING(timestamps,12,2);转换日期结果如下：
6、异常值处理
导入的数据选取的时间是2017年11月25日至2017年12月3日之间
1）通过查询date列，看是否存在异常值
# 查询日期列最小值和最大值
SELECT min(date),max(date) FROM userbehavior;
2）删除指定日期之外的异常值
# 删除指定日期之外的异常值
DELETE FROM userbehavior
WHERE date > '2017-12-03' or date < '2017-11-25';
# 查询日期列最小值和最大值检查是否处理异常值
SELECT min(date),max(date) FROM userbehavior;处理异常值后的结果
五、业务分析
1、用户整体购物情况怎样？
统计数据集中所有行为数据，总用户数，商品数，商品类别数，用户行为数。
# 统计数据集中所有行为数据，总用户数，商品数，商品类别数，用户行为数
SELECT
count( * ) AS 行为数,
count( DISTINCT userid ) AS 用户数,
count( DISTINCT itemid ) AS 商品数,
count( DISTINCT categoryid ) AS 商品类别数,
count( DISTINCT behavior ) AS 用户行为数
userbehavior;
从结果课程看出，数据集中总共有99956条行为数据，其中包含983位用户，64440个商品，3128个类别，4种用户行为。
2、用户粘性如何？
（1）跳出率
跳出率：用户只产生pv行为后没有后续其他行为的次数占所有访问次数的比例
# 跳出率
select concat(round(sum(pv)/(select count(*)from userbehavior)*100,2),'%') as 跳出率
(select userid,
sum(case behavior when 'pv' then 1 else 0 end) as pv,
sum(case behavior when 'fav' then 1 else 0 end) as fav,
sum(case behavior when 'cart' then 1 else 0 end) as cart,
sum(case behavior when 'buy' then 1 else 0 end) as buy
from userbehavior
group by userid) as a
where pv>0 and fav=0 and cart=0 and buy=0;结果如下：
跳出率为2.51%，用户跳出率低，说明平台对用户的吸引力较大。
（2）用户复购率
复购用户：产生两次或两次以上购买的用户并且购买日期不在同一天
用户复购率=复购用户数/总购买用户数
# 复购用户数
SELECT count(*) as 复购用户数
FROM # 查找复购用户
(SELECT userid,count(*) as 购买次数 FROM userbehavior
WHERE behavior='buy'
GROUP BY userid
HAVING count(DISTINCT date)>=2) as a;
# 总购买用户数
SELECT count(DISTINCT userid) as 总购买用户数 FROM userbehavior
WHERE behavior='buy';结果如下：
用户复购率=复购用户数/总购买用户数=365/671=54.40%
（3）复购用户购买次数占比
# 复购用户购买次数
SELECT sum(购买次数) as 复购用户购买次数
FROM # 查找复购用户
(SELECT userid,count(*) as 购买次数 FROM `userbehavior`
WHERE behavior='buy'
GROUP BY userid
HAVING count(DISTINCT date)>=2) as a;
# 总购买次数
SELECT count(*) as 总购买次数
FROM userbehavior
WHERE behavior='buy';结果如下：
复购用户购买次数占比=复购用户购买次数/总购买次数=1668/2101=79.40%
由以上结果可以看出，用户的复购率超过了50%，同时复购用户购买次数占比达到了79.40%，说明平台用户粘性很高，且购买积极性也高。
（4）留存率
使用群组分析方法，按每天新增的用户进行分组，通过每天新增用户的留存率来判断平台对用户的吸引力，由于数据集仅有九天的数据，这里使用2017年11月25日的所有用户作为第一天新增用户，仅计算这九天的新增用户留存率。
创建视图lading统计每天的用户名单（按userid和日期去重后的数据）
#创建视图lading统计每天的用户名单
create view lading as
select userid,date
from userbehavior
GROUP BY userid,date;计算每日新增用户留存率：
# 计算每日留存率
select a.date, count(distinct a.userid) as 日新增用户数,
concat(round(100*count(distinct if(datediff(b.date,a.date)=1, b.userid, null))/count(distinct a.userid),2),'%') as 次日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=2, b.userid, null))/count(distinct a.userid),2),'%') as 二日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=3, b.userid, null))/count(distinct a.userid),2),'%') as 三日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=4, b.userid, null))/count(distinct a.userid),2),'%') as 四日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=5, b.userid, null))/count(distinct a.userid),2),'%') as 五日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=6, b.userid, null))/count(distinct a.userid),2),'%') as 六日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=7, b.userid, null))/count(distinct a.userid),2),'%') as 七日留存率,
concat(round(100*count(distinct if(datediff(b.date,a.date)=8, b.userid, null))/count(distinct a.userid),2),'%') as 八日留存率
from #统计每日新增的用户名单
(select d1.*
from lading as d1 left join lading as d2 on d1.userid=d2.userid and d1.date>d2.date
where d2.date is null) as a
left join
lading as b on a.userid=b.userid
group by a.date;结果如下：
可视化结果如下：
由以上结果可以看出，每日的留存率都处在较高的水平，这说明用户的忠诚度较高。
综上所述：从用户的跳出率、复购率和留存率可以看出，平台用户粘性很高，忠诚度高且购买积极性也高。
3、用户的消费行为习惯怎样？
分析用户消费行为随时间的变化趋势
（1）每天用户访问变化情况
UV(unique visitor)，代表独立访客人数。
PV/UV：就是平均一个独立访问者所浏览的页面访问量，引申含义就是页面重复访问量，反映用户购买意向，比值越大反映用户购买意向强烈
SELECT date as 日期,
sum(case when behavior='pv' then 1 else 0 end) as 点击量,
count(DISTINCT userid) as 用户数,
(sum(case when behavior='pv' then 1 else 0 end)/count(DISTINCT userid)) as 重复访问量
FROM userbehavior
GROUP BY date;结果如下：
数据可视化如下：
由以上结果可以看出，在11-30日至12-3日期间，点击量和用户数呈明显上升趋势，都在12-2日到达峰值，而重复访问量先快速攀升而后又迅速下降，在12-1日达到峰值（12-1日为周五）。
（2）每天用户购买行为的特点
SELECT date as '日期',count(behavior) as 用户行为总量,
sum(case when behavior='pv' then 1 else 0 end) as 点击量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏数,
sum(case when behavior='cart' then 1 else 0 end) as 加购数,
sum(case when behavior='buy' then 1 else 0 end) as 购买数
FROM userbehavior
GROUP BY date;结果如下：
数据可视化如下：
由以上结果可以看出，在11-30日至12-3日期间用户购买行为整体呈上升趋势，综合（1）、（2）的结果，通过查询可知是由于双十二大促的预售活动导致访问量的激增，用户表现强烈的购买意愿，将商品收藏、加入购物车为双十二当天购买做准备。
（3）一天中用户的活跃时段分布
SELECT hours as '时段',count(behavior) as 用户行为总量,
sum(case when behavior='pv' then 1 else 0 end) as 点击量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏数,
sum(case when behavior='cart' then 1 else 0 end) as 加购数,
sum(case when behavior='buy' then 1 else 0 end) as 购买数
FROM userbehavior
GROUP BY hours
ORDER BY hours;结果如下：
数据可视化如下：
从结果可以看出，每日0点到4点用户活跃度快速降低，降到一天中的活跃量最低值，符合人们正常作息时间规律，5点到10点用户活跃度快速上升，12点到15点用户活跃度缓慢回升，18点到21点用户活跃度快速上升，达到一天中的最高值，在此时段用户最活跃可推送促销活动或产品直播，刺激消费者购买。
（4）一周中用户活跃时段分布
SELECT DATE_FORMAT(date,'%W') as '星期',
count(behavior) as 用户行为总量,
sum(case when behavior='pv' then 1 else 0 end) as 点击量,
sum(case when behavior='fav' then 1 else 0 end) as 收藏数,
sum(case when behavior='cart' then 1 else 0 end) as 加购数,
sum(case when behavior='buy' then 1 else 0 end) as 购买数
FROM userbehavior
GROUP BY DATE_FORMAT(date,'%W')
ORDER BY WEEKDAY(date);结果如下：
数据可视化如下：
从结果可以看出，每周一至周四用户活跃度较稳定，周五活跃度会有小幅上升，周末活跃度大幅度攀升达到最大值，这是周末双十二大促销活动引起，可见在周末推送促销活动更能刺激消费者购买。
4、用户在哪个环节流失的？
本次数据集里面用户行为字段中包括四种行为：详情页点击，收藏，加入购物车和购买，为分析每一位用户的行为路径，将数据集中全部数据按不同用户进行分组，统计每位用户行为分布。如果在数据中，点击行为数据大于0，购买行为大于0，并且其他两项行为为0，则可判定该用户的行为路径为 点击—购买，其他用户行为路径判定方法与此同理。
创建一个视图user_behavior，统计用户行为分布：
# 创建一个视图user_behavior，统计用户行为分布
create view user_behavior as
select userid,count(behavior) as total,
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='buy' then 1 else 0 end) as buy
from userbehavior
group by userid;
# 查看每一位用户的购物情况
select * from user_behavior ORDER BY 总行为数 desc;结果如下：
（1）用户整体购买行为
因为加购和收藏行为没有先后顺序，因此把它们合并在一起，分析用户整体购买行为。
从视图user_behavior中汇总各购买行为
# 从视图user_behavior中汇总各购买行为
select sum(total) as total,
sum(pv) as pv,
sum(fav) as fav,
sum(cart) as cart,
sum(buy) as buy
from user_behavior;结果如下：
计算各行为转化率
# 计算各行为转化率
SELECT concat(round(sum(pv)/sum(total)*100,2),'%') as total_pv,
concat(round((sum(fav)+sum(cart))/sum(total)*100,2),'%') as 'total_fav/cart',
concat(round(sum(buy)/sum(total)*100,2),'%') as total_buy
FROM user_behavior;结果如下：
将以上数据进行可视化：
从结果上可以看出，点击占比为89.7%，而加购与收藏的占比有8.19%，并且购买的占比仅有2.1%，这说明用户对网站的内容感兴趣，但转化购买的人相对少，这需要找出背后流失的原因。
（2）用户行为路径分析
分析用户购买行为路径中主要考虑以下几种：
计算过程如下：
# 从创建的视图user_behavior统计各路径流失情况
select sum(case when pv>0 then 1 else 0 end) as 'pv',
sum(case when pv>0 and buy>0 and cart=0 and fav=0 then 1 else 0 end) as 'pv-buy',
sum(case when pv>0 and (cart>0 or fav>0) then 1 else 0 end) as 'pv-cart/fav',
sum(case when pv>0 and (cart>0 or fav>0) and buy>0 then 1 else 0 end) as 'pv-cart/fav-buy',
sum(case when pv>0 and (cart>0 or fav>0) and buy=0 then 1 else 0 end) as 'pv-cart/fav-lost',
sum(case when pv>0 and cart=0 and fav=0 and buy=0 then 1 else 0 end) as 'pv-lost'
from user_behavior;结果如下：
从以上结果可以看出：
1） 用户点击后流失率为7%，这说明平台对用户具有很大吸引力。
2） 用户点击-收藏/加购-购买的转化率比点击-购买的转化率高，用户更喜欢把商品收藏或加入购物车后购买，应从产品交互界面、营销机制等方面积极引导用户把商品加入购物车或收藏。
3） 从流失率分析，用户点击-收藏/加购后的流失率比用户直接点击后流失率大，用户把商品收藏或加入购物车后有28.9%放弃购买，这就需要我们找出这部分用户流失的原因。
（3）用户流失原因分析
这里运用假设检验分析方法，从用户和产品两个维度提出假设，并通过收集证据来验证假设。
假设一：平台推荐商品不是用户想买商品？
要想验证平台推荐商品是不是用户想买商品，可以通过分析用户的高浏览量是否为高购买量
I.创建视图统计浏览量TOP10商品种类和购买量TOP10商品种类
# 创建浏览量TOP10商品种类视图
create view pv_top10_category as
select categoryid,count(*) as 浏览量top10
from userbehavior
WHERE behavior='pv'
GROUP BY categoryid
ORDER BY count(*) desc
limit 10;
# 创建购买量TOP10商品种类视图
create view buy_top10_category as
select categoryid,count(*) as 购买量top10
from userbehavior
WHERE behavior='buy'
GROUP BY categoryid
ORDER BY count(*) desc
limit 10;根据categoryid对比分析浏览量TOP10和购买量TOP10重合部分商品种类
# 浏览量TOP10商品种类
SELECT * FROM pv_top10_category as a
left join buy_top10_category as b
USING(categoryid);
# 购买量TOP10商品种类
SELECT * FROM buy_top10_category as a
left join pv_top10_category as b
USING(categoryid);结果整理如下：
由结果可以看出，浏览量TOP10商品种类和购买量TOP10商品种类中仅有50%的商品种类重合，重合度并不高。
II.创建视图统计浏览量TOP10商品和购买量TOP10商品
# 创建浏览量TOP10商品视图
create view pv_top10_item as
select itemid,count(*) as 浏览量top10
from userbehavior
WHERE behavior='pv'
GROUP BY itemid
ORDER BY count(*) desc
limit 10;
# 创建购买量TOP10商品视图
create view buy_top10_item as
select itemid,count(*) as 购买量top10
from userbehavior
WHERE behavior='buy'
GROUP BY itemid
ORDER BY count(*) desc
limit 10;根据itemid对比分析浏览量TOP10和购买量TOP10重合部分商品
# 浏览量TOP10商品
SELECT * FROM pv_top10_item as a
left join buy_top10_item as b
USING(itemid);
# 购买量TOP10商品
SELECT * FROM buy_top10_item as a
left join pv_top10_item as b
USING(itemid);结果整理如下：
由结果可以看出，浏览量TOP10商品和购买量TOP10商品中没有一个重复的商品ID，重合度为0。
综上所述，用户高浏览量并不能带来高购买量，即平台推荐商品不是用户想买商品，假设一成立。
假设二：商品种类太少？
可以通过统计商品类目数量和涵盖的商品数量来验证商品种类是否太少
# 整体商品类目与对应的商品数量
SELECT
count( DISTINCT categoryid ) AS 商品类目数量,
count( DISTINCT itemid ) AS 商品数量
userbehavior;结果如下：
由以上结果可以看出，数据集共有3128中商品类目涵盖64440中商品，供用户选择的商品种类多，所以假设二不成立。
假设三：商品没有吸引力？
通过分析商品购买次数的占比，验证商品是否没有吸引力
统计每个商品的购买次数
# 每个商品的购买次数
SELECT itemid,count(*) AS 购买次数
FROM userbehavior
WHERE behavior = 'buy'
GROUP BY itemid
ORDER BY count(*) DESC;结果如下：
购买次数占比
# 购买次数占比
SELECT
a.购买次数,
count( a.itemid ) AS 商品数,
concat(
round( count( a.itemid ) / ( SELECT count( DISTINCT itemid ) FROM userbehavior WHERE behavior = 'buy' ) * 100, 2 ),
) AS 占比
( SELECT itemid, count( * ) AS 购买次数 FROM userbehavior WHERE behavior = 'buy' GROUP BY itemid ) AS a
GROUP BY
a. 购买次数;结果如下：
可视化结果：
由以上结果可以看出，商品购买次数最多不超过4次，且购买1次的商品占比94.81%，说明产品复购率低，商品对用户没有足够多的吸引力，假设三成立。
总结：
1） 平台对用户吸引力巨大，用户在平台的行为路径中通过点击-收藏/加购-购买的转化率最高，应积极引导用户把商品加入购物车或收藏
2） 用户流失的环节主要在收藏/加购，流失的原因主要是平台推荐的商品并非用户喜欢的商品，同时平台也缺乏足够吸引的爆款产品
3） 优化平台推荐机制，把更多流量给到顾客愿意购买的商品，通过打造爆款产品，吸引更多用户复购
5、哪些是最有价值的用户？
运用RFM分析方法，对用户按价值分层，找出最有价值用户。
RFM是3个指标的缩写，最近一次消费时间间隔（Recency），消费频率（Frequency），消费金额（Monetary）。
R（Recency）：用户最近一次交易时间的间隔。R值越小，表示用户上一次消费离得越近，户价值越高。
F（Frequency）：用户在最近一段时间内交易的次数。F值越大，表示用户交易越频繁，用户价值越高。
M（Monetary）：用户在最近一段时间内交易的金额。M值越大，表示客户消费金额越高越高，用户价值越大。
通过对RFM这3个值对应的价值的高低排序组合，得到了下面用户分类的规则：
由于数据源没有相关的金额数据，暂且通过 R 和 F 的数据对客户价值进行分类。
1）计算R、F 值
由于数据集包含的时间是从2017年11月25日至2017年12月3日，这里选取2017年12月3日作为计算日期，统计客户最近产生交易的日期距离2017年12月3日间隔天数和交易的次数。
# 最近一次消费时间间隔R和消费频率F
SELECT userid,DATEDIFF('2017-12-04',max(date)) AS R,count(*) as F
FROM userbehavior
WHERE behavior = 'buy'
GROUP BY userid;结果如下：
2）给R、F 值按价值打分
根据上面计算得到的用户R、F值，定义一个打分规则，如下图：
打分计算过程如下：
# 对最近一次消费时间间隔（R）和消费频率（F）进行评分，并创建视图rfm
create view rfm as
SELECT a.*,(case
when R>20 then 1
when R between 10 and 20 then 2
when R between 5 and 10 then 3
when R between 3 and 5 then 4
when R<=3 then 5
else 0 end) as Rscore,
(case
when F<=2 then 1
when F between 2 and 6 then 2
when F between 6 and 8 then 3
when F between 10 and 20 then 4
when F>20 then 5
else 0 end) as Fscore
(SELECT userid,DATEDIFF('2017-12-04',max(date)) AS R,count(*) as F
FROM userbehavior
WHERE behavior = 'buy'
GROUP BY userid) as a;结果如下：
3）计算打分平均值
# 计算打分平均值
SELECT avg(Rscore) as avg_R, avg(Fscore) as avg_F
FROM rfm;结果如下：
4） 用户分类
按照R值和F值的平均分对用户进行分类
# 用户分类
select userid,Rscore,Fscore,
(case when Rscore>4.158 then '高' else '低' end) as 'R值高低',
(case when Fscore>1.5484 then '高' else '低' end) as 'F值高低'
from rfm;结果如下：
因为我们这里仅考虑R、F因素，把R、F值都低的用户定义为一般价值用户，因此按下面用户分类规则进行分类：
对用户进行标记：
#对用户进行标记
select userid,R值高低,F值高低,
(case
when R值高低='高' and F值高低='高' then '重要价值用户'
when R值高低='低' and F值高低='高' then '重要保持用户'
when R值高低='高' and F值高低='低' then '重要发展用户'
when R值高低='低' and F值高低='低' then '一般价值用户'
else 0
end) as '用户分类'
(select userid,Rscore,Fscore,
(case when Rscore>4.158 then '高' else '低' end) as 'R值高低',
(case when Fscore>1.5484 then '高' else '低' end) as 'F值高低'
from rfm) as a;结果如下：
5） 对用户分类进行统计
# 对用户分类统计
select 用户分类,count(*) as 用户数
(select userid,R值高低,F值高低,
(case
when R值高低='高' and F值高低='高' then '重要价值用户'
when R值高低='低' and F值高低='高' then '重要保持用户'
when R值高低='高' and F值高低='低' then '重要发展用户'
when R值高低='低' and F值高低='低' then '一般价值用户'
else 0
end) as '用户分类'