MySQL面试真题（二十七）——RFM分析法对用户进行分类

RFM分析法对用户进行分类

下面是2020年1月份某电商平台的用户行为记录表：

业务问题：店铺在对用户进行盘点时发现，用户运营过于粗放，没能做到用户分类运营。老板想在下一个月对不同的用户进行有针对性的营销，达到降低成本提高收入，精细化运营的效果。怎么办？

涉及到用户分类，可以用RFM分析方法。

一.什么是RFM分析方法？

RFM分析方法是根据用户的最近一次消费时间间隔(Recency)、消费频率(Frequency)、消费金额(Monetary)来对用户进行打分，进而将用户按分数划分为不同的类型，然后对不同的用户使用不同的运营策略，从而实现辅助精准运营。
RFM分析方法将用户分为以下8类：


二.如何用SQL实现RFM分析方法？
1.定义指标R、F、M
将指标定义中的“一段时间“定义为1月份（31天）。

最近一次消费时间间隔®：用户最近一次消费距离现在（1月31日）多长时间了。

消费频率(F)：用户一段时间内（1月份）消费了多少次。

消费金额(M)：用户一段时间内（1月份）的消费金额，对应表中的“消费金额“。

WITH temp1 AS (
SELECT *
FROM 用户行为记录表
WHERE 用户行为类型='购买'
),
temp2 AS (
SELECT 用户编号,MAX(用户活跃日期) AS 最近一次购物日期
FROM temp1
GROUP BY 用户编号
),
temp3 AS (-- 求出用户编号和最近一次消费时间间隔R
SELECT 用户编号,DATEDIFF('2020-01-31',temp2.最近一次购物日期) AS 最近一次消费时间间隔R
FROM temp2
),
temp4 AS (-- 求出用户编号和消费频率F
SELECT 用户编号,COUNT(*) AS 消费频率F
FROM temp1
WHERE DATEDIFF('2020-01-31',用户活跃日期)<=30
GROUP BY 用户编号
),
temp5 AS (-- 求出用户编号和消费金额M
SELECT 用户编号,SUM(消费金额) AS 总消费金额M
FROM temp1
WHERE DATEDIFF('2020-01-31',用户活跃日期)<=30
GROUP BY 用户编号
),
temp6 AS(-- 汇总用户编号及RFM
SELECT temp3.用户编号, 最近一次消费时间间隔R AS R,消费频率F AS F,总消费金额M AS M FROM temp3
JOIN temp4 ON temp3.用户编号=temp4.用户编号
JOIN temp5 ON temp3.用户编号=temp5.用户编号
),
temp7 AS (-- 给各用户的RFM打分
SELECT *,
CASE WHEN R>15 THEN 1
WHEN R>10 AND R<=15 THEN 2
WHEN R>5 AND R<=10 THEN 3
WHEN R>3 AND R<=5 THEN 4
ELSE 5 END AS R分,
CASE WHEN F<=1 THEN 1
WHEN F>1 AND F<=3 THEN 2
WHEN F>3 AND F<=5 THEN 3
WHEN F>5 AND F<=7 THEN 4
ELSE 5 END AS F分,
CASE WHEN M<=500 THEN 1
WHEN M>500 AND M<=1000 THEN 2
WHEN M>1000 AND M<=1500 THEN 3
WHEN M>1500 AND M<=2000 THEN 4
ELSE 5 END AS M分
FROM temp6
),
temp8 AS (-- 求出RFM分的平均值
SELECT AVG(R分) AS R分平均,AVG(F分) AS F分平均,AVG(M分) AS M分平均
FROM temp7
),
temp9 AS (-- 把每个用户的RFM与对应的平均值对比
SELECT temp7.用户编号,
CASE WHEN R分>R分平均 THEN '高' ELSE '低' END AS R分类,
CASE WHEN F分>F分平均 THEN '高' ELSE '低' END AS F分类,
CASE WHEN M分>M分平均 THEN '高' ELSE '低' END AS M分类
FROM temp7
JOIN temp8
),
temp10 AS (-- 匹配用户分类
SELECT 用户编号,
CASE WHEN R分类='高' AND F分类='高' AND M分类='高' THEN '重要价值用户'
WHEN R分类='高' AND F分类='低' AND M分类='高' THEN '重要价值用户'
WHEN R分类='低' AND F分类='高' AND M分类='高' THEN '重要保持用户'
WHEN R分类='低' AND F分类='低' AND M分类='高' THEN '重要挽留用户'
WHEN R分类='高' AND F分类='高' AND M分类='低' THEN '一般价值用户'
WHEN R分类='高' AND F分类='低' AND M分类='低' THEN '一般发展用户'
WHEN R分类='低' AND F分类='高' AND M分类='低' THEN '一般保持用户'
WHEN R分类='低' AND F分类='低' AND M分类='低' THEN '一般挽留用户'
END AS 用户分类
FROM temp9
)
SELECT * FROM temp10
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

结果如下：

根据分析结果，就可以对用户进行精细化运营啦~