SQL案例练习（3）：产品销售客户分析

【背景】

企业需要甄别客户价值，制定针对性的营销与维护方案。

【数据源】

交易金额：以万为单位
交易类型：0-赠送、1-正常价格下单、2-退货、3-特价下单

【RFM模型】

用于评估客户价值和客户分群。
它基于三个维度：
最近一次购买时间（Recency）——客户粘性、
购买频率（Frequency）——客户对企业的忠诚度、
以及购买金额（Monetary）——客户对企业的贡献。

应用步骤

（1）统计每个客户对应的R、F、M值。

（2）找到指标切分阈值：给指标设置正负值，从而将用户按3维度划分成8种客户类型。
均类数据可以按平均值切分。
不均匀数据按平均值切分，误差较大。具体划分方法需要按实际业务判断。

例如，M值，ABC客户交易金额分别为1w、5k、1k，若取平均数则为5333，A客户为重要客户，BC为一般客户。但从业务测角度，交易金额超过4k算大客户，交易金额超6k为vip客户，故直接用平均值切分不合理。

销售数据一般是不均匀的，大部分销售额往往由小部分客户带来（二八定律），可以跟业务人员沟通，分别对R、F、M值按区间拆分后打分，再求打分的平均值，根据打分平均值切分。
例如，跟业务员沟通后，先按区间拆分后打分，2000以内-1分、2001-4000-2分、4001-6000-3分、大于6k-4分，此时ABC客户打分分别为1、3、4，打分平均分为2.6分，此时AB客户为重要客户，C客户为一般客户。

mark下一种关于M值阈值的找法。
若无明确的策略划分，建议可将累计金额从大到小排序，用窗口函数求累计下单金额在总金额的占比，按二八定律找到对应阈值，以此区分重要/一般客户。

（3）计算每个客户的R、F、M值是否大于阈值
大于或等于阈值标记1，小于阈值标记0

（4）将客户按价值分类

【案例运用】

1.数据质量分析，数据清洗与整理

原数据类型包含交易金额为0、交易类型为赠送的数据，需要剔除。

select *
from sheet1
where 交易金额 !=0 and 交易类型 !=0

--创建视图“数据清洗表”，便于后续调用
create view 数据清洗表 as (select *
    from sheet1
    where 交易金额 !=0 and 交易类型 !=0)

2.计算每个客户的R、F、M值

R：取最后一次交易日期与最近分析当日的时间间隔。案例中，按客户ID分组，datediff(‘2020-12-31’,max(交易日期)) as R
F：总交易次数。案例中，按客户ID分组，count(订单号) as F
M：交易总金额。案例中，按客户ID分组，sum(交易金额) as M

--计算R、F、M值
select 客户ID,
    datediff('2020-12-31',max(交易日期)) as R,
    count(订单号) as F,
    sum(交易金额) as M
from 数据清洗表
group by 客户ID;

--创建视图“rfm值”，便于后续调用
create view RFM值 as (select 客户ID,
    datediff('2020-12-31',max(交易日期)) as R,
    count(订单号) as F,
    sum(交易金额) as M
from 数据清洗表
group by 客户ID)

3. 找到指标切分阈值

1）打分规则
具体划分，须与实际业务和经验判断。与业务员沟通后，确定下的打分规则。

2）利用case when 对R、F、M值打分

-- 给RFM值打分        
select 客户ID,
    (case when R <=2 then 5
       when R between 3 and 5 then 4
       when R between 6 and 9 then 3
       when R between 10 and