基于MySQL的淘宝用户行为分析

一、分析背景

随着移动互联网多年的发展，移动互联网不再依靠红利来经营和发展业务。对于电商企业的竞争愈来愈激烈，电商行业逐渐转变为精细化运营，结合市场、渠道、用户行为等数据分析，对用户展开有针对性的运营活动，提供个性化、差异化的运营策略，以实现运营目的的行为。
在2C领域，用户行为分析是一个绕不开的话题，提高各个环节的转化率，提高用户留存，才能带来更大GMV，才会有更高的收益。用户在电商平台上有购买意愿时，会先浏览商品界面，在比质比价之后，先收藏或先加入购物车，之后再完成支付。而在任何一个环节，用户都有可能流失，从而交易终止。

二、分析目的

由于消费者每完成一笔交易，就会在淘宝网的数据库系统生成一条记录，而这些海量的数据就形成了“数据金矿”。而利用这些数据可以进一步发现消费者的消费行为，提高商品的转化率，为企业挖掘增长机会。具体而言，是利用电商常见的业务指标，如用户访问量（PV），独立访客量（UV），商品收藏情况，商品加购情况，商品购买情况等信息，从而了解到不同商品的转化情况，即利用漏斗模型分析用户转化情况，并为企业经营提供针对性的建议。

三、理解数据

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

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

注意到，用户行为类型共四种，它们分别是：

关于数据集大小的一些说明如下：

原数据集数据记录达到1亿条，数据量庞大，为了方便分析与效率，本项目将用Navicat选取从500万行至600万行的100万条记录进行分析。

四、提出相关问题

1、相关问题

1）纵向分析
用户从浏览到最终购买的整个过程的流失情况，进一步发现转化率低的原因，提出改善转化率的意见。从流量指标和转化指标入手。
2）基于时间维度了解用户的行为习惯
可以观察每天用户的四种行为的变化趋势、以及每小时这四种行为的变化趋势，找出哪些日期和时间段是用户最活跃的时候，可以根据用户的活跃程度来制定不同的营销策略。
3）基于商品维度分析用户的行为习惯
对浏览量、购买量前10的商品以及商品类目进行分析，找出热搜商品和热销商品，优化产品销售
4）基于用户维度分析用户的行为差异和用户价值
首先，分析出购买率前10的用户和购买率最后10名用户的四种行为特征，观察两类人群在四种行为上是否存在较大的差异；其次，基于 RFM 模型找出有价值的用户。

2、分析框架

五、数据清洗

1、选择子集

数据集的每个字段都有作用，因此不需要选子集

2、数据类型修改

导入的数据中用户ID、商品ID、商品所属类目ID都变成了整型，需要转换为字符串类型。

ALTER TABLE userbehavior MODIFY user_id VARCHAR(8);

ALTER TABLE userbehavior MODIFY items_id VARCHAR(8);

ALTER TABLE userbehavior MODIFY category_id VARCHAR(8);

3、重复值检查

该数据表中存在很多用户id一样的记录，但不影响分析，因为一个用户可能在同一时段或不同时段出现重复浏览商品或购买商品等的行为，因此这里就不对此部分的数据进行处理。
注：如果存在重复值，则需求是删除重复值且相同的仅保留一条记录，参考
SQL 删除重复数据，只保留一行

4、缺失值处理

通过查询各列数据中是否有缺失值。

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

由此可知，无缺失值。

5、一致性处理

由于 timestamp 列为时间戳格式，我们需要使用 from_unixtime() 函数将数据转换为日期格式，为了方便后期的分析，可以添加一列数据，将时间戳格式转换为小时列。

# 添加两个字段
ALTER TABLE userbehavior ADD date VARCHAR(10);
ALTER TABLE userbehavior ADD hour VARCHAR(4);

# 将时间戳转换为日期格式
UPDATE userbehavior SET date=FROM_UNIXTIME(timestamp,'%Y-%m-%d');

# 将时间戳转换为小时格式
UPDATE userbehavior SET hour=SUBSTR(FROM_UNIXTIME(timestamp,'%Y-%m-%d-%H'),-2);

# 删除timestamp字段
ALTER TABLE userbehavior DROP timestamp;

6、异常值处理

通过按日期进行排序，可以发现，数据集中存在很多异常时间，故需要将这部分时间剔除掉。

SELECT date
FROM userbehavior
WHERE date IS NOT NULL
ORDER BY date;

因为数据是 2017 年 11 月 25 日- 2017 年 12 月 3 日之间的数据，所以我们需要将异常时间的数据清洗掉。这里采用的是新创建一个 user 表，将查询得到的数据插入新表中。

CREATE TABLE user
SELECT * FROM userbehavior
WHERE date BETWEEN '2017-11-25' AND '2017-12-03'

六、分析过程

1、纵向分析之流量情况

1）整体流量情况——访客数UV、访问量PV、平均访问深度（即平均访问量PV/UV）：

SELECT a.pv AS '总访问量', a.uv AS '总访问用户数', a.pv/a.uv AS '平均访问深度'
FROM
    (SELECT COUNT(behavior) AS 'pv',
            (SELECT COUNT(DISTINCT user_id) FROM user) AS 'uv'
    FROM user
    WHERE behavior='pv') AS a

从整体数据来看，该 APP 在这段时间内的总访问次数为 893907 次，总访问用户数为 9710人，平均访问深度为92，即每个人一周内大概会访问92个页面。

2）局部流量情况——每天PV和每天UV、每小时PV和每小时UV

• 以天为单位展示商品的浏览量和用户数的变化趋势

SELECT date,
       SUM(IF(behavior='pv',1,0)) AS 'PV',
       COUNT(DISTINCT user_id) AS 'UV'
FROM user
GROUP BY date
ORDER BY date;

从上图可以看出，PV、UV 这两个指标的变化趋势几乎保持一致。在 2017 年 11 月 25 日-12 月 1 日之间，都是较为稳定的，而在 2017 年 12 月 2 日这天开始，PV 和 UV 都不断增加。可以就此提出假设：

假设 1：非工作日的 PV 和 UV 会得到大幅度提升；验证假设：通过查看日历发现，11 月 25 日和 11 月 26 日也是非工作日，但 PV 和 UV 并没有得到大幅度的提升，所以假设 1 不成立。 假设 2：可能是大促的影响；验证假设：12 月 2 日和 12 月 3 日距离“双十二”活动较近，可能是双 12 活动的预热阶段，吸引了一部分新老用户。对于一些周期性比较强的产品，需要先确定产品的周期。

• 以小时为单位展示商品的浏览量和用户数的变化趋势

SELECT hour,
       SUM(IF(behavior='pv',1,0)) AS 'PV',
       COUNT(DISTINCT user_id) AS 'UV'
FROM `user`