淘宝用户行为数据分析（MySQL+Excel）

这是第一次数据分析的实践，选择了比较经典的淘宝用户行为数据集，考虑到这个数据集比较大，能够了解一下各种程序对几个G的数据文件的运行能力。

本次分析主要分五个步骤进行：

理解数据 →提出问题 →数据清洗 →建模分析 →总结建议

由于是先拿到数据集，再进行分析，所以此次分析的步骤为先理解数据，再思考通过这样一份数据的分析能够获得的对业务有用的信息。

一、理解数据

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1

本数据集包含了2017年11月25日（周六）至2017年12月3日（周日）之间，有行为的约一百万随机用户的所有行为（行为包括点击、购买、加购、喜欢）。数据集的每一行表示一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和时间戳组成，每一列的详细描述如下：

列名称	说明
user_id	整数类型，序列化后的用户ID
item_id	整数类型，序列化后的商品ID
category_id	整数类型，序列化后的商品所属类目ID
behavior	字符串，枚举类型，用户行为类型，包括('pv','buy','cart','fav')
Timestamp	行为发生的时间戳

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

行为类型	说明
pv	商品详情页pv，等价于点击
buy	商品购买
cart	将商品加入购物车
fav	收藏商品

数据预览

观察数据，这份数据集的分析报告对象应该是淘宝的管理者，对于这份数据的分析主要应该站在一个淘宝平台数据运营的角度，思考这份数据对于业务的提升有什么作用，深度挖掘各数据指标背后所隐藏的有用信息。

二、提出问题

1、数据集期间平台的各项总体运营指标（流量、转化率等）如何？
2、不同时间周期下的用户行为有什么规律？如何利用这些规律？
3、基于AARRR模型分析每个环节的用户运营情况，提出改进建议。
4、分析每个用户独特的行为偏好，考虑能够提供怎样的个性化的服务？
5、浏览、收藏、加购物车、下单最多的商品分别是哪些？相互之间有什么关联？
6、哪些商品类别的销量较高？有什么特征？

三、数据清洗

缩减数据集——数据抽样
由于原始数据1亿多条记录过于庞大，直接导入MySQL数据库太慢（使用Navicat Premium），本文选择按照固定间距为30按顺序抽取user_id中的部分用户的行为数据，最终生成的数据表记录为3308461条。这一过程使用到了Python，顺便添加了列标题，实现代码如下：

import pandas as pd

file_path = "/Users/***/Desktop/UserBehavior.csv"
data = pd.read_csv(file_path,names = ['user_id','item_id','category_id','behavior','Timestamp'])

lst = list(range(1,1000000,30))

data2 = data[data['user_id'].isin(lst)]
data2.to_csv("/Users/***/Desktop/tb_user.csv",index=False)

导入数据库

先在数据库中建好对应表

导入数据——选择数据文件

由于数据表中已有列标题，将栏位名行改为1，第一行改为2。

导入成功，用时30s。
选取子集

只有5个字段，每个字段都具有分析意义，无需剔除。
重复值处理

本数据集中允许存在每个字段都完全相同的两条记录，理解为某一用户在某一时间对数量为2的同一个商品进行的操作（例如在一单中购买两个数量的某商品），因此无需删除重复值。
缺失值处理

数据导入时设置了NOT NULL，杜绝缺失值出现。
一致化处理
- 添加date_time字段，将原Timestamp列时间戳转换为日期+时间格式
- 添加date字段，提取date_time中的日期部分
- 添加hour字段，提取date_time中的小时部分

ALTER TABLE tb_user ADD COLUMN date_time datetime;
UPDATE tb_user SET date_time = FROM_UNIXTIME(`Timestamp`);

ALTER TABLE tb_user ADD COLUMN date date;
UPDATE tb_user SET date = DATE(date_time);

ALTER TABLE tb_user ADD COLUMN hour int;
UPDATE tb_user SET hour = EXTRACT(HOUR FROM date_time);

（这段代码的执行速度比较慢，300万行的数据量会中断连接1次，需将UPDATE语句重复执行两次）

异常值处理

6.1 在表界面，点击字段名称后面的小箭头，选择升序和降序排序，查看user_id、item_id、category_id列无异常值。

6.2 date_time列按升序和降序排列后发现都有一些异常值。

异常数据无法确认真实时间，对超出本数据集时间范围2017-11-25日至2017-12-03日之外的数据，全部作删除处理，一共有2065条记录。
```
DELETE FROM tb_user
WHERE date > '2017-12-03'
OR date < '2017-11-25';
```
6.3 检查behavior列，无异常值
```
SELECT * FROM tb_user
WHERE behavior
NOT IN ('pv','buy','cart','fav');
```

四、建模分析

1. 总体运营指标

总体运营指标可以对一段时间内平台用户使用的整体情况有一个宏观的了解，通过与不同时期数据的纵向对比，可以得出这段时期内的整体运营情况好坏。通过与同行业竞争者数据的横向对比，可以发现自身平台的优势与不足。

1 流量类指标
- 独立访客总数uv
- 页面总访问数pv
- 人均页面访问数pv/uv

SELECT COUNT(DISTINCT user_id) as uv
    ,(SELECT COUNT(behavior) FROM tb_user WHERE behavior = 'pv') as pv
    ,(SELECT COUNT(behavior) FROM tb_user WHERE behavior = 'pv')/COUNT(DISTINCT user_id) as 'pv/uv'
FROM tb_user;

2 用户行为转化率——漏斗模型

-- 各种行为发生次数
SELECT behavior ,COUNT(behavior) as 次数
FROM tb_user
GROUP BY behavior
ORDER BY behavior DESC;

用户在网上购物过程中收藏和加入购物车两个环节没有先后之分，所以可以将这两个环节合并为一个环节。

可以看到，所有用户从浏览到收藏+购物车的转化率是9.39%，从收藏+购物车到购买的转化率是23.77%，最终从浏览到购买的访问下单转化率只有2.23%，说明用户花费了大量的时间浏览寻找合适的产品，这就需要优化平台的筛选功能，使用户更容易找到合适的产品，提升使用体验。

3 跳失率
跳失率 = 只点击1次浏览的用户数量/独立访客总数

-- 只点击1次浏览用户
SELECT t1.user_id ,behavior
FROM tb_user t1
INNER JOIN(
    SELECT user_id ,COUNT(behavior)
    FROM tb_user
    GROUP BY user_id
    HAVING COUNT(behavior) = 1) as t2
ON t1.user_id = t2.user_id
WHERE behavior = 'pv';

如图，查询结果为空，跳失率是0，所有的用户在第一次浏览后又发生了其他的行为，说明淘宝拥有足够的吸引力让用户停留在界面中。

2. 基于AARRR模型的用户运营分析

1 用户获取
本数据集中的用户全部是已激活使用的用户，此处用新增访客数作为获客能力的分析。淘宝本身用户基数已经比较大，由于数据集期间较短，新增访客可能主要是一段时间未使用的用户，也会有少数新注册用户，此处统一作为新用户看待。

-- 日新增访客数
SELECT 日期 ,COUNT(user_id) as 新增访客数
FROM (SELECT user_id ,MIN(date) as 日期
      FROM tb_user
      GROUP BY user_id) as s1
GROUP BY 日期;

2017年11月25日是第一天，新增访客数没有参考意义。从第二天开始分析，后面几天新增访客数快速减少，12月2日和3日甚至零新增，平台获客能力大大下降。最后两天的访客数大幅增加，说明主要是前几日流失的客户受活动吸引，又重新回来消费了，活动完全没有吸引到新用户。后期需要加强平台的渠道曝光量和活动宣传范围，设置长期未使用用户的专属优惠券或新客专享优惠以吸引新用户。

2 提高活跃度
1. 2.1 不同日期的用户活跃规律

SELECT `date`
       ,COUNT(DISTINCT user_id) as 访客数
       ,SUM(CASE WHEN behavior = 'pv' THEN 1 ELSE 0 END) as 点击
       ,SUM(CASE WHEN behavior = 'fav' THEN 1 ELSE 0 END) as 收藏
       ,SUM(CASE WHEN behavior = 'cart' THEN 1 ELSE 0 END) as 加购物车
       ,SUM(CASE WHEN behavior = 'buy' THEN 1 ELSE 0 END) as 购买
FROM tb_user
GROUP BY `date`;

从图中可以得出如下结论：
1、日点击量和日访客数的趋势有高度相关性；
2、从2017年11月25日~2017年12月1日的一周内，各项指标都没有明显的波动，周末与工作日没有太大区别。点击、收藏、加购物车次数在周一至周四略低于周五至周日，购买次数则相反，说明用户在空闲时间充足的时候更喜欢浏览和挑选，在工作期间则更有下单的冲动；
3、同样是周末，2017年12月2日~2017年12月3日的各项数据比起上个周末有了明显的增加，可能是在这两天做了活动；
4、日人均页面访问量基本持平，在13±0.5之间波动，说明周末和平台活动都对人均页面访问量没有显著的影响，要提高流量数据主要还是在于增加访客数。
1. 2.2 一天内不同时间的用户行为分析

SELECT `hour`
       ,SUM(CASE WHEN behavior = 'pv' THEN 1 ELSE 0 END) as 点击
       ,SUM(CASE WHEN behavior = 'fav' THEN 1 ELSE 0 END) as 收藏
       ,SUM(CASE WHEN behavior = 'cart' THEN 1 ELSE 0 END) as 加购物车
       ,SUM(CASE WHEN behavior = 'buy' THEN 1 ELSE 0 END) as 购买
FROM tb_user
GROUP BY `hour`;

可以看出，用户每天0点~8点睡眠时间活跃度较低，白天9点~18点都保持在一个较为平稳的中等活跃度水平，从19点开始，活跃度迅速上升，21~22点达到顶峰，之后回落。说明用户喜欢在晚上下班至睡觉前的时间段内逛淘宝，在这段时间推送一些短信或系统通知可以更容易地吸引用户进入淘宝使用。
此外，用户购买次数在白天和傍晚差不多，说明人们多数会在晚上逛淘宝挑选商品，在白天下单。可以将一些下单优惠（如淘金币抵扣），或用户收藏和购物车内商品的相关通知放在白天推送，提升用户下单转化率。还可以建议商家把一天的发货时间设在18点以后，以减少用户等待发货的时间，提升用户使用体验度。

3 用户留存
计算用户留存率较为复杂，分为四个步骤：
第一步：获取每个用户第一次使用的日期
第二步：计算每个用户之后每次使用与第一次使用日期的间隔天数
第三步：汇总每日新增用户不同间隔天数的留存人数
第四步：计算每日留存率

SELECT firstday
      ,day0 as 新增用户
      ,CONCAT(ROUND(day1/day0*100,2),'%') as 'day1_%'
      ,CONCAT(ROUND(day2/day0*100,2),'%') as 'day2_%'
      ,CONCAT(ROUND(day3/day0*100,2),'%') as 'day3_%'
      ,CONCAT(ROUND(day4/day0*100,2),'%') as 'day4_%'
      ,CONCAT(ROUND(day5/day0*100,2),'%') as 'day5_%'
      ,CONCAT(ROUND(day6/day0*100,2),'%') as 'day6_%'
      ,CONCAT(ROUND(day7/day0*100,2),'%') as 'day7_%'
      ,CONCAT(ROUND(day8/day0*100,2),'%') as 'day8_%'
FROM(
SELECT firstday
      ,SUM(CASE WHEN days_diff = 0 THEN 1 ELSE 0 END) as day0
      ,SUM(CASE WHEN days_diff = 1 THEN 1 ELSE 0 END) as day1
      ,SUM(CASE WHEN days_diff = 2 THEN 1 ELSE 0 END) as day2
      ,SUM(CASE WHEN days_diff = 3 THEN 1 ELSE 0 END) as day3
      ,SUM(CASE WHEN days_diff = 4 THEN 1 ELSE 0 END) as day4
      ,SUM(CASE WHEN days_diff = 5 THEN 1 ELSE 0 END) as day5
      ,SUM(CASE WHEN days_diff = 6 THEN 1 ELSE 0 END) as day6
      ,SUM(CASE WHEN days_diff = 7 THEN 1 ELSE 0 END) as day7
      ,SUM(CASE WHEN days_diff = 8 THEN 1 ELSE 0 END) as day8
FROM(
SELECT t1.user_id ,t1.date ,t2.firstday ,DATEDIFF(t1.date,t2.firstday) as days_diff
FROM tb_user as t1
INNER JOIN (SELECT user_id ,MIN(`date`) as firstday
            FROM tb_user
            GROUP BY user_id) as t2
ON t1.user_id = t2.user_id
GROUP BY t1.user_id,t1.date
) as t3
GROUP BY firstday
) as t4
GROUP BY firstday;

可以看出，每一期的用户留存率都比较稳定，没有随着时间的推移而衰减，说明留存用户对淘宝的使用粘性非常高；
最后两日没有新增用户，活动吸引的主要是前几日的老用户，两天活动期间的留存率达到了95%以上，有20%~30%是因为活动而回流，说明老用户对于活动的关注度和参与度都非常高。

4 用户收益
1. 4.1 付费用户占比PUR

-- 总消费人数
SELECT COUNT(DISTINCT user_id) as 总消费人数
FROM tb_user
WHERE behavior = 'buy';

付费用户占比 = 总消费人数/独立访客总数 = 22017/32366*100% = 68.03%

使用淘宝的用户中有68%都产生了购买行为，用户付费转化率相当高。
1. 4.2 复购率
  -- 不同消费次数的人数

SELECT buy_times
      ,COUNT(user_id) as 人数
FROM(
     SELECT user_id
         ,COUNT(behavior) as buy_times
     FROM tb_user
     WHERE behavior = 'buy'
     GROUP BY user_id
     ) as t1
GROUP BY buy_times;

消费1次的人数占比 = 7335/22017*100% = 33.32%
复购率 = 1 - 消费1次的人数占比 = 66.68%
由此可知，在有购买行为的用户中，有66.68%的用户会再次发生购买行为，复购率比较高。另外消费次数大部分集中在10次以内，占总消费人数的97.80%，消费1次的人数最多，占总消费人数的1/3。最高消费次数可达53次，需重点关注高消费频率客户的客户价值。

3. 单用户行为分析

-------- 针对每个用户提供个性化服务

1 单用户商品偏好分析
-- 用户浏览商品所属类目排名

SELECT user_id ,category_id ,COUNT(behavior) as view_times
FROM tb_user
WHERE behavior = 'pv'
GROUP BY user_id ,category_id
ORDER BY user_id ,view_times DESC;

用户浏览某个类目下的商品次数较多时，说明他可能是在同一类目下的不同商品之间挑选，此时可以根据该用户浏览商品类目次数的排名，在推荐页面优先推荐浏览次数多的类目下的其他商品。

2 单用户购买偏好
-- 用户收藏或加入购物车了，但是没有购买的商品

SELECT t1.user_id ,t1.item_id
FROM(
    SELECT user_id ,item_id
    FROM tb_user
    WHERE behavior = 'fav'
    OR behavior = 'cart'
    GROUP BY user_id,item_id
    ) as t1
LEFT JOIN(
    SELECT user_id ,item_id
    FROM tb_user
    WHERE behavior = 'buy'
    GROUP BY user_id,item_id
    ) as t2
ON t1.user_id = t2.user_id
AND t1.item_id = t2.item_id
WHERE t2.user_id IS NULL;

用户对收藏或加入购物车的商品购买意愿是比较强烈的，可能因为某些原因还没有成交。针对这些商品有针对性地给用户推出一些优惠活动，可以提高这些用户下单的概率。

4. 商品相关分析

1 不同行为的商品排名分析
-- 商品销量前20（其他排名代码类似）

SELECT item_id ,count(behavior) AS '销售量'
FROM tb_user
WHERE behavior = 'buy'
GROUP BY item_id
ORDER BY 销售量 DESC
LIMIT 20;

销量最高的商品是item_id为3122135的商品，购买次数有37次，排在第二和第三位的商品购买次数均为33次。
在浏览量前20的商品中，只有3个商品销量较高，浏览量前6名的商品都不在销量榜单中，说明这些商品在吸引用户方面做的较好，但并没有很好的转化为实际销量。
收藏数前20的商品也仅有740947和1535294的销量排名较高，但收藏数前几位的商品与浏览量前几名重合度较高，说明浏览量高的商品收藏转化率也较高。
加购数前20的商品中，有5个进入了销量前20，且集中排列在销量第4位至第9位之间，说明销量与加购数的关系更为密切。
总的来说，做的最好的商品是1535294，各项指标都排在靠前的位置，将高浏览量成功地转化为了收藏和加购数，进而转化为销量，可以作为一个典型的例子推广学习。对其他单销量高或单浏览量高的商品群可以进一步分析其特征优势，从而交叉向有另一方面弱势的商家推出对应的增值指导服务。

2 商品购买次数分布
-- 计算不同购买次数的商品数各有多少个

SELECT 购买次数 ,COUNT(item_id) as 商品数
FROM (SELECT item_id ,COUNT(behavior) as 购买次数
      FROM tb_user
      WHERE behavior = 'buy'
      GROUP BY item_id) as buy_times
GROUP BY 购买次数;

只购买1次的商品最多，有42118个，占购买商品总数的82.77%，购买两次的商品有5902个，占比11.60%，说明商品的销售主要是依靠长尾商品的累计效应，而非爆款商品的带动。

3 商品类别偏好
-- 计算不同类别下商品的总销量以及包含的商品数，按销量排序

SELECT t1.category_id
      ,t2.sales
      ,COUNT(distinct t1.item_id) as item_num
FROM tb_user t1
JOIN (SELECT category_id ,COUNT(*) as sales
      FROM tb_user
      WHERE behavior = 'buy'
      GROUP BY category_id) as t2
ON t1.category_id = t2.category_id
GROUP BY category_id
ORDER BY sales DESC;

如图，销量最高的几个商品类别总销量均在1000个左右，且有一个共同特征——所包含的商品数都很多，这也比较好理解，销量多的商品类别受用户关注度高，入驻的商家就会增多，各商家之间的产品相互替代性较高，这就形成了商品销售的长尾现象。在平台活动页面，可以将销量高的商品类别入口放在更显眼的位置，以更好地吸引用户。

五、总结建议

用户从浏览到收藏+购物车的转化率是9.39%，从收藏+购物车到购买的转化率是23.77%，访问下单转化率只有2.23%，说明用户花费了大量的时间浏览寻找合适的产品。需要优化平台的筛选和推荐功能，使用户更容易找到合适的产品，提升使用体验。
基于AARRR模型的分析结论
（1）用户获取：数据集期间每日新增访客数快速减少，平台活动也完全没有吸引到新用户。淘宝本身用户基数已经比较大，获取新注册用户的边际成本太高，要提高访客数应更重视休眠用户的激活。后期需要加强平台的渠道曝光量和活动宣传范围，设置长期休眠用户的激活优惠券或新客专享优惠以吸引新增用户。
（2）提高活跃度：
①用户使用淘宝的频率在工作日与周末的差别不大，周末和平台活动都对日人均页面访问量没有显著的影响，要提高流量数据主要还是在于增加访客数。在周末推出一些优惠活动能显著地提高用户的活跃度。
②在一天之内，用户更喜欢在晚上下班至睡觉前的时间内逛淘宝，在19点之后开始推送一些短信或系统通知可以更容易地吸引客户使用淘宝。白天9点~18点的时间段内则可以多推送一些下单优惠，或用户收藏和购物车内商品的相关通知，提升用户下单转化率。
（3）用户留存：淘宝用户的留存率比较高且比较稳定，留存用户使用粘性非常强，通过适当地推出活动能够达到95%以上的留存率。
（4）用户收益：用户付费转化率可达到68%，付费用户中有复购行为的用户又占到了66.68%，说明用户整体的消费能力相当强。针对消费频率较高的客户，需重点关注其客户价值，了解用户的偏好和需求，提供诸如会员服务等更加个性化的服务以维持客户。
（5）传播推荐：从新增访客数来看，淘宝用户的自传播能力较差，可以通过设置联合使用优惠券或推荐新客奖励，提高用户传播率。
用户个性化服务方面，根据用户浏览商品类目次数的排名，可以在推荐页面提供更加个性化的推荐服务，优先推荐浏览次数多的类目下的其他商品。对于用户收藏或加入了购物车，还没有购买的商品，可以向用户推送一些商品的优惠信息，刺激用户消费，提高下单转化率。
浏览量排名靠前的商品都不在销量前20的商品中，仅更多地转化为了收藏和加购数，说明这些商品在吸引用户方面做的较好，但并没有很好的转化为实际销量。平台可以对单销量高或单浏览量高的商品群进一步分析其特征优势，从而交叉向有另一方面弱势的商家推出对应的增值指导服务。
在用户购买的商品中，只购买1次的商品最多，占购买商品总数的82.77%，说明商品的销售主要是依靠长尾商品的累计效应，而非爆款商品的带动。
销量高的商品类别所包含的商品数也较多，因为销量多的商品类别受用户关注度高，入驻的商家就会增多，各商家之间的产品相互替代性较高，这就形成了商品销售的长尾现象。在平台活动页面，可以将销量高的商品类别入口放在更显眼的位置，以更好地吸引用户。

收获

1、体验了一次比较完整的数据分析过程。巩固了SQL和Python代码知识、Excel作图能力。尤其是数据清洗过程，当初觉得学的代码还比较扎实，但在后面学习新知识的过程中还是会慢慢生疏，需要多实践几次才能印象更深刻。

2、明确分析目的、选对分析角度非常重要。如本文中数据集为淘宝全平台的用户数据，则应该站在淘宝平台运营或同行业竞争对手的角度，选择分析方向，而不是站在某一淘宝商家的角度，导致分析结论一会是用于平台的，一会是用于商家的，过于混乱。

3、指标结合业务的深入理解很重要。在刚开始无从下手的时候会先了解一些行业常用的分析指标体系，看起来好像很简单，但在分析时一定要对每个指标和指标之间的关联有正确和深刻的理解，才能够选对合适的分析指标，获得正确的分析结果。还好对电商购物都是比较熟悉的，能够结合自己的经验加入一些思考，上面肯定还有很多理解有偏差的地方，以后有了新的收获会重新修改。

4、写文章的过程中一定要保持思路清晰，对筛选出来的一堆指标要做好恰当的分类、排序，才能让分析的目标更加明确，让文章看起来有逻辑性。

淘宝用户行为数据分析（MySQL+Excel）

本次分析主要分五个步骤进行：

理解数据 →提出问题 →数据清洗 →建模分析 →总结建议

一、理解数据

二、提出问题

三、数据清洗

导入数据库

选取子集

重复值处理

缺失值处理

一致化处理

异常值处理