【SQL实战项目】电商平台数据分析项目

一.分析背景和目的

背景介绍

电商平台数据分析是最为典型的一个数据分析赛道，且电商数据分析有着比较成熟的数据分析模型，比如：人货场模型。此文中我将通过分析国内最大的电商平台——淘宝的用户行为，来巩固数据分析技能以及思维。通过分析用户行为，以此来实现精准营销，总结现有问题，获得业务增长。

数据源

数据源：阿里云天池数据集

数据说明

该数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。

二.分析框架和思路

数据分析的关键是指标体系的建立，从什么角度着手去达成数据分析的目的，在此次数据分析中，是对淘宝用户行为进行一个分析，而在电商用户行为分析中最常用的是“人货场”模型，人：即用户角度；货：即商品角度；场:即卖场，电商平台指标体系角度。

2.1场：电商指标体系角度

电商指标体系维度可以参见一些常见的指标：流量（包括总体流量，每日流量，每时流量），流量漏斗转换，复购率，跳失率。然后下面对每一个概念进行解释：
知识点1：衡量流量的指标为UV，PV，关于他们的解释参考这个链接。如何清除的理解UV和PV的定义
知识点2：流量漏斗转化模型的介绍
知识点3：跳出率
知识点4：重复购买率

2.2人：用户角度

从用户角度，可以采用用户转化漏斗模型和RFM用户分层模型，了解用户转化率，以及高价值用户。

2.3货：商品角度

知识点1：SKU的解释
商品角度可以从SKU下单量，SKU浏览量进行商品流量分析，结合浏览量和下单量对商品进行流量四象限分析。

三.分析主体

数据分析流程包括：
提出问题–理解数据-数据处理和清洗–构建模型-数据可视化

3.1提出问题

• 用户在哪个环节流失以及流失的原因
• 如何提升用户转化率
• 哪些用户是高价值用户
• 基于用户分层如何进行精准运营
• 哪些商品是畅销商品
• 商品流量随着时间段的变化

3.2理解数据

• 用户ID：整数类型，序列化后的用户ID；
• 商品ID：整数类型，序列化后的商品ID；
• 商品类目ID：整数类型，序列化后的商品所属类目ID
• 行为类型：字符串，包括（“pv”:相当于点击，“buy”:商品购买，“cart”:将商品加入购物车，“fav”:收藏商品）
• 时间戳：行为发生的时间戳

3.3数据处理和清洗

3.3.1数据导入

这里分析的数据量为10w+，如果用excel进行分析的话，超出excel的承载，无法进行有效分析，因此这里使用了数据库管理软件Navicat进行分析处理。

（1）新建数据库
（2）导入数据源

3.3.2去除重复值

去除重复值这里，可以将user_id，item_id,timestamp三者进行联合，形成联合主键，对数据集进行分组。如果数据集中没有重复值，则理论上用三者进行联合分组之后，
count(user_id)应该是不大于1的；如果数据集中有重复值，则理论上用三者进行联合分组之后，count(user_id)应该是大于1的。因此在这里判断count(user_id)是否大于1可以用having函数。

SELECT use_id FROM userbavior
GROUP BY user_id,item_id,timestamp
HAVING COUNT(user_id)>1

SQL查询结果如下：

结果显示没有重复值。

3.3.3查看缺失值

在查看缺失值板块，可以统计每一个字段下有多少行，如果行数是相等的说明没有缺失值。（对应于python数据分析中，可以用pandas库中的info()函数，来判断是否存在缺失值)。

SELECT COUNT(user_id),COUNT(item_id),COUNT(category_id),COUNT(behavior_type),COUNT(timestamp)
FROM userbehavior;

从查询结果来看，不存在缺失值，数据质量高。

3.3.4时间格式转换

数据库中时间以int类型进行储存，对于分析师来说非常的棘手，如何将整数类型的时间转换为肉眼能读懂的时间格式呢？可以采用如下函数。
采用函数：FROM_UNIXTIME
作用：将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示。
这里用到的知识点：MYSQL格式时间戳函数：FROM_UNIXTIME

#新增date,hour时间列
ALTER TABLE userbehavior
ADD date VARCHAR(20),
ADD hour VARCHAR(20);
#时间格式转换
#将hour列的时间转换为-年-月-日
UPDATE userbehavior SET date = FROM_UNIXTIME(timestamp,'%Y-%m-%d');
#将hour列的时间转换为-时
UPDATE userbehavior SET hour = FROM_UNIXTIME(timestamp,'%H');
将timestamp列的时间转换为-年-月-日 -时-分-秒
UPDATE userbehavior SET timestamp = FROM_UNIXTIME(timestamp,'%Y-%m-%d %H:%i:%S');

运行结果如下：

3.3.5剔除异常值

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

检查下是否将异常值剔除干净

SELECT MAX(date),MIN(date) FROM userbehavior;

运行结果如下：

3.4构建模型

3.4.1电商数据指标分析

3.4.1.1 总体流量分析

总体流量可以从以下几个指标分析：

SQL查询语句如下：

SELECT
COUNT(DISTINCT user_id) AS UV,
SUM(CASE behavior_type WHEN 'pv' THEN 1 ELSE 0 END) AS PV,
SUM(CASE behavior_type WHEN 'fav' THEN 1 ELSE 0 END) AS 总收藏数量,
SUM(CASE behavior_type WHEN 'cart' THEN 1 ELSE 0 END) AS 总加购数量,
SUM(CASE behavior_type WHEN 'buy' THEN 1 ELSE 0 END)