中文字幕无码av不卡一区,亚洲综合AV永久无码精品一区二区,黑人巨大白妞出浆,黄色高清无码免费看,久久久久免费精品国产,久久无码人妻丰满熟妇区毛片,欧美日韩国产综合草草,久久福利网站,亚洲一区二区三区中文字幕在线,av国内精品久久久久影院

在產品運營中，我們經常需要將用戶進行分層，以便更好針對性做運營策略。本文分享了如何用SQL結合RFM模型，對用戶進行分層的方法，供大家參考學習。

RFM模型通常用于分析用戶數據庫，以識別最有價值的用戶。

Recency (R)– 用戶最后一次購買的時間。距離現在時間越短，用戶再次購買的可能性越大。Frequency (F)-用戶在一定時間內購買的次數。頻率越高，表明用戶對品牌的忠誠度越高。Monetary (M)-用戶在一定時間內為公司帶來的總收益。金額越高，表明用戶的價值越大。

通過RFM模型，企業可以對用戶進行細分，比如將用戶分為高價值用戶、需要挽留的用戶、有潛力的用戶等，然后根據這些細分采取不同的營銷策略。

作為產品經理如何使用SQL計算RFM模型，對用戶進行分層呢？

一、數據源準備

用戶會員表數據

訂單表數據(部分字段)

因 MySQL 性能問題，我們將數據通過Binlog訂閱同步到 Hive 進行計算；

而、數據計算

2.1、RFM模型的計算步驟如下：

確定時間范圍：首先確定分析的時間范圍，比如過去一年或過去六個月。

這里我們使用

AND TO_DATE(o.SOCreateTime) >= ‘2024-04-01′
AND TO_DATE(o.SOCreateTime) <=’2024-06-30’

收集數據：收集客戶在所選時間范圍內的所有交易記錄。

SELECT m.mimemberid AS memberid,
MAX(o.socreatetime) AS last_order_time,
DATEDIFF(‘2024-07-01’, MAX(o.socreatetime)) AS R,
COUNT(o.soordersn) AS F,
SUM(o.sototalamount) AS M
FROM ods_travel.v_teschoolinnermarket_memberinfo m
LEFT JOIN paimon.fts_base_tetravelrvsorder.schoolorder o
ON m.mimemberid = o.somemberid
WHERE o.SOPayStatus = 2
AND m.MIStatus = 0
AND TO_DATE(o.SOCreateTime) >= ‘2024-04-01’
AND TO_DATE(o.SOCreateTime) <= ‘2024-06-30’
GROUP BY m.mimemberid
ORDER BY R ASC;

計算Recency (R)：

• 對于每個客戶，找出最后一次購買的日期。
• 計算從最后一次購買到當前日期的天數或月數。

計算Frequency (F)：

• 對于每個客戶，計算在所選時間范圍內的購買次數。

計算Monetary (M)：

• 對于每個客戶，計算在所選時間范圍內的總購買金額。

DROP TABLE IF EXISTS adsxyt_travel.userrfm;
CREATE TABLE adsxyt_travel.userrfm
STORED AS ORC AS
WITH mada_order_num AS (
SELECT a.SOMemberId AS memberid, COUNT(*) AS ordernum
FROM paimon.fts_base_tetravelrvsorder.SchoolOrder a
INNER JOIN paimon.fts_base_tetravelrvsorder.SchoolOrderExpand b ON a.SOOrderSn = b.SOEOrderSn
WHERE a.SOPayStatus = 2
AND TO_DATE(a.SOCreateTime) >= ‘2024-04-01’
AND TO_DATE(a.SOCreateTime) <= ‘2024-06-30’
GROUP BY a.SOMemberId
),
base_data AS (
— 查詢最原始的RFM值
SELECT m.mimemberid AS memberid, MAX(o.socreatetime) AS last_order_time
, DATEDIFF(‘2024-07-01’, MAX(o.socreatetime)) AS R
, COUNT(o.soordersn) AS F, SUM(o.sototalamount) AS M
FROM ods_travel.v_teschoolinnermarket_memberinfo m
LEFT JOIN paimon.fts_base_tetravelrvsorder.schoolorder o ON m.mimemberid = o.somemberid
WHERE o.SOPayStatus = 2
AND m.MIStatus = 0
AND TO_DATE(o.SOCreateTime) >= ‘2024-04-01’
AND TO_DATE(o.SOCreateTime) <= ‘2024-06-30’
GROUP BY m.mimemberid
),
quartiles AS (
— 按照數據的4分位數計算RFM得分
SELECT *, NTILE(4) OVER (ORDER BY R) AS R_score
, NTILE(4) OVER (ORDER BY F DESC) AS F_score
, NTILE(4) OVER (ORDER BY M DESC) AS M_score
FROM base_data
),
quartiles_fixed AS (
— 四分位數修正
SELECT *
, CASE
WHEN R_score = 1 THEN 4
WHEN R_score = 2 THEN 3
WHEN R_score = 3 THEN 2
ELSE 1
END AS R_score_fixed
, CASE
WHEN F_score = 1 THEN 1
WHEN F_score = 2 THEN 2
WHEN F_score = 3 THEN 3
ELSE 4
END AS F_score_fixed
, CASE
WHEN M_score = 1 THEN 1
WHEN M_score = 2 THEN 2
WHEN M_score = 3 THEN 3
ELSE 4
END AS M_score_fixed
FROM quartiles
),
means AS (
SELECT AVG(R_score_fixed) AS r_mean, AVG(F_score_fixed) AS f_mean
, AVG(M_score_fixed) AS m_mean
FROM quartiles_fixed
)
SELECT qf.memberid, mc.ordernum, qf.last_order_time, qf.R, qf.F
, qf.M, qf.R_score_fixed AS R_score, qf.F_score_fixed AS F_score, qf.M_score_fixed AS M_score, m.r_mean
, m.f_mean, m.m_mean
, CASE
WHEN R_score_fixed > m.r_mean THEN ‘高’
ELSE ‘低’
END AS R_label
, CASE
WHEN F_score_fixed > m.f_mean THEN ‘高’
ELSE ‘低’
END AS F_label
, CASE
WHEN M_score_fixed > m.m_mean THEN ‘高’
ELSE ‘低’
END AS M_label
FROM quartiles_fixed qf
CROSS JOIN means m
LEFT JOIN order_num mc ON qf.memberid = mc.memberid
ORDER BY qf.R ASC;

通過一系列公共表表達式（CTEs）構建了一個RFM（最近購買行為、購買頻率、購買金額）分析模型，用于對會員進行分類。首先，它計算了每個會員在指定時間段內的訂單數量、最后下單時間、以及基于這些數據的RFM原始值。接著，通過四分位數方法為每個RFM值分配得分，并進行修正以確保得分與會員價值正相關。然后，計算這些得分的平均值，用于確定每個會員的RFM標簽（高或低）。最后，結合這些標簽和訂單數量，對會員進行分類，并按最近購買行為進行排序。

為RFM打分：

• 將R、F、M的值分別進行標準化或歸一化，以便于比較。例如，可以使用排名或百分比來為每個維度打分。
• Recency可以按照時間從近到遠進行排序，然后分配分數，時間越近分數越高。
• Frequency可以按照購買次數從多到少進行排序，然后分配分數，購買次數越多分數越高。
• Monetary可以按照總金額從高到低進行排序，然后分配分數，金額越高分數越高。

綜合RFM得分：

• 將R、F、M的分數相加，得到每個客戶的RFM總分。
• 根據總分將客戶分為不同的群體，如高價值客戶、需要挽留的客戶、低價值客戶等。

分析和應用：

• 分析不同RFM群體的特征，制定相應的營銷策略。
• 例如，對于高RFM得分的客戶，可以提供忠誠度獎勵或個性化服務；對于低RFM得分的客戶，可以設計促銷活動以提高其購買頻率和金額。

四等位數法

其中使用了四分位數，是統計學分位數中的一種，把所有數值從低到高（或者從高到底）排列并分成四等份，處于三個分割點位置的數值就是四分位數。
一般表示為：
Q1：樣本排列中處于25%位置的數字；
Q2：又稱為中位數，指的是樣本排列中處于50%位置，即中間位置的數據；
Q3：樣本排列中處于75%位置的數字。

假設樣本數據項數一共是N：
則Q1的位置數值=（N+1）/4；
Q2的位置數值=（N+1)/2；
Q3的位置數值=3（N+1)/4。
如果（N+1）恰好是4的倍數，則確定四分位數比較簡單，如果不是4的倍數，相關位置的四分位數就應該是相鄰兩個數值的標志值的平均數。權數的大小取決于兩個數值距離的遠近，距離越近權數越大，距離越遠，權數越小，權數之和等于1。

DROP TABLE IF EXISTS adsxyt_travel.userrfmcategory;
CREATE TABLE adsxyt_travel.userrfmcategory
STORED AS ORC AS
SELECT memberid, mada_ordernum, last_order_time, R, F, M
, R_score, F_score, M_score, r_mean, f_mean
, m_mean, R_label, F_label, M_label
, CASE
WHEN R_label = ‘高’
AND F_label = ‘高’
AND M_label = ‘高’
THEN ‘重要價值用戶’
WHEN R_label = ‘高’
AND F_label = ‘低’
AND M_label = ‘高’
THEN ‘重要發展用戶’
WHEN R_label = ‘低’
AND F_label = ‘高’
AND M_label = ‘高’
THEN ‘重要保持用戶’
WHEN R_label = ‘低’
AND F_label = ‘低’
AND M_label = ‘高’
THEN ‘重要挽留用戶’
WHEN R_label = ‘高’
AND F_label = ‘高’
AND M_label = ‘低’
THEN ‘一般價值用戶’
WHEN R_label = ‘高’
AND F_label = ‘低’
AND M_label = ‘低’
THEN ‘一般發展用戶’
WHEN R_label = ‘低’
AND F_label = ‘高’
AND M_label = ‘低’
THEN ‘一般保持用戶’
WHEN R_label = ‘低’
AND F_label = ‘低’
AND M_label = ‘低’
THEN ‘一般挽留用戶’
ELSE ‘未分類’
END AS user_category
FROM adsxyt_travel.userrfm;

• 使用CASE語句根據R、F、M的標簽值（’高’或’低’）來確定用戶類別。這些標簽可能代表了用戶在最近性（Recency）、頻率（Frequency）、貨幣價值（Monetary）三個方面的表現。
• user_category是根據R、F、M的標簽值組合來定義的用戶類別，如“重要價值用戶”、“重要發展用戶”等。（參照上述表格）

三、數據結果

敏感數據不做暫時，本文提供 SQL 計算解決思路，具體可參照實驗。