「MySQL」电商“人货场”指标体系

vlambda
2022-04-24

「MySQL」电商“人货场”指标体系

本文你将学到：

如何搭建电商“人货场”指标体系
MySQL：留存率分析
MySQL：RFM模型分析
MySQL：用户行为路径分析

细心的同学可能会发现，这个符号的「标题」都是实战项目，因为学习的目的不是学习本身，而是为了解决实际问题。本文通过案例来讲述MySQL在公司里是如何应用的。

本文适用于有一定MySQL基础，想进阶练习的同学。（真的都是干货，请大家多多练习）思维导图附在文章末尾，配合文章一起使用效果更佳。

一、项目背景

电商行业从早些年的粗放式经营，逐步转化为精细化运营。随着平台数据量的不断增加，通过数据分析挖掘消费者的潜在需求、消费偏好成为平台运营过程中的重要环节。

本项目目的如下：

探索用户行为规律，寻找高价值用户
分析商品特征，寻找高贡献商品
分析产品功能，优化产品路径

二、使用“人货场”拆解方式建立指标体系

「人」是整个运营的核心，所有举动都围绕着怎么让更多的人有购买行为，且买的更多买的更贵。

「货」对应着供给，涉及到了商品分层，哪些是红海，哪些是蓝海，如何进行动态调整。

「场」就是消费者在什么场景下，以什么样的方式接触到了这个商品。（哇这句话太精彩了）

建立指标体系如下：

人货场对应的指标（部分）

三、问题确认

1、基于RFM模型找出核心付费用户群，对这部分用户进行精准营销。「人」

2、商品分析：找出热销商品，研究热销商品特点。「货」

3、基于漏斗模型的用户购买流程各环节分析指标，确定各环节的转化率，便于找到需要改进的环节。「场」

四、准备数据

CREATE TABLE temp_trade(
user_id int(9) COMMENT '⽤户ID',
item_id int(9) COMMENT '商品ID',
behavior_type int(1) COMMENT '⽤户⾏为类型（1-曝光；2-购买；3-加⼊购物⻋；4-加⼊收藏夹。）',
user_geohash varchar(14) COMMENT '地理位置',
item_category int(5) COMMENT '品类ID',
date_time datetime COMMENT '用户行为发生时间',
dates date COMMENT '用户行为发生日期'
);

从navicat导入以下数据源：

temp_tradegitee数据源：https://gitee.com/hu-weiqing/datasource/blob/master/temp_trade.xlsx

数据源前10条展示如下：

「MySQL」电商“人货场”指标体系

五、SQL实现

1、用户指标体系

1.1 基础数据：统计每日PV、UV、PV/UV

-- UV：统计distinct user_id数量
-- PV：统计behavior_type=1的记录数

SELECT
a.dates,
count(distinct a.user_id) as 'UV',
count(if(a.behavior_type=1,user_id,null)) as 'PV',
count(if(a.behavior_type=1,user_id,null)) / count(distinct a.user_id) as 'PV/UV'
FROM temp_trade a 
GROUP BY a.dates ;

「MySQL」电商“人货场”指标体系

1.2 统计留存率⭐️⭐️⭐️

# 不得不说，我代码写得实在太漂亮了，还有注释，绝了绝了，人美心善


-- 第三步：把下面这一大坨代码用with as封装成一个临时表（要和第四步的select配合查询）
WITH r AS (

-- 第二步：把每天来过的用户在接下来的第1，2，3，7，15，30天内下过单的求出来
SELECT
a.dates,
count(distinct a.user_id) as uv,
count(distinct if(datediff(b.dates,a.dates) = 1,b.user_id,null)) as remain1,
count(distinct if(datediff(b.dates,a.dates) = 2,b.user_id,null)) as remain2,
count(distinct if(datediff(b.dates,a.dates) = 3,b.user_id,null)) as remain3,
count(distinct if(datediff(b.dates,a.dates) = 7,b.user_id,null)) as remain7,
count(distinct if(datediff(b.dates,a.dates) = 15,b.user_id,null)) as remain15,
count(distinct if(datediff(b.dates,a.dates) = 30,b.user_id,null)) as remain30
FROM
(
-- 第一步：求出每天来的用户在之后日期的访问数据
SELECT a.user_id,a.dates FROM temp_trade a GROUP BY a.user_id,a.dates) a 
LEFT JOIN (SELECT a.user_id,a.dates FROM temp_trade a GROUP BY a.user_id,a.dates) b on a.user_id = b.user_id
# 取日期之后的数据
WHERE b.dates >= a.dates 
GROUP BY a.dates
)

-- 第四步：查询每日uv和第1，2，3，7，15，30天的留存率
SELECT 
r.dates,
r.uv,
concat(round(100* r.remain1 / r.uv ,2),'%') as 'day1_%',
concat(round(100* r.remain2 / r.uv ,2),'%') as 'day2_%',
concat(round(100* r.remain3 / r.uv ,2),'%') as 'day3_%',
concat(round(100* r.remain7 / r.uv ,2),'%') as 'day7_%',
concat(round(100* r.remain15 / r.uv ,2),'%') as 'day15_%',
concat(round(100* r.remain30 / r.uv ,2),'%') as 'day30_%'
FROM r ;

「MySQL」电商“人货场”指标体系

1.3 RFM模型⭐️⭐️⭐️

由于该数据没有M值，故只建立了4个分类：重要价值客户、重要发展客户、重要保持客户、重要挽留客户。

1）分别求出R、F和R_value、F_value

#1.求出R和R_value

-- 第四步：建立视图，方便下面计算使用
CREATE VIEW r_level AS

SELECT 
a.user_id,
a.max_date,
-- 第二步：求出统计日期与最近一次下单时间的间隔天数（本次统计日期默认是2019-12-18）
datediff('2019-12-18',a.max_date) as diff,

-- 第三步：根据间隔天数，自定义R值的评分机制
-- 这里是根据2、4、6、8这些值划分成5个档次：1、2、3、4、5，数字越高代表R值越好
(case when datediff('2019-12-18',a.max_date) <= 2 then 5
when datediff('2019-12-18',a.max_date) <= 4 then 4
when datediff('2019-12-18',a.max_date) <= 6 then 3
when datediff('2019-12-18',a.max_date) <= 8 then 2
else 1 end) as R_value
 
FROM (
 -- 第一步：先求出客户的最近一次下单时间
SELECT 
a.user_id,
max(a.dates) as max_date
FROM temp_trade a 
WHERE a.behavior_type = 2 
GROUP BY a.user_id
) a ;

r_level表
「MySQL」电商“人货场”指标体系

# 2.求出F和F_value

-- 第三步：建立视图，方便下面计算使用
CREATE VIEW f_level AS

SELECT 
a.user_id,
a.pay_date,

-- 第二步：根据购买频次建立评分机制
-- 这里是根据2、4、6、8这些值划分成5个档次：1、2、3、4、5，数字越高代表F值越好
(case when a.pay_date <=2 then 1 
when a.pay_date <=4 then 2
when a.pay_date <=6 then 3
when a.pay_date <=8 then 4
else 5 end ) as F_value

FROM (
-- 第一步：先求出客户购买频次（这里是根据下单天数去重，有些公司是根据订单号去重）
SELECT 
a.user_id,
count(distinct a.dates) as pay_date
FROM temp_trade a 
WHERE a.behavior_type = 2
GROUP BY a.user_id
) a ;

f_level表
「MySQL」电商“人货场”指标体系

2）求出RF均值

-- R均值
SELECT avg(R_value) as 'r_agv' FROM r_level ; -- 2.7939

-- R均值
SELECT avg(F_value) as 'f_agv' FROM f_level ; -- 1.9939

3）RF值与RF均值做比较，整合成4种用户类型（本来有8种）
本次只看R值和F值
「MySQL」电商“人货场”指标体系

SELECT 
a.*,
b.pay_date,
b.F_value,
case when a.R_value > 2.7939 and b.F_value > 1.9939 then '重要价值客户'
when a.R_value > 2.7939 and b.F_value < 1.9939 then '重要发展客户'
when a.R_value < 2.7939 and b.F_value > 1.9939 then '重要保持客户'
when a.R_value < 2.7939 and b.F_value < 1.9939 then '重要挽回客户'
end as user_level

FROM r_level a 
LEFT JOIN f_level b on a.user_id = b.user_id ;

RFM模型运行结果
「MySQL」电商“人货场”指标体系

2、商品指标体系

-- 商品的点击、收藏、加购、购买以及转化率计算，按照购买降序
SELECT
a.item_id,
sum(case when a.behavior_type = 1 then 1 else 0 end) as '点击',
sum(case when a.behavior_type = 4 then 1 else 0 end) as '收藏',
sum(case when a.behavior_type = 3 then 1 else 0 end) as '加购',
sum(case when a.behavior_type = 2 then 1 else 0 end) as '购买',
count(distinct(case when a.behavior_type = 2 then a.user_id else null end)) / count(distinct a.user_id) as '购买转化率'

FROM temp_trade a 
GROUP BY a.item_id 
ORDER BY sum(case when a.behavior_type = 2 then 1 else 0 end) DESC;


-- 品类维度同理，将item_id改成item_category即可。

（数据是假，但掩盖不了我写文章的真）
「MySQL」电商“人货场”指标体系

3、平台指标体系

3.1 行为分析

SELECT
a.dates, -- 把上面的item_id改成dates就好了
sum(case when a.behavior_type = 1 then 1 else 0 end) as '点击',
sum(case when a.behavior_type = 4 then 1 else 0 end) as '收藏',
sum(case when a.behavior_type = 3 then 1 else 0 end) as '加购',
sum(case when a.behavior_type = 2 then 1 else 0 end) as '购买',
count(distinct(case when a.behavior_type = 2 then a.user_id else null end)) / count(distinct a.user_id) as '购买转化率'

FROM temp_trade a 
GROUP BY a.dates
ORDER BY sum(case when a.behavior_type = 2 then 1 else 0 end) DESC;

「MySQL」电商“人货场”指标体系

3.2 行为路径分析 ⭐️⭐️⭐️

1）取每条记录的前4个行为——lag()over()

-- 第三步：创建视图
CREATE VIEW rt AS

-- 第二步：用窗口函数lag()over()取每条记录的前4条记录
SELECT 
a.user_id,
a.item_id,
a.behavior_type,
a.date_time,
a.dates,
-- order by后默认升序
lag(a.behavior_type,4) over(partition by a.user_id,a.item_id order by a.date_time) as lag_4,
lag(a.behavior_type,3) over(partition by a.user_id,a.item_id order by a.date_time) as lag_3,
lag(a.behavior_type,2) over(partition by a.user_id,a.item_id order by a.date_time) as lag_2,
lag(a.behavior_type,1) over(partition by a.user_id,a.item_id order by a.date_time) as lag_1

FROM temp_trade a ;


-- 第一步：因为本数据源没有特别代表性的值，所以自己插入一条，便于验证逻辑准确性，此步骤可省略
-- INSERT INTO  temp_trade value(47328088,289585954,4,null,5232,'2019-12-10 12:00:00','2019-12-10')

-- 第四步：随机查询一条数据验证逻辑是否正确
SELECT * FROM rt WHERE rt.user_id = 47328088 and rt.item_id = 289585954;

运行结果：lag()over()
「MySQL」电商“人货场”指标体系

2）取发生购买行为的前4个行为——behavior_type = 2

-- 本次用户行为路径分析，只取发生过购买行为的行为路径，分析购买前的路径规律
CREATE VIEW rt_1 AS 
SELECT rt.*
FROM rt 
WHERE rt.behavior_type = 2;

运行结果：behavior_type = 2
「MySQL」电商“人货场”指标体系

3）用户购买同一种商品，则取最近一次购买行为的数据——rank()over()

-- 第三步：创建视图
CREATE VIEW rt_way AS 

-- 第二步：再取最近一条记录
SELECT rt_1.* FROM(
-- 第一步：先将数据按照用户和商品降序
SELECT 
rt_1.*,
rank() over(partition by rt_1.user_id,rt_1.item_id order by rt_1.date_time desc) as rank_n
FROM rt_1
) rt_1
WHERE rt_1.rank_n = 1 ;

-- 数据终于处理好了
SELECT * FROM rt_way ;

运行结果：rank()over()
「MySQL」电商“人货场”指标体系

4）查询每个路径下有多少客户数

-- 将购买及购买前四个行为用-符号连接起来，分别统计路径的购买人数
SELECT
concat(
ifnull(a.lag_4,'空'),'-',
ifnull(a.lag_3,'空'),'-',
ifnull(a.lag_2,'空'),'-',
ifnull(a.lag_1,'空'),'-',
a.behavior_type
) as user_way,
count(distinct a.user_id) as buyer_num
FROM rt_way a
GROUP BY concat(
ifnull(a.lag_4,'空'),'-',
ifnull(a.lag_3,'空'),'-',
ifnull(a.lag_2,'空'),'-',
ifnull(a.lag_1,'空'),'-',
a.behavior_type
) ;

用户路径分析最终运行结果
「MySQL」电商“人货场”指标体系

六、结论

1、用户分析

「MySQL」电商“人货场”指标体系

UV异常分析：每日UV数据中，明显异常点为双十二活动造成，该影响为已知影响。
用户成上升趋势，其中11.26，12.3等数据下降还需结合其它数据再做判断。
双十二活动后用户周环比会相应下降，为正常原因。

猜测可能的问题有:

内部问题：产品BUG(网站bug)、策略问题(周年庆活动结束了)、营销问题(代言人换了)等;
外部问题：竞品活动问题(其他平台大酬宾)，政治环境问题(进口商品限制)，舆情口碑问题(平台商品爆出质量问题)等;

2、用户精细化运营

「MySQL」电商“人货场”指标体系

可以在后续精细化运营场景中直接使用细分用户，做差异化运营。

3、商品分析

找到热销商品；
其中’5027‘、’5399‘品类购买转化率较其余商品品类偏低，需要结合更多数据做进一步解读。可能的原因：品类自有特性导致用户购买较低，比如非必需品、奢侈品等等。

4、产品功能路径分析

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

为了体现逻辑性，便于你们理解和实操，又是写了2天改了1天～优秀的文章不用多说，这就是了～欢迎点赞收藏评论交流😁

附思维导图：

vlambda博客
学习文章列表