数据分析实战——淘宝用户行为分析_淘宝用户行为分析系统

淘宝用户行为分析

天池实验室数据：UserBehavior.csv
分析工具：SQLyog，Tableau，Mysql

1. 数据介绍

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

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

2. 思路分析

分析背景：通过对2017年11月25日至2017年12月3日之间的用户行为数据分析，优化更加合理的隐式反馈推荐系统，原数据约为1亿条用户行为数据，数据过大，所以对原数据集抽样10万条数据进行淘宝用户数据行为的分析。

分析思路：根据所选数据集——淘宝用户行为数据，从以下三个方面进行分析：

1. 用户行为分析（基于AARRR模型与漏斗分析模型）

   • 获取用户情况：讨论日新增用户情况（这里将该时间段的初次使用时间为注册时间，仅用来进行数据分析思维逻辑的锻炼。）
   • 激活用户情况：分析总体uv，pv，人均浏览次数以及日均uv，pv，人均浏览次数情况，查看统计时间段内数据是否存在异常波动，从用户侧，商品侧反应平台吸引用户的能力以及用户粘性和忠诚度
   • 用户留存情况：讨论用户次日留存率、三日留存率以及七日留存率
   • 收益增加情况：讨论日均成交量、ARPU（人均付费）、ARPPU（付费用户人均付费）、用户复购率、付费率情况
   • 用户推荐情况：计算各个环节用户行为的转化率，结合漏斗模型分析不同行为的转化率情况，确定节点位置，提出改善转化率的意见

2. 用户偏好分析

   • 用户商品类别偏好分析：统计浏览TOP10，收藏TOP10，加购TOP10以及购买TOP10的商品，分析用户对平台商品偏好程度，以及依据畅销商品优化首页商品类别信息展示，提高畅销商品类别的曝光量
   • 用户行为习惯偏好分析：从时间的维度分析用户的行为习惯，按照24小时时间段统计用户行为数据，针对不同行为的在各个时间段的数据统计，找出用户活跃的日期以及每天的活跃时间段，可以进行符合用户在平台购物习惯的营销活动

3. 用户价值分析（基于RFM模型）

   • 由于原数据集中没有消费金额，因此只分析RF两个值，F是消费频数，R是近期消费时间，选择这两个维度进行用户分层，挖掘更多的用户价值

3. 数据处理

3.1 数据查看

导入SQLyog查看数据（文件-打开-所有数据类型）

3.2 数据导入

在所使用数据库下创建一个与原数据集匹配的数据表，然后点开表选择导入本地csv文件（原数据集没有表头）

USE dbtest1;
CREATE TABLE IF NOT EXISTS user_behavior(user_id INT not null,sku_id INT not null,category_id INT not null,behavior VARCHAR(15) not null,order_time VARCHAR(15));
SHOW CREATE TABLE user_behavior;
1
2
3

由于原数据集量过大，此次选择导入前10万条数据进行分析：

CREATE TABLE user_bh
AS
SELECT * FROM user_behavior
LIMIT 100000;
1
2
3
4

3.3 数据清洗

数据去重：

select DISTINCT * FROM user_bh
group by user_id,sku_id,category_id,behavior,order_time;
1
2

缺失值处理：

select * FROM user_bh
where user_id is null and sku_id is null and category_id is null and behavior is null and order_time is null;
-- 结果显示无缺失值
1
2
3

**时间戳数据处理：**统一数据口径，划分成日期时间、日期、时间三列

-- 新增 order_dt 日期时间列
ALTER TABLE user_bh ADD COLUMN order_dt DATETIME;
UPDATE user_bh
SET order_dt = FROM_UNIXTIME(order_time);
-- 新增 order_date 日期列
ALTER TABLE user_bh ADD COLUMN order_date DATE;
UPDATE user_bh
SET order_date = FROM_UNIXTIME(order_time);
-- 新增 order_dtime 时间列
ALTER TABLE user_bh ADD COLUMN order_dtime TIME;
UPDATE user_bh
SET order_dt = FROM_UNIXTIME(order_time);
1
2
3
4
5
6
7
8
9
10
11
12

**异常值处理：**将2017年11月25日至2017年12月3日时间段之外的信息数据进行剔除。

delete from user_bh
where order_date < '2017-11-25' or order_date > '2017-12-03';
1
2

共有44条异常数据，剔除后还剩余99956行数据。

相关数据校对：

SELECT MAX(order_date),MIN(order_date) FROM user_bh;
-- 2017-11-25至2017-12-03
1
2

4. 用户行为分析(基于AARRR模型和漏斗模型)

4.1 获取用户情况：日新增用户

SELECT first_date,COUNT(DISTINCT user_id) new_uv
FROM (SELECT user_id,MIN(order_date) first_date FROM user_bh 
      where MIN(order_date) between '2017-11-25' and '2017-12-04'
      GROUP BY user_id) t
GROUP BY first_date;
1
2
3
4
5

分析：

• 日新增人数随着时间变化其增长幅度逐日降低，在2017-11-27以后基本没有新增人数；
• 2017-11-25至2017-11-30期间，用户从695增长至983，新增涨幅比为41%，其中在2017-11-25日和26日增长良好，可以探究日新增人数增长的原因同时思考后续新增人数减少的原因，是否与曝光量等宣传有关；
• 分析新增用户的渠道曝光量、渠道转化率，渠道ROI，分析哪些渠道的获客效果最好，并加大投入流量和资金。

4.2 激活用户情况

分析总体uv，pv，人均浏览次数以及日均uv，pv，人均浏览次数情况，查看统计时间段内数据是否存在异常波动，从用户侧，商品侧反应平台吸引用户的能力以及用户粘性和忠诚度

整体情况：

-- 计算总体uv、pv、人均浏览次数
SELECT COUNT(DISTINCT user_id) AS uv,(SELECT COUNT(*) FROM user_bh WHERE behavior = 'pv') AS pv,
(SELECT COUNT(*) FROM user_bh WHERE behavior = 'pv')/COUNT(DISTINCT user_id) AS '人均浏览次数' FROM user_bh;
1
2
3

日均情况：

-- 计算日均uv、pv、人均浏览次数
SELECT t1.order_date,t1.uv '日均uv',t2.pv '日均pv',t2.pv/t1.uv '人均浏览次数' 
FROM
(SELECT order_date,COUNT(DISTINCT user_id) AS uv FROM user_bh GROUP BY order_date) t1
LEFT JOIN (SELECT order_date,COUNT(*) pv FROM user_bh WHERE behavior = 'pv' GROUP BY order_date)t2
ON t1.order_date = t2.order_date;
1
2
3
4
5
6

分析：

• 日均uv与日均pv呈现相同的增长趋势：在11月25日到12月1日，pv和uv基本保持稳定，12月2日点击量与活跃人数猛增，活跃人数教前一日增长33.98%，点击量较前一日增长19.27%，随后点击量与日活人数均略下降；
• 整体人均页面访问量处于稳定变化，人均浏览次数基本稳定在13左右，即用户与网站的粘性良好，侧面反应出网页未出现重大问题；
• 12月2日出现的点击量突增现象可能是由于当天为周六，而且各个品牌都在预热淘宝双十二活动，可能会有前期的活动导致点击量增多；对比同期新增人数，可以分析该日并无新增人数，即是老客日活跃人数增长33.98%导致点击量增加19.27%；
• 结合所提出的假设进行验证，调研12月2日是否举行促销、促活活动，活动的具体方式是什么，可做市场活动复盘，记录让日活跃人数暴涨的原因及措施。

4.3 用户留存情况：留存率

分析次日留存率、3日留存率、七日留存率的思路：先找出每个用户使用时间和第一次使用时间，计算出两者之间的间隔天数，对这些间隔天数进行分段即得第一次使用时间后的次日留存人数、3日留存人数，7日留存人数；

-- 获取每个用户的使用时间与第一次使用时间
SELECT t1.user_id,t2.first_day,t1.order_date
FROM (SELECT DISTINCT user_id,order_date FROM user_bh)t1
INNER JOIN (SELECT user_id,MIN(order_date) first_day FROM user_bh GROUP BY user_id) t2 ON t1.user_id = t2.user_id;
1
2
3
4

-- 计算后面使用时间与首次使用时间之间相隔的天数
SELECT user_id,first_day,order_date,DATEDIFF(order_date,first_day) df_day 
FROM 
(SELECT t1.user_id,t2.first_day,t1.order_date
FROM (SELECT DISTINCT user_id,order_date FROM user_bh)t1
INNER JOIN (SELECT user_id,MIN(order_date) first_day FROM user_bh GROUP BY user_id) t2 ON t1.user_id = t2.user_id)t;
1
2
3
4
5
6

-- df_day表示留存天数
SELECT first_day, 
SUM(CASE WHEN df_day=0 THEN 1 ELSE 0 END) AS 'day_0',
SUM(CASE WHEN df_day=1 THEN 1 ELSE 0 END) AS 'day_1',
SUM(CASE WHEN df_day=2 THEN 1 ELSE 0 END) AS 'day_2',
SUM(CASE WHEN df_day=3 THEN 1 ELSE 0 END) AS 'day_3',
SUM(CASE WHEN df_day=4 THEN 1 ELSE 0 END) AS 'day_4',
SUM(CASE WHEN df_day=5 THEN 1 ELSE 0 END) AS 'day_5',
SUM(CASE WHEN df_day=6 THEN 1 ELSE 0 END) AS 'day_6',
SUM(CASE WHEN df_day=7 THEN 1 ELSE 0 END) AS 'day_7',
SUM(CASE WHEN df_day=8 THEN 1 ELSE 0 END) AS 'day_8'
FROM
(SELECT user_id,first_day,order_date,DATEDIFF(order_date,first_day) df_day 
FROM 
(SELECT t1.user_id,t2.first_day,t1.order_date
FROM (SELECT DISTINCT user_id,order_date FROM user_bh)t1
INNER JOIN (SELECT user_id,MIN(order_date) first_day FROM user_bh GROUP BY user_id) t2 ON t1.user_id = t2.user_id)t)a
GROUP BY first_day;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- 计算留存率
select first_day,concat(cast((day_0/day_0)*100 as DECIMAL(18,2)),'%') as '当日新增人数',
concat(cast((day_1/day_0)*100 as DECIMAL(18,2)),'%') as '次日留存',
concat(cast((day_2/day_0)*100 as DECIMAL(18,2)),'%') as '2日留存',
concat(cast((day_3/day_0)*100 as DECIMAL(18,2)),'%') as '3日留存',
concat(cast((day_4/day_0)*100 as DECIMAL(18,2)),'%') as '4日留存',
concat(cast((day_5/day_0)*100 as DECIMAL(18,2)),'%') as '5日留存',
concat(cast((day_6/day_0)*100 as DECIMAL(18,2)),'%') as '6日留存',
concat(cast((day_7/day_0)*100 as DECIMAL(18,2)),'%') as '7日留存',
concat(cast((day_8/day_0)*100 as DECIMAL(18,2)),'%') as '8日留存'
from (-- df_day表示留存天数
SELECT first_day, 
SUM(CASE WHEN df_day=0 THEN 1 ELSE 0 END) AS 'day_0',
SUM(CASE WHEN df_day=1 THEN 1 ELSE 0 END) AS 'day_1',
SUM(CASE WHEN df_day=2 THEN 1 ELSE 0 END) AS 'day_2',
SUM(CASE WHEN df_day=3 THEN 1 ELSE 0 END) AS 'day_3',
SUM(CASE WHEN df_day=4 THEN 1 ELSE 0 END) AS 'day_4',
SUM(CASE WHEN df_day=5 THEN 1 ELSE 0 END) AS 'day_5',
SUM(CASE WHEN df_day=6 THEN 1 ELSE 0 END) AS 'day_6',
SUM(CASE WHEN df_day=7 THEN 1 ELSE 0 END) AS 'day_7',
SUM(CASE WHEN df_day=8 THEN 1 ELSE 0 END) AS 'day_8'
FROM
(SELECT user_id,first_day,order_date,DATEDIFF(order_date,first_day) df_day 
FROM 
(SELECT t1.user_id,t2.first_day,t1.order_date
FROM (SELECT DISTINCT user_id,order_date FROM user_bh)t1
INNER JOIN (SELECT user_id,MIN(order_date) first_day FROM user_bh GROUP BY user_id) t2 ON t1.user_id = t2.user_id)t)a
GROUP BY first_day) p;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

分析：

• 11月25日新增的客户一直处于稳定留存状态；

• 11月30日新增的用户在3日留存后出现断崖式下降，可以假设是由于促销活动等吸引过来的不稳定用户；

• 结合数据可以知道数据基本在前3天稳定留存，但是后期无法持久留存。

  如何提高留存率呢？

  • 可以先做一个用户分群，对比留存人群和流失人群的行为，构建画像，对比两者之间的相异点，使用A/Btest测试检验每一个不同点对用户产生的影响；
  • 咨询流失掉的用户走掉的原因，利用逻辑分析树的方法分析竞争对手、产品设计、用户体验、bug等有可能流失掉的原因，从而分析出流失的主要原因；
  • 根据流失原因对其进行相应措施补救，并不断验证和改善，最终提升留存率。

4.4 增加收益情况：成交量，复购率

• 成交量

select order_date,sum(购买) as '日成交量'
from
(select order_date,count(behavior) as '购买'
from user_bh
where behavior='buy'
group by order_date) as a
group by order_date;
1
2
3
4
5
6
7

• 复购率：表示一段时间内，消费次数大于等于两次以上的人数所占比例

-- 消费次数大于等于两次以上的用户
select distinct user_id,count(behavior) as '购买次数' 
from user_bh
where behavior='buy'
group by user_id
having 购买次数>=2;

-- 购买的总共用户数
select count(distinct user_id) 
from user_bh
where behavior='buy';    # 671

-- 计算复购率
select (select count(*) from (select distinct user_id,count(behavior) as '购买次数' 
from user_bh where behavior='buy' group by user_id having 购买次数>=2)f) '复购次数',(select count(distinct user_id) from user_bh where behavior='buy') '购买次数',(select count(*) from (select distinct user_id,count(behavior) as '购买次数' 
from user_bh where behavior='buy' group by user_id having 购买次数>=2)f)/(select count(distinct user_id) from user_bh where behavior='buy') as '复购率'
from user_bh limit 1;  

-- 购买次数人数分布
select buyer '购买次数' ,count(*) '购买人数'
from (select distinct user_id,count(behavior) as buyer
from user_bh where behavior='buy' group by user_id having buyer>=2)f
group by buyer;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

分析：

• 成交量在12月2日有明显增长，相比于12月1日 增长分别为26.17%，12月2号以及12月3号即该周周末比上周的周末的数据也有明显提高，可能原因是双12从12月2号的的预热活动，刺激用户消费。
• 用户复购率达到了65.87%，说明用户粘性以及忠诚度较高，购物体验基本符合用户心理预期，基本能够满足用户购物的需求。
• 其中复购次数集中在2-8次，最多的是2次占比是33.94%，超过8次占比仅为6.79%，最高复购次数是43次；
• 针对复购次数8次以上的用户重点观察，极大有可能是重要价值客户，对其进行重点维护。

4.5 推荐转化情况：转化率(基于漏斗模型)

-- 创建用户行为视图
create view ub
as
select distinct user_id,sum(case when behavior = pv then 1 else 0 end) if_pv,sum(case when behavior = fav then 1 else 0 end) if_fav,sum(case when behavior = cart then 1 else 0 end) if_cart,sum(case when behavior = buy then 1 else 0 end) if_buy
from user_bh
group by user_id;
  
-- 浏览(点击)人数
select count(user_id) as '浏览(点击)人数' from ub;
-- 浏览加购人数
select sum(case when if_pv>0 and if_cart >0 then 1 else 0 end) as '浏览加购用户数' from ub;
-- 收藏人数
select sum(case when if_pv>0 and if_cart >0 and if_fav >0 then 1 else 0 end) as '浏览加购收藏用户数' from ub;
-- 购买人数
select sum(case when if_pv>0 and if_cart >0 and if_fav >0 and if_buy >0 then 1 else 0 end) as '浏览加购收藏购买用户数' from ub;

-- 基于用户购买路径
-- 浏览-购买 人数
select sum(case when if_buy>0 then 1 else 0 end) as '购买用户数' from ub where if_fav = 0 and if_cart = 0;
-- 浏览-收藏-购买 人数
select sum(case when if_fav >0 then 1 else 0 end) as '浏览收藏用户数',sum(case when if_fav >0 and if_buy >0 then 1 else 0 end) as '浏览收藏购买用户数' from ub where if_cart = 0;
-- 浏览-加购-购买 人数
select sum(case when if_cart >0 then 1 else 0 end) as '浏览加购用户数',sum(case when if_cart >0 and if_buy>0 then 1 else 0 end) as '浏览加购购买用户数' from ub where if_fav = 0;
-- 浏览-收藏且加购-购买
select sum(case when if_fav > 0 and if_cart >0 then 1 else 0 end) as '浏览加购和收藏用户数',sum(case when if_fav >0 and if_cart >0 and if_buy >0 then 1 else 0 end) as '浏览加购收藏购买用户数' from ub;
-- 浏览-收藏或加购-购买
select sum(case when if_fav > 0 or if_cart >0 then 1 else 0 end) as '浏览加购和收藏用户数',sum(case when (if_fav >0 or if_cart >0) and if_buy >0 then 1 else 0 end) as '浏览加购收藏购买用户数' from ub;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

分析：

• 可以看见用户添加购物车的转化率为73.24%%，收藏率为24.7%，说明用户群体可能是不习惯或者没有意识在浏览后添加购物车或者收藏，而且有一部分用户浏览后直接下单支付，省去购物车和收藏环节，用户支付成功转化率仅为19.84%，转化率较低；
• 用户在加购后的转化率减低66.28%，关键节点是购物车到收藏之间的转化率，因此需要适当引导激励该步骤的转化；
• 对于浏览到添加购物车之间流失的用户，可以进行用户画像分析，适当减少其购买过程环节复杂程度，实现一键式购买，提高用户购买效率。

5. 用户偏好分析

5.1 用户商品类别偏好分析

• 统计浏览TOP10，收藏TOP10，加购TOP10以及购买TOP10的商品，分析用户对平台商品偏好程度，以及依据畅销商品优化首页商品类别信息展示，提高畅销商品类别的曝光量

-- 商品种类总数
select count(category_id) '商品种类总数'  from (select DISTINCT category_id from user_bh) t;
-- 点击次数TOP10的商品种类
SELECT category_id,COUNT(category_id) AS '点击次数' FROM user_bh WHERE behavior= 'pv' GROUP BY category_id ORDER BY COUNT(category_id) DESC LIMIT 10;
-- 收藏次数TOP10的商品种类
SELECT category_id,COUNT(category_id) AS '收藏次数' FROM user_bh WHERE behavior= 'fav' GROUP BY category_id ORDER BY COUNT(category_id) DESC LIMIT 10;
-- 加购次数TOP10的商品种类
select category_id,count(category_id) as '加购次数'FROM user_bh WHERE behavior= 'cart' GROUP BY category_id ORDER BY COUNT(category_id) DESC LIMIT 10;
-- 购买次数TOP10的商品种类
select category_id,count(category_id) as '购买次数'FROM user_bh WHERE behavior= 'buy' GROUP BY category_id ORDER BY COUNT(category_id) DESC LIMIT 10;
1
2
3
4
5
6
7
8
9
10

• 流失分析：需求品类和购买品类分析

-- 需求频率以pv指标去衡量
SELECT a.category_id,a.需求品类频次, (SELECT COUNT(category_id) FROM user_bh WHERE behavior='buy' AND category_id= a.category_id)AS '购买品类频次' ,CONCAT(((SELECT COUNT(category_id) FROM user_bh WHERE behavior='buy' AND category_id= a.category_id)*100/a.需求品类频次),'%') 'ROI'
FROM
(SELECT category_id,COUNT(category_id) AS '需求品类频次' FROM user_bh
WHERE behavior='pv'
GROUP BY category_id
ORDER BY 需求品类频次 DESC
LIMIT 10) AS a ;
-- 购买频率以buy指标去衡量
select a.category_id , (select count(category_id) from user_bh where behavior='pv' and category_id=a.category_id )as '需求品类频次', a.购买品类频次,CONCAT((a.购买品类频次*100/(SELECT COUNT(category_id) FROM user_bh WHERE behavior='buy' AND category_id= a.category_id)),'%') 'ROI'
from
(select category_id,count(category_id) as '购买品类频次' from user_bh
where behavior='buy'
group by category_id
order by 购买品类频次 DESC
limit 10) as a;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

分析：

• 受欢迎商品品类分析：

  • 在TOP10商品分布中，浏览，收藏，加购TOP10相同的商品种类有9种，说明商品推荐以及商品检索的精准程度能够很好的满足用户的需求，达到用户心里对于搜寻商品的预期，然而热销(购买次数)商品TOP10的商品种类与浏览，收藏，加购TOP10有较大出入，而且热销排名靠前并不是浏览，收藏，加购TOP10商品列表中排名靠前的商品，应思考推送机制是否存在问题；
  • 最受欢迎的前三类品类是：4756105，415813，3607361；
  • 平台需要加大对热销商品的曝光量，优化热销商品种类商品展示，从而提高销量，同时分析用户钟爱的品类中哪些商品的受欢迎程度，可以针对性进行拉新促活以及促销活动，从而提高店铺收益。

• 流失分析：

  • 需求品类频次，意指商家推送广告的流量多，点击率就会提高。

  • ROI=用户贡献消费额/获客费用，我们姑且用ROI的类似的概念解决性价比的问题；ROI=购物品类频次/需求品类频次，通俗’性价比’,推送的广告多，转化率最好也要高，满足用户需求

  • ROI越高证明投放的广告越满足用户的购买要求，匹配程度越高；反之越低，证明投放广告并没有带来较高的转化率，匹配程度低，需要改善。

  • 需求品类排名图中，ROI最低的是2520377，其次是982629，突出表现的是3002521

  • 购买品类排名图中，ROI最低的4756105，此品类是投放量最多的品类，但购买力却很差，ROI最高的是1464116，其次是4789432，1299190，2735466

  • 整体概况（vs对比）：

    • 需求品类排名中ROI最低的品类是2520377，投放量是1769，购买数仅为9；相比较购买数排名第一的品类2735466，投放量仅为1114 ，是2520377的62.97%，购买次数却是其4.2倍；
    • 购买品类排名中ROI最高的品类是1464116，投放量为628，购买数为35，ROI高达5.57%，是需求排名第一的品类4756105的10.92倍。
    • 需求品类vs购买品类对比表中需求品类排名中前10名中仅有50%的品类处在购买排名前十中，而购买力较强的品类2735466/1464116/2885642/1299190/4789432投放量却很低，没有达到投放量前十。

    结论：

    • 整体看两图，运营商对商品的投放，推送机制存在严重的问题；未能引导用户搜索到核心商品是流失率高，转化率低的主要原因；
    • 其中优质内容的推送+匹配机制做的不合理，没有实现效益最大化，客户未能精准的找到想要找到的商品品类；
    • 应该继续考虑RFM模型中的重要价值客户的需求和购买是否一致。

    措施：

    • 减少需求品类频次排名靠前但是ROI较低产品的投放量，适当增加购买力强的产品的投放量
    • 对不同价值的用户针对性的精准投放商品广告
    • 完善推送匹配机制，挖掘优质内容、优质商品推送给精准用户
    • 选择在晚上21-23点加大投放量，引导用户找到合适的商品，提高转化率。

5.2 用户行为习惯偏好分析

从时间的维度分析用户的行为习惯，按照24小时时间段统计用户行为数据，针对不同行为的在各个时间段的数据统计，找出用户活跃的日期以及每天的活跃时间段，可以进行符合用户在平台购物习惯的营销活动

-- 24小时各时间段的用户行为习惯分布
-- 创建视图统计用户使用时段
CREATE VIEW m AS
SELECT user_id,category_id,behavior,order_date,
order_dtime,HOUR(order_dtime) dtime FROM user_bh;
-- 统计各时段使用用户人数
select dtime,count(behavior) as '用户行为总数',
sum(case when behavior ='pv' then 1 else 0 end) as 'pv',
sum(case when behavior ='fav' then 1 else 0 end) as 'fav',
sum(case when behavior ='cart' then 1 else 0 end) as 'cart',
sum(case when behavior ='buy' then 1 else 0 end) as 'buy'
from m
group by dtime
order by dtime;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

分析：

• 用户活跃时间在晚上19-23时间段，用户点击、加购、收藏、购买的行为趋于一致，商品的浏览，加购，收藏，购买均有所提升；
• 19点开始点击量稳步上升，到22点到达顶峰，23点稍有回落，到24点明显下降，大部分用户会在晚上时段购物。
• 不过从浏览到购买的总转化率相比中午11点到下午1点的时间较低，因此商家可以在该时间段加大流量投放力度，推送广告，进行一些促销类的活动，有助于转化率提升，提高购买人数，进而提高总交易金额；
• 同时用户偏爱添加购物车，收藏意识相对薄弱。

6. 用户价值分析（基于RFM模型）

由于原数据集中没有消费金额，因此只分析RF两个值，F是消费频数，R是近期消费时间，选择这两个维度进行用户分层，挖掘更多的用户价值

按照打分标准计算用户的R与F得分情况：

-- 创建近期消费时间视图，将近期购买时间提取到视图中
create view R
as
select user_id,max(order_date) as '近期购买时间' from user_bh
where behavior = 'buy' 
group by userid;

-- 建立R等级划分视图，将客户近期购买时间进行等级划分，越接近2017-12-04的R越大；
create view R等级划分
as
select user_id, 近期购买时间,datediff('2017-12-04',近期购买时间) as 'R' from R;
-- 构建R数值型和R字符串型，前者用于后续的计算全体用户的平均值，后者用于计算每个等级的数量；
SELECT user_id, 近期购买时间,CASE  WHEN R<=2 THEN '4' 
       WHEN R<=4 THEN '3'
       WHEN R<=6 THEN '2'
       ELSE '1' END  AS 'R值' FROM R等级划分;
       
-- 创建消费频数视图
create view F
as
select DISTINCT user_id, count(behavior) as '购买次数' from user_bh
where behavior = 'buy' 
group by user_id
having 购买次数>=2;

-- 创建F等级划分视图
create view F等级划分
as
SELECT user_id, 购买次数,CASE WHEN 购买次数<=6 THEN '1'
       WHEN 购买次数<=10 THEN '2'
       WHEN 购买次数<=14 THEN '3' 
       ELSE '4' END AS 'F值' FROM F;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 构建RFM模型
-- 计算R平均值与F平均值
select AVG(R值) as 'R平均值' FROM r等级划分;    #3.02
select avg(F值) as 'F平均值' from f等级划分;    #1.19

-- 根据用户个人R,F的得分与整体R,F得分的平均值作比较，进行用户分层
CREATE VIEW RFM
AS
SELECT a.user_id,a.近期购买时间,b.购买次数,a.R值,b.F值,
(CASE WHEN a.R值>=3.02 AND b.F值>=1.19 THEN '重要高价值客户'
WHEN a.R值<3.02 AND b.F值>=1.19 THEN '重要保持客户'
WHEN a.R值>=3.02 AND b.F值<1.19 THEN '重要发展客户'
WHEN a.R值<3.02 AND b.F值<1.19 THEN '重要挽留客户'
END ) AS '客户分类'
FROM R等级划分 a, F等级划分 b
WHERE a.user_id=b.user_id;

-- 客户分类频数分布
select 客户分类, count(客户分类) as '频数' from RFM
group  by 客户分类 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

分析：

• **重要高价值客户：**近期交易的高频率用户占比9.73%，该类用户极有可能发展为忠实用户，留存率高；对这部分高质量高粘性客户，应该加强维持呵护，可以提供VIP服务以及定向优惠活动，对于他们所购买的商品种类，可以进行精准营销。

• **重要保持客户：**无近期交易的高频率用户占比4.52%，近期没有消费过，有可能发展为流失的重要客户，有可能被竞争对手拉过去了，针对这部分客户采取赠送、奖励、红包等方式唤回，通过PUSH，短信，站内信等方式促进用户活跃度，刺激消费，增加粘性，提高他们的付费转化率。

• **重要发展客户：**近期交易的低频率用户占比44.12%，具有巨大的潜力；根据这部分用户可以根据以往的消费记录，个性化推荐，增加优惠券刺激消费，增加粘性。

• **重要挽留客户：**无近期交易的低频率用户占比41.63%，这部分客户很有可能是已经或者即将流失的客户，针对这部分客户找到流失原因，并根据联系方式，尝试联系问其不满意的地方，对有相似特征的群体进行预警，分析这部分用户流失的原因，弄清楚这部分用户的需求，购物体验为什么没有达到用户预期，针对其原因进行改善。

7. 复盘和总结

分析总结：

1. 日新增人数随着时间变化其增长幅度逐日降低，在2017-11-27以后基本没有新增人数；2017-11-25至2017-11-30期间，用户从695增长至983，新增涨幅比为41%，其中在2017-11-25日和26日增长良好，可以探究日新增人数增长的原因同时思考后续新增人数减少的原因，是否与曝光量等宣传有关；
2. 日均uv与日均pv呈现相同的增长趋势：在11月25日到12月1日，pv和uv基本保持稳定，12月2日点击量与活跃人数猛增，活跃人数教前一日增长33.98%，点击量较前一日增长19.27%，随后点击量与日活人数均略下降；整体人均页面访问量处于稳定变化，人均浏览次数基本稳定在13左右，即用户与网站的粘性良好，侧面反应出网页未出现重大问题；
3. 11月25日新增的客户一直处于稳定留存状态；11月30日新增的用户在3日留存后出现断崖式下降，可以假设是由于促销活动等吸引过来的不稳定用户；结合数据可以知道数据基本在前3天稳定留存，但是后期无法持久留存；
4. 成交量在12月2日有明显增长，相比于12月1日 增长分别为26.17%，12月2号以及12月3号即该周周末比上周的周末的数据也有明显提高，可能原因是双12从12月2号的的预热活动，刺激用户消费；用户复购率达到了65.87%，说明用户粘性以及忠诚度较高，购物体验基本符合用户心理预期，基本能够满足用户购物的需求；其中复购次数集中在2-8次，最多的是2次占比是33.94%，超过8次占比仅为6.79%，最高复购次数是43次；
5. 用户添加购物车的转化率为73.24%%，收藏率为24.7%，说明用户群体可能是不习惯或者没有意识在浏览后添加购物车或者收藏，而且有一部分用户浏览后直接下单支付，省去购物车和收藏环节，用户支付成功转化率仅为19.84%，转化率较低；用户在加购后的转化率减低66.28%，关键节点是购物车到收藏之间的转化率，因此需要适当引导激励该步骤的转化；对于浏览到添加购物车之间流失的用户，可以进行用户画像分析，适当减少其购买过程环节复杂程度，实现一键式购买，提高用户购买效率；
6. 在TOP10商品分布中，浏览，收藏，加购TOP10相同的商品种类有9种，说明商品推荐以及商品检索的精准程度能够很好的满足用户的需求，达到用户心里对于搜寻商品的预期，然而热销(购买次数)商品TOP10的商品种类与浏览，收藏，加购TOP10有较大出入，而且热销排名靠前并不是浏览，收藏，加购TOP10商品列表中排名靠前的商品，应思考推送机制是否存在问题；
7. 需求品类排名中ROI最低的品类是2520377，投放量是1769，购买数仅为9；相比较购买数排名第一的品类2735466，投放量仅为1114 ，是2520377的62.97%，购买次数却是其4.2倍；购买品类排名中ROI最高的品类是1464116，投放量为628，购买数为35，ROI高达5.57%，是需求排名第一的品类4756105的10.92倍；需求品类vs购买品类对比表中需求品类排名中前10名中仅有50%的品类处在购买排名前十中，而购买力较强的品类2735466/1464116/2885642/1299190/4789432投放量却很低，没有达到投放量前十。
8. 用户活跃时间在晚上19-23时间段，用户点击、加购、收藏、购买的行为趋于一致，商品的浏览，加购，收藏，购买均有所提升；19点开始点击量稳步上升，到22点到达顶峰，23点稍有回落，到24点明显下降，大部分用户会在晚上时段购物；不过从浏览到购买的总转化率相比中午11点到下午1点的时间较低，因此商家可以在该时间段加大流量投放力度，推送广告，进行一些促销类的活动，有助于转化率提升，提高购买人数，进而提高总交易金额；同时用户偏爱添加购物车，收藏意识相对薄弱。
9. 重要价值客户：近期交易的高频率用户占比9.73%；重要保持客户：无近期交易的高频率用户占比4.52%；重要深耕客户：近期交易的低频率用户占比44.12%；重要挽留客户：无近期交易的低频率用户占比41.63%。

建议：

• **提高日新增用户：**可以做拉新活动，通过运营新媒体账号，吸引粉丝并导入到APP里。常用新媒体平台有：微信、微博、今日头条等。也可以发帖推广，通过在其他论坛发软文或硬广，要么用心写好软文，要么花钱联系版主。优先找大平台的垂直领域，或者垂直领域的大平台，如小红书等APP。
• **提高用户活跃度：**用户从浏览到最终购买整个过程的流量，用户12月2日点击量突增，而在3日又出现下滑趋势，应调研12月2日是否举行促销、促活活动，活动的具体方式是什么，可做市场活动复盘，记录让日活跃人数暴涨的原因及措施。
• 提高用户留存率：让用户保持使用淘宝电商平台的习惯是提高留存率的关键，可以按照使用频率和购买次数积攒积分，每天上线点击量达到某个数值即可自动领取积分，到月末换取购物礼券，也可以对于年购买次数和金额达到规定量的客户推出VIP服务，购买次数同比上升之后相应福利也上升，利用这种方法提高用户的留存率和对平台的忠诚度。
• **提高复购率【哪些产品的复购率高】：**对用户添加购物车、收藏、购买，复购率高的商品，可以针对店铺做些修改，产品分类；促销区活动搭配；热销产品；充分的购买信息，商品的展示技巧等，都可以促进用户的重复购买欲。
• **购买转化率低：**利用店铺将用户引流到微信，建立社群，在朋友圈发布促销商品或者新品，利用朋友圈的特点图和视频的方式吸引顾客；该方式是目前电商引流培养精准粉丝的流行方式。
• 用户RFM分类：对不同类别的用户采用不同的方式刺激消费，建立流失用户的定义模型，确定怎么样的用户算是流失用户，比如「近期未消费的低频率用户」算是流失。找到用户流失原因，弥补流失问题，发消息召回用户，告知产品最新变化。

思路总结：

本次分析内容数据选取阿里天池淘宝用户行为数据，分析步骤：导入数据，数据清洗，数据分析，提出建议。

数据分析从以下三个方面入手：用户行为分析，用户偏好分析以及用户价值分析。

基于数据源的数据，搭建相关数据指标体系可以将数据归纳为指标：

• 行为分析指标：日新增，PV,UV，留存率，复购率，成交量，转化率
• 偏好分析指标：TPO10商品排行，用户行为时间段分布
• 价值分析指标：消费频数，近期购买时间

基于上述三个层面的指标，也会出现一些基于公式的衍生指标，比如PV/UV(日人均浏览次数)。该分析报告中，运用了数据分析中几个常用的模型，基于用户行为的AARRR模型、购买路径分析转化率的漏斗模型以及体现用户价值的RFM模型。

参考：

淘宝用户行为分析(Mysql) - 知乎 (zhihu.com)

阿里天池项目：淘宝用户行为数据分析 - 知乎 (zhihu.com)