淘宝用户行为分析MySQL

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

项目背景：数据源于阿里天池2014年11月18日至2014年12月18日之间的用户行为数据，包括用户ID、商品类别、商品ID、时间戳、行为类别（1表示点击，2表示收藏，3表示加购，4表示购买）、地理位置；

项目目的：掌握总体运营情况，通过对用户的行为进行多维度的分析，从而发现问题，对用户进行精细化营销运营。

一、数据清洗

由于数据量由几亿，所以选择导入前999999条数据进行分析，查看是否有空值；

select count(user_id),count(item_id),count(item_category),count(behavior_type),count(time)
from tianmao

结果如下：

数据各列均没有空值；

查看时间列是否有异常值：

SELECT min(time),max(time) from tianmao;

时间是2014-11-18至2014-12-18一个月的用户数据，所以没有异常值；

查看数据：

由于time列由年月日+小时组成，所以新建两列，分别为日期time_date、时间time_hour;

alter table tianmao add time_date varchar(15);
alter table tianmao add time_hour varchar(15);
update  tianmao set time_date=mid(time,1,10);
update tianmao set time_hour=right(time,2);
alter table tianmao drop column user_geohash; --用户地理信息进行了加密处理，无法看到具体位置，所以删除

二、总体情况

1、UV、PV、人均浏览次数

SELECT COUNT(DISTINCT user_id) 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 '购买',
	ROUND(SUM(CASE WHEN behavior_type = 1 THEN 1 ELSE 0 END)/COUNT(DISTINCT user_id)) AS '人均浏览次数'
FROM tianmao ;

页面点击量PV为942229，独立访客数为8058，人均浏览次数为117；

2、复购率、跳失率

select 
	count(DISTINCT a.user_id) 总购买人数,
	sum(if(购买次数>1,1,0)) 复购人数,
	concat(round(sum(if(购买次数>1,1,0))/count(DISTINCT 	a.user_id)*100,2),'%') as 复购率
from 
	(select user_id,count(behavior_type) as 购买次数
	 from tianmao 
	 where behavior_type=4
	 group by user_id ) a

select concat(round(sum(if(收藏数=0 and 加购数=0 and 购买数=0,1,0))/count(b.user_id)*100,2),'%') 跳失率 
from
	(select
		user_id,
		sum(if(behavior_type=2,1,0)) as 收藏数,
		sum(if(behavior_type=3,1,0)) as 加购数,
		sum(if(behavior_type=4,1,0)) as 购买数
	from tianmao 
	group by user_id)b；

在一个月内，跳失率为18.7%，属于正常区间，复购率在53%，说明总体用户忠诚度是不错的，可以进一步对消费的顾客进行召回消费，培养用户忠诚度。

三、用户行为分析

1、行为转化漏斗分析

select behavior_type,count(user_id) as 用户数 
from tianmao
group by behavior_type
order by 用户数 desc;-- 总体用户行为漏斗
select behavior_type,count( distinct user_id) as 用户数 
from tianmao
group by behavior_type;-- 独立访客转化漏斗

将收藏与加购合并后的总体行为漏斗：

独立访客行为转化漏斗：

使用Power BI 进行可视化，在独立访客行为转化中，加购占比为61%，购买占比达到了51%，与加购相比，占了有83%，整体来看转化率较高，说明平台满足了用户的大部分需求；在总体行为中，用户点击后，收藏与加购的百分比为5%左右，最后购买占第一步只有1%左右，占上一步20%，说明用户在点开详情页，有大量流失的情况。
综合来看，平台需要优化推荐算法，完善商品关键词，推送的商品尽可能符合顾客需求。

2、时间维度用户行为分析

用户每日的uv、pv、收藏、加购、购买数：

select time_date as 日期,count(distinct user_id) 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 tianmao
group by time_date
order by time_date；

由图可看出，在11月18号至12月10号之间数据比较平稳，波动较小，从12月11号至12月13日数据出现了波动，11日由于双12的预热，所以数据开始增长，一直到双十二达到峰值，活动的结束，导致13日数据又回落到平稳状态。

用户每小时用户行为：

select time_hour as 时间,count(distinct user_id) 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 tianmao
group by time_hour
order by time_hour；

从图中可知，0-5时由于大部分人休息睡眠时间，所以数据整体下滑，6-10时，人们开始起床上班，数据又开始慢慢增长，用户10点在上午是最活跃的时间，用户在下午中15点中是比较活跃的，11-12时和16-17时由于吃午晚饭、下班等因素数据有小幅度的下滑，在18-22时数据开始持续增长，直到达到最大值，21-22时是用户一天中最活跃的时间段；所以可以根据用户活跃的时间进行优惠劵的发放、活动的宣传等。

3、商品维度用户消费行为分析

首先了解下商品的数量：

select count(distinct item_id) 商品数量,count(distinct item_category) 商品种类
from tianmao ；

共有673014个商品，商品类别有7034种；

查看销量TOP20的商品类别：

select item_category,count(user_id) as 销量  
from tianmao
where behavior_type=4
group by item_category
ORDER BY 销量 desc
limit 20

商品销量TOP20:

select item_id,count(user_id) as 销量  
from tianmao
where behavior_type=4
group by item_id
ORDER BY 销量 desc
limit 20

商品盈利主要是长尾效应，即并不是依靠某个爆款商品来盈利，而是通过满足用户个性化、零散的需求来进行盈利。

查看点击量前十的商品的购买量：

select a.item_id,a.item_category,点击量,sum(case when behavior_type=4 then 1 else 0 end) as 购买量
from (select item_id,item_category,count(behavior_type) as 点击量
from tianmao
where behavior_type=1
group by item_id
ORDER BY 点击量 desc
limit 10) a 
left join tianmao a1
on a.item_id=a1.item_id 
group by a.item_id
order by 点击量 desc

点击量前十中有有5个的商品类别都是6000，说明6000类别的商品是用户常需的或者平台推送的商品类别是符合用户需求的，但点击量前十的商品最后只卖出了四件，说明平台推送的商品虽然是用户感兴趣的，但是可能商品的详情页、图片等因素导致用户没有进行购买，所以应该完善详情页、转变商品图片风格等。

四、RFM模型用户价值分层

根据R（最近一次消费）、F（消费频率）、M（消费金额）对用户价值进行分层，由于本数据集没有商品金额，所以用RF值对用户分层：

create view r等级划分 as
select user_id,近期购买时间,datediff('2014-12-18',近期购买时间)距今天数,
(case when datediff('2014-12-18',近期购买时间)<=3 then 5 
	when datediff('2014-12-18',近期购买时间)<=6 then 4
	when datediff('2014-12-18',近期购买时间)<=9 then 3 
	when datediff('2014-12-18',近期购买时间)<=12 then 2 else 1 end) as r值
from r；
select AVG(`r值`) from `r等级划分`

create view f as
select user_id,count(user_id) as 消费次数
from tianmao
where behavior_type=4
group by user_id ;

create view f等级划分 as
select user_id,消费次数,
(case when 消费次数<=1 then 1
			when 消费次数<=3 then 2
			when 消费次数<=5 then 3
			when 消费次数<=7 then 4 else 5 end)as f值
from f;
SELECT AVG(`f值`) from `f等级划分`;

create view 用户价值 as 
select a.user_id,a.`f值`,a.`r值`,
(case when `r值`>2.8 and `f值`>2 then '重要价值客户'
			when `r值`>2.8 and `f值`<=2 then '发展客户'
			when `r值`<2.8 and `f值`>2 then '保持客户'
			when `r值`<2.8 and `f值`<=2 then '挽留客户' else 0 end) as 'RF分层'
from(
select `r等级划分`.user_id,`r值`,`f值`
from `r等级划分`
join `f等级划分`
on `r等级划分`.user_id=`f等级划分`.user_id) as a

select RF分层,count(user_id)
from 用户价值
GROUP BY RF分层 -- 查看各层用户数

RF各层中占比做的为挽留客户、发展客户，对于发展用户，应当对这些用户进行短信触达、优惠消息提醒等方式提高用户消费频率；对于挽留用户，应当进行问卷调查、回访等方式，找到流失原因，召回用户；对于较少重要价值用户，应当为其提供专属的VIP 服务，采取专属定制运营策略来维系，保证用户的粘性；对于保持用户，可以上新提醒、精准商品推荐、优惠券发放等刺激用户消费，以免用户流失。

结论与建议

1.行为转化漏斗分析
根据用户行为转化分析来看，用户从点击到购买的转化率只有1%左右，说明用户浏览详情后，并没有购买，而是退出界面，通过不断对商品进行对比后再购买；
建议：平台应该对用户进行更精准的画像，根据用户画像推送商品，提高搜索关键词与商品的匹配度，提供更细致的筛选功能，减少用户时间成本。
2.日期时间维度用户行为分析
从日期的维度来看，整体是出于较稳定的状态，由于双十二活动的原因产生了一些波动，可以根据历年双十二的数据进行同比分析，对比后看数据是否存在减少趋势，也可进行各月的环比分析，来观察数据情况。
从时间维度来看，用户一天中在21-22时是最活跃的状态，所以在这个时间段对用户进行推送、活动宣传、优惠发放、直播带货等活动关注度是最高的，转化率也是较高的；在上午的10时和下午15时也是用户比较活跃的时间点，可以再结合晚上的活动进行预热。
3.商品维度分析
商品类别为6344是购买量最多的，说明6344可能是用户在常用品类或者当下流行，其盈利主要是依靠长尾效应，并不是通过爆款来盈收，可以根据用户搜索行为或者了解当下的流行来打造爆款，增加营收，提高活跃度；
4.用户价值分析
通过RFM模型中RF两个维度对用户进行分层，占比最多的为挽留用户，占比41.8%，说明平台用户流失比较严重，应该对用户进行召回，可以通过短信、优惠券发放、优化个性主页等活动提高用户活跃度；其次便是占比40%的发展用户，这些用户的消费频次比较低，可以上新提醒、推荐当下流行的商品等提高消费频次，增加用户粘性；对重要价值客户，提供专属VIP服务，通过办卡积分兑奖或者抵用券等形式来维护重要价值用户，提高用户忠诚度。