刷了不少MySQL的题了，今天在一个公众号上找到了一个淘宝相关的数据集，试着用它来完成一些数据分析。
一、分析背景及目的
1.分析背景
数据来源：阿里云天池
根据介绍，该数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢），原始数据有一亿多行。
字段解释如下：pv为点击，buy为买，cart为加入购物车，fav为收藏。
2.分析目的
对用户不同行为的发生规律作出分析，规律背后的影响因素，从而给相关运营团队提出建议以优化后续策略。
二、分析思路
1、从时间角度分析不同用户行为规律
时间角度包括一年中不同的日期段和每日不同的时间段，从这两个角度分别分析。
2、从商品角度分析不同用户行为规律
找出热销商品和滞销商品，并分析相关的用户行为规律
3.分析各种用户行为间的转化率
分析在点击-加购、收藏-购买的流程中每个环节的转化率，从而在后续对薄弱环节加强以增加最后的购买数。
三、数据清洗
1、选择合适数量样本
考虑到此次分析主要是为了在实战中加深对Mysql的理解，故在网上下载原数据集的部分数据，最终导入到Navicat的数据量约有10万行。
2.删除重复值
先查询出有无所有字段值均相同的数据，有的话删除重复值。
SELECT *
from userbehavior
group by user_id,item_id,category_id,behavior_type,userbehavior.`timestamp`
having count(*) >1
运行结果：
说明该数据集中无重复值。
3.补充空值
先查询出有无空值，有的话补充。
SELECT *
from userbehavior
where user_id is null or item_id is null
or category_id is null or behavior_type is null
or userbehavior.`timestamp` is null
运行结果：
说明该数据集中无空值。
4.处理时间数据
该数据集中的时间戳是一串数字，查资料得知可用from_unixtime函数转化为正常时间显示格式，并增加两列分别存放日期和时间：
alter table userbehavior add (date varchar(255))
alter table userbehavior add (time varchar(255))
update userbehavior set `date` = FROM_UNIXTIME(`timestamp`,'%Y-%m-%d')
update userbehavior set `time` = FROM_UNIXTIME(`timestamp`,'%H:%i:%s')
运行结果：
5.异常值处理
先查询出有无异常日期值（不在11.25和12.3之间），有的话删去。
select `date`
from userbehavior
where `date` not between '2017-11-25' and '2017-12-03'
发现有44条异常日期值，执行删除操作：
delete from userbehavior
where `date` not between '2017-11-25' and '2017-12-03'
删除后再次执行查询，发现没有异常值了，则处理成功：
四、分析
1.从时间角度分析不同用户行为规律
先从日期角度分析。
#create table result as
select `date`,sum(case when `behavior_type` = 'pv' then 1 else 0 end) as 每日浏览量,
sum(case when `behavior_type` = 'fav' then 1 else 0 end) as 每日收藏量,
sum(case when `behavior_type` = 'cart' then 1 else 0 end) as 每日加购量,
sum(case when `behavior_type` = 'buy' then 1 else 0 end) as 每日购买量
from userbehavior
group by `date`
order by `date`
结果显示：
数据看上去不直观，将结果导入Excel作可视化分析（在代码前加上create table as 来创建临时表，然后在客户端导出）（每日浏览量对应左侧坐标轴，其余对应右侧）:
通过曲线可以发现，在12月2日时，四种用户行为都有增加，猜测是由于12月2日开始有双十二活动预热。
再从每日时间段角度分析。
select hour(`time`),sum(case when `behavior_type` = 'pv' then 1 else 0 end) as 小时浏览量,
sum(case when `behavior_type` = 'fav' then 1 else 0 end) as 小时收藏量,
sum(case when `behavior_type` = 'cart' then 1 else 0 end) as 小时加购量,
sum(case when `behavior_type` = 'buy' then 1 else 0 end) as 小时购买量
from userbehavior
group by hour(`time`)
order by hour(`time`)
同上，导入Excel作可视化分析（每日浏览量对应左侧坐标轴，其余对应右侧）：
通过曲线发现用户在晚上八点到十点时处于活跃状态，各项用户行为都处于比较高的状态下，符合一般人的作息规律。
2.从商品角度分析不同用户行为规律
先查询出销量最高的商品：
select item_id,count(item_id) as 购买量
from userbehavior
where behavior_type = 'buy'
group by item_id
order by count(item_id) desc
limit 10
发现查询到的前十销量商品数量不大，不具有代表意义，于是舍弃该查询，转而查询商品类别：
select category_id,sum(case when `behavior_type` = 'pv' then 1 else 0 end) as 浏览量,
sum(case when `behavior_type` = 'fav' then 1 else 0 end) as 收藏量,
sum(case when `behavior_type` = 'cart' then 1 else 0 end) as 加购量,
sum(case when `behavior_type` = 'buy' then 1 else 0 end) as 购买量
from userbehavior
group by category_id
order by 购买量 desc
limit 15
将结果导入到Excel进行可视化分析：
从图表中可以发现一个现象：浏览量、收藏量、加购量基本有着同样的运动趋势，而购买量却没有和它们一样的趋势，也就是说，在这段时间内，大多数用户只是将这些商品收藏或者加入了购物车，并没有最终购买，猜测是因为双十二时会有促销活动，因此多数用户选择等到有折扣时再进行购买，具体原因分析需要双十二期间数据的支持，此处仅作猜测。
3.分析各种用户行为间的转化率
按用户分组查询：
select user_id,
sum(case when `behavior_type`='pv' then 1 else 0 end ) as 个人浏览量,
sum(case when `Behavior_type`='cart' then 1 else 0 end ) as 个人加购量,
sum(case when `Behavior_type`='fav' then 1 else 0 end ) as 个人收藏量,
sum(case when `Behavior_type`='buy' then 1 else 0 end ) as 个人购买量
from userbehavior
group by user_id
将查询结果导入Excel进行可视化分析：
由于数据过多，图表比较难看，但从颜色还是可以看出，几乎所有的用户都进行了大量的浏览后，才有收藏、加购乃至购买行为，转化率较低。
五、总结建议
根据以上分析，得出以下结论：
1.12月2日前后，浏览量、收藏量、加购量均有明显提升；晚上八点到十点是各种行为发生的高峰期。
2.促销商品的浏览量、收藏量、加购量呈同样趋势，而购买量相差并不大。
3.几乎所有用户都在进行了大量的浏览之后才进行收藏、加购和购买。
根据结论提出建议：
加强预热阶段相关折扣活动的推送，加强晚上八点到十点的推送，强化推送的质量以获得更高转化率，滞销产品可以参照相关热门产品做出改进。
