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

挖数网精选
挖数网精选
挖数网精选
444
文章
0
评论
2020-07-0213:07:00 评论 246 3395字
摘要

SQL实战案例分享,本文选取数据包含了2017年11月25-12月3号期间,随机选取的1,000,000用户行为信息进行分析,希望能对你有所启发。

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

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

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

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

一.提出问题

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

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

二.数据清洗

1. 选择子集

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

2. 重命名

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

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

3. 删除重复值

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

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

from userbehavior

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

having count(*)>1;

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

结果显示没有重复值

4. 处理缺失值

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

from userbehavior;

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

结果显示无缺失值

5. 一致化处理

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

-- 增加日期列

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

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

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

查看处理后数据

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

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

-- 增加日期列

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);

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

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

6. 异常值处理

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

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

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

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

结果共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 | 100万用户的行为分析实战

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

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

用户访问量的时间变化

可以看出每天凌晨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 | 100万用户的行为分析实战

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

从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 日期;

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

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

(4)每日人均购买次数

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

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

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

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

(5)漏斗分析/转化率

①整体转化情况

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

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

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

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

浏览商品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";

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

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

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

2. 产品数据指标

(1)不同行为用户数、商品数和类型数

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

from userbehavior group by 行为类型;

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

(2)商品数量

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

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

以上数据总结:

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

四.总结与建议

通过以上数据可以发现,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.

作者:杨帆

来源:知乎

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

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: