写在前面的话
为了巩固这段时间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(