MySQL数据分析2_tb用户行为分析

MySQL数据分析2_tb用户行为分析

本文数据来源于：天池
使用工具：MySQL，tableau

1.简介

用户行为是淘宝上用户行为的数据集，用于带有隐式反馈的推荐问题。该数据集由阿里巴巴提供。

2.数据理解

该数据集包括随机选择的约100万用户，以及这些用户在2017年11月25日至12月3日之间具有点击，购买，向购物车中添加商品和偏爱商品的行为，所有数据量超过1亿行，考虑到数据库运行时间，这里我就取200W左右的值进行分析。
字段理解如下：

字段	解释
user_id	用户编号
item_id	商品编号
category_id	系列编号
behavior type	行为类型（PV：项目详细信息页面的页面视图，相当于项目单击；buy：购买物品；cart：将项目添加到购物车；fav：收藏一个项目）
timestamp	行为发生时的时间戳

3.设立目标

1. 分析用户行为和常见电商指标，找到需要加强的环节；
2. 分析最核心用户群体，对其进行分析；
3. 分析用户在一定的时间周期内的用户行为，找到用户行为规律。

4.数据清洗

1）选择数据子集

这里选定导入的所有数据进行分析

2）删除重复值

这里为用户购物行为，确实可能存在相同的产品，用户会产生不同的操作的现象（比如先加购再购买），故不作处理

3）缺失值处理

SELECT count(1)
FROM userbehavior
WHERE user_id=' ' OR user_id IS NULL;

SELECT count(1)
FROM userbehavior
WHERE item_id=' ' OR item_id IS NULL;

SELECT count(1)![在这里插入图片描述](https://img-blog.csdnimg.cn/20200922154037150.PNG?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjMxNTcwMg==,size_16,color_FFFFFF,t_70#pic_center)

FROM userbehavior
WHERE category_id=' ' OR category_id IS NULL;

SELECT count(1)
FROM userbehavior
WHERE `behavior type`=' ' OR `behavior type` IS NULL;

SELECT count(1)
FROM userbehavior
WHERE `timestamp`=' ' OR `timestamp` IS NULL;

结果均为0，不存在缺失值。

4）合法性检查

检查数据有无不合逻辑的值，在本项目中表现为，用户行为时间是否在2017年11月25日至12月3日范围内：

-- 合法性检查
SELECT *,FROM_UNIXTIME(`timestamp`)
FROM userbehavior
WHERE `timestamp`<UNIX_TIMESTAMP('2017-11-25') OR `timestamp`>UNIX_TIMESTAMP('2017-12-4')

结果发现有992条记录是不合法的，如下图

删除以上数据

DELETE FROM userbehavior 
WHERE `timestamp`<UNIX_TIMESTAMP('2017-11-25') OR `timestamp`>UNIX_TIMESTAMP('2017-12-4')

5）数据一致性处理

为了之后研究方便这里将时间戳转化为正常的日期格式，并分成两列，一列为日期，一列为小时。

SELECT *,FROM_UNIXTIME(`timestamp`,'%Y-%m-%d') date,FROM_UNIXTIME(`timestamp`,'%H') hour
FROM userbehavior;

ALTER TABLE userbehavior ADD COLUMN date VARCHAR(255) NULL;
ALTER TABLE userbehavior ADD COLUMN hour VARCHAR(255) NULL;

UPDATE userbehavior SET date=FROM_UNIXTIME(`timestamp`,'%Y-%m-%d');
UPDATE userbehavior SET hour=FROM_UNIXTIME(`timestamp`,'%H');

数据清洗结束，清洗后的部分数据截图如下

5.数据分析

1. 分析用户行为和常见电商指标，找到需要加强的环节：

分析用户行为套用AARRR模型，
在本次数据中，主要涉及到用户购物行为，主要指标如下：
页面访问量PV：1792729
独立访客数UV：19569
每个访客的平均访问量PV/UV：91.6107

SELECT count(DISTINCT(user_id)) AS UV,
(SELECT count(1) FROM userbehavior WHERE `behavior type`='pv')AS PV, 
(SELECT count(1) FROM userbehavior WHERE `behavior type`='pv')/count(DISTINCT(user_id)) AS 'PV/UV'
FROM userbehavior;

跳出率：≈0

SELECT *,count(1)
FROM(SELECT user_id
FROM userbehavior
GROUP BY user_id
HAVING count(1)=1)t

2017年11月25日至12月3日共9天中，只有一人浏览一个网页就退出了APP，跳出率可以忽略不计，说明淘宝是很博人眼球的app。
对用户行为进行分析，完整的用户购物行为为 “浏览PV”→“加入购物车cat”/“收藏fav”→“购买buy”。

SELECT `behavior type`,count(1) 数量
FROM userbehavior
GROUP BY `behavior type`;

当然这里没有考虑在商品浏览页面直接进行购物的用户，从上图来看，收藏&添加转化率较低，为9.41%，说明顾客浏览的商品中仅存在不到10%的商品吸引到顾客，而成交转化率仅为2.25%，说明大多数顾客都是以浏览商品为主，不过结合时间因素，本数据所在的时间处于双十一刚过，双十二还没到的时候，人们可能刚刚结束上一次大型消费，在为下一次大型促销做准备。
独立访客漏斗模型如下：

SELECT `behavior type`,count(DISTINCT user_id) 行为数量
FROM userbehavior
GROUP BY `behavior type`;

可以看出付费转化率高达68.5%。

2. 分析最核心用户群体，对其进行分析

划分用户价值套用RMF模型
构建模型：先筛选出有购买行为的记录

CREATE VIEW RFM AS
SELECT *,DATEDIFF('2017-12-03',date) AS 'diff'
FROM userbehavior
WHERE userbehavior.`behavior type`='buy';

R：最近一次购买日期距2017年12月3日的天数。
分为5挡

天数差值	分数
0	4
1/2	3
3/4	2
5/6	1
7/8	0

建立R视图

CREATE VIEW R AS
SELECT DISTINCT user_id,
case WHEN min(diff)=0 THEN 4
when min(diff)<=2 THEN 3
WHEN min(diff)<=4 THEN 2
WHEN min(diff)<=6 THEN 1
ELSE 0 END score
FROM RFM
GROUP BY user_id
ORDER BY score DESC;

部分结果如下：

F：在2017年11月25日至12月3日内的购买次数。
查看在这段周期内所有用户最大购买和最小购买次数：

SELECT user_id,max(a)
FROM
(SELECT user_id,count(1) as 'a'
FROM RFM
GROUP BY user_id)t;

SELECT user_id,min(a)
FROM
(SELECT user_id,count(1) as 'a'
FROM RFM
GROUP BY user_id)t;

结果最大为72次，最小为1次，分为5档

购买次数	分数
1~15	0
16~30	1
31~45	2
46~60	3
61~72	4
建立F视图：

-- 建立F参数模型
CREATE VIEW F AS
SELECT DISTINCT user_id,
CASE WHEN count(user_id)<=15 THEN 0
WHEN count(user_id)<=30 THEN 1
WHEN count(user_id)<=45 THEN 2
WHEN count(user_id)<=60 THEN 3
WHEN count(user_id)<=72 THEN 4
END score
FROM RFM
GROUP BY user_id
ORDER BY score DESC;

M：在2017年11月25日至12月3日内的金额，这里不涉及金额，不考虑
联结视图R和F，选出分数共同为4的顾客

SELECT r.user_id
FROM r INNER JOIN f ON
r.user_id=f.user_id
WHERE r.score=4 AND f.score=4;

这部分用户就是最核心用户群体，商家做的各种营销服务应优先考虑这部分用户的意见。

3. 分析用户在一定的时间周期内的用户行为，找到用户行为规律。

因为原始数据记录时间有限，这里就按照两个时间维度来分析用户行为：
1.以天为单位，分析用户在一周内的用户行为；
2.以小时为单位，分析用户在一天之间的用户行为。

以天为单位，分析用户在一周内的用户行为：
选取2017年11月25日至12月1日的数据进行分析

-- 选取2017年11月25日至12月1日的数据进行分析
SELECT t1.*,t2.PV,t3.BUY,t4.fav,t5.cart
FROM 
(SELECT date,count(1) AS 'count'
FROM userbehavior
WHERE date BETWEEN '2017-11-25' AND '2017-12-01'
GROUP BY date)t1
INNER JOIN
(SELECT date,count(1) AS PV
FROM userbehavior
WHERE date BETWEEN '2017-11-25' AND '2017-12-01' AND `behavior type`='pv'
GROUP BY date)t2
ON t1.date=t2.date
INNER JOIN
(SELECT date,count(1) AS BUY
FROM userbehavior
WHERE date BETWEEN '2017-11-25' AND '2017-12-01' AND `behavior type`='buy'
GROUP BY date)t3
ON t2.date=t3.date
INNER JOIN
(SELECT date,count(1) AS fav
FROM userbehavior
WHERE date BETWEEN '2017-11-25' AND '2017-12-01' AND `behavior type`='fav'
GROUP BY date)t4
ON t3.date=t4.date
INNER JOIN
(SELECT date,count(1) AS cart
FROM userbehavior
WHERE date BETWEEN '2017-11-25' AND '2017-12-01' AND `behavior type`='cart'
GROUP BY date)t5
ON t5.date=t4.date;

可视化处理：

可以看出所有用户在周五和周日的用户行为达到高峰，分析用户在周五的下班会刺激用户放松消费，周六可能顾客会忙着出门社交，所以用户行为量会低于周五，而周末用户可能会选择在家休息，有更长的时间使用app，所以用户行为量又再次达到高峰，过了周末又恢复正常工作，所以行为量再次下降。
所以商家可以选择在周五和周日来进行一些促销活动。
以小时为单位，分析用户在一天内的用户行为：
选择两天对比分析，选择2017/11/27（周一）和2017/11/26（周日）

-- 选取2017年11月27日（周一）的数据进行分析
SELECT t1.*,t2.PV,t3.BUY,t4.fav,t5.cart
FROM 
(SELECT hour,count(1) AS 'count'
FROM userbehavior
WHERE date='2017-11-27' 
GROUP BY hour)t1
INNER JOIN
(SELECT hour,count(1) AS PV
FROM userbehavior
WHERE date='2017-11-27' AND `behavior type`='pv'
GROUP BY hour)t2
ON t1.`hour`=t2.`hour`
INNER JOIN
(SELECT hour,count(1) AS BUY
FROM userbehavior
WHERE date='2017-11-27' AND `behavior type`='buy'
GROUP BY hour)t3
ON t2.`hour`=t3.`hour`
INNER JOIN
(SELECT hour,count(1) AS fav
FROM userbehavior
WHERE date='2017-11-27' AND `behavior type`='fav'
GROUP BY hour)t4
ON t3.`hour`=t4.`hour`
INNER JOIN
(SELECT hour,count(1) AS cart
FROM userbehavior
WHERE date='2017-11-27' AND `behavior type`='cart'
GROUP BY hour)t5
ON t5.`hour`=t4.`hour`
ORDER BY `hour`;

-- 选取2017年11月26日（周日）的数据进行分析
SELECT t1.*,t2.PV,t3.BUY,t4.fav,t5.cart
FROM 
(SELECT hour,count(1) AS 'count'
FROM userbehavior
WHERE date='2017-11-26' 
GROUP BY hour)t1
INNER JOIN
(SELECT hour,count(1) AS PV
FROM userbehavior
WHERE date='2017-11-26' AND `behavior type`='pv'
GROUP BY hour)t2
ON t1.`hour`=t2.`hour`
INNER JOIN
(SELECT hour,count(1) AS BUY
FROM userbehavior
WHERE date='2017-11-26' AND `behavior type`='buy'
GROUP BY hour)t3
ON t2.`hour`=t3.`hour`
INNER JOIN
(SELECT hour,count(1) AS fav
FROM userbehavior
WHERE date='2017-11-26' AND `behavior type`='fav'
GROUP BY hour)t4
ON t3.`hour`=t4.`hour`
INNER JOIN
(SELECT hour,count(1) AS cart
FROM userbehavior
WHERE date='2017-11-26' AND `behavior type`='cart'
GROUP BY hour)t5
ON t5.`hour`=t4.`hour`
ORDER BY `hour`;

对比工作日和休息日的用户数据图可知，两数据曲线趋势基本一致，在工作日的中午13:00和晚上21:00 ~ 22:00的时候用户会达到一天之中的高峰期，而周末的高峰期重点体现为晚上21:00 ~ 22:00，由此可知，商家应在晚上21:00 ~ 22:00内增加广告和消息推送，或者是采取限时折扣的方式来增加客流量，促进消费。

6.总结

1. 分析用户行为和常见电商指标，找到需要加强的环节

1）获取用户
本数据中未包含获取用户的信息，可以使用用户浏览的行为作为获取用户的标志
2）激活用户
收藏&添加转化率低，说明大部分商品吸引不到顾客，商家可以优化商品推送服务，根据用户浏览信息，推送用户感兴趣的商品；
还可以通过一些营销手段，比如折扣，拼团，分享得优惠券等来促进消费。
不过结合时间因素，本数据所在的时间处于双十一刚过，双十二还没到的时候，人们可能刚刚结束上一次大型消费，在为下一次大型促销做准备。
3）用户留存
每个访客的平均访问量PV/UV：91.6107，跳出率约为0，这说明淘宝APP很吸引人们的关注，不需要考虑留存问题。
4）增加收益
付费转化率高达68.5%。
5）用户推广
针对地区平台补贴商家邮费，扩大在小城市的用户消费群体。

2. 分析最核心用户群体，对其进行分析

使用RMF模型进行分析，R和F都为4的用户群体，是粘性最大最忠诚的核心用户群体，商家制定营销活动或平常运营时，应该特别注意这些用户群体的利益和反馈；对于R值为4，F值较低的用户群体，是重要唤回用户，可以通过推送优惠信息，或者对这些用户启用用户回归福利来唤回这部分用户。

3. 分析用户在一定的时间周期内的用户行为，找到用户行为规律

以一天为周期进行分析可知，每天客流量高峰期都在晚上21:00 ~ 22:00，由此可知，商家应在晚上21:00 ~ 22:00内增加广告和消息推送，或者是采取限时折扣的方式来增加客流量，促进消费。
以一周为周期进行分析可知，在周五和周日的用户行为达到高峰，所以商家可以选择在周五和周日来进行一些促销活动。