最全数仓开发需要了解的BI数据分析方法，2024年最新醍醐灌顶

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

| 客户类型与等级 | R | F | M | 客户特征 |

| — | — | — | — | — |

| 重要价值客户

(A级/111) | 高(1) | 高(1) | 高(1) | 最近消费时间近、消费频次和消费金额都很高 |

| 重要发展客户

(A级/101) | 高(1) | 低(0) | 高(1) | 最近消费时间较近、消费金额高，但频次不高，忠诚度不高，很有潜力的用户，必须重点发展 |

| 重要保持客户

(B级/011) | 低(0) | 高(1) | 高(1) | 最近消费时间交远，消费金额和频次都很高。 |

| 重要挽留客户

(B级/001) | 低(0) | 低(0) | 高(1) | 最近消费时间较远、消费频次不高，但消费金额高的用户，可能是将要流失或者已经要流失的用户，应当基于挽留措施。 |

| 一般价值客户

(B级/110) | 高(1) | 高(1) | 低(0) | 最近消费时间近，频率高，但消费金额低，需要提高其客单价。 |

| 一般发展客户

(B级/100) | 高(1) | 低(0) | 低(0) | 最近消费时间较近、消费金额，频次都不高。 |

| 一般保持客户

(C级/010) | 低(0) | 高(1) | 低(0) | 最近消费时间较远、消费频次高，但金额不高。 |

| 一般挽留客户

(C级/000) | 低(0) | 低(0) | 低(0) | 都很低 |

数据分析案例

• 效果图

• 实现步骤

假设有如下的样例数据：

| 客户名称 | 日期 | 消费金额 | 消费数量 |

| — | — | — | — |

| 上海****有限公司 | 2020-05-20 | 76802 | 2630 |

需要将数据集加工成如下格式：

具体SQL实现

SELECT

customer_name,-- 客户名称

customer_avg_money,-- 当前客户的平均消费金额

customer_frequency, – 当前客户的消费频次

total_frequency,-- 所有客户的总消费频次

total_avg_frequency, – 所有客户平均消费频次

customer_recency_diff, – 当前客户最近一次消费日期与当前日期差值

total_recency, – 所有客户最近一次消费日期与当前日期差值的平均值

monetary,-- 消费金额向量化

frequency, – 消费频次向量化

recency, – 最近消费向量化

rfm, – rfm

CASE

WHEN rfm = “111” THEN “重要价值客户”

WHEN rfm = “101” THEN “重要发展客户”

WHEN rfm = “011” THEN “重要保持客户”

WHEN rfm = “001” THEN “重要挽留客户”

WHEN rfm = “110” THEN “一般价值客户”

WHEN rfm = “100” THEN “一般发展客户”

WHEN rfm = “010” THEN “一般保持客户”

WHEN rfm = “000” THEN “一般挽留客户”

END AS rfm_text

FROM

(SELECT

customer_name,-- 客户名称

customer_avg_money,-- 当前客户的平均消费金额

customer_frequency, – 当前客户的消费频次

total_avg_money ,-- 所有客户的平均消费总额

total_frequency,-- 所有客户的总消费频次

total_frequency / count(*) over() AS total_avg_frequency, – 所有客户平均消费频次

customer_recency_diff, – 当前客户最近一次消费日期与当前日期差值

avg(customer_recency_diff) over() AS total_recency, – 所有客户最近一次消费日期与当前日期差值的平均值

if(customer_avg_money > total_avg_money,1,0) AS monetary, – 消费金额向量化

if(customer_frequency > total_frequency / count(*) over(),1,0) AS frequency, – 消费频次向量化

if(customer_recency_diff > avg(customer_recency_diff) over(),0,1) AS recency, – 最近消费向量化

concat(if(customer_recency_diff > avg(customer_recency_diff) over(),0,1),if(customer_frequency > total_frequency / count(*) over(),1,0),if(customer_avg_money > total_avg_money,1,0)) AS rfm

FROM

(SELECT

customer_name, – 客户名称

max(customer_avg_money) AS customer_avg_money , – 当前客户的平均消费金额

max(customer_frequency) AS customer_frequency, – 当前客户的消费频次

max(total_avg_money) AS total_avg_money ,-- 所有客户的平均消费总额

max(total_frequency) AS total_frequency,-- 所有客户的总消费频次

datediff(CURRENT_DATE,max(customer_recency)) AS customer_recency_diff – 当前客户最近一次消费日期与当前日期差值

FROM

(SELECT

customer_name, – 客户名称

avg(money) over(partition BY customer_name) AS                customer_avg_money, – 当前客户的平均消费金额

count(amount) over(partition BY customer_name) AS customer_frequency, – 当前客户的消费频次

avg(money) over() AS total_avg_money,-- 所有客户的平均消费总额

count(amount) over() AS total_frequency, --所有客户的总消费频次

max(sale_date) over(partition BY customer_name) AS customer_recency – 当前客户最近一次消费日期

FROM customer_sales) t1

GROUP BY customer_name)t2) t3

通过上面的分析，可以为相对应的客户打上客户特征标签，这样就可以针对某类客户指定不同的营销策略。

波士顿矩阵与数据可视化

---

基本概念

波士顿矩阵BCG Matrix又称市场增长率-相对市场份额矩阵、波士顿咨询集团法、四象限分析法、产品系列结构管理法等。

BCG矩阵区分出4种业务组合:

• 1.明星型业务（Stars，指高增长、高市场份额）

• 2.问题型业务（Question Marks，指高增长、低市场份额）

• 3.现金牛业务（Cash cows，指低增长、高市场份额）

• 4.瘦狗型业务（Dogs，指低增长、低市场份额）

波士顿矩阵通过销售增长率（反映市场引力的指标）和市场占有率（反映企业实力的指标）来分析决定企业的产品结构。

案例

• 效果图

• 实现步骤

本案例以分析客户为背景，将客户分类，找到明星客户、现金牛客户、问题客户以及瘦狗客户。

假设数据集的样式如下：

| 客户类型 | 客户名称 | 消费金额 | 消费日期 |

| — | — | — | — |

| A类 | 上海****公司 | 20000 | 2020-05-30 |

• 首先需要计算客单价：每个客户的平均消费金额，即客单价=某客户总消费金额)/某客户消费次数

• 其次需要计算记录数：每个客户的消费次数，即某个客户总共消费的次数

• 接着需要计算平均消费金额：所有客户的平均消费金额，即所有客户的总消费金额/所有客户消费次数

• 最后计算平均消费次数：所有客户的平均消费次数，即所有客户的总消费次数/总客户数

具体SQL实现：

SELECT

customer_name, – 客户名称

customer_avg_money, – 客单价

customer_frequency , – 当前客户的消费次数

total_avg_money,-- 所有客户的平均消费金额

total_frequency / count(*) over() AS total_avg_frequency – 平均消费次数

FROM

(SELECT

customer_name, – 客户名称

max(customer_avg_money) AS customer_avg_money, – 客单价

max(customer_frequency) AS customer_frequency , – 当前客户的消费次数

max(total_avg_money) AS total_avg_money,-- 所有客户的平均消费金额

max(total_frequency) AS total_frequency --所有客户的总消费频次

FROM

(

SELECT

customer_name, – 客户名称

avg(money) over(partition BY customer_name) AS customer_avg_money, – 客单价

count(*) over(partition BY customer_name) AS customer_frequency, – 当前客户的消费次数

avg(money) over() AS total_avg_money,-- 所有客户的平均消费金额

count(*) over() AS total_frequency --所有客户的总消费频次

FROM customer_sales ) t1

GROUP BY customer_name) t2

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

) t1

GROUP BY customer_name) t2

[外链图片转存中…(img-dfBXIcHf-1715805871648)]
[外链图片转存中…(img-ZjMn4eiV-1715805871648)]
[外链图片转存中…(img-q2tUfI2K-1715805871648)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取