SQLServer 学习笔记5 RFM模型

R=Recency 最近一次消费的间隔时间，R值越大，表示距离用户最近一次购买行为的时间越长，反之时间越短

F=Frequency 最近一段时间内的购买次数，F值越大，表示用户越活跃，反之，表示用户活跃度不够

M=Moneytary 最近一段时间内交易的金额，M值越大，表示用户越有价值，反之，表示用户价值越低

111 '重要价值客户' 该类用户近期有过购买行为，购买频次高，客单价高。对商家来说是最有价值用户，需要重点维护

011 '重要保持客户' 该类用户很长一段时间没有发生购买行为，但是之前的购买频次和购买金额都很高，说明此类用户价值很高，需要重点唤回

101 '重要发展客户' 该类用户在近期有过购买行为且金额高，需要提高该类用户的购买频次，发展成重要价值客户

001 '重要挽留客户' 该类用户很长一段时间没有发生购买行为，之前购买频次比较低，但是消费金额很高，说明这类用户购买能力很强，如果能够挽留住这类用户，可以创造更大价值。

110 '一般价值客户' 该类用户在近期有过购买行为，购买频次高，但是消费金额低，这类用户很有潜力，说明对商家的认可度高，可以通过下单喜好进行有针对性引导，提高消费金额。

010 '一般维持客户' 该类用户最近没有发生购买行为，客单价也比较低，但是消费频次高于平均值。对于此类用户，进行正常维系即可。

100 '一般发展客户' 该类用户近期有过购买行为，购买频次低，购买金额低，整体数据过少，无法判断用户的实际价值，所以会被纳入一般发展用户或新用户行列，商家要做好数据跟踪，后续依据数据变化进行相应调整。

000 '一般挽留客户' 该类用户很一段时间没有发生购买行为，之前购买频次较低，购买金额也较低。这类用户基本可以放弃维护。

WITH RFM_VAL AS
(SELECT
t1.客户ID,
t1.最后购买日期,
t1.最后购买距离天数,
t1.订单数,
t1.购买金额,
CUME_DIST() OVER (ORDER BY t1.最后购买距离天数) as R_Value,
CUME_DIST() OVER (ORDER BY t1.订单数) as F_Value,
CUME_DIST() OVER (ORDER BY t1.购买金额) as M_Value
FROM
(
SELECT
t0.客户ID,
t0.最后购买日期,
DATEDIFF(DAY,CONVERT(VARCHAR(10),GETDATE(),120),t0.最后购买日期) as 最后购买距离天数,
--DATEDIFF(DAY,t0.最后购买日期,CONVERT(VARCHAR(10),GETDATE(),120)) as 最后购买距离天数,
t0.订单数,
t0.购买金额
FROM (SELECT a.FCUSTOMERID as 客户ID,
MAX(a.FDATE) as 最后购买日期,
COUNT(DISTINCT(a.FBILLNO)) as 订单数,
SUM(c.FALLAMOUNT_LC) as 购买金额
FROM T_SAL_OUTSTOCK a
INNER JOIN T_SAL_OUTSTOCKENTRY b ON a.FID = b.FID
LEFT JOIN T_SAL_OUTSTOCKENTRY_F c ON b.FENTRYID = c.FENTRYID
LEFT JOIN T_BD_CUSTOMER d ON a.FCUSTOMERID = d.FCUSTID
WHERE YEAR(a.FDATE) = YEAR(a.FDATE)
AND a.FDOCUMENTSTATUS = 'C'
--AND d.FNUMBER NOT IN ('xxxx','xxxx-01','xxxx-02')
GROUP BY a.FCUSTOMERID
) t0
) t1),
AVG_VAL AS
(SELECT
AVG(R_Value) as avg_r,
AVG(F_Value) as avg_f,
AVG(M_Value) as avg_m
FROM RFM_VAL),
rfm as
(SELECT
客户ID,
最后购买日期,
最后购买距离天数,
订单数,
购买金额,
R_Value,
F_Value,
M_Value,
avg_r,
avg_f,
avg_m,
iif (R_Value > avg_r,1,0) as R,
iif (F_Value > avg_f,1,0) as F,
iif (M_Value > avg_m,1,0) as M
FROM RFM_VAL
JOIN AVG_VAL ON 1=1)
SELECT
客户ID,
TD.FNAME 客户名称,
最后购买日期,
最后购买距离天数,
订单数,
购买金额,
R_Value,
F_Value,
M_Value,
avg_r,avg_f,avg_m,
R,F,M,
CASE
WHEN r * 100 + f * 10 + m = 111 THEN '重要价值客户'
WHEN r * 100 + f * 10 + m = 011 THEN '重要保持客户'
WHEN r * 100 + f * 10 + m = 101 THEN '重要发展客户'
WHEN r * 100 + f * 10 + m = 001 THEN '重要挽留客户'
WHEN r * 100 + f * 10 + m = 110 THEN '一般价值客户'
WHEN r * 100 + f * 10 + m = 010 THEN '一般维持客户'
WHEN r * 100 + f * 10 + m = 100 THEN '一般发展客户'
WHEN r * 100 + f * 10 + m = 000 THEN '一般挽留客户'
ELSE '' END AS RFM_TYPE
FROM rfm
LEFT JOIN T_BD_CUSTOMER TC ON 客户ID = TC.FCUSTID
LEFT JOIN T_BD_CUSTOMER_L TD ON TC.FCUSTID = TD.FCUSTID AND TD.FLOCALEID = 2052
ORDER BY 最后购买距离天数 DESC