淘宝+SQL：100万用户的行为分析实战

数据包含了2017年11月25-12月3号期间，随机选取的1,000,000用户行为信息，表中所包含字段信息如下：

其中行为类型的pv、buy、cart、fav分别对应点击、购买、添加购物车和收藏四种行为。

由于此次数据集数据量大，无法和用Excel进行分析，因此将选择其中10万条数据使用navicat导入SQL中进行下一步分析。

一.提出问题

根据目前表中的信息，可以先按照用户行为进行漏斗分析，观察各环节转化率；此外可以从用户行为类型和时间发现用户每日行为规律，了解用户行为偏好。

二.数据清洗

1. 选择子集

由于数据集包含数据类型较少，此次分析将用到所有数据，因此不做任何处理

2. 重命名

按照从左到右，分别命名为用户ID、商品ID、商品类型ID、行为类型和时间戳

3. 删除重复值

观察数据发现，用户ID、商品ID、商品类型ID以及行为类型都存在合理的重复值，因此需要需要关联不同数据类型，即每个用户在针对一个商品是否存在多个相同时间的相同操作

select 用户ID,商品ID,时间戳

from userbehavior

group by 用户ID,商品ID,时间戳

having count(*)>1;

结果显示没有重复值

4. 处理缺失值

select count(用户ID),count(商品ID),count(商品类型ID),count(行为类型),count(时间戳)

from userbehavior;

结果显示无缺失值

5. 一致化处理

从数据集中时间戳格式来看，属于unix timestamp需要转化为正常日期显示，处理流程如下：

-- 增加日期列

alter table userbehavior add column 时间 varchar(255);

-- 将unixtime转化成正常时间显示

update userbehavior set 时间=FROM_UNIXTIME(时间戳);

查看处理后数据

再从转化后的正常格式时间中抽取出日期和具体时间

-- 增加日期列

alter table userbehavior add column 日期 varchar(255);

-- 增加时间列

alter table userbehavior add column 时间 varchar(255);

-- 从正常时间抽取日期

update userbehavior set 日期=SUBSTRING(正常时间,1,10);

-- 从正常时间抽取时间

update userbehavior set 时间=SUBSTRING(正常时间,12,9);

完成后，将时间戳格式分别转为对应的日期和具体时间。

6. 异常值处理

查询日期是否如数据集所描述在17年11月25日-12月3日之间：

-- 查询日期不在11月25号和12月3号的数据

select * from userbehavior where 日期<"17-11-25" or 日期>"17-12-04";

结果共44条数据，做删除处理：

-- 删除不符合日期要求的数据

DELETE from userbehavior where 日期<"17-11-25" or 日期>"17-12-04";

三.构建模型

1. 用户行为指标

（1）用户使用时间段

-- 增加小时分段

alter table userbehavior add column 时间段 varchar(255);

-- 抽取小时数

update userbehavior set 时间段=SUBSTRING(时间,1,2);

-- 统计各小时用户操作次数

select 时间段,count(时间段) as 用户操作次数 from userbehavior group by 时间段 order by 时间段;

从SQL中导出数据进行可视化分析:

用户访问量的时间变化

可以看出每天凌晨3点是访问量最低点，随后用户访问次数逐渐增加，在早上10点达到一个小高峰，在12点出现下降。从12点后一直到下午16点都处于一个稳定上升的状态，随后开始在17点出现大幅下降的情况，然后在19点出现突然增长的情况，一直增长至21点达到全天最高峰。

总结：全天用户访问高峰分为两个时间段，下午10-16点为小高峰，晚上20-22点为全日最高时段，可以看出用户每天早晨10点和下班回家后会频繁使用淘宝浏览购物，中午12点休息吃饭时间会出现暂时的下降情况，符合大部分用户的作息习惯。（由于日期范围较短，因此没有区分工作日和周末，推测上述结果更符合用户在工作日使用习惯）

（2）每日pv量和购买次数

SELECT 日期,

SUM(if(行为类型= "pv",1,0) ) 单日点击量,

SUM(if(行为类型 = "buy" ,1,0) ) 单日购买量

FROM userbehavior

GROUP BY 日期;

从SQL中到处进行可视化后，查询日期发现11月25、26号和12月2、3号是周末，购买量从11月25号到12月3号呈现波动状态，访问次数出现平稳上升的情况，周末的数据略高于工作日，但并没有明显差异。

（3）每日人均pv量

select 日期,count(行为类型)/count(DISTINCT 用户ID) as 访问次数 from userbehavior

where 行为类型="pv" group by 日期;

每日人均pv次数在13次左右，因无对比数据无法展开分析。

（4）每日人均购买次数

select 日期,count(行为类型)/count(DISTINCT 用户ID) as 访问次数 from userbehavior

where 行为类型="buy" group by 日期;

可以说明用户平均每天至少发生一次购物行为，用户购买频率比较稳定；

（5）漏斗分析/转化率

①整体转化情况

select 行为类型,count(行为类型) as 行为数,

concat(round(count(行为类型)/(select count(行为类型)

from userbehavior where 行为类型="pv")*100,2),"%") as 转化率from userbehavior group by 行为类型 order by count(行为类型) desc;

浏览商品PV到购物车和收藏夹的转化率分别为6.07%和3.06%，浏览-购物车几乎是浏览-收藏夹的一倍以上；浏览-最终购买的转化率只有2.34%，意味着每浏览100次商品，才会发生2次购买行为。

②收藏/添加购物车-购买转化情况

select 行为类型 as "收藏/添加购物车-购买转化情况",

concat(round(count(行为类型)/

((select count(行为类型) from userbehavior where 行为类型="cart")+(select count(行为类型) from userbehavior where 行为类型="fav"))*100,2),"%") as 转化率

from userbehavior where 行为类型="buy";

将商品收藏和添加购物车总数到购买的转化率为25.65%，意味着添加购物车和收藏商品的四分之一都将会被购买。

2. 产品数据指标

（1）不同行为用户数、商品数和类型数

select 行为类型,count(行为类型),count(DISTINCT 用户ID) ,count(DISTINCT 商品ID)

from userbehavior group by 行为类型;

（2）商品数量

select count(DISTINCT 商品ID),count(DISTINCT 商品类型ID) from userbehavior;

以上数据总结：

四.总结与建议

通过以上数据可以发现，2017年11月25号-12月3号期间：

（1）用户每日主要访问时段是在下午15-16点和晚上20-22点，其中晚上时段为全日访问最高峰

（2）用户平均每日PV次数在13次左右，每日购买商品次数1.5次

（3）从整体转化率来看，pv到购物车和收藏夹的转化率分别为6.07%和3.06%，到购买的转化率为2.34%，其中浏览-购物车几乎高出浏览-收藏夹的转化率一倍以上，可见用户更倾向于将喜爱的物品添加都购物车中；收藏/添加购物车的商品最终转化为购买的转化率为25.65%，说明每4次添加购物者或者收藏商品，就会有一次最终转化成购买

（4）由于缺少行业数据，因此无法对上述发现的数据进行进一步的分析（比如转化率水平是否是低于平均值，可能的原因是什么，通过假设分析进一步发掘，找到原因并给出意见），所以也无法提出有效建议，此次分析只用来说明数据集基本情况

End.

作者：杨帆

来源：知乎

本文为转载分享，如侵权请联系后台删除