SQL分析阿里云淘宝电商数据

本文使用SQL语句对50w条淘宝用户数据进行分析。
数据库：SQL Server。

一、提出问题

1、分析目的

本文期待通过挖掘淘宝用户数据价值，研究用户活跃度，购买行为，商品销量等情况，对提升电商关键业务指标提出建议。
具体讨论问题如下：

1. 从点击到购买，用户流失情况如何？
2. 日常用户活跃度如何？具体的时间段用户活跃度呢？
3. 特殊日期（周末）用户活跃度情况如何？
4. 用户分时间段下单情况？哪个时间段下单最多？
5. 核心用户有哪些，特点如何？
6. 用户点击与购买之间是否存在关联？
7. 哪些商品，商品类目最受欢迎，订单量最高?

2、分析框架

二、数据准备

1、数据来源

数据源自阿里云天池数据库——User Behavior Data from Taobao for Recommendation

2、数据描述

数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。
详见下图：

3、数据预处理

3.1截取数据

由于100w条数据量太大，使用查询语句运转太慢，所以改为选取其中50w，运行速度需6s。

--使用Rand()进行随机抽取
select top 500000 * into data from UserBehavior
order by RAND() 

3.2设置列名及属性

进入数据管理器，选择设计，修改列名及属性如下图：

如果出现报错，可以参考此文 SQL SERVER不能修改表的解决方法（增加字段、修改字段名）

3.3时间戳的转化

--转化时间戳为时间
select DATEADD(S, 时间戳+ 8 * 3600,'1970-01-01 00:00:00' ) from data
ALTER TABLE data ADD 时间  datetime NULL;
update data
set 时间 = DATEADD(S, 时间戳+ 8 * 3600,'1970-01-01 00:00:00' )

--新增列，日期
ALTER TABLE data ADD 日期  date NULL;
update data
set 日期 = Datename(year, 时间)+'-'+Datename (month,时间)+'-'+Datename(day,时间) 
from data

--新增列，时间
ALTER TABLE data ADD 时分秒 time(0) NULL;
update data
set 时分秒 = Datename(HOUR, 时间)+':'+Datename (MINUTE,时间)+':'+Datename(SS,时间) 
from data

3.4数据检查

去除重复项：

select distinct * from data

判断行为类型是否符合四类。

select *from data
WHERE 用户ID IS NULL OR 商品ID IS NULL OR 商品类目ID IS NULL
OR 行为类型 IS NULL OR 时间戳 IS NULL
select 行为类型, COUNT(*) from data group by 行为类型

判断日期是否在2017年11月25日至2017年12月3日区间。

--查看是否存在日期异常
select MAX(时间),MIN(时间),MAX(日期),MIN(日期) from data
delete from data
--删除过大或者过小的日期
where 时间 < '2017-11-25 00:00:00' 
or 时间 > '2017-12-04 00:00:00'

总计删除219条数据，有效数据为499782条。

三、数据分析

1、数据概况

1.1 用户数量，不同商品数量，商品类目数量统计

49.9w条数据，主要包含4864名用户记录，24.2w商品细类和5065个商品类目。

--用户数量
select COUNT(distinct 用户ID) as 用户总数 from data
--不同具体商品数
select COUNT(distinct 商品ID) as 不同商品总数 from data
--不同商品类目
select COUNT(distinct 商品类目ID) as 商品类目总数 from data

1.2 行为类型

关注4个客户行为类型的基本数据，分析用户行为路径。

select 行为类型, COUNT(*) as 次数, COUNT(distinct 用户ID) as 人数
from data group by 行为类型

转化率节点位置

从点击量89.83%到最后购买2.05%，流失最大的位置是点击到购物车环节。

用户UV转化指标

购买人数占点击人数的69.7%，转化率很高。当物品被放入购物车后，有91%的几率下单。

2、用户活跃度分析

用户的点击量体现的是活跃度，通过点击量与时间的交叉分析，聚焦产品活动时间段，分析用户潜能，实现有效推广。

2.1 用户访问变化情况

select  日期,COUNT(用户ID) as 点击数 from data
where 行为类型= 'pv'
group by 日期
order by 点击数 desc

点击率前三分别是12.2（周六）, 12.3（周日）, 12.1（周五）。12.2之前的用户平均点击量约为4.6w，12.2与12.3实现了平均32%的点击量增幅。与一周前的周六日11.25和11.26点击量对比，这个数据展示了周末用户活跃度可以经过运营活动提升的潜能。

2.2 活跃度的具体时间段

ALTER TABLE data ADD 小时 int NULL;
update data
set 小时=DATENAME(HH, 时分秒) from data
select 小时, COUNT(小时) as 计数 from data where 行为类型='pv'
group by 小时
order by 小时

0-8时睡觉时间，点击量很低，低于平均值；
9-18时工作时间，点击量在均值上下浮动；
19-23时下班时间，点击量呈现正态分布，在21-22时达到峰值。

2.3 特殊日期（周末）活跃度

上文提到12.2与12.3日为点击量最多的日期，而同为周末的11.25与11.26则点击量表现不显著。下面对其进行可视化对比：

select 小时, COUNT(小时) as 计数 from data 
where 行为类型='pv' and 日期 in ('2017-12-02','2017-12-03' )
group by 小时
order by 小时
select 小时, COUNT(小时) as 计数 from data 
where 行为类型='pv' and 日期 in ('2017-11-25','2017-11-26' )
group by 小时
order by 小时

【周末】数据曲线与【点击量与时间点】曲线基本趋势相同。
下面对12.2-12.3和11.25-11.26两个周末的时间点进行对比：
细节上，10时是第一个小峰值，12时均有回落，但11月底的周末回落幅度大于12月初周末；
第二个小峰值到第二个低谷的时间长度不同，11.25-11.26下午3点达到峰值，而12.2-12.3保持点击量增长直到下午4点才开始下降；
从第二个低谷至全天峰值增长百分比不同，12月初的周末从下午5低谷至21点峰值的点击量增长率为94.5%，11月底周末的增长率为79.5%；
总体来说，0-8时之间点击量相差不大，9时之后点击量拉开差距，在21时实现差值最大，这也体现了运营活动对用户活跃度产生的影响。
19时至22时为用户最活跃时间段，点击量增长潜力巨大，商家可考虑在此时间段推出活动从而提升销售额；另外，考虑到用户的活跃时间，应注意调整客服时间，及时回复消息。

3、 用户购买行为分析

3.1 分时段下单情况

select 小时, COUNT(小时) as 下单数 from data 
where 行为类型='buy' 
group by 小时
order by 小时

这个曲线趋势与用户活跃时间曲线基本相同，说明用户点击量与购买量存在正相关。但白天下单量与晚间差值不大，晚间时段大部分用户只浏览不购买。

3.2 下单用户分析

不同下单数量的频数分布

--创建视图
create view user_buy 
as 
select  用户ID, COUNT(*) as 下单数 from data 
where 行为类型='buy' 
group by 用户ID
go
--视图基础上筛选下单数top10
select top 10 下单数,  COUNT(*) as 频数 from user_buy
group by 下单数
order by 频数 desc

取前十个频数最多的数据来看，下单次数越多的用户占比越小。下单1次的用户占比最多，达33%，应多调动这部分用户的活跃度，培养用户购买习惯，提升复购率；下单2-3次的潜在忠诚客户占比为40%，应加大活动力度，稳定用户成为高频使用会员；平台高频率用户占27%，需要注重产品质量与客服，获得用户的深度认同。

核心用户的购买行为

select top 10 用户ID,COUNT(*) as 订单量, 
count( distinct 商品ID) as 商品数量 from data
where 行为类型='buy' 
group by 用户ID
order by 订单量 desc

除了订单数量外，订单额也是需要引入的维度。同时拥有高订单量和高订单额特征的用户才最具价值。需要对这些用户予以高度关注，针对其消费喜好进行定制化服务，赢得他们对产品的深度认同。

以订单量最多的用户ID1008380为例：

create view No1_buy
as
select 商品类目ID, COUNT(用户ID) as 购买次数 from data
where 行为类型='buy' and 用户ID ='1003983'
group by 商品类目ID

create view No1_pv
as
select 商品类目ID, COUNT(用户ID) as 浏览次数 from data
where 行为类型='pv' and 用户ID ='1003983'
group by 商品类目ID

select * from No1_pv left join No1_buy on No1_buy.商品类目ID=No1_pv.商品类目ID
order by 购买次数 desc, 浏览次数 desc

商家可根据其浏览及购买数据生成客户偏好数据（偏好价格区间，相似商品类目等），着重关注多次浏览商品，智能推送优选产品，从而提升销售额。

4、商品销量分析

4.1 商品销量排行榜（Top 10）

select top 10 商品ID, COUNT( 用户ID) as 购买次数 from data
where 行为类型='buy'
group by 商品ID
order by 购买次数 desc

上表为销量前十的商品，数据维度信息太少，无法从中获取更有用的信息，引入点击量与比例维度（购买次数/点击次数）。

4.2 【购买】与【点击】环节相关分析

--创建视图商品销量
create view 商品销量
as
select 商品ID, COUNT( 用户ID) as 购买次数 from data
where 行为类型='buy'
group by 商品ID

--创建视图商品点击量
create view 商品点击量
as
select 商品ID, COUNT( 用户ID) as 点击次数 from data
where 行为类型='pv'
group by 商品ID


select top 10 商品销量.商品ID, 购买次数, 点击次数, 购买次数/点击次数  as 比例
 from 商品销量 left join 商品点击量
on 商品销量.商品ID =商品点击量.商品ID
where 购买次数/点击次数 >= 1
order by 购买次数 desc , 点击次数 desc, 比例 desc

总计筛选出1993个商品，占总商品数（242165）的0.8%。
上表选取展示销量前十的且购买数大于等于浏览量的商品。这些商品对购买者具有很强的吸引力，使得购买者未多加思索便进行购买。对这一现象的具体分析需要更多的数据支持（商品特征，销售策略，品牌，质量，服务，消费者行为明细等），从而提升商品吸引力。

反过来，我们可以使用【比例】升序筛选出低于总体平均值0.02（即浏览超过50次下单1次）的商品，分析商品存在的吸引力缺失因素，为提升转化率提供解决方案。
这里的平均值=购买量（10222）/点击量（448970）

select 商品销量.商品ID, 购买次数, 点击次数, 购买次数*1./点击次数  as 比例
 from 商品销量 left join 商品点击量
on 商品销量.商品ID =商品点击量.商品ID
where 购买次数*1./点击次数 < 0.02
order by 比例 asc

4.3 商品类目销量排行榜（Top 10）

select top 10 商品类目ID, COUNT(distinct 用户ID) 客户数, 
COUNT(distinct 商品ID) 商品数  from data
where 行为类型='buy'
group by 商品类目ID
order by 客户数 desc

商品类目可以为商家标签化购买者偏好提供线索，可惜的是，原始数据只有类目ID，缺少数据支持。

四、结论与建议

1.对淘宝用户路径行进行漏斗检查，从点击量89.83%到最后购买2.05%，流失最大的位置（即夹点）是点击到购物车的环节。这对这一环节，建议如下：提高搜索和标签精度，以便帮助用户快速获取所需商品信息，降低用户跳出率；根据搜索关键词进行优选智能推荐，增加用户购买几率。
2. UV（Unique Visitor）用户购买人数占点击人数的69.7%，转化率很高。当物品被放入购物车后，有91%的几率下单。将淘宝用户的浏览及购买记录生成客户偏好数据（偏好价格区间，商品类目等），着重关注多次浏览商品，实施智能精准推送。
3. 点击量最高的日期为12.2（周六）与12.3（周日）。12.2之前的用户平均点击量约为4.6w，12.2与12.3实现了平均32%的点击量增幅。与一周前的周六日11.25和11.26点击量对比，这个数据展示了周末用户活跃度可以经过运营活动提升的潜能。
4. 全天分时段来看，0-8时点击量不大，9时之点击量逐渐提升，在平均值附近徘徊，19时开始最活跃度快速上升，21-22时达到峰值，随后回落。19-23时这一时间段点击量增长潜力巨大，商家可考虑在此时间段推出活动从而提升销售额；另外，考虑到用户的活跃时间，应注意调整客服时间，及时回复消息。