使用MySQL进行数据分析——以淘宝用户数据为例

使用MySQL进行数据分析——以淘宝用户数据为例

1. 背景介绍

本文主要是练习使用 mysql 进行数据分析，结合 excel 进行可视化分析，数据来源为阿里云天池的淘宝用户数据集，本数据集(UserBehavior.csv)包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。

字段解释为：

列名称说明
用户ID整数类型，序列化后的用户ID
商品ID整数类型，序列化后的商品ID
商品类目ID整数类型，序列化后的商品所属类目ID
行为类型字符串，枚举类型，包括(‘pv’, ‘buy’, ‘cart’, ‘fav’)
时间戳行为发生的时间戳

注意到用户行为类型有四种，分别是：

行为类型说明
pv商品详情页pv，等价于点击
buy商品购买
cart将商品加入购物车
fav收藏商品

关于数据集的大小说明：

维度数量
用户数量987,994
商品数量4,162,024
用户数量987,994
商品类目数量9,439
所有行为数量100,150,807

由于数据集太过庞大，本人电脑可能不堪重负，用 python 读取发现有十亿条数据，用 mysql 载入也太费时间（导入一个小时才导入了几百万条），因此本文使用部分数据进行分析。

2.

3. 数据清洗

3.1 缺失值处理

select count(UserID),count(ItemID),count(CategoryID),count(BehaviorType),count(TimeStamp)
from userbehavior

在这里插入图片描述

发现并没有缺失值，总共有3694900条数据

3.2 重复值处理

select UserID,ItemID,Timestamp
from userbehavior 
group by UserID,ItemID,Timestamp
having count(*) > 1

在这里插入图片描述
发现有两行数据是重复的，因此删除这两行数据。

3.3 时间变量处理

观察数据发现Timestamp字段为时间戳，并非传统的时间日期，需要对这一字段进行处理。

alter table userbehavior add date DATE ,add time VARCHAR(10)

新增 日期date 和 时间time 字段

update userbehavior set date = from_unixtime(Timestamp,'%Y-%m-%d'),
time = from_unixtime(Timestamp,'%k')

利用 from_unixtime 函数将 日期 和 时间 从时间戳中提取出来，这一过程时间很长，可能需要几分钟，大家可以喝口水稍等一会儿 😃😃😃
处理好了！！！(上一步我并没有删除重复值) 😦
在这里插入图片描述

3.4 离群值处理

此数据集中存在的离群值可能是日期时间不在我们研究的范围内，我们研究的时间范围是2017年11月25日至2017年12月3日之间，因此在这之外的均属于离群值

select count(*)
from userbehavior
where date<'2017-11-25' or date>'2017-12-03'

在这里插入图片描述
因此，删除这1870行数据，

delete
from userbehavior
where date<'2017-11-25' or date>'2017-12-03'

删除完成，数据集已经处理完毕，最终数据集为3693030行数据。

4. 数据分析

4.1 用户行为分析

类似于 用户漏斗分析
首先创建一个新视图，

create view behaviorcount(
select Behaviortype, count(*) count
from userbehavior
group by Behaviortype)

select Behaviortype, count(*) count, concat(round(count(*)/3693030 * 100,2),'%') 比例,
       case when Behaviortype='pv' then 1
		    when Behaviortype='cart' then concat(round((select count from behaviorcount where Behaviortype='cart')/(select count from behaviorcount where Behaviortype='pv') *100,2),'%')
		    when Behaviortype='fav' then concat(round((select count from behaviorcount where Behaviortype='fav')/(select count from behaviorcount where Behaviortype='cart') *100,2),'%')
			when Behaviortype='buy' then concat(round((select count from behaviorcount where Behaviortype='buy')/(select count from behaviorcount where Behaviortype='fav') *100,2),'%') 
			end as 上一阶段比例
from userbehavior
group by Behaviortype
order by count desc

结果如下：
在这里插入图片描述
由运行结果可以看出，用户点击量、加购、收藏、购买占比分别为89.53%、5.57%、2.89%、2.00%，从用户浏览到用户购买的转化率仅仅有2.00%，所以我们要对如此低的转化率进行分析。而2.00%只是整体的从用户开始浏览到下单成功的转化率，一般用户来到商品页面可能会有以下几个可能的行为路径：浏览-购买、浏览-加购-购买、浏览-收藏-购买、浏览-加购并收藏-购买、浏览-流失，那么我们就从这几个方面进行拆解，运用漏斗分析方法分析找出具体哪种路径的哪个环节出了问题，造成整体转化率低。
根据上一阶段比例，可以看出从 浏览-购物车 仅占比6.22%，从 加购-收藏 占比为51.91%，从收藏-购买占比为69.28%。

计算各个阶段的转化率
创建视图
根据用户和商品计算出 四种行为 的数量

create view user_b
as
select UserID,ItemID,
sum(case when Behaviortype='pv' then 1 else 0 end) 点击,
sum(case when BehaviorType='cart' then 1 else 0 end) 加购,
sum(case when BehaviorType='fav' then 1 else 0 end) 收藏,
sum(case when BehaviorType='buy' then 1 else 0 end) 购买
from userbehavior
group by UserID,ItemID

在这里插入图片描述
计算各个阶段的数量

#点击量-3306494
SELECT SUM(点击) FROM user_b;

#点击-购买-36343
SELECT SUM(购买) FROM user_b
WHERE 点击>0 AND 购买>0 AND 收藏=0 AND 加购=0;

#点击-加购-99087
SELECT SUM(加购) FROM user_b
WHERE 点击>0  AND 收藏=0 AND 加购>0;

#点击-加购-购买-10488
SELECT SUM(购买) FROM user_b
WHERE 点击>0 AND 购买>0 AND 收藏=0 AND 加购>0;

#点击-收藏-41269
SELECT SUM(收藏) FROM user_b
WHERE 点击>0  AND 收藏>0 AND 加购=0;

#点击-收藏-购买-3550
SELECT SUM(购买) FROM user_b
WHERE 点击>0 AND 购买>0 AND 收藏>0 AND 加购=0;


#点击-收藏+加购-6497
SELECT SUM(收藏)+SUM(加购) FROM user_b
WHERE 点击>0 AND 收藏>0 AND 加购>0;

#点击-收藏+加购-购买-475
SELECT SUM(购买) FROM user_b
WHERE 点击>0 AND 收藏>0 AND 加购>0 AND 购买>0;

#点击-流失-2910307
SELECT SUM(点击) FROM user_b
WHERE 点击>0 AND 收藏=0 AND 加购=0 AND 购买=0;

在这里插入图片描述
从计算结果可以看出，用户直接购买的转化率为1.1%，加购后购买的转化率为10.58%，收藏后购买的转化率为8.6%，加购并收藏后购买的转化率为7.31%，很明显，用户在加购、收藏后购买的转化率比浏览之后直接购买的转化率高的多，针对这种情况，商家店铺可以从优化产品宣传介绍页面、鼓励用户收藏加购的优惠活动、限时优惠活动等营销手段方面促进用户加购收藏行为，从而一定程度上提升购买转化率。

另外，浏览量很大，但是有购买行动的却是非常少，大量用户流失，从浏览到直接购买、加购、收藏、加购并收藏的转化率都非常低，也许是用户在浏览过程中没有找到喜欢的，对此，可以针对不同用户的喜好，精准推荐顾客喜欢的比较热销的一些产品，从而促进转化。

4.2 分析转化率

针对平台问题，运用假设分析方法，假设：用户推荐机制不合理，平台推荐商品不是用户喜欢的，造成转化率低。

通过分析高浏览量商品和高购买量商品是否相关，如果是，则假设不成立，如果不是，则假设成立；

浏览量前10的商品类：

select CategoryID, count(*) 点击次数
from userbehavior
where Behaviortype = 'pv'
group by CategoryID
order by 点击次数 desc
limit 10

购买量前10的商品类：

select CategoryID, count(*) 点击次数
from userbehavior
where Behaviortype = 'buy'
group by CategoryID
order by 点击次数 desc
limit 10

在这里插入图片描述
由结果可以看出，高点击量不一定高购买量

再细分到每个商品分析；
分析浏览量前10的商品的购买次数

#利用之前创建的视图uesr_b
select ItemID, sum(点击) 点击次数, sum(购买) 购买量
from user_b
where ItemID IN(select u1.ItemID 
from (select ItemID, count(ItemID) 点击次数
from userbehavior
where Behaviortype = 'pv'
group by ItemID
order by 点击次数 desc
limit 10) u1)
group by ItemID

在这里插入图片描述
接着分析购买量前10的商品的浏览量：

select ItemID, sum(点击) 点击次数, sum(购买) 购买量
from user_b
where ItemID IN(select u1.ItemID 
from (select ItemID, count(ItemID) 购买量
from userbehavior
where Behaviortype = 'buy'
group by ItemID
order by 购买量 desc
limit 10) u1)
group by ItemID

在这里插入图片描述
分析结果，点击量高的商品购买量却很低，几乎没有，意味着高流量商品最后转化率很低；而购买量高的商品浏览量并不是很高，而且没有点击量前10的商品，即高购买量并没有是由高浏览量带来；

结论：平台的推荐机制不合理，给了高流量的商品最后并没有带来转化，没有带来效益，最后造成抓化率低。应该把流量推给购买量高的商品类和具体商品，如商品类目2735466、1464116，商品3122135、2964774等。

转化率低原因分析总结：

1、优化平台推荐机制，把更多流量给到购买量高的商品，提升转化率；

2、针对大量流失用户，通过积分会员制、店铺页面优化、更精准用户推荐等措施降低流失率；

3、引导加购、收藏，可通过限时优惠活动、加购收藏后享受优惠等活动间接提高用户购买转化率；

4.3 用户活跃时间分析

根据不同日期对用户行为进行分析
假设1：用户在周末购买量会增加；

select date,
       sum(case when Behaviortype = 'pv' then 1 else 0 end) as '点击',
	   sum(case when Behaviortype = 'cart' then 1 else 0 end) as '加购',
	   sum(case when Behaviortype = 'fav' then 1 else 0 end) as '收藏',
	   sum(case when Behaviortype = 'buy' then 1 else 0 end) as '购买'
from userbehavior 
group by date

利用excel进行可视化分析
在这里插入图片描述
从图标可以看出，在周六、周日用户点击量、加购量、收藏量、购买量均有小幅增加，说明假设成立。但是由于周期较短，本案例数据仅仅有一周数据可供分析，所以结果有待考量。

假设2：用户在一天中晚上时间活跃度上升；

select time,
     sum(case when Behaviortype = 'pv' then 1 else 0 end) as '点击',
	   sum(case when Behaviortype = 'cart' then 1 else 0 end) as '加购',
	   sum(case when Behaviortype = 'fav' then 1 else 0 end) as '收藏',
	   sum(case when Behaviortype = 'buy' then 1 else 0 end) as '购买'
from userbehavior 
group by time
order by time asc

在这里插入图片描述
由用户活跃可视化图可以看出，用户在一天中零点到4点时段活跃快速下降，从4点到10点逐步上升，10点到18点基本平稳，从18点到22点活跃度急剧上升，达到最高。所以用户在晚上的时段确实更活跃，假设成立。在这个时段可以加大商品推荐和促销活动，从而提高转化率。

4.4 RFM分析法对用户价值进行分类

RFM是三个指标缩写，分别为最近一次消费时间间隔Recency，消费频率Frequency，消费金额Monetary，往往通过这3个指标对用户进行价值分类。

R=当前时间-最近一次购买时间

F=在分析周期内购买的次数

M=在分析周期内用户消费额

本文由于没有用户消费相关数据，所以不作分析，接下来对R、F值进行计算：

select UserID, datediff('2017-12-03',max(date))+1 R, count(behaviortype) F
from userbehavior
where Behaviortype = 'buy'
group by UserID

在这里插入图片描述
给R、F值打分（具体公司分析业务可根据业务情况制定打分标准）：

按价值打分RF
18-9天1-6次
25-7天7-12次
33-5天13-18次
42天内19次以及以上

接下来对上述结果进行打分：

select *,
			 (case when R between 8 and 9 then 1
					  when R between 5 and 7 then 2
					  when R between 3 and 5 then 3
						when R <= 2 then 4 
			 end) as R_score,
			 (case when F between 1 and 6 then 1
			      when F between 7 and 12 then 2
						when F between 13 and 18 then 3
						when F >= 19 then 4
				end) as F_score
from (select UserID, datediff('2017-12-03',max(date))+1 R, count(behaviortype) F
			from userbehavior
			where Behaviortype = 'buy'
			group by UserID) m

在这里插入图片描述
计算 R 和 F 的均值

select avg(R_score), avg(F_score)
from (select *,
			 (case when R between 8 and 9 then 1
					  when R between 5 and 7 then 2
					  when R between 3 and 5 then 3
						when R <= 2 then 4 
			 end) as R_score,
			 (case when F between 1 and 6 then 1
			      when F between 7 and 12 then 2
						when F between 13 and 18 then 3
						when F >= 19 then 4
				end) as F_score
from (select UserID, datediff('2017-12-03',max(date))+1 R, count(behaviortype) F
			from userbehavior
			where Behaviortype = 'buy'
			group by UserID) m)n

在这里插入图片描述
RFM用户价值分类规则如下：
在这里插入图片描述
找出不同价值用户的人数：

select p.客户类型, count(*) as "人数"
from 
(select *,
(case when R_score > 3.0477 and F_score > 1.1035 then '重要价值客户'
     when R_score > 3.0477 and F_score < 1.1035 then '重要发展客户'
		 when R_score < 3.0477 and F_score > 1.1035 then '重要保持客户'
		 when R_score < 3.0477 and F_score < 1.1035 then '重要挽留客户' end ) as '客户类型'
from 
(select *,
			 (case when R between 8 and 9 then 1
					  when R between 5 and 7 then 2
					  when R between 3 and 5 then 3
						when R <= 2 then 4 
			 end) as R_score,
			 (case when F between 1 and 6 then 1
			      when F between 7 and 12 then 2
						when F between 13 and 18 then 3
						when F >= 19 then 4
				end) as F_score
      from (select UserID, datediff('2017-12-03',max(date))+1 R, count(behaviortype) F
			      from userbehavior
			      where Behaviortype = 'buy'
			      group by UserID) m)n)p
group by p.客户类型

在这里插入图片描述
结论：

对于重要价值用户，消费频率高且最近消费距离现在时间短，需要给其提供VIP服务；

对于占比较大的重要发展用户，其消费频率低，但最近消费距离现在时间较短，想办法提高其消费频率；

对于重要保持用户，最近消费距离现在时间较远，也就是R值低，但是消费频次高，这样的用户，是一段时间没来的忠实客户，可以采取邮件推送、APP推送提醒、促销活动时短信提醒等方式主动和他们保持联系，提高复购率；

对于占比较大的重要挽留用户，最近消费时间距离现在较远、消费频率低，这种用户有即将流失的危险，需要主动联系用户，调查清楚哪里出了问题，并想办法挽回；

参考资料 SQL数据分析实战案例