使用Mysql建立RFM模型，由于图片使用国外CDN，首次打开本页面会有加载时间

使用Mysql对海外电子产品用户购买数据建立RFM模型

博客挂载于Github，图片挂载于SMMS图床，首次打开请静候一分钟时间加载
使用的2020年海外电子产品用户购买数据集来源于kaggle社区，

1.观测数据

观测数据发现，用户id、下单金额字段出现了大量的null值，且数据集中存在大量下单时间为1970-01-01 00:33:40的错误数据

为了在下面步骤更加方便，使用create table语句建表并清洗数据

清洗数据并建表

建表后的数据，通过观察已经是正常的了

查看数据集的时间范围

查看数据集行数

代码如下

-- 查看数据集的下单时间范围
SELECT
MAX(`下单时间`)
,MIN(`下单时间`)
FROM rfm_data
-- 返回2020-01-05到2020-11-21，则R的时间间隔计算基准为11-22

-- 查看数据集各行行数
SELECT COUNT(*)
,COUNT(`下单金额`)
,COUNT(`下单时间`)
,COUNT(`用户id`)
FROM rfm_data
-- 返回结果一致

-- 通过sum嵌套ISNULL函数对三个字断的null值进行计数
SELECT
SUM(ISNULL(`下单时间`))
,SUM(ISNULL(`下单金额`))
,SUM(ISNULL(`用户id`))
FROM rfm_data
-- 返回结果都是0

-- 统计用户人数并根据二八定律区分高分用户
SELECT
COUNT(DISTINCT `用户id`
,round(COUNT(DISTINCT `用户id`)*0.2,0) 用户数
FROM rfm_data
-- 返回用户数为233576,高分用户为用户中前46715位

处理后的数据集无空值，可以进行下一步操作了

2.建立RFM模型

2.1建立分类阈值，以便后续对用户行为评分

计算F的高分阈值

计算R的高分阈值

计算M的高分阈值

代码如下

-- 使用with as 临时表方法
WITH a AS
(
    select 
    `用户id`
    ,TIMESTAMPDIFF(day,max(`下单时间`),'2020-11-22') R
    ,COUNT(*)F
    ,ROUND(SUM(`下单金额`),2)M
    from rfm_sql.rfm_data
    group by 1
)
SELECT
R  
FROM a
ORDER BY 1 ASC # 其中在计算F与M的分类阈值时使用DESC降序
LIMIT 46715,1

-- 最终计算得出，R的分类阈值为27，F为3，M为732.42

2.2根据返回的分类阈值，对用户评分并建立RFM模型

初步建立RFM模型

用户RFM三个维度的评分，即下方代码临时表b

建表代码如下

-- 使用create table as 嵌套with as方法建表，建立RFM模型
CREATE TABLE rfm as
-- 建立临时表a为计算各用户R、F、M三个维度的值
WITH a AS
(
    select 
    `用户id`
    ,TIMESTAMPDIFF(day,max(`下单时间`),'2020-11-22') R
    ,COUNT(*)F
    ,ROUND(SUM(`下单金额`),2)M
    from rfm_sql.rfm_data
    group by 1
)
-- 建立临时表b为用户的R、F、M三个维度进行评分
,b as 
(
    select `用户id`
    ,case when R <=27 then '高' else '低' end R
    ,case when F >= 3 then '高' else '低' end  F
    ,case when M >= 732.42 then  '高' else '低' end M
    from a
)
-- 建立临时表c，为最终的RFM模型，至此即可为业务方提供数据支持
,c AS
(
    select *, 
    (case when M = '高' then '重要' else '一般' end ) as 重要度,
    (case when F = '高' and R = '高' then '价值客户'
    when F = '高' and R = '低' then '唤回客户'
    when F = '低' and R = '高' then '发展客户'
    when F = '低' and R = '低' then '挽留客户' end) as 类型
    from b
)
SELECT * FROM c