SQL | 淘宝用户行为数据分析

一、项目背景

利用淘宝用户行为数据集，学习电商平台数据分析的相关指标和分析模型，培养业务场景下的分析思路。

二、数据预处理

1.数据来源

数据源：User Behavior Data from Taobao for Recommendation

2.理解数据

数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为，数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成。其中，用户行为类型有如下四种：

3.数据清洗

3.1 导入数据
利用Navicat，选取100万行数据导入MySQL。

3.2 重命名数据表列名称
在“设计表”窗口中重命名列名

3.3 查找并删除重复值

select *
from userbehavior
group by user_id,item_id,category_id,behavior_type,timestamp
having count(*)>1

没有返回数据，可见数据集没有重复值。

3.4 缺失值处理

select count(user_id),count(item_id),count(category_id),count(behavior_type),count(timestamp)
from userbehavior

返回值均为999999，没有缺失值。

3.5 标准化处理
为了方便后续的分析，将日期信息分为时间（date_time）、日期（date）、小时（time）字段。
添加date_time字段，根据timestamp字段返回时间信息：

ALTER TABLE userbehavior add COLUMN date_time TIMESTAMP NULL
UPDATE userbehavior
set date_time = FROM_UNIXTIME(`timestamp`)

添加date字段，根据timestamp字段返回日期信息：

ALTER TABLE userbehavior ADD COLUMN date char(10) NULL
UPDATE userbehavior
SET date = FROM_UNIXTIME(`timestamp`,'%y-%m-%d')

添加time字段，根据timestamp字段返回小时信息：

ALTER TABLE userbehavior ADD COLUMN time char(10) null
update userbehavior
set time = SUBSTRING(date_time FROM 12 FOR 2)

得到结果如下：

3.6 异常值处理
通过对日期字段进行升序、降序排序，发现有2017年11月25日到2017年12月3日时间段之外的数据，需要删掉这些异常数据。

DELETE FROM userbehavior
where date > "17-12-03" or date < "17-11-25"

3.7 数据整体情况

SELECT count(DISTINCT user_id) as 用户数,
count(DISTINCT item_id) as 商品数量,
count(DISTINCT category_id) as 商品类目数量,
count(behavior_type) as 行为数量
FROM userbehavior

三、分析角度

1.用户行为分析

通过分析不同时间段的PV、UV以及其它指标，推断用户活跃时段，了解用户行为习惯。

2.购买情况分析

通过分析不同时间段的订单量及其对应的商品、商品类目，以及人均订单量、复购率等指标，了解用户的购买偏好和商品的销售规律。

3.用户行为转化分析

通过用户的收藏转化率、加购转化率、购买转化率，对用户从浏览商品到购买商品的一系列行为进行漏斗分析。

4.用户特征分析

基于RFM模型，将用户划分为不同的等级和层次，从而衡量用户价值。

四、分析过程

1.用户行为分析

1.1 2017年11月25日至2017年12月3日之间的PV（页面浏览量）为：895635

SELECT count(behavior_type) as 浏览量
FROM userbehavior
where behavior_type = 'pv'

1.2 2017年11月25日至2017年12月3日之间的UV（独立访客量）为：9739

SELECT count(DISTINCT user_id)as 访客量
from userbehavior

1.3 平均浏览量为：895635/9739 ≈ 92
即每个用户平均访问了92个页面

1.4 跳失率
只有点击行为的用户数为：567

select count