在日常工作中，我们一般使用Excel来处理数据。但Excel处理的数据集容量比较有限，理论上最大可以处理1048576行*16384列，但往往当数据到达“十万级”时，再处理一些复杂逻辑就会变得比较卡了，操作也不方便，所以对于动辄上百万、上千万乃至更大的数据集，我们可以使用MySQL来处理。本文通过“淘宝用户行为分析”来说明如何利用MySQL进行数据分析。

一、分析背景及目的

分析背景

数据源：

User Behavior Data from Taobao for Recommendation-数据集-阿里云天池​tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1

该数据集来源于阿里云天池，包含了2017年11月25日至2017年12月3日之间，约100万随机用户的所有行为数据（行为包括点击、购买、加购、喜欢），数据量超过1亿行（下载到本地的csv文件达3.4G）。

数据集中的字段含义：

字段含义用户行为类型

2. 分析目的

从数据集中分析出用户行为的规律，找出数据背后反映的问题，并给运营团队提出合理建议。

二、分析思路

分析时间和用户行为之间的关系

1）从日期来分析用户行为有什么规律

2）从每日不同时间段来分析用户行为有什么规律

2. 分析商品和用户行为之间的关系

1）热销商品的各项用户行为有什么表现

2）滞销商品的各项用户行为有什么表现

3. 分析各个用户行为之间的关系（即转化率）

从浏览到加购、收藏再到购买，实际上就是“AARRR模型”的应用，因此我们可以结合“AARRR模型”来分析各个环节用户行为之间转化率

三、数据清洗

选择子集

由于整个数据集过于庞大（>1亿行数据），这里我们只导入前100万行数据进行分析。通过数据库管理工具Navicat将数据集导入MySQL数据库。

2. 多删少补

1）删除重复值

由于该数据表没有主键，因此重复值应该是指五个字段值均相同的记录，用SQL查询：

-- 查询重复值 SELECT * FROM userbehavior GROUP BY `User ID`,`Item ID`,`Category ID`,`Behavior type`,`Timestamp` HAVING COUNT(*)>1;

结果显示：

也即数据集中不存在重复值。

2）填补缺失值

查询表中存在空值的记录

-- 查询含空值的记录 SELECT * FROM userbehavior WHERE `User ID` IS NULL OR `Item ID` IS NULL OR `Category ID` IS NULL OR `Behavior type` IS NULL OR `Timestamp` IS NULL;

结果显示：

也即数据集中不存在空值。

3. 一致化处理

观察发现表中的时间显示的不是日期格式，而是字符型格式（导入数据时Unix时间戳必须选字符型格式，否则时间显示全为0），因此要把时间转换成日期格式。

-- 增加一列存放日期ALTER TABLE userbehavior ADD `Date` VARCHAR(255); -- 从时间戳里抽取出日期 UPDATE userbehavior SET `Date` = FROM_UNIXTIME(`Timestamp`,%Y-%m-%d); -- 增加一列存放时间 ALTER TABLE userbehavior ADD `Time` VARCHAR(255); -- 从时间戳里抽取出时间 UPDATE userbehavior SET `Time` = FROM_UNIXTIME(`Timestamp`,%H:%i:%s);

结果显示：

4. 异常值处理

观察数据集发现，User ID、Item ID、Category ID无法判断是否异常，所以只需判断Behavior type和Timestamp即可

1）判断Behavior type中是否存在pv/cart/fav/buy之外的值

-- 判断Behavior type是否存在异常值SELECT `Behavior type` FROM userbehavior WHERE `Behavior type` NOT IN(pv,cart,fav,buy);

结果显示：

也即Behavior type中不存在异常值

2）判断Timestamp是否存在异常值，这里只需要判断是否存着2017年11月25日至2017年12月3日这段时间之外的日期即可（因为数据集取于这段时间）

-- 判断时间是否存在异常值SELECT `Date` FROM userbehavior WHERE `Date` NOT BETWEEN 2017-11-25 AND 2017-12-03;

结果显示：

也即有470条异常记录，需要删除掉

-- 创建临时表存放异常值 CREATE TABLE del_tmp AS ( SELECT * FROM userbehavior WHERE `Date` NOT BETWEEN 2017-11-25 AND 2017-12-03 ); -- 删除异常日期记录 DELETE FROM userbehavior WHERE `Date` IN ( SELECT `Date` FROM del_tmp WHERE `Date` NOT BETWEEN 2017-11-25 AND 2017-12-03 );

至此，数据清洗完毕，下面进入正式的分析过程。

四、分析过程

分析时间和用户行为之间的关系

1）从日期来分析用户行为规律

按照日期Date分组，查询各个用户行为每日的总数量

-- 查询每日总浏览量 SELECT `Date`,COUNT(`Behavior type`) AS 每日总浏览量 FROM userbehavior WHERE `Behavior type` = pv GROUP BY `Date` ORDER BY Date ASC; -- 查询每日总加购数 SELECT `Date`,COUNT(`Behavior type`) AS 每日总加购数 FROM userbehavior WHERE `Behavior type` = cart GROUP BY `Date` ORDER BY Date ASC; -- 查询每日总收藏数 SELECT `Date`,COUNT(`Behavior type`) AS 每日总收藏数 FROM userbehavior WHERE `Behavior type` = fav GROUP BY `Date` ORDER BY Date ASC; -- 查询每日总购买量 SELECT `Date`,COUNT(`Behavior type`) AS 每日总购买量 FROM userbehavior WHERE `Behavior type` = buy GROUP BY `Date` ORDER BY Date ASC;

查询结果导入到Excel进行可视化（“每日总浏览量”对应左侧主坐标轴，其他三个行为对应右侧次坐标轴）：

从曲线走势上看，12月2日的浏览量、加购数、收藏数都有明显的增加，但是最终的购买量并没有明显增加。

为什么会这样呢？

结合我们实际生活中的经验，“双十二”即将到来，可能是商家提前做了宣传，用户浏览了商品，也加购或者收藏了商品，但是想要等到双十二再购买。因为这里的数据有限，没办法验证是否这个猜想。

2）从每日不同时间段来分析用户行为规律

以小时划分，在原表上增加一列“Hour”用来显示一天中的“时”

-- 增加一列存放小时 ALTER TABLE userbehavior ADD `Hour` varchar(255); -- 从时间戳里抽取出小时 UPDATE userbehavior SET `Hour` = FROM_UNIXTIME(`Timestamp`,%H);

接着查询不同时间段内各个用户行为的总数量

-- 按照时间Houe分组，查询不同时间段内各个用户行为的总数量SELECT `Hour`, SUM(CASE WHEN `Behavior type` = pv THEN 1 ELSE 0 END) AS 总浏览量, SUM(CASE WHEN `Behavior type` = cart THEN 1 ELSE 0 END) AS 总加购数, SUM(CASE WHEN `Behavior type` = fav THEN 1 ELSE 0 END) AS 总收藏数, SUM(CASE WHEN `Behavior type` = buy THEN 1 ELSE 0 END) AS 总购买量 FROM userbehavior GROUP BY `Hour` ORDER BY `Hour` ASC;

结果可视化（“总浏览量”对应左侧主坐标，其他三项对应右侧次坐标）：

从图中可以看出，晚上19:00~22:00各项行为总数呈上升趋势，在22:00~23:00之间达到波峰；晚上23:00~次日06:00各项行为呈下降趋势，在06:00达到波谷

此现象可以解释为晚上19:00后用户空闲时间比较多，使用淘宝的各项行为指标自然就会上涨；到了23:00之后，用户陆陆续续休息了，行为指标就下降了

3）小结：

12月2日浏览量、加购数、收藏数都较前几天明显增加，但购买量没有明显变化，可能是商家双十二促销行为，需要更多数据作进一步验证；晚上19:00~23:00，用户的活跃度持续升高；23:00之后，活跃度迅速下降。这符合用户的作息规律。

2. 分析商品和用户行为之间的关系

首先从整体上看一下商品数量和商品类目数量

-- 查询商品数量和商品类目数量 SELECT COUNT(DISTINCT `Item ID`) AS 商品数量, COUNT(DISTINCT `Category ID`) AS 商品类目数量 FROM userbehavior ;

结果显示：

即有近40万件商品和近6000个商品类目。由于商品数量较多，差异较大，因此选用商品类目来分析比较具备代表性

1）分析热销商品的各项用户行为表现

① 第一步，查询各类商品的销量

-- 查询各类商品的销量 SELECT `Category ID`,COUNT(`Behavior type`) AS 销量 FROM userbehavior WHERE `Behavior type` = buy GROUP BY `Category ID`;

② 第二步，查询销量前十的商品类目

-- 查询销量前十的商品类目 SELECT * FROM ( SELECT `Category ID`,COUNT(`Behavior type`) AS 销量 FROM userbehavior WHERE `Behavior type` = buy GROUP BY `Category ID` ) AS a ORDER BY a.`销量` DESC LIMIT 10;

③ 第三步，查询销量前十的商品类目对应的所有用户行为

-- 查询销量前十的商品类目对应的所有用户行为 SELECT c.`Category ID`,b.`Behavior type`,c.`销量` FROM userbehavior AS b INNER JOIN ( SELECT * FROM ( SELECT `Category ID`,COUNT(`Behavior type`) AS 销量 FROM userbehavior WHERE `Behavior type` = buy GROUP BY `Category ID` ) AS a ORDER BY a.`销量` DESC LIMIT 10 ) AS c ON b.`Category ID` = c.`Category ID`;

④ 第四步，查询销量前十的商品类目对应的各个用户行为数量

-- 查询销量前十的商品类目对应的各个用户行为数量 SELECT d.`Category ID`, SUM(CASE WHEN d.`Behavior type`=pv THEN 1 ELSE 0 END ) AS 浏览量, SUM(CASE WHEN d.`Behavior type`=cart THEN 1 ELSE 0 END ) AS 加购数, SUM(CASE WHEN d.`Behavior type`=fav THEN 1 ELSE 0 END ) AS 收藏数, SUM(CASE WHEN d.`Behavior type`=buy THEN 1 ELSE 0 END ) AS 购买量 FROM ( SELECT c.`Category ID`,b.`Behavior type`,c.`销量` FROM userbehavior AS b INNER JOIN ( SELECT * FROM ( SELECT `Category ID`,COUNT(`Behavior type`) AS 销量 FROM userbehavior WHERE `Behavior type` = buy GROUP BY `Category ID` ) AS a ORDER BY a.`销量` DESC LIMIT 10 ) AS c ON b.`Category ID` = c.`Category ID` ) AS d GROUP BY d.`Category ID`,d.`销量` ORDER BY d.`销量` DESC;

查询结果为：

把结果可视化（“浏览量”对应左侧主坐标，其余三项对应右侧次坐标）：

从图中可以看出，浏览量多的商品，加购数和收藏数也会多，但是最终的购买量却不一定；最明显的类目为4756105的商品，浏览量、加购数和收藏数都是最多的，但购买量却排在第五名。

如果用户不喜欢这类商品，那么只会浏览量高，加购数和收藏数会低，但现在是三者都高，就证明该类商品用户是喜欢的。喜欢为什么又不买呢？这很可能就是前面提到的，用户把商品放在购物车里或者收藏起来，等到双十二促销的时候再买。这里欠缺数据，无法继续确认原因。

2）分析滞销商品的各项用户行为表现

查询销量最后十名的商品类目对应的各个用户行为数量

-- 查询销量最后十名的商品类目对应的各个用户行为数量（正确）SELECT d.`Category ID`, SUM(CASE WHEN d.`Behavior type`=pv THEN 1 ELSE 0 END ) AS 浏览量, SUM(CASE WHEN d.`Behavior type`=cart THEN 1 ELSE 0 END ) AS 加购数, SUM(CASE WHEN d.`Behavior type`=fav THEN 1 ELSE 0 END ) AS 收藏数, SUM(CASE WHEN d.`Behavior type`=buy THEN 1 ELSE 0 END ) AS 购买量 FROM ( SELECT c.`Category ID`,b.`Behavior type`,c.`销量` FROM userbehavior AS b INNER JOIN ( SELECT * FROM ( SELECT `Category ID`,COUNT(`Behavior type`) AS 销量 FROM userbehavior WHERE `Behavior type` = buy GROUP BY `Category ID` ) AS a ORDER BY a.`销量` ASC LIMIT 10 ) AS c ON b.`Category ID` = c.`Category ID` ) AS d GROUP BY d.`Category ID`,d.`销量` ORDER BY d.`销量` ASC;

结果可视化：

相对于庞大的数据集，从上图看滞销商品的数据并没有太明显的特征，因此无法作出判断。

3）小结

热销类目前十的商品，其浏览量、加购数、收藏数表现一致，但购买量相差却不大，可能是部分商品提前开启了双十二促销活动，导致浏览量、加购数、收藏数大幅提升，购买量最终会在双十二当天体现出来滞销类目后十的商品，其数据没有反应出明显特征

3. 分析各个用户行为之间的关系（即转化率）

画出“AARRR模型”，把数据集中的字段对应到“AARRR模型”中

查询各个行为的总数量：

-- 查询各个行为的总数量 SELECT `Behavior type`,COUNT(`Behavior type`) AS 总数 FROM userbehavior GROUP BY `Behavior type`;

结果可视化，并转换成百分比条形图：

把cart和fav归为一类，则从行为数量的角度看，“获取——激活——收入”的转化率为“100%——9%——2%”，换句话说，就是激活率9%，成交率2%

这个数据合不合理呢？这里无法直接判断。我们再从行为人数的角度来看一下：

查询各个行为的人数：

-- 查询有浏览行为的用户数 SELECT COUNT(a.`User ID`) AS 浏览人数 FROM ( SELECT `User ID`,COUNT(`Behavior type`) AS 浏览量 FROM userbehavior WHERE `Behavior type` = pv GROUP BY `User ID` ) AS a ; -- 查询有加购行为的用户数 SELECT COUNT(b.`User ID`) AS 加购人数 FROM ( SELECT `User ID`,COUNT(`Behavior type`) AS 加购数 FROM userbehavior WHERE `Behavior type` = cart GROUP BY `User ID` ) AS b ; -- 查询有收藏行为的用户数SELECT COUNT(c.`User ID`) AS 收藏人数 FROM ( SELECT `User ID`,COUNT(`Behavior type`) AS 收藏数 FROM userbehavior WHERE `Behavior type` = fav GROUP BY `User ID` ) AS c ; -- 查询有购买行为的用户数 SELECT COUNT(d.`User ID`) AS 购买人数 FROM ( SELECT `User ID`,COUNT(`Behavior type`) AS 购买量 FROM userbehavior WHERE `Behavior type` = buy GROUP BY `User ID` ) AS d ;

结果可视化

以浏览人数为基准，换成百分比条形图

可以看出，从产生行为的人数的角度来看，“pv—buy”的转化率并不低，达到近七成，也即10个产生浏览行为的用户里，有7个产生购买行为。那么为什么从行为总数量来看转化率只有2%？

我们把各个行为的数据找出来：

-- 查询各用户行为情况 SELECT `User ID`, sum( CASE WHEN `Behavior type`=pv THEN 1 ELSE 0 END ) AS 浏览量, sum( CASE WHEN `Behavior type`=cart THEN 1 ELSE 0 END ) AS 加购数, sum( CASE WHEN `Behavior type`=fav THEN 1 ELSE 0 END ) AS 收藏数, sum( CASE WHEN `Behavior type`=buy THEN 1 ELSE 0 END ) AS 购买量 FROM userbehavior GROUP BY `User ID`;

把得到的结果导入Excel，并使用描述统计分析

从描述统计分析中可以看出：

人均浏览量达到92，而人均购买量只有2从中位数反映出整体上67个浏览才会产生1个购买四个行为的变异系数都很大，也就意味着四个行为的数据集离散程度都很高结合前面的分析，可以知道虽然有浏览行为的人里有接近七成的人产生购买行为，而且大部分用户会浏览大量页面后才会最终选择购买

小结：

获取到的用户有接近七成产生了购买行为，大部分用户会在浏览大量页面后选择加购、收藏或者购买，无效浏览比较多

五、分析结论和建议

综合上述的结论并给出建议：