淘宝APP用户行为分析

2014年是阿里巴巴集团并购业务快速发展的一年，如2014年11月11日移动终端的总商品数量(GMV)占GMV总量的42.6%。与PC时代相比，移动终端上的网络接入可以随时随地进行。此外，它们还具有丰富的背景数据，如用户的位置信息、访问时间的规律性等。下面主要是对用户行为数据的分析。

一、数据分析的问题和思路

1）分析思路

数据分析框架是通过不同观察的角度（维度）去衡量事物发展的程度（指标）。相同的行业不同的目标分析，需要借助的维度和指标也不同。本文主要通过平台整体运营指标、客户异常值、用户行为和RFM模型去掌握客户行为，并挖掘潜在客户潜在价值，为运营提供方向。（详见下图）。

2）分析指标

本文主要主要涉及到指标

1.浏览量：PV（page view），用户访问页面的总数，用户每访问一个页面就算一个访问量，同一页面刷新多次也算一个访问量,日均PV和月均PV。

2. 独立访客：UV（unique visitor）即一台电脑为一个独立的访问人数。一般以天为单位来统计24小时内的UV总数，一天内重复访问的只算一次，日均UV和月均VU。

3.新访客数：指客户首次访问页面的用户数。

4.回访客数：再次访问的用户数。

5.转化指标：电商核心分析指标之一。转化是指用户点击、收藏、加购物车、购买等这些动作。那么转化率就是进行以上动作的访问量除以总访问量的比率。

6.RFM模型：根据客户活跃程度和交易金额贡献（本文采用交易笔数），进行客户价值细分的一种方法。

R-Recency：最近一次购买时间；

F-Frequency：消费频率；

M- Monetory：消费金额

7.RFM模型的实现步骤：

7.1、计算RFM的各项值，及RFM最大和最小值

7.2、0-1标准化RFM值

7.3 RFM值打分

7.4 求RFM各平均分，并将各分与平均分比较，大于为↑,小于或等于为↓，对客户进行分类

3）提出问题

1.淘宝平台整体活跃量及转化情况。

2. 47周新增客户量留存情况及2012-12-12客户购买率增加的原因？

3.平台客户活跃规律（日时数或者周）。

4.对已在平台购买的客户进行分类，挖掘潜出潜在客户。

二、理解数据

1）数据集描述

三、数据清洗

经过重命名、删除重复值、异常值清洗整理后获得1048575条记录。

①选择子集列，1048575×6。

创建淘宝APP数据库，并将文件通过导入向导导入Navicat Premium中。

②缺失值处理

item_category 列地理位置的数据大多是空值NULL，且位置信息被加密处理了，难以研究。所以后面不做和位置有关的分析。

-- 1.删除位置列  ALTERTABLEuserdropCOLUMNuser_geohash;

③数据一致化处理

由于time字段的时间包含（年-月-日）和小时，为了方便分析，将该字段分成2个字段，一个日期列和一个小时列。

-- 2.添加日期列

ALTER TABLE user ADD COLUMN date VARCHAR(20) NULL;

-- 3.将日期列赋值对应日期

UPDATE user set date = SUBSTRING_INDEX(time,' ',1);

-- 4.提取时间列的时间

UPDATE user set time = SUBSTRING_INDEX(time,' ',-1);

-- 5.修改日期列的数据类型

ALTER table user MODIFY date date;

由于 behavior_type 列的四种行为类型分别用 1，2，3，4 表示点击、收藏、加购物车、支付四种行为，为了方便查看将数字转换为 ‘pv'、’fav‘，’cat'，‘buy' 。

-- 6.转换用户行为列表示方式

UPDATE user set behavior_type = replace(behavior_type,'1','pv');

UPDATE user set behavior_type = replace(behavior_type,'2','fav');

UPDATE user set behavior_type = replace(behavior_type,'3','cat');

UPDATE user set behavior_type = replace(behavior_type,'4','buy');

④ 创建用户行为视图，以便于后期调用

-- 7.创建用户行为视图

CREATE VIEW user_behavior AS 

SELECT date,user_id,

COUNT(behavior_type) as '总访问量',

SUM(case when behavior_type ='pv' then 1 else 0 end) as '点击数',

SUM(case when behavior_type ='fav' then 1 else 0 end) as '收藏数',

SUM(case when behavior_type ='cat' then 1 else 0 end) as '加购物车数',

SUM(case when behavior_type ='buy' then 1 else 0 end) as '购买数'

FROM user

GROUP BY date,user_id

order by date asc,user_id;

四、业务分析

4.1 淘宝平台运营分析
-- 1.平台运营指标分析

SELECT date,sum(总访问量) as '日总访问量',

sum(点击数) as '日点击数',

sum(收藏数) as '日收藏数',

sum(加购物车数) as '日加购数',

sum(购买数) as '日购买数',

COUNT(user_id) as '日客户数',

SUM(case when 购买数 >= 1 then 1 else 0 end) as '日购买客户数'

from user_behavior

GROUP BY date

ORDER BY date ;

从上图中可以看出，

平台在这一个月内无论是日PV、日UV、日收藏数、日加购物车和日购买数变化趋势都比较稳定，只有12月12日出现异常波动，根据生活常识这是因为12月12日平台搞了活动导致各值升高。但是到底是由于新客户增加还是老客户回访产生的需要进一步分析。

根据AARRR模型，发现从客户点击→加购物车→收藏→ 购买四个环节，转化率递减。从用户点击最后真正购买的只有1%，购买转化率比较低，后期还有很大的增长空间。而在客户点击→加购物车节点客户转化率为3%，从收藏→购买这个环节转化率为48.8%，应该重点提高这两个关键点客户转化率。

4.2 用户异常值分析

4.2.1 前期新增客户质量

-- 0.每日新增客户分析

SELECT c.date,c.客户总数,d.新客户 from

      (SELECT date,count(DISTINCT user_id) as '客户总数'

      from user_behavior

      GROUP BY date) as c

join

      (SELECT first_day,count(DISTINCT a.user_id) as '新客户'

      from user_behavior as a join

          (SELECT user_id,MIN(date) as 'first_day'

            from user_behavior

            group by user_id )as b

on a.user_id = b.user_id

group by first_day) as d

on c.date = d.first_day;

从上图可得出：观察新增客户指标发现双十二期间当日或者近几天新客户不高，因此可以确定双十二流量高是老客户复访问导致的。此外2014-11-19日到2014-11-23日新增客户远远高于后面的，这说明在这个区间段可能是搞了推广或者平台获取客户能力在萎缩。分析其中原因还需要采集2014-11-18之前或2014-12-19之后的数据来验证。其中2014-11-18日作为客户期初点，客户数量点大后期分析也不放入在内。

观察流失客户指标发现2014-11-19日到2014-11-23日客户流失数很大，那流失率是不是大，需要进一步分析。

-- 4.创建1客户留存视图

create view user_list

as

SELECT first_day,a.date,count(*) as '日客户数'

from user_behavior as a join

(SELECT user_id,MIN(date) as 'first_day'

from user_behavior

group by user_id )as b

on a.user_id = b.user_id

GROUP BY first_day,a.date;

-- 5. 统计11.18日-11.23日客户留存情况

SELECT first_day,date,日客户数

FROM user_list;

where first_day <='2014-11-22';

从图中可以看出，

对2014-11-19日到2014-11-23新增客户做次日、7日、25日等留存率分析，平均留存率：平均次留46%；7日留存46%；25日留存43%（数据量不够做30日留存）。电商行业的参考标准：次留35-40%；7日留存15-20%；30日留存5-8%。这个区间段的新增客户留存率稳定，说明这批客户对平台的粘度比较好。

4.2.2双十二购买量异常分析

-- 1.每日购买率的计算方式

SELECT date,count(*) as '总客户数',

sum(case when 购买数 >= 1 then 1 else 0 end) as '购买客户数',

CONCAT(round(sum(case when 购买数 >= 1 then 1 else 0 end)*100/count(*),2),'%') as '购买率'

from user_behavior

GROUP BY date;

-- 2.统计12.12日够买率的计算方式

SELECT first_day,count(*) as '总客户数',

sum(case when 购买数 >= 1 then 1 else 0 end) as '购买客户数',

CONCAT(round(sum(case when 购买数 >= 1 then 1 else 0 end)*100/count(*),2),'%') as '购买率'

from user_behavior as a join

(SELECT user_id,MIN(date) as 'first_day'

from user_behavior

group by user_id )as b

on a.user_id = b.user_id

where a.date = '2014-12-12'

group by first_day ;

从图中可以看出，

根据每日客户购买率分析，发现双十二当日购买率为16.4%,远远高于平均购买率5.95%，而其他日期购买率在平均购买率上下波动，变化不大。

根据双十二当日不同批次客户购买率分析，发现除了12-7日、12-8日和12-4日三天的新增客户购买率小于平均购买率，来自于其他的日期的新增客户购买率都高于平均购买率，这说明双十二活动基本把客户的购买欲调动起来了。

而跟双十二当日购买率对比， 11-18日、12-6日和12-11日购买率高于它，同时查看购买客户批次占比，发现11-18日客户占了66%，而12-6日和12-11日占比不到1%，这充分说明双十二活动购买率高主要批次客户为11-18日。

4.3用户行为指标

-- 1.日时客户行为分析

SELECT time,

COUNT(behavior_type) as '总访问量',

SUM(case when behavior_type ='pv' then 1 else 0 end) as '点击数',

SUM(case when behavior_type ='fav' then 1 else 0 end) as '收藏数',

SUM(case when behavior_type ='cat' then 1 else 0 end) as '加购物车数',

SUM(case when behavior_type ='buy' then 1 else 0 end) as '购买数'

FROM user

GROUP BY time

order by time;

-- 2.周期客户行为分析

SELECT week(date,1) as '周数',DATE_FORMAT(date,'%W') AS weeks, SUM(总访问量),SUM(购买数)

FROM user_behavior

GROUP BY date

ORDER BY DATE_FORMAT(date,'%w');

从图中可以看出，

从前一日的23点到当日5点用户活跃度快速降低，降到一天中的活跃量最低值，6点到10点用户活跃度快速上升，10点到18点用户活跃度较平稳，17点到22点用户活跃度快速上升，达到一天中的最高值。与用户产生互动的运营行为可以参考用户比较活跃的时间段。

由于第五周（即51周）数据不全，不考虑到此次数据分析中。每周用户活跃度较稳定，每周五活跃度会有小幅降低，但是周末会慢慢回升。其中第50周周五（12-12日）用户活跃度突增是由双十二大促活动引起。

4.4用户行为指标

-- 1.RFM创建视图

create view rfm

AS

select user_id,

DATEDIFF('2014-12-19',max(date)) AS recent,

count(*) as frequency,

sum(购买数) as Monetory

from user_behavior

where 购买数 >= 1

GROUP BY user_id;

-- 2.创建FRM_score 视图

系数=（数值-min )/(max -min )

(rencent max:min(31:1);frequency(27:1);Monetory(82:1))

create view rfm_score

as

SELECT user_id,

      round((recent -1)/30,1) as r_value,

(case when round((recent -1)/30,1)> 0.8 then 1

      when round((recent -1)/30,1)> 0.6 then 2

when round((recent -1)/30,1)> 0.4 then 3

when round((recent -1)/30,1)> 0.2 then 4

else 5 end) as r_score,

      round((frequency -1)/26,1) as f_value,

(case when round((frequency -1)/26,1)> 0.8 then 5

      when round((frequency -1)/26,1)> 0.6 then 4

when round((frequency -1)/26,1)> 0.4 then 3

when round((frequency -1)/26,1)> 0.2 then 2

else 1 end) as f_score,

      round((Monetory -1)/6,2) as m_value,

(case when round((Monetory -1)/81,2)> 0.8 then 5

      when round((Monetory -1)/81,2)> 0.6 then 4

when round((Monetory -1)/81,2)> 0.4 then 3

when round((Monetory -1)/81,2)> 0.2 then 2

else 1 end) as m_score

from RFM ;

-- 3.RFM模型客户分析

select 

    ( case when r_score > 3.88 and f_score > 1.01 and m_score >1.00 then "重要价值客户"

      when r_score <= 3.88 and f_score > 1.01 and m_score >1.00 then "重要保持客户"

when r_score > 3.88 and f_score <= 1.01 and m_score >1.00 then "重要发展客户"

when r_score <= 3.88 and f_score <= 1.01 and m_score >1.00 then "重要挽留客户"

when r_score > 3.88 and f_score > 1.01 and m_score <= 1.00 then "潜力客户"

      when r_score <= 3.88 and f_score > 1.01 and m_score <= 1.00 then "一般保持客户"

when r_score > 3.88 and f_score <= 1.01 and m_score <= 1.00 then "新客户"

else "流失客户" end ) as user_type,count(user_id) as user_num

from rfm_score

group by

( case when r_score > 3.88 and f_score > 1.01 and m_score >1.04 then "重要价值客户"

      when r_score <= 3.88 and f_score > 1.01 and m_score >1.00 then "重要保持客户"

when r_score > 3.88 and f_score <= 1.01 and m_score >1.00 then "重要发展客户"

when r_score <= 3.88 and f_score <= 1.01 and m_score >1.00 then "重要挽留客户"

when r_score > 3.88 and f_score > 1.01 and m_score <= 1.00 then "潜力客户"

      when r_score <= 3.88 and f_score > 1.01 and m_score <= 1.00 then "一般保持客户"

when r_score > 3.88 and f_score <= 1.01 and m_score <= 1.00 then "新客户"

else "流失客户" end )

order by ( case when r_score > 3.88 and f_score > 1.01 and m_score >1.00 then "重要价值客户"

      when r_score <= 3.88 and f_score > 1.01 and m_score >1.00 then "重要保持客户"

when r_score > 3.88 and f_score <= 1.01 and m_score >1.00 then "重要发展客户"

when r_score <= 3.88 and f_score <= 1.01 and m_score >1.00 then "重要挽留客户"

when r_score > 3.88 and f_score > 1.01 and m_score <= 1.00 then "潜力客户"

      when r_score <= 3.88 and f_score > 1.01 and m_score <= 1.00 then "一般保持客户"

when r_score > 3.88 and f_score <= 1.01 and m_score <= 1.00 then "新客户"

else "流失客户" end );

从表中可以得出

根据RFM模型分析发现，平台产生过购买的客户流失客户占到三分之一，而新客户接近三分之二，潜力客户、重要价值客户和发展客户只是零星的几个，这充分说明平台客户质量不佳，有待挖掘。

根据平台前面部分批次客户留存率分析（访问量角度），发现客户留存率达到40%以上，这说明平台客户粘度好，但是这些客户没有产生价值，需要提高客户购买率。

五、总结和建议

根据AARRR模型，发现从用户点击→购买的转化率为1%，而在客户点击→加购物车节点客户转化率为3%，从收藏→购买这个环节转化率为48.8%，应该重点提高这两个关键点的客户转化率低。

淘宝平台在数据集区间内无论是日PV、日UV、日收藏数、日加购物车和日购买数变化趋势都比较稳定，在12月12日各值几乎是平时的2倍。经过新增客户和购买率分析，发现为非当日新增客户引起PV和UV值增大，而是老客户购买率的提高，尤其是11-18日的客户。按正常逻辑双十二会有很多新客户，或许是数据经过脱敏的原因，如需验证需要2014-11-17前的更多数据来分析。

数据集中每日新增客户从11-19日到23日逐渐递减，且远远高于后面日期，这说明在这个区间段可能是搞了推广或者平台获取客户能力在萎缩。分析其中原因还需要采集时间更长的时间区段来验证。且该区段新增客户平均次留存率、7日留存率和25日留存率（数据量不够做30日留存）高于电商行业标准，说明这批客户对平台的粘度较好。

大部分用户的主要活跃时间在10点到22点，在20点到23点达到一天的顶峰。每周五的活跃度有所下降，但周末开始回升。与用户产生互动的运营行为可以参考用户比较活跃的时间段。其中第50周周五（12-12日）用户活跃度突增是由双十二大促活动引起。

根据RFM模型分析和部分批次客户留存率分析，平台重要的客户质量不佳（即能产生价值的客户少），但是平台粘度好，急需要提高客户的转化率。