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

邪恶马铃薯

已于 2024-09-13 22:22:50 修改

阅读量799

点赞数 23

分类专栏： MySQL案例练习文章标签： sql 数据库数据分析

于 2024-09-13 22:20:16 首次发布

本文链接：https://blog.csdn.net/sherrinzhang/article/details/142228544

版权

MySQL案例练习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

【背景】

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

【数据源】

交易金额：以万为单位
交易类型：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 13 then 2
       else 1 end) as R_score,
    (case when F>=70 then 5
       when F between 65 and 69 then 4
       when F between 60 and 64 then 3
       when F between 52 and 59 then 2
       else 1 end) as F_score,
    (case when M >= 12000 then 5
       when M between 10000 and 11999 then 4
       when M between 8000 and 9999 then 3
       when M between 6500 and 7999 then 2
       else 1 end) as M_score
from rfm值

在这里插入图片描述

--创建视图“rfm打分表”，便于后续调用
create view rfm打分表 as (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 13 then 2
       else 1 end) as R_score,
    (case when F>=70 then 5
       when F between 65 and 69 then 4
       when F between 60 and 64 then 3
       when F between 52 and 59 then 2
       else 1 end) as F_score,
    (case when M >= 12000 then 5
       when M between 10000 and 11999 then 4
       when M between 8000 and 9999 then 3
       when M between 6500 and 7999 then 2
       else 1 end) as M_score
from rfm值)

在这里插入图片描述

4.查询RFM打分的平均值，即位阈值

select avg(R_score),avg(F_score),avg(M_score)
from rfm打分表

在这里插入图片描述

--创建视图“打分阈值”
create view 打分阈值 as (select avg(R_score),avg(F_score),avg(M_score)
from rfm打分表)

在这里插入图片描述

5.判断RFM打分是否大于打分的平均值，得到最终的RFM值。

select 客户ID,
        (case when R_score >= (select avg(R_score) from rfm打分表) then 1 else 0 end) as R_dim,
        (case when F_score >= (select avg(F_score) from rfm打分表) then 1 else 0 end) as F_dim,
        (case when M_score >= (select avg(M_score) from rfm打分表) then 1 else 0 end) as M_dim
from rfm打分表

在这里插入图片描述

--创建视图“最终rfm值”
create view 最终rfm值 as (select 客户ID,
        (case when R_score >= (select avg(R_score) from rfm打分表) then 1 else 0 end) as R_dim,
        (case when F_score >= (select avg(F_score) from rfm打分表) then 1 else 0 end) as F_dim,
        (case when M_score >= (select avg(M_score) from rfm打分表) then 1 else 0 end) as M_dim
from rfm打分表)

在这里插入图片描述

6.根据最终的RFM值，对客户类型分类

在这里插入图片描述

select 客户id,
    (case when R_dim =1 and F_dim=1 and M_dim=1 then '重要价值客户'
        when R_dim =1 and F_dim=0 and M_dim=1 then '重要深耕客户'
        when R_dim =0 and F_dim=1 and M_dim=1 then '重要挽回客户'
        when R_dim =0 and F_dim=0 and M_dim=1 then '重要挽留客户'
        when R_dim =0 and F_dim=1 and M_dim=0 then '一般维持客户'
        when R_dim =1 and F_dim=0 and M_dim=0 then '新客户'
        when R_dim =1 and F_dim=1 and M_dim=0 then '潜力客户'
        else '流失客户' end) as 客户类型
 from 最终rfm值

在这里插入图片描述

--创建视图“客户类型表”
create view 客户类型表 as (select 客户id,
    (case when R_dim =1 and F_dim=1 and M_dim=1 then '重要价值客户'
        when R_dim =1 and F_dim=0 and M_dim=1 then '重要深耕客户'
        when R_dim =0 and F_dim=1 and M_dim=1 then '重要挽回客户'
        when R_dim =0 and F_dim=0 and M_dim=1 then '重要挽留客户'
        when R_dim =0 and F_dim=1 and M_dim=0 then '一般维持客户'
        when R_dim =1 and F_dim=0 and M_dim=0 then '新客户'
        when R_dim =1 and F_dim=1 and M_dim=0 then '潜力客户'
        else '流失客户' end) as 客户类型
 from 最终rfm值)

7.客户类型分析

1）各类型客户数量及占比

select 客户类型,count(客户id) as 客户数量,concat(round(count(客户id)/(select count(*) from 客户类型表),2)*100,'%') as 客户数量占比
from 客户类型表
group by 客户类型
order by count(客户id) desc

在这里插入图片描述
2）各类型客户销售金额及占比

select a.客户类型,round(sum(b.交易金额),0) as 交易金额,concat(round(sum(b.交易金额)/(select sum(交易金额) from 数据清洗表),2)*100,'%') as 销售金额占比
from 客户类型表 a inner join 数据清洗表 b on a.客户id=b.客户ID
group by a.客户类型
order by sum(b.交易金额) desc

在这里插入图片描述
#【总结与建议】
（1）现存问题：
1.重要客户数量少。数量占比仅47%，销售金额占比达到了53%，包括重要价值客户、重要挽回客户、重要深耕客户、重要挽留客户
2.新客户数量多、共享高，潜力大。数量占比达23%，销售金额占比达20%。
3.流失客户数量较多。数量占比达13%，销售金额占比达11%。

（2）建议：
1.重要价值客户：企业核心用户，需重点维系。提供新品，挖掘新的增长点。
2.重要深耕客户：帮助客户消化产品，增加复购频次。
3.重要保持客户：了解用户近期没有进货的原因，提供优惠促销等激励方案。
4.重要挽留客户：了解长时间未进货、进货频次低的原因，提供优惠促销等激励方案。
5.新客户：比例较大，金额也相对较高，对企业增长潜力大。建议可通过优惠活动、提升服务品质，增加新客户粘性，促使其转化为老顾客。
6.潜力客户：意味着有一定购买力，但需求未被充分挖掘，可以通过市场调研了解这一人群的需求，提供更符合用户需求的产品与服务。
7.一般维持客户：对企业贡献小，一般维护。
8.流失客户：通过问卷调查等方式分析流失原因，并采取相应策略挽回老用户。