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

写在前面的话
为了巩固这段时间SQL的学习成果，同时方便以后回忆和二次学习，我决定把自己做过的项目 淘宝用户行为分析 整理出来，和各位网友分享。同时欢迎大家提出不同意见和建议，我们一起讨论，共同学习和进步。

处理数据用到的软件
数据导入和导出：Navicat Premium 12
可视化：Tableau 2019.4 、Excel 2019

一、数据收集

1.1 数据获取

数据源：阿里云-天池-淘宝数据分析

1.2 数据说明

UserBehavior.csv 是阿里巴巴提供的一个淘宝用户行为数据集，用于隐式反馈推荐问题的研究。

文件名称	说明	包含特征
UserBehavior.csv	包含所有的用户行为数据	用户ID，商品ID，商品类目ID，行为类型，时间戳

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

列名称	说明
用户ID	整数类型，序列化后的用户ID
商品ID	整数类型，序列化后的商品ID
商品类目ID	整数类型，序列化后的商品所属类目ID
行为类型	字符串，枚举类型，包括(‘pv’, ‘buy’, ‘cart’, ‘fav’)
时间戳	行为发生的时间戳

1.3 数据导入

由于数据量庞大，无法使用Excel直接处理，故本次使用数据库管理软件Navicat，导入了10W条数据集，进行分析处理。接下来是导入流程：

1.3.1 新建数据库

1.3.2 导入数据源

表–右键单击–导入向导–选择数据源–选择分隔符–选择导入记录条数与时间格式–接下来默认即可-开始耐心等待导入


这部分就不赘述啦，csv文件导入网上教程很多，大家可以搜索关键词 Navicat 导入csv。

Nacivat使用心得：这是我自己在使用中遇到的问题，对表做操作以后刷新，居然没反应，后来我发现可以这样解决：打开表-随便选中某一列-右键-刷新，屡试不爽啊。

二、数据清洗

2.1 去除重复值

我们可以通过这三个字段：userid, itemid, timestamps 是否完全相同，来检查是否有相同的记录。也就是说，我们认为userid, itemid, timestamps这三个字段相当于这个表的主键，三者联合起来，完全定义一条独立的记录。
SQL查询语句如下：

SELECT userid FROM userbehavior
GROUP BY userid,itemid,timestamps
HAVING COUNT(userid) > 1;

SQL查询结果如下：

结果显示没有重复记录

2.2 检查缺失值

我们使用计数，既可以得到每一列的非空值数目
SQL查询语句如下：

-- 1.检查是否有缺失值
SELECT COUNT(userid),COUNT(itemid),COUNT(categoryid),count(behaviortype),COUNT(timestamps) 
FROM userbehavior

SQL查询结果如下：

每一列的记录条数都相同，所以没有缺失值。

2.3 日期格式转换

在mysql中因为timestamp无法支持到毫秒，所以很多时候采用毫秒进行存储。那么如何将存储在数据库中Int类型的时间，如： 1344954515 ，转换成我们正常可以肉眼能看懂的时间格式呢？

知识点1：MySQL格式化时间戳函数：FROM_UNIXTIME()
知识点2：Mysql字符串截取总结：left()、right()、substring()、substring_index()

因为源数据格式不符合我们接下来的分析需求，所以需要进行数据转换：

2.3.1 处理timestamp时间列

SQL查询语句如下：

-- 2.1 处理timestamp时间列
-- 添加新列dates_time，返回日期和时间
ALTER TABLE userbehavior 
ADD COLUMN dates_time TIMESTAMP(0) NULL;
UPDATE userbehavior SET dates_time = FROM_UNIXTIME(timestamps);

2.3.2 添加新列dates，返回日期

SQL查询语句如下：

-- 2.2 添加新列dates，返回日期
-- 注意指定格式的大小写
ALTER TABLE userbehavior 
ADD COLUMN dates CHAR(10) NULL;
UPDATE userbehavior SET dates = FROM_UNIXTIME(timestamps,'%Y-%m-%d');

2.3.3 添加新列time，返回时间

SQL查询语句如下：

-- 2.3 添加新列time，返回时间
ALTER TABLE userbehavior 
ADD COLUMN time CHAR(10) NULL;
UPDATE userbehavior SET time = FROM_UNIXTIME(timestamps,'%H:%i:%S');-- 注意指定格式的大小写

2.3.4 添加新列time_hour，返回hour

SQL查询语句如下：

-- 2.4 添加新列time_hour，返回hour
-- 为了方便后面按照小时对用户习惯进行分析
ALTER TABLE userbehavior 
ADD COLUMN time_hour CHAR(10) NULL;
UPDATE userbehavior SET time_hour = LEFT(time,2);

2.3.5 数据展示

经过上面的数据清洗过程，源数据如下图所示：

2.4 剔除异常值

因为我们分析的时间范围是2017-11-25至2017-12-3 (9天)，所以需要剔除不在这9天的数据：

SQL查询语句如下：

-- 3.剔除异常值
-- 排除日期不在2017-11-25至2017-12-3这9天的数据
DELETE FROM userbehavior
WHERE dates < '2017-11-25' or dates > '2017-12-03';

然后我们再次检查一下源数据：

-- 4.再次检查
SELECT MIN(dates),MAX(dates) FROM userbehavior;

SQL查询结果如下：

结果显示，目前的日期范围已经符合我们的分析需求。

三、数据分析

3.1 分析思路

3.2 电商数据分析

知识点1：如何清楚易懂的解释“UV和PV"的定义? - 知乎
知识点2：跳出率
知识点3：重复购买率
知识点4：SEM流量四象限分析法

3.2.1 总体流量 – uv / pv / 收藏 / 加购 / 下单数量

SQL查询语句如下：

-- 1.总体 uv/点击/收藏/加购/下单数量
select 
COUNT(DISTINCT userid) AS 总用户数,
SUM(CASE WHEN behaviortype = 'pv'   THEN 1 ELSE 0 END ) AS 总点击数量,
SUM(CASE WHEN behaviortype = 'fav'  THEN 1 ELSE 0 END ) AS 总收藏数量,
SUM(CASE WHEN behaviortype = 'cart' THEN 1 ELSE 0 END ) AS 总加购数量,
SUM(CASE WHEN behaviortype = 'buy'  THEN 1 ELSE 0