sql_数据分析之电商人货场模型分析之指标体系拆解+代码实操 (用户留存、RFM模型、 用户路径分析等)

本文最早发表在csdn时间为:2021-09-03
本案例数据链接：
点我下载

数据来源为工作中接触到的某公司后台数据，在完成工作相关分析后，本人对该部分数据虚拟重建用以复盘整理

大家好，我是Captain,一个从业两年的数据分析师。
公主号：CaptainData

本文将从下图思路整理分析过程~

下面正文开始~
觉得有用的话请一定动个小手关注转发收藏点赞~

（国际惯例，先讲一下行业背景）

• 随着电商⾏业近⼏年的迅猛发展，电⼦商务从早些年的粗放式经营，逐步转化为精细化运营。
• 随着平台数据量的不断积累，通过数据分析挖掘消费者的潜在需求与消费偏好成为平台运营过程中的一个重要环节。
• 本项⽬基于为本人职业内接触到的某真实电商平台⽤户⾏为数据，
• 本项目主要从以下几方面对数据进行分析解读：
  – 探索⽤户⾏为规律，寻找⾼价值⽤户；
  – 分析商品特征，寻找⾼贡献商品；
  – 分析产品功能，优化产品路径。

「⼈」（⽤户) 是整个运营的核⼼。所有举动都围绕着，如何让更多的⼈有购买⾏为，让他们买的更多，买的更贵。

• 分析方向：
  获知⽬前平台上的主⼒消费⼈群有哪些特征，
  主⼒消费⼈群对货品有哪些需求，
  主⼒消费⼈群活跃在哪些场，
  还有哪些有消费⼒的⼈⽬前不在平台上，
  以及 uv pv pv/uv(浏览深度) roi 新老用户 dau mau 等监控指标

• 分析意义
  找到主⼒消费，持续扩大主力消费群体，挖掘用户需求，根据活跃场景持续优化转化下单率

「货」就对应供给,涉及到了货品分层（二八法则，波士顿矩阵），哪些是红海，哪些是蓝海，如何进⾏动态调整，是要做⾃营还是平台，以满⾜消费者的需求

• 分析方向：
  找到目前平台最受欢迎商品（品类），
  各产品类⽬的订单量，排名，
  点击 收藏 加购
  曝光
  下单率等等
• 分析意义
  洞悉平台商品出货情况，找到主要利润来源，高销量商品，转化情况，持续优化sku、库存（自营），提高转化率（下单率）

「场」就是消费者在什么场景下，以什么样的⽅式接触到了这个商品。

早期的导购做的⽐较简单，野蛮，⽬前的场就⽐较丰富，但也暴露了淘宝和京东在导购⽅⾯的⼀些问题。⽐如内容营销，⽬前最好的可能是微信的 KOL ⽣态和⼩红书，甚⾄微博，⽽不在电商⾃⼰的场。如何做⼀个全域的打通，和消费者进⾏多触点的接触，⽐如社交和电商联动，来完成销售转化，这就是腾讯和阿⾥⼀直都在讲的「全域营销」

这段话有点费 总结就是：场 就是所有生活（网络）中可以投放得地方（让广告打得更悄无声息~）

• 分析方向：
  找到转化率最高，roi最大的场，
  在用户路径中找到下单量最大的路径，流失最大的路径（漏斗模型），分析原因，pv,领券率等
• 分析意义
  场无处不在 ，是客户触达的第一步，场的相关指标分析有利于提高ROI,转化率，提高用户群体，提高uv,dv ,进而提高gmv

数据字段描述：

列名	说明
user_id	⽤户ID
item_id	商品ID
behavior_type	⽤户⾏为类型（1-曝光；2-购买；3-加⼊购物⻋；4-加⼊收藏夹。）
user_geohash	地理位置
item_category	品类ID
time	⽤户⾏为发⽣的时间 （年-月-日 时 ）

数据内容如下：

1. 人: 基于RFM模型找出核⼼付费⽤户群，对这部分⽤户进⾏精准营销。

2. 货: 商品分析：找出热销商品，研究热销商品特点；

3. 场: 基于漏⽃模型的⽤户购买流程各环节分析指标，确定各个环节的转化率，便于找到需要改进的环节；

-- 电商项目 ：人货场拆解
/*
0 数据准备
0.1 建表
*/
DROP TABLE if EXISTS shop_data_captain;
create table shop_data_captain
(
user_id int (10) ,
item_id int (10),
behavior_type int (1),
user_geohash varchar (14),
item_category int (5),
date_time datetime (13)
);

导入本案例数据：
点我下载
5.1.2数据预处理：
观察数据:后续留存与RFM模型需要用到日期字段，这里先预处理一下数据，做一个日期列：

# 0.2 新建字段 date_time，dates
alter TABLE shop_data_captain add dates VARCHAR(10) null;
UPDATE shop_data_captain set dates =date(date_time);
# 查一下数据量
SELECT count(*) FROM shop_data_captain;

数据条数:50607

5.1.3 数据去重

通过 select distinct * from table_name 将数据创建到虚拟表

# 0.3数据去重
DROP view if EXISTS shop_data_captain_view;
CREATE view shop_data_captain_view as
SELECT DISTINCT * FROM shop_data_captain;
# 查一下数据量
SELECT count(*) FROM shop_data_captain_view;

数据条数:50606

注意：平时公司数据会有唯一id,该数据没有数据id 故采用这种去重方式
5.2 用户（人）侧分析
5.2.1 基础指标计算
基础指标

统计 uv、pv、pv/uv （浏览深度） (每日)

• 思路：
  uv 用户这天来了就记一次（一天只记一次 distinct）
  pv 曝光了就要记一次（认定曝光就是浏览了一个页面 behavior_type=1）

# 0.3 统计 uv、pv、pv/uv （浏览深度）   (每日)
SELECT 
dates,
count(DISTINCT user_id) uv,
count(if(behavior_type=1,1,null)) pv,
count(if(behavior_type=1,1,null))/count(DISTINCT user_id) 'pv/uv'
FROM shop_data_captain_work_view
GROUP BY dates
ORDER BY dates;

5.2.2 用户留存

• 思路：
  用数据表以user_id左关联，左a右b,筛选b.dates>=a.dates,
  利用dateDIFF(b.dates,a.dates)=间隔天数筛选用户从起始日往后数据计数作为留存，以时间（日期）分组
  间隔天数后留存率 = 间隔天数后计数量 / 起始日期

运行结果：
-- 04. 用户留存
with p as 
(
SELECT 
a.dates,
count(DISTINCT a.user_id )uv,
count(if(dateDIFF(b.dates,a.dates)=1,a.user_id,null)) re_v_1,
count(if(dateDIFF(b.dates,a.dates)=2,a.user_id,null)) re_v_2,
count(if(dateDIFF(b.dates,a.dates)=3,a.user_id,null)) re_v_3,
count(if(dateDIFF(b.dates,a.dates)=5,a.user_id,null)) re_v_5,
count(if(dateDIFF(b.dates,a.dates)=7,a.user_id,null)) re_v_7,
count(if(dateDIFF(b.dates,a.dates)=15,a.user_id,null)) re_v_15,
count(if(dateDIFF(b.dates,a.dates)=30,a.user_id,null)) re_v_30
FROM 
(SELECT USER_id,dates  FROM  shop_data_captain_work_view GROUP BY USER_id,dates) a
LEFT JOIN 
(
SELECT USER_id,dates  FROM  shop_data_captain_work_view GROUP BY USER_id,dates) b
on a.user_id=b.user_id 
WHERE b.dates>=a.dates
GROUP BY a.dates ORDER BY a.dates
)

SELECT 
dates,
uv,
concat(ROUND(re_v_1/user_count*100,2),"%")  'day_1',
concat(ROUND(re_v_2/user_count*100,2),"%")  'day_2',
concat(ROUND(re_v_3/user_count*100,2),"%")  'day_3',
concat(ROUND(re_v_5/user_count*100,2),"%")  'day_5',
concat(ROUND(re_v_7/user_count*100,2),"%")  'day_7',
concat(ROUND(re_v_30/user_count*100,2),"%")  'day_30'
FROM p;

运行结果：

5.2.3 RFM模型

RFM模型解释：
RFM模型是衡量客户价值和客户创利能力的重要工具和手段。在众多的客户关系管理(CRM)的分析模式中，RFM模型是被广泛提到的。该机械模型通过一个客户的近期购买行为、购买的总体频率以及花了多少钱3项指标来描述该客户的价值状况。
这3个要素构成了数据分析最好的指标：
最近一次消费 (Recency)
消费频率 (Frequency)
消费金额 (Monetary)

RS分类	FS分类	MS分类	客户类型	策略
高	高	高	高价值客户	保持现状
低	高	高	重点保持客户	适当召回促销
高	低	高	重点发展客户	提高频次
低	低	高	重点挽留客户	重点召回
高	高	低	一般价值客户	刺激消费
低	高	低	一般保持客户	流失召回
高	低	低	一般发展客户	挖掘需求
低	低	低	潜在客户

RS分布:R的平均值
FS分布:F的平均值
MS分布:M的平均值
高:高于平均值时
低:低于平均值时
通过将每个特征分为高低两种状态，2 * 2 * 2 将客户群体分为8个种类（可对每个特征分多种，比如3 * 3 * 3）
实操：
5.2.3.1 先建立R维度
找到数据最大日期：

-- 05 RFM
-- 05.1 定义一个动态最大时间（后续可以封装进函数或存储过程）

SET @max_buy_date=0;
SELECT @max_buy_date:=max(dates) FROM shop_data_captain_work_view;
SELECT @max_buy_date;

设计R等级划分：以表内最大购买时间为以这个点为最后一天，划分时间价值：

• 离最后下单时间间隔2天内 得5分
• 离最后下单时间间隔4天内 得4分
• 离最后下单时间间隔6天内 得3分
• 离最后下单时间间隔8天内 得2分
• 离最后下单时间间隔超过8天 得1分

-- 05.2 设计R等级划分 设计R等级划分：以表内最大购买时间为以这个点为--- 最后一天，划分时间价值：
-- 

DROP TABLE if EXISTS R_level;
CREATE table R_level 
as 
with p as
(
SELECT 
user_id,
max(dates) 
as 
recent_buy_date 
FROM 
shop_data_captain_work_view
WHERE behavior_type=2
GROUP BY user_id
)

SELECT 
user_id,
recent_buy_date,
@max_buy_date max_date,
DATEDIFF(@max_buy_date,recent_buy_date) recent_diff_num,
case 
WHEN DATEDIFF(@max_buy_date,recent_buy_date)<=2 then 5
WHEN DATEDIFF(@max_buy_date,recent_buy_date)<=4 then 4
WHEN DATEDIFF(@max_buy_date,recent_buy_date)<=6 then 3
WHEN DATEDIFF(@max_buy_date,recent_buy_date)<=8 then 2
else 1 end  as R_value
FROM p;

SELECT * FROM R_level ORDER BY R_value desc;

运行结果：

5.2.3.2 建立F维度

behavior_type=2 为下单
对该群体该数据时间段内下单次数打分：

• 该时间段内下单次数2单内 得1分
• 该时间段内下单次数4单内 得2分
• 该时间段内下单次数6单内 得3分
• 该时间段内下单次数8单内 得4分
• 该时间段内下单次数超过8单的 得5分

# 05.2 F 最近一段规定时间内的消费频率  这里选择全体区域时间
DROP TABLE if EXISTS F_level;
CREATE table F_level 
as
with p as
(
SELECT 
user_id,
count(user_id) buy_frequency 
FROM 
shop_data_captain_work_view 
WHERE behavior_type=2 
GROUP BY 
user_id
)

SELECT 
user_id,
buy_frequency,
case 
WHEN buy_frequency<=2 then 1
WHEN buy_frequency<=4 then 2
WHEN buy_frequency<=6 then 3
WHEN buy_frequency<=8 then 4
else 5 end  as F_value
FROM p;

# 查询一下结果
SELECT * FROM F_level ORDER BY F_value desc;

运行结果：

5.2.3.3 整合结果

本次由于数据维度原因不做M维度分析

通过最近消费（R）和消费频率（F）建⽴RFM模型：
重要⾼价值客户：指最近⼀次消费较近⽽且消费频率较⾼的客户；
重要唤回客户：指最近⼀次消费较远且消费频率较⾼的客户；
重要深耕客户：指最近⼀次消费较近且消费频率较低的客户；
重要挽留客户：指最近⼀次消费较远且消费频率较低的客户；

R分类	F分类	客户类型	策略
高	高	高价值客户	保持现状
低	高	重点保持客户	适当召回促销
高	低	重点发展客户	提高频次
低	低	重点挽留客户	重点召回

思路：
对上面R_level与F_level表得到的分值求平均，然后联表用case when 映射对映客户群体

设定动态均值（后续可封装，数据增加后可自动调整，便于提交公司平台）

-- 5.3设定动态指标参数
set @R_avg=0;
set @F_avg=0;
SELECT @R_avg:=avg(R_value) FROM R_level;
SELECT @F_avg:=avg(F_value) FROM F_level;

联表映射：

-- 5.4联表映射：
SELECT 
r.user_id,
r.recent_buy_date,
r.recent_diff_num,
f.buy_frequency,
R.R_value,
F.F_value,
case 
when R.R_value>@R_avg and F.F_value>@F_avg then '重要高价值客户'
when R.R_value<@R_avg and F.F_value>@F_avg then '重要唤回客户'
when R.R_value>@R_avg and F.F_value<@F_avg then '重要深耕客户'
when R.R_value<@R_avg and F.F_value<@F_avg then '重要挽留客户'
end RFM_value
FROM R_level R,F_level F WHERE R.user_id=F.user_id
ORDER BY
R_value desc
;

运行结果：

至此， 人（用户侧）数据分析完成~
5.3 商品（货）侧相关分析

牢记：对商品的研究 要对商品进行分组，group by 商品

• 商品的点击量 收藏量 加购量 购买次数 购买转化
• 思路：对商品进行分组 计算点击量 收藏量 加购量 购买次数，
• behavior_type =2是下单，下单人数/商品浏览人数 =转化率

对商品sku分析

/*
-- 5.3 商品的点击量 收藏量 加购量 购买次数 购买转化
（该商品的所有用户中有购买转化的⽤户⽐）按照商品进行分组统计

behavior_type 用户行为为类型（1-曝光；2-购买；3-加⼊购物⻋；4-加⼊收藏夹  购买转化率
*/
SELECT 
item_id,
sum(case WHen behavior_type=1 then 1 else 0 end) as 'pv',-- 点击量
sum(case WHen behavior_type=4 then 1 else 0 end) as 'fav',-- 收藏量
sum(case WHen behavior_type=3 then 1 else 0 end) as 'carv',-- 加购量
sum(case WHen behavior_type=2 then 1 else 0 end) as 'buyv' ,-- 点击量
count(DISTINCT case WHEN behavior_type=2 then user_id ELSE null end) 下单人数,
count(DISTINCT user_id ) 总浏览人数,
count(DISTINCT case WHEN behavior_type=2 then user_id ELSE null end)/count(DISTINCT user_id )  as 'buy_rate'
FROM shop_data_captain_work_view
GROUP BY
item_id
ORDER BY
buyv desc
;

运行结果：

商品销量占比分析

-- 商品销量占比
WITH p as 
(
SELECT 
item_id,count(user_id) 销量
FROM shop_data_captain_work_view
WHERE behavior_type=2
GROUP BY 
 item_id
ORDER BY 销量 desc
)
SELECT 
p.item_id,
concat(cast((销量/sum(销量) over())*100 as DECIMAL(10,2)),"%") 销量占比
FROM 
p;

运行结果：

对商品品类（item_category）分析：

-- 5.4 商品品类的分析
SELECT 
shop_data_captain_work_view.item_category,
sum(case WHen behavior_type=1 then 1 else 0 end) as 'pv',-- 点击量
sum(case WHen behavior_type=4 then 1 else 0 end) as 'fav',-- 收藏量
sum(case WHen behavior_type=3 then 1 else 0 end) as 'carv',-- 加购量
sum(case WHen behavior_type=2 then 1 else 0 end) as 'buyv' ,-- 点击量
count(DISTINCT case WHEN behavior_type=2 then user_id ELSE null end) 下单人数,
count(DISTINCT user_id ) 总浏览人数,
count(DISTINCT case WHEN behavior_type=2 then user_id ELSE null end)/count(DISTINCT user_id )  as 'buy_rate'
FROM shop_data_captain_work_view -- WHERE item_category=8
GROUP BY
item_category
ORDER BY
buyv
 desc
;

运行结果：

品类销量占比

-- 品类销量占比
WITH p as 
(
SELECT 
item_category,count(user_id) 销量
FROM shop_data_captain_work_view
WHERE behavior_type=2
GROUP BY 
item_category
ORDER BY 销量 desc
)
SELECT 
p.item_category,
concat(cast((销量/sum(销量) over())*100 as DECIMAL(10,2)),"%") 销量占比
FROM 
p;

5.4平台（场）侧分析

-- 5.4平台购物指标数据

SELECT 
dates,
count(DISTINCT user_id) 用户量,
count(1) as "每日总量",
sum(case WHen behavior_type=1 then 1 else 0 end) as 'pv',-- 点击量
sum(case WHen behavior_type=4 then 1 else 0 end) as 'fav',-- 收藏量
sum(case WHen behavior_type=3 then 1 else 0 end) as 'carv',-- 加购量
sum(case WHen behavior_type=2 then 1 else 0 end) as 'buyv' ,-- 点击量
count(DISTINCT case WHEN behavior_type=2 then user_id ELSE null end)/count(DISTINCT user_id )  as 'buy_rate'
FROM shop_data_captain_work_view -- WHERE item_category=8
GROUP BY
dates
ORDER BY
dates  desc
;

运行结果：

路径分析：
假设某用户在第5个动作是下单了，那我们想查看该用户前4个动作对该商品做了什么动作（数据埋点采集）(-- PARTITION by user_id ,item_id oORDER by date_time) ，通过lag 偏移函数可将数据向下偏移 ，将动作整理到同一行再拼接，用于用户路径研究

利用lag 函数，将用路径拼接：

  WITH p as 
 (
 SELECT 
 user_id,
 item_id,
 behavior_type,
 date_time,
 lag(behavior_type,4) over(PARTITION by user_id ,item_id ORDER by date_time ) lag_4,
 lag(behavior_type,3) over(PARTITION by user_id ,item_id ORDER by date_time ) lag_3,
 lag(behavior_type,2) over(PARTITION by user_id ,item_id ORDER by date_time ) lag_2,
 lag(behavior_type,1 ) over(PARTITION by user_id ,item_id ORDER by date_time ) lag_1	
 FROM  
 shop_data_captain_work_view
 )
 SELECT * FROM
 (
 SELECT 
 *,rank() over(PARTITION by user_id ,item_id ORDER by date_time desc) rank_date 
 FROM p
 ) t_1
 WHERE rank_date=1 and behavior_type=2;

运行结果：

-- 查询每个路径下用户人数
 SELECT 
CONCAT(

lag_4,
'-',
lag_3,
'-', 
lag_2,
'-',
lag_1,
'-',
ifnull(behavior_type,'空') 
) 
as user_way,
count(DISTINCT user_id) as user_count
 FROM path_base_view
 GROUP BY 
 user_way
ORDER BY user_count desc
 ;

看的出来，主体下单人群直接就下单了第二多的就是‘曝光-下单’路径了

UV异常分析：每⽇UV数据中，明显异常点为双⼗⼆活动造成，该影响为已知影响，只看每日uv难以定位问题，下面看看周环比增长率

-- 周环比增长率

WITH p as 
(
SELECT 
dates,
count(DISTINCT user_id) uv
FROM shop_data_captain_work_view
GROUP BY dates
ORDER BY dates
)
SELECT 
t_1.*,
(uv-lag_7_uv)/lag_7_uv 周环比增长率
FROM 
(
SELECT 
p.*,
lag(dates,6,0) over( ORDER BY dates) lag_7,
lag(uv,6,0)over( ORDER BY dates)lag_7_uv
FROM 
p
)
t_1
;

运行结果：

tableau出图：

对于UV周环⽐的分析：
⽇常周环⽐数据⼤多⼤于0，说明⽤户程⼀定上升趋势，其中如11⽉26⽇、12⽉5⽇、12⽉7⽇等的数据为下降数据，需要结合其他数据做进⼀步的下降原因分析。双⼗⼆活动后⽤户周环⽐会相应下降，为正常原因。

猜测可能的问题有：

• 内部问题：
  产品BUG（⽹站bug）、
  策略问题（周年庆活动结束了）、
  营销问题（代⾔⼈换了）等；

• 外部问题：
  竞品活动问题（其他平台⼤酬宾），
  政治环境问题（进⼝商品限制），
  舆情⼝碑问题（平台
  商品爆出质量问题）等；
  参考PEST(政治经济社会技术)分析

6.2 ⽤户精细化运营

通过RFM模型中的⽤户最近⼀次购买时间、⽤户消费频次分析，分拆得到以下重要⽤户,可以在后续精细化运营场景中直接使⽤细分⽤户，做差异化运营：

• 对⾼价值客户做VIP服务设计，增加⽤户粘性同时通过设计优惠券提升客户消费；
• 对深耕客户做⼴告、推送刺激，提升消费频次；
• 对挽留客户做优惠券、签到送礼策略，增加挽留⽤户粘性；
• 对唤回客户做定向⼴告、短信召回策略，尝试召回⽤户。
  6.3 商品分析
  热销商品品类如下所示。

其中有些品类购买转化率较其余商品品类偏低，需要结合更多数据做进⼀步解读。（可能的原因：品类⾃有特性导致⽤户购买较低，⽐如⾮必需品、奢侈品等等。）

6.4 产品功能路径分析
以下为主要购买路径：
可以发现⽤户多以直接购买为主；
添加购物⻋的购买在主要购买路径中数量较少;
后续的产品加购功能和产品收藏功能还需要结合更多数据做改进⽅案

以上便是本项目所有分析过程了~
欢迎点赞收藏评论转发~