案例：淘宝用户行为数据分析（SQL实现）

一、项目介绍

本文针对淘宝APP的运营数据，使用阿里云天池上公开的数据集，以行业常见指标对用户行为进行分析，主要工具为MySQL，涉及分组汇总，引用变量，视图，关联查询等内容，可视化工具为excel和python。

分析思路

1. 用户购物情况整体分析
   利用PV、UV指标，计算总访问量、平均访问量、跳失率等情况
2. 用户行为转化漏斗分析
   根据商品的点击、收藏、加购、购买频率，利用漏斗模型确定各个环节流失率
3. 用户行为时间维度分析
   根据用户行为在一月、一周和一天中的时间活跃时段分布，研究用户在不同时间尺度下的行为规律和活跃规律
4. 用户RFM模型分析
   基于RFM模型分析用户价值度，并对不同价值用户进行精准化管理

二、数据处理

1.数据来源及数据说明

阿里云天池（链接）: https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1.
数据说明：
该数据包含了1亿多条数据，数据为淘宝APP在2014年11月18日至2014年12月18日的用户行为数据，共计7列字段：
user_id：用户ID（脱敏）
item_id：商品ID（脱敏）
behavior_type：用户行为类型（包含点击pv、收藏fav、加购物车cart、支付buy四种行为）
user_geohash：用户的地理位置（脱敏）
item_category：商品所属的品类（脱敏）
timestamp: 用户行为发生的时间戳

2.数据清洗

数据清洗包括子集选择、删除重复值、缺失值处理、一致化处理。
1. 子集选择
由于数据集的user_geohash即地理位置字段缺失值众多且没有太大的分析必要，因此决定对该字段删除，选择其他6个字段进行分析；为了存取方便，删除id>100,0000的数据，对前100万条数据进行分析
2. 删除重复值
新增主键id列，没有重复数据。
3. 缺失值处理
在删除地理位置字段后，没有缺失值。
4. 一致化处理
转换时间类型，将timestamp时间戳转化为date日期和hour小时格式。

#将时间戳更换为易读的时间格式
ALTER TABLE taobaouser ADD COLUMN `time` CHAR(10) NULL;
UPDATE taobaouser
SET `time`=FROM_UNIXTIME(`timestamp`,'%y-%m-d');
#在表中添加一个日期列
ALTER TABLE taobaouser ADD DATE VARCHAR(10) AFTER item_category;
#将表中的time分成两列，分别是日期和小时
UPDATE taobaouser SET `date`=SUBSTRING(TIME,1,10);
UPDATE taobaouser SET `hour`=SUBSTRING(TIME,12,2);
ALTER TABLE taobaouser MODIFY `hour` CHAR(2);
#将date列转为时间格式
ALTER TABLE taobaouser MODIFY `date` DATE;

三、分析正文

1. 用户购物情况整体分析

PV(访问量)：即Page View, 具体是指网站的是页面浏览量或者点击量，页面被刷新一次就计算一次。
UV(独立访客)：即Unique Visitor,访问您网站的一台电脑客户端为一个访客。

页面总访问量（PV）：942230
访问用户总数（UV）：8055
日平均页面总访问量（pre_PV）：30395
日平均访问用户数（pre_UV）：260

根据用户行为日活量图，可以看到在双十二活动这天，淘宝页面总访问量和访客用户最多。

统计一天中的各个时段，除了1-7点睡眠时间外，8-16点、23-0点的用户活跃度大致在一个平均水平；而对于页面访问量，19-22点有一个持续上升的过程，21-22点到达一个峰值状态，推测下班之后访问量开始持续增多，晚上睡前访问量最高。

购买率

在所有的用户中，有51.13%的淘宝用户购买了商品，购买率可观。

2. 用户行为转化漏斗分析

1) 获客：每日新增用户情况

此处假设2014年11月18日为APP启用的首天，新增用户为出现第一次购买行为的用户。

如上图所示，2014年11月18日至2014年12月18日期间，每天都有新增用户，新增用户平稳小幅波动，但在12月12日当天有大幅增长，故推出大规模营销活动如双十二购物节这种活动能够显著吸引新用户。

2）留存：新增用户的留存情况

由下表数据可知，次日留存率大多在30%-40%，3日留存率大多在20%-30%，7日留存率基本上还能保持在20%-30%的水平，甚至会有提高。


由此看，周留存率表现很优秀，次日留存率还有提升的空间，可以结合产品设计和新用户转化路径来分析用户的流失原因，通过不断的修改和调整来降低用户流失，专注提升次日留存率。
注：本次研究把2014年11月18日当天的用户作为用户使用的第一天，与实际情况不符，考虑实际情况，淘宝APP的留存已经过了流失期和蒸馏期，进入稳定期，在这样的条件下，留存率达到20%-30%还是很可观的。

3）转化：转化及流失情况

PV/UV:116.97

加购转化率：61.54%
付费转化率：51.15%
注：此处的加购和付费转化率是加购用户数/点击用户数、付费用户数/点击用户数计算而得

跳失率=只点击一次浏览的用户数量/总用户访问量

可以看到用户的跳失率很低，仅为0.0015，几乎可以忽略不计，说明淘宝有足够的吸引力让用户停留。

4）变现：复购分析

复购比：53.01%


整体复购情况可观，但是具体分析不同复购次数对应的用户数量，发现复购5次及以上的用户仅占有购买行为用户数的6.97%，高复购次数的用户很少，商家可以从商品质量、服务质量、物流体验三方面寻找原因，定位所在问题点，重点转化为培养用户的忠诚度，寻求高复购率的突破。

漏斗图分析

可以看到由点击到收藏和加购有64.51%的转化率，由点击收藏加购到购买有18.54%的转化率，夹点出现在收藏加购到购买，该环节较低的转化率不利于提升购买率，增加销售额。
注：此处的转化率计算方式与上面不一样，此处为有了点击接着有收藏和加购行为计算收藏和加购转化率，同理，有了收藏加购继续有购买行为的用户才会计算其购买率，直接购买的不计算在内。

3.用户行为时间维度分析

1) 月时段下用户行为分布

在这一月当中，用户各行为都在平稳小幅波动，但在12月12日当天有大幅增长，双十二营销活动带来了个用户行为的激增。

2) 周时段下用户行为分布

按周统计来看，星期二至星期四用户各行为（点击、收藏、加购）的频率较高，购买量是周五最高，结合月时段下用户行为分布可知，双十二是周五，因此可知双十二的购买量拉高了周五的平均水平。不过结果与大家普遍认知的周末各行为频率更高不同，猜测可能周五晚上或者周末有其他活动安排导致在周二至周四的下班时间用户各行为更活跃。

3) 日时段下用户行为分布

由这张图可以明显看出，从19点开始用户各行为快速增长，在21-22点达到峰值并开始回落，用户在晚上下班之后对于淘宝APP的使用度更高、更集中。

4.用户RFM模型分析

由于不同用户对公司带来的收益差别很大，而且根据二八定律20%的做有价值用户能带来80%的收益，因此需要对用户进行价值评价，找到最有价值的用户群，并针对这部分用户进行差异化的营销。

RFM 模型：
R-Recency（最近一次购买时间），R指用户上一次消费的时间，上一次购物时间距今最近的顾客通常在近期响应营销活动的可能性也最大，对于APP而言，很久没有购物行为可能意味着用户放弃了APP的使用，重新唤起用户也需要更多的成本。
F-Frequency（消费频率），F指用户在某段时间内的购物次数，消费频率越高意味着这部分用户对产品的满意度最高，用户粘性最好，忠诚度也最高。
M-Money（消费金额），M指用户在某段时间内的购物金额，这也是为公司带来价值的最直接体现，而消费金额较高的用户在用户总体中人数较少，却能创造出更多价值，是需要重点争取的对象。
这三个维度互相关联，反映了每个用户的现在价值和潜在价值，将每个维度进行评分，通过计算分数找到最有价值的用户，并对用户进行分类，可以有针对性的不同类型用户采用不同的营销策略。

由于数据缺少M（消费金额）列，暂且通过R（最近一次购买时间）和F（消费频率）的数据对客户价值进行打分。

以R、F两个维度对用户群进行划分，划分标准为：

	R低	R高
F低	重要挽留客户	重要发展用户
F高	重要保持客户	重要价值用户

四种类型客户比例：

价值客户群占比有49.2%，说明淘宝的高价值用户比率还是很高的。

四、结论与建议

本文分析了淘宝APP用户行为数据共100万条，从四个不同角度提出业务问题，使用漏斗模型和RFM模型分析数据给出如下结论和建议。

1.用户购物情况整体分析

由用户各行为整体情况来看，19点—22点是用户活跃高峰期，是电商的黄金时段，是获客、激活、留存、变现和推荐的关键时期。这期间可以优化各种推荐策略，推荐用户喜欢或可能需要的商品，并在一定程度上也可以推荐新品。
在大型的营销活动例如双十二购物节等活动上，用户的各行为活跃度陡然上升，可以在各个换季时节适时推出这种打折、满减等大型营销活动。

2.通过漏斗模型分析用户行为转化

由点击到收藏和加购有64.51%的转化率，这个转化率是可观的，说明淘宝有足够的吸引力。从收藏加购到购买的转化率相对较低，为18.54%，要注意提升用户的购买率。针对这一环节改善购买率的建议有：
① 优化电商平台的搜索匹配度和推荐策略，首页推荐用户喜好收藏的商品；
② 个性化推出营销活动，例如用户限时付款可以享受一定程度的优惠，吸引其下单付款；
③ 客服提醒，贴心提示，询问用户对加购物品的意见以及及时下单可以享受某些优惠。

3.研究用户在不同时间尺度下的行为规律，找到用户在不同时间周期下的活跃规律

用户在周二至周四的下班时间各行为更活跃，说明上班族在日常空闲时间的消费欲望存在很大的挖掘潜力。针对下班晚高峰可以进行各类营销活动，此时使用人数最多，活动容易触达用户，营销活动的形式可以通过促销、拼团等形式进行。

4.通过RFM模型分类用户群，针对不同的用户群制定不同的营销策略

价值客户群是体系中的最具价值的核心付费用户群，需要重点关注，并且活动投放时需谨慎对待，不要引起用户反感；对于发展客户群，其粘性不强但消费时间间隔较短，运营活动可以重点针对这部分用户，提高用户的产品使用频率，通过拼团打折、积分兑换等活动唤起用户注意力；而对于保持客户群这类忠诚度不高而购买能力强的用户，则需要关注他们的购物习惯做精准化营销。

完整代码

导入.csv数据后：

#添加序列号ID
ALTER TABLE taobaouser ADD id INT(8) FIRST;
ALTER TABLE taobaouser MODIFY id INT(8) PRIMARY KEY AUTO_INCREMENT;
#为了方便，只选取前100万行数据，删除表中ID大于100万的数据
DELETE FROM taobaouser WHERE id>1000000;

#更改类型
ALTER TABLE taobaouser MODIFY `user_id` INT(10);
ALTER TABLE taobaouser MODIFY `item_id` INT(10);
ALTER TABLE taobaouser MODIFY `behavior_type` CHAR(5);
ALTER TABLE taobaouser MODIFY `item_category` INT(10);
ALTER TABLE taobaouser ADD COLUMN `time` CHAR(10) NULL;
UPDATE taobaouser
SET `time`=FROM_UNIXTIME(`timestamp`,'%y-%m-d');

#检查表中除了ueser_geohash外是否有缺失值——>无
SELECT * FROM taobaouser
WHERE user_id IS NULL OR item_id IS NULL OR behavior_type IS NULL OR item_category IS NULL OR `time` IS NULL;

#将behavior_type列的1,2,3,4替换为’pv’,’fav’,’cart’,’buy’
UPDATE taobaouser SET behavior_type='pv' WHERE behavior_type='1';
UPDATE taobaouser SET behavior_type='fav' WHERE behavior_type='2';
UPDATE taobaouser SET behavior_type='cart' WHERE behavior_type='3';
UPDATE taobaouser SET behavior_type='buy' WHERE behavior_type='4';

#在表中添加一个日期列
ALTER TABLE taobaouser ADD DATE VARCHAR(10) AFTER item_category;
#将表中的time分成两列，分别是日期和小时
UPDATE taobaouser SET `date`=SUBSTRING(TIME,1,10);
UPDATE taobaouser SET `hour`=SUBSTRING(TIME,12,2);
ALTER TABLE taobaouser MODIFY `hour` CHAR(2);
#将date列转为时间格式
ALTER TABLE taobaouser MODIFY `date` DATE;

#总pv、uv访问量，日均pv、uv访问量
SELECT COUNT(1) AS `pv`,
COUNT(DISTINCT user_id) AS `uv`,
COUNT(1)/(COUNT(DISTINCT user_id)) AS `pv/uv`,
(DATEDIFF(MAX(`date`),MIN(`date`))+1) AS `day`,
ROUND(COUNT(1)/(DATEDIFF(MAX(`date`),MIN(`date`))+1)) AS pre_pv,
ROUND(COUNT(DISTINCT user_id)/(DATEDIFF(MAX(`date`),MIN(`date`))+1)) AS pre_uv
FROM taobaouser
WHERE behavior_type='pv';
#有购买行为的用户数
<