2021-08-02

RFM基本概念

关于RFM的基本概念大家可以参见我上一篇文章数据分析RFM模型 Python实现

mysql关键点

本次主要是分享通过mysql的方法实现RFM，进行分析前需要对数据集进行清洗，清洗数据尽量还是使用python，感觉效率会高一些，10W行的数据，简单的left join加分组运算，navicat300多秒都不止。

我这里只选择了200行数据做示例，原因是查询真的是太慢了。

但是mysql确实展现了它的优越性，很多计算直接一个sql就搞定了，相比python的步骤可能少了很多。

navicat操作

在图形界面，直接将3个数据集导入进来，字符集和字符比较规则都是utf8和常规。

导入数据之后，进行简单的预览，主要就是观察下字段。

SELECT * FROM cus LIMIT 10;
SELECT * from `orders`LIMIT 10;
SELECT * FROM pay LIMIT 10;

需要对cus、orders、pay3个表进行left join， 连接键分别为customer_id和order_id。这里我限制了200条数据，否则太慢了。

SELECT  cus.customer_unique_id, orders.order_id, orders.order_purchase_timestamp, pay.payment_value
FROM cus LEFT JOIN orders ON cus.customer_id = orders.customer_id LEFT JOIN pay ON pay.order_id = orders.order_id
LIMIT 200

在上面sql的基础上按customer_unique_id进行分组，求得每个用户最近一次距规定日期的下单时间间隔R、下单次数F、消费总金额M。

SELECT a.customer_unique_id, datediff('2018-09-01',max(a.order_purchase_timestamp)) AS R, COUNT(a.order_id) AS F, sum(a.payment_value) AS M
FROM
(
SELECT  cus.customer_unique_id, orders.order_id, orders.order_purchase_timestamp, pay.payment_value
FROM cus LEFT JOIN orders ON cus.customer_id = orders.customer_id LEFT JOIN pay ON pay.order_id = orders.order_id
LIMIT 200
) AS a
GROUP BY a.customer_unique_id

在以上基础中，求得总体用户R、F、M的平均值，分别为250、1和129。

SELECT AVG(R), AVG(F), avg(M)
FROM
(
SELECT a.customer_unique_id, datediff('2018-09-01',max(a.order_purchase_timestamp)) AS R, COUNT(a.order_id) AS F, sum(a.payment_value) AS M
FROM
(
SELECT  cus.customer_unique_id, orders.order_id, orders.order_purchase_timestamp, pay.payment_value
FROM cus LEFT JOIN orders ON cus.customer_id = orders.customer_id LEFT JOIN pay ON pay.order_id = orders.order_id
LIMIT 200
) AS a
GROUP BY a.customer_unique_id
) AS b;

接下来就是用每个用户R、F、M的数量于总体用户的均值相比较。由于我的mysql版本是5.7，无法使用窗口函数，所以就直接把均值写上去了。
这个步骤主要使用case when的函数进行评分，R大于均值得0分，小于均值得1分，F大于均值得1分，小于均值得0分，M大于均值得1分，小于均值得0分。

SELECT (case WHEN R > 250 THEN 0 ELSE 1 END) as R_score,
				(case when F > 1 THEN 1 ELSE 0 END) as F_score,
				(case when M> 129 then 1 else 0 end) as M_score
FROM
(
SELECT a.customer_unique_id, datediff('2018-09-01',max(a.order_purchase_timestamp)) AS R, COUNT(a.order_id) AS F, sum(a.payment_value) AS M
FROM
(
SELECT  cus.customer_unique_id, orders.order_id, orders.order_purchase_timestamp, pay.payment_value
FROM cus LEFT JOIN orders ON cus.customer_id = orders.customer_id LEFT JOIN pay ON pay.order_id = orders.order_id
LIMIT 200
) AS a
GROUP BY a.customer_unique_id
) AS b
GROUP BY customer_unique_id;

最后使用case when按照R、F、M的得分进行用户分组，这里做个示例。

SELECT customer_unique_id, (case when R_score =1 and F_score = 0 and M_score = 0 THEN '挽留'
				when R_score = 1 and F_score = 0 and M_score = 1 THEN '高价值'
				WHEN R_score = 0 and F_score = 0 and M_score = 0 THEN '流失'
				ELSE 'other' END) as cus_value
FROM
(
SELECT customer_unique_id, (case WHEN R > 250 THEN 0 ELSE 1 END) as R_score,
				(case when F > 1 THEN 1 ELSE 0 END) as F_score,
				(case when M> 129 then 1 else 0 end) as M_score
FROM
(
SELECT a.customer_unique_id, datediff('2018-09-01',max(a.order_purchase_timestamp)) AS R, COUNT(a.order_id) AS F, sum(a.payment_value) AS M
FROM
(
SELECT  cus.customer_unique_id, orders.order_id, orders.order_purchase_timestamp, pay.payment_value
FROM cus LEFT JOIN orders ON cus.customer_id = orders.customer_id LEFT JOIN pay ON pay.order_id = orders.order_id
LIMIT 200
) AS a
GROUP BY a.customer_unique_id
) AS b
GROUP BY customer_unique_id
) AS c

主要使用方法

left join,group by,datediff,max,count,sum,avg,case when
大部分都是基础的函数，没什么难度，可能只有case when会稍微费力一些

建议

面对10W的数据，进行left join后的查询速度极速下降，这个和python比感觉要若很多，但是在逻辑性上mysql的灵活行非常好，只要把顺序想清楚很快就可以做出用户分组，当然前提是数据已经完成了清洗。
最后就是拿着用户分组和业务部分聊聊具体的运营动作啦～多和其他部门交流没有坏处滴。