实战：淘宝app SQL分析 + Excel可视化

分析框架：

 

数据集下载：天池竞赛中有。

数据集网址：https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1

将下载到的csv文件导入到mysql数据库，请读者自行百度。

正文：

拿到数据，先看一眼数据长啥样，是否有null值，什么样的数据类型；

select * from tianchi_mobile_recommend_train_user limit 10;

user_id:用户唯一标识

item_id:商品标识

behavior_type:用户对商品行为类型，{1,浏览；2，收藏；3，加入购物车；4，购买}

user_geohash:用户位置空间标识，由经纬度通过保密算法生成

item_category:商品分类标识

time：行为时间，精确到小时

说明：用户位置空间大多为null，难以补充和研究，本例不做地理分析；
商品分类暂时不做分析；

数据范围：从2014年11月18号~2014年12月18号淘宝APP一个月的行为数据；

select max(time),min(time) from tianchi_mobile_recommend_train_user;

 

select count(user_id) as pv,count(distinct user_id) as uv,count(distinct user_id) / count(user_id) as 'uv/pv'
from tianchi_mobile_recommend_train_user 
where behavior_type=1;

pv：统计周期内页面浏览次数（不去重）

uv：统计周期页面访问用户数（去重）

pv/uv：1155；平均每1155次浏览对应一个用户。对比大盘，历史基线看这个数据是否可以优化提高；

select count(user_id)
from ( select user_id
from tianchi_mobile_recommend_train_user
group by user_id
having count(behavior_type)=1 ) as A;

where 后不能接单行函数，而having可以；having要跟在group by后使用，对分组后数据执行筛选（一般用单行函数，比如计数，平均等）；
子查询给表起别名A是必须的，否则报语法错误！
count后不能有空格，真的烦唉；

查询结果为4，意味着只有4个用户在30天内只浏览了页面，占比4/10000=0.04%（页面跳失率=只点击一次浏览的用户数/总用户数）；说明淘宝app的粘性很大。

提问：如果将上述语言改为以下，将会发生什么？

select count(user_id)
from tianchi_mobile_recommend_train_user
group by user_id
having count(behavior_type)=1 

学会一个套路：

先筛选满足条件的字段建立一个子查询，然后对子查询执行计数，平均等操作。

接下来对用户行为进行分析

评价各功能占比：

select behavior_type,count(distinct user_id)
from tianchi_mobile_recommend_train_user
group by behavior_type;

浏览是用户进入淘宝的第一交互，而第二个交互是收藏，加入购物车，还是直接购买，从数据上看都是可以的，也符合业务逻辑（比如小编本人拿到工资的时候，浏览--购买；等钱花的差不多的时候，就是浏览--加入购物车---（有钱后）购买）。

进入淘宝app的10000个用户中，67.3%的用户选择了收藏，86.1%的用户选择了加入购物车，89%的用户完成了购买。

进行路径分析

不区分时间

select behavior_type,count( user_id)
from tianchi_mobile_recommend_train_user
group by behavior_type;

没有去除重复值，统计的是点击次数（一个人可以点击多次）；浏览次数11550581，收藏次数343564（收藏/占比=2.9%），购物车次数242556（购物车/浏览=2.1%），下单次数120205（下单/浏览=1%）。

select count(distinct t1.user_id),count(distinct t2.user_id),count(distinct t3.user_id),count(distinct t4.user_id)
from (
select distinct user_id 
from tianchi_mobile_recommend_train_user
where behavior_type=1
)t1
left join (
	select distinct user_id
	from tianchi_mobile_recommend_train_user
	where behavior_type=2
)t2 on t1.user_id=t2.user_id
left join (
	select distinct user_id,
	from tianchi_mobile_recommend_train_user
	where behavior_type=3
)t3 on t2.user_id=t3.user_id
left join (
	select distinct user_id 
	from tianchi_mobile_recommend_train_user
	where behavior_type=4
)t4 on t3.user_id=t4.user_id

此为漏斗模型，业务中经常使用left join统计漏斗数据---基于前端埋点。

漏斗模型画法请读取自行百度

对漏斗的说明：统计周期内，10000名浏览用户中，有6730名用户完成了收藏（完成一次就记录，实际上可能同一用户多次点击收藏），收藏用户中有6020名用户添加了购物车，添加购物车用户中有5730名用户完成下单。下单率（完成下单用户/进入app浏览用户）为57.3%。

不同时间尺度下的用户行为路径分析

最开始我是这样写的，

select t1.date,count(distinct t1.user_id),count(distinct t2.user_id),count(distinct t3.user_id),count(distinct t4.user_id)
from (
select distinct user_id ,DATE_FORMAT(time,'%Y-%m-%d') as date
from tianchi_mobile_recommend_train_user
where behavior_type=1
)t1
left join (
	select distinct user_id,DATE_FORMAT(time,'%Y-%m-%d') as date
	from tianchi_mobile_recommend_train_user
	where behavior_type=2
)t2 on t1.user_id=t2.user_id
and t1.date=t2.date
left join (
	select distinct user_id,DATE_FORMAT(time,'%Y-%m-%d') as date
	from tianchi_mobile_recommend_train_user
	where behavior_type=3
)t3 on t2.user_id=t3.user_id
and t2.date=t3.date
left join (
	select distinct user_id,DATE_FORMAT(time,'%Y-%m-%d') as date
	from tianchi_mobile_recommend_train_user
	where behavior_type=4
)t4 on t3.user_id=t4.user_id
and t3.date=t4.date
GROUP BY t1.date
ORDER BY t1.date

12256906行*6列然后3个left join的数据用一台16G的笔记本电脑想跑出结果---不存在的！（跑了20分钟，没出结果的我不想等了）。

代码的原意是按天做漏斗，比如看2018-12-12这一天有多少用户浏览，浏览的用户中有多少收藏，收藏的用户中有多少添加购物车，添加购物车的用户中有多少愿意下单。

月维度的用户行为分析

select DATE_FORMAT(time,'%Y-%m-%d'),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 tianchi_mobile_recommend_train_user
group by DATE_FORMAT(time,'%Y-%m-%d')
ORDER BY DATE_FORMAT(time,'%Y-%m-%d')

这个语句22s出结果。
实现的需求：知道某天有多少浏览量，多少收藏量，多少添加购物车，多少下单。
导出时注意设置格式。

从图中发现：2014-12-12这天，各项指标都达到了高峰。背后原因是淘宝平台双十二做活动，拉高了各项指标。

其中，下单次数环比昨日上涨373%，添加购物车次数环比昨日上涨56.7%，收藏次数环比昨日上涨12.2%，浏览次数环比昨日上涨39.4%。活动当天，下单涨幅最大，是因为很多用户提前看好了产品，等活动上线就开买；收藏涨幅最低，是因为用户使用收藏的心理预期是下次再看看，这次不再自己购买物品清单内，优先级是最低的；添加购物车涨幅度也很大，是因为用户添加购物车可以使用批量付款的原因，这样可以节省用户的购买时间。四项指标中围绕付款的浏览，添加购物车，下单都得到了较大的涨幅，只有收藏于下单相背离，涨幅较为平稳。

建议：对用户收藏的商品sku进行推荐，在双十二活动结束后一周，二周密集推动高关联商品sku，促进收藏商品下单转化率。

分析框架：what---why--action模式。新手，老油子的区别就在于新手只会描述，老油子可以提出有洞察性的建议。

周维度的用户行为分析

我们取双十二所在周，与双十二之前相距较远一周2014-11-24~2014-11-30对比分析

对比二三象限，平常周（三象限）周五是各项指标最低的时间点，双十二（二象限）恰好在周五各项指标“逆风翻盘”。

对比一四象限，浏览次数、添加购物车次数、收藏次数同比周一~周日，各项指标都有所增加，双十二周五浏览次数、添加购物次数同比它周周五强势上涨；可见双十二活动，提升当周其它时间点的效果还是很显著的，背后体现的逻辑就是用户在为双十二当天活动做前期准备；

从下单用户上看，双十二当周同比其它周，除了周五强势上涨，其它时间都处于下跌的趋势并且下单次数不如其它周；这从数据上解释了双十二的“下单吸血效应“现象是存在的。

对于一四象限本打算合成一张图像，通过设置主次坐标轴可以实现，但会造成一个视觉误差：添加购物车次数曲线在浏览次数之上。---实际上，换算成百分比指标看趋势效果会好些。

小时维度的用户行为分析

select hour(time),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 tianchi_mobile_recommend_train_user
where DATE_FORMAT(time,'%Y-%m-%d')='2014-12-12'
group by hour(time)
ORDER BY hour(time)

从图中可以发现，四个指标趋势一致；其中，双十二当天零点，浏览、添加购物车、下单达到峰值A，此后持续到2点到B开始急剧下降，2~6点是各指标低谷；从6点开始上涨直到10点达到均衡B，10点到19点维持凌晨两点级别的均衡B；19点之后开始上涨直到22点达到0点级别的峰值A，此后从22点到24点维持均衡A。

这条数据洞察与我们的生活作息规律是强相关的。

基于RFM模型寻找最有价值用户

由于数据集中没有消费金额这列数据，就只能找R（最近一次购买时间间隔），F（购买频率）来寻找最有价值用户！

关于R

统计周期为1个月，最近购买时间区间1-30，将其分为五档，0-6，7-12，13-18，19-24，25-30分别对应0-4分

create view pay_b as 
select user_id,DATEDIFF(max(DATE_FORMAT(time,'%Y-%m-%d')),min(DATE_FORMAT(time,'%Y-%m-%d'))) as b--每一个用户极端点购买时间之差
from tianchi_mobile_recommend_train_user 
where behavior_type=4
group by user_id;

select user_id,
(case when b BETWEEN 25 and 30 then 0
			when b BETWEEN 19 and 24 then 1
			when b BETWEEN 13 and 28 then 2
			when b BETWEEN 7 and 12 then 3
			when b BETWEEN 0 and 6 then 4
			else null
			end ) as R
from pay_b
order by R desc;

关于F

select min(a),max(a)
from (
select count(distinct time)  as a
from tianchi_mobile_recommend_train_user
where behavior_type=4
group by user_id
) as A；

订单用户消费次数从低到高为1-227次，分为五档，1~45，46~90，91~136，137~182，183~227分别对应0-4

create view pay_a as 
select user_id,count(distinct time) as a
from tianchi_mobile_recommend_train_user 
where behavior_type=4
group by user_id;

select user_id,
(case when a BETWEEN 1 and 45 then 0
			when a BETWEEN 46 and 90 then 1
			when a BETWEEN 91 and 136 then 2
			when a BETWEEN 137 and 182 then 3
			when a BETWEEN 183 and 227 then 4
			else null
			end ) as F
from pay_a
order by F desc;

合并RFM得到：

select t1.user_id,t1.R,t2.F
from (
 select user_id,
(case when b BETWEEN 25 and 30 then 0
			when b BETWEEN 19 and 24 then 1
			when b BETWEEN 13 and 28 then 2
			when b BETWEEN 7 and 12 then 3
			when b BETWEEN 0 and 6 then 4
			else null
			end ) as R
from pay_b
)t1
inner join (
select user_id,
(case when a BETWEEN 1 and 45 then 0
			when a BETWEEN 46 and 90 then 1
			when a BETWEEN 91 and 136 then 2
			when a BETWEEN 137 and 182 then 3
			when a BETWEEN 183 and 227 then 4
			else null
			end ) as F
from pay_a
)t2
on t1.user_id=t2.user_id
group by t1.user_id
order by t1.R desc, t2.F desc

我们尽量选择R，F分数靠前的用户作为优质用户，然后拉活动，做投放，但要注意小成本试错，先小范围试点，再全量推广；

对于R为4，F为0的用户，用户消费时间间隔短而付费次数少，运营活动可以重点针对这部分用户，比如拼团打折，积分兑换，唤起这部分的购买热情。