数据分析实战项目：SQL分析淘宝用户行为

一、项目背景及目的

1.1 项目背景

移动互联网企业从粗放式到精细化运营管理过程中，需要结合市场、渠道、用户行为等数据分析，对用户开展有针对性的运营活动，提供个性化、差异化的运营策略，以实现运营业务指标。本项目利用sql对淘宝用户行为数据进行分析，通过用户行为分析业务问题，提供针对性的运营策略。

1.2 项目目的

本次分析的目的是想通过对淘宝用户行为数据分析，为以下问题提供解释和改进建议：
1.分析用户使用APP过程中的常见电商分析指标，建立用户行为转化漏斗模型，确定各个环节的流失率，找到需要改进的环节
2.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律，在用户活跃时间点推出相应营销策略
3.找到用户对不同种类商品的偏好，找到针对不同商品的营销策略
4.找出最具价值的核心付费用户群，对这部分用户的行为进行分析

1.3 数据集来源与介绍

• 数据集来自阿里云天池：数据链接
• 本数据集包含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的组织形式和MovieLens-20M类似，即数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，并以逗号分隔。关于数据集中每一列的详细描述如下：
  | 列名称 | 说明 |
  | 用户ID | 整数类型，序列化后的用户ID |
  | 商品ID | 整数类型，序列化后的商品ID |
  | 商品类目ID | 整数类型，序列化后的商品所属类目ID |
  | 行为类型 | 字符串，枚举类型，包括(‘pv’, ‘buy’, ‘cart’, ‘fav’) |
  | 时间戳 | 行为发生的时间戳 |
  | 行为类型 | 说明 |
  | pv | 商品详情页pv，等价于点击 |
  | buy | 商品购买 |
  | cart | 将商品加入购物车 |
  | fav | 收藏商品 |

二、数据导入

两种导入方式：

• 1、使用图形界面工具导入，例如Navicat（操作简单）
• 2、cmd中以系统命令行导入（速度更快）

2.1 图形界面工具导入

新建数据库

导入数据表

选择导入前1000000行数据
需要注意源数据集中不包含字段名称行，导入时字段名行设置为0，第一个数据行设置为1。

设置字段类型

导入完成，耗时1分21秒

修改字段名

• 注意：源数据集中，时间存储格式是从新纪元开始后的秒数，需要进行进行数据格式转换。

2.2 以系统命令行导入

使用命令行连接MySQL，并进入到taobao数据库

创建新表user

载入源数据

• 速度更快，导入3835331行数据，仅用27.88秒，但是相比于图形界面工具操作更复杂。

三、数据清洗

3.1 删除重复值

# 统计重复值
SELECT *
FROM userbehavior
GROUP BY user_id,item,category,time
HAVING count(user_id)>1;

从查询结果可知，数据中不存在重复记录。

3.2 查看缺失值

# 统计缺失值
SELECT count(user_id),count(item),count(category),count(behavior),count(time)
FROM userbehavior;

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

3.3 时间格式转换

# 新增date、hour时间字段
ALTER TABLE userbehavior
ADD date VARCHAR(20),
ADD hour VARCHAR(20); 
# 时间格式转换
UPDATE userbehavior SET date = FROM_UNIXTIME(time,"%Y-%m-%d");
UPDATE userbehavior SET hour = FROM_UNIXTIME(time,"%H");
UPDATE userbehavior SET time = FROM_UNIXTIME(time);

# 调整一下time字段数据的样式
UPDATE userbehavior SET time = SUBSTRING_INDEX(time,'.',1);

3.4 过滤异常值

由于数据集时间范围为2017-11-25至2017-12-3，因此需要对不在该时间范围内的异常数据进行过滤。

# 筛选异常数据
SELECT * 
FROM userbehavior
WHERE date < '2017-11-25' or date > '2017-12-03';
# 过滤异常数据
DELETE FROM userbehavior
WHERE date < '2017-11-25' or date > '2017-12-03';

总共过滤掉470条异常记录。

四、数据分析

• 分析框架
  

4.1 基于用户行为转化漏斗模型分析用户行为

4.1.1 常见电商指标分析

4.1.1.1 UV、PV、UV/PV

# UV、PV、UV/PV指标统计
SELECT count(DISTINCT user_id) as UV,
	   sum(case when behavior='pv' then 1 else 0 end) as PV,
	   sum(case when behavior='buy' then 1 else 0 end) as Buy,