在電商工作三年，誤打誤撞接觸會員經營領域也已接近一年，期間也懵懵懂懂的建立起一些相關行銷活動。最近抱持著"我是不是有漏掉什麼"的心態，拜讀了<MARTECH經營大數據會員行銷>這本書，看完一方面感嘆自己還有很多事情可以做，另一方面也想藉著這本書提到關於會員經營的切角，結合自己工作上面的實戰經驗做一些紀錄。
“剛進入一間公司，對這裡的會員一無所知，該從哪裡下手? “
如果你跟我有一樣的疑問，這時可以就下列的項目一一進行探索，而這些通常也會是老闆想問你的問題!
RFM模型: 打造分眾行銷的基礎
RFM是一種常見於分析顧客價值和行為的模型，對於找出高價值的用戶、將手上的用戶分群進行分眾行銷很有幫助，下面來一一介紹!
R(Recency): 指的是"最近一次購買時間"，但通常會轉換成"最近訂單距今天數"比較好量化。我們可以試著用SQL這樣撈取(假設你已經有一張表叫'order_database'，包含會員編號、訂單編號、訂單日期等數據):
WITH RFM_database AS (
SELECT
`會員編號`,
`訂單編號`,
`日期`,
ROW_NUMBER() OVER(PARTITION BY `會員編號` ORDER BY `日期` DESC, `訂單編號` DESC) AS `Row_num`
--透過 Fetching 函數幫每一個會員的訂單編號，日期越近的號碼越小
FROM order_database
),
Recent_order AS (
SELECT
`會員編號`,
`訂單編號`,
`日期`
FROM RFM_database
WHERE `Row_num` = 1 --最近的日期編碼 = 1
)
SELECT
`會員編號`,
`日期`,
TIMESTAMP_DIFF(CURRENT_DATE(), `日期`, DAY) AS `最近訂單距今天數` --跟今天日期相減
FROM Recent_order
ORDER BY `最近訂單距今天數` DESC;OK~ 現在得到了每個會員的最近訂單距今天數，但這個數字能幹嘛呢? 我們可以用他來判斷會員的活躍程度。這可以根據你的產業而定，主要取決於消費週期長短。比方3C類型的可能長一點，FMCG可能就短一些。以下用90天當分界判斷用戶的活躍程度:
...
Recency AS (
SELECT
`會員編號`,
`日期`,
TIMESTAMP_DIFF(CURRENT_DATE(), `日期`, DAY) AS `最近訂單距今天數`
FROM Recent_order
ORDER BY `最近訂單距今天數` DESC) --延續剛剛撈出內容
SELECT
DISTINCT `會員編號`,
CASE WHEN `最近訂單距今天數`<=90 THEN '高活躍' --用90天當分界來判斷用戶的活躍程度
WHEN `最近訂單距今天數`BETWEEN 91 AND 180 THEN '中活躍'
WHEN `最近訂單距今天數`BETWEEN 181 AND 360 THEN '低活躍'
ELSE '流失客' END AS `R分類`
FROM Recency
讚! 這樣一來，我們已經可以回答像是"所有的會員中，有多少比例用戶是活躍的?" 或是 "有哪些用戶可能流失?" 這類的問題。
F(Frequency): 指的是"消費頻率"，我當初看到頻率這個詞，原本以為是要算"用戶每一筆訂單時間差的平均值"，比方說 訂單A >(30天後) 訂單B > (60天後)訂單C，消費頻率就是 (30+60)/2 = 45天/次 (但後來發現，這比較接近購買週期的概念); 但實際上，不管在S電商還是P電商，用到的都比較像是"一段時間內購買產品或服務的次數"，比方說一年累積買12次(=平均每個月買一次)。
概念上會有一點點不一樣，比方說他一年買12次，有可能每個月買1單，但也有可能一個月買12單。不過不管如何，都是拿來回答用戶是否頻繁購買(是否為重度使用者)的指標。
以剛剛提到的”一段時間內購買產品的次數”的概念，試著用SQL來撈取手上用戶過去360天累積下單次數:
SELECT
`會員編號`,
COUNT(DISTINCT `訂單編號`) AS `年累積訂單數` --計算每個用戶不重複訂單編號的加總
FROM order_database
WHERE `日期` BETWEEN CURRENT_DATE()-360 AND CURRENT_DATE()-1 --只看過去360天
GROUP BY `會員編號`接著我們可以用年買家/季買家/月買家來分類看看:
WITH RFM_database AS (
SELECT
`會員編號`,
COUNT(DISTINCT `訂單編號`) AS `年累積訂單數`
FROM order_database
WHERE `日期` BETWEEN CURRENT_DATE()-360 AND CURRENT_DATE()-1
GROUP BY `會員編號`
)--延續剛剛撈出內容
SELECT
DISTINCT `會員編號`,
CASE WHEN `年累積訂單數`= 0 THEN '無訂單'
WHEN `年累積訂單數`= 1 THEN '年買家' -- 1年平均買1次就叫他年買家，以此類推
WHEN `年累積訂單數`BETWEEN 2 AND 4 THEN '季買家'
WHEN `年累積訂單數`BETWEEN 5 AND 12 THEN '跨月買家'
ELSE '月買家' END AS `F分類`
FROM RFM_database透過上面簡單的計算，我們就可以回答”我們平均每個月就下一次單的用戶多不多阿?” 或是 “我們的用戶過去一年只買過一單的比例高嗎?” 這類的問題。
M(Monetary): 指的是”營收貢獻”，也有幾種計算方式，但通常不外乎是用"一段時間內累積的銷售額、毛額"或"客單價(銷售額/訂單數)"。話不多說，我們一樣試著用SQL來撈取手上用戶過去360天累積的銷售額、毛額、客單價。
SELECT
`會員編號`,
COUNT(DISTINCT `訂單編號`) AS `年累積訂單數`,
SUM(`銷售額`) AS `年累積銷售額`,
SUM(`毛額`) AS `年累積毛額`,--如果數據上沒有，就要自己計算(=商品銷售額-商品成本-行銷成本)
ROUND(SUM(`折後銷售額`) / COUNT(DISTINCT `訂單編號`),0) AS `客單價`,--銷售額/訂單數
FROM order_database
WHERE `日期` BETWEEN CURRENT_DATE()-360 AND CURRENT_DATE()-1
GROUP BY `會員編號`,`最近訂單距今天數`)一樣，我們可以幫他們分類，但分界怎麼抓呢? 通常可以先看一下用戶累積銷售額(或毛額/客單價)的分布情形來判斷，或是以平均值、中位數來判斷用戶的銷售額是相對高還是相對低(註1，可見補充文件)，抓個80百位數也蠻有說服力的(82法則!)。下面是一個簡單的例子:
WITH RFM_database AS (
SELECT
`會員編號`,
COUNT(DISTINCT `訂單編號`) AS `年累積訂單數`,
SUM(`銷售額`) AS `年累積銷售額`,
SUM(`毛額`) AS `年累積毛額`,
ROUND(SUM(`折後銷售額`) / COUNT(DISTINCT `訂單編號`),0) AS `客單價`,
FROM order_database
WHERE `日期` BETWEEN CURRENT_DATE()-360 AND CURRENT_DATE()-1
GROUP BY `會員編號`,`最近訂單距今天數`)--延續剛剛撈出內容
SELECT
DISTINCT `會員編號`,
CASE WHEN `年累積銷售額`<= 10000 THEN '低貢獻' --假設算出來平均值是10,000
WHEN `年累積銷售額`BETWEEN 10001 AND 50000 THEN '中貢獻'
WHEN `年累積銷售額`BETWEEN > 50000 THEN '高貢獻' -- 然後 80 百分位是 50,000
ELSE '無累積訂單銷額' END AS `M分類`
FROM RFM_database如此一來，我們可以回答”高貢獻用戶佔全用戶多少比例?” 或是 “高貢獻用戶貢獻多少我們的總銷售額?” 這類的問題。
現在你已經掌握了用戶有關RFM（Recency, Frequency, Monetary）的基本樣貌，接下來就組合時間!
經過剛剛的努力，我們應該已經知道每一個用戶的RFM狀態(偷偷將Frequency調整成三個高中低分類):
...
R_table AS( --延續剛剛撈出 R(Recency) 內容
SELECT
DISTINCT `會員編號`,
CASE WHEN `最近訂單距今天數`<=90 THEN '高活躍'
WHEN `最近訂單距今天數`BETWEEN 91 AND 180 THEN '中活躍'
WHEN `最近訂單距今天數`BETWEEN 181 AND 360 THEN '低活躍'
ELSE '流失客' END AS `R分類`
FROM Recency
)
,
F_table AS ( --延續剛剛撈出 F(Frequency) 內容
SELECT
DISTINCT `會員編號`,
CASE WHEN `年累積訂單數` BETWEEN 1 AND 4 THEN '低頻率'
WHEN `年累積訂單數` BETWEEN 4 AND 11 THEN '中頻率'
WHEN `年累積訂單數` >=12 '高頻率'
ELSE '無訂單' END AS `F分類`
FROM RFM_database
)
,
M_table AS ( --延續剛剛撈出 M(Monetary) 內容
SELECT
DISTINCT `會員編號`,
CASE WHEN `年累積銷售額`<= 10000 THEN '低貢獻'
WHEN `年累積銷售額`BETWEEN 10001 AND 50000 THEN '中貢獻'
WHEN `年累積銷售額`BETWEEN > 50000 THEN '高貢獻'
ELSE '無累積訂單銷額' END AS `M分類`
FROM RFM_database
)
,
RFM_tag AS (SELECT
`會員編號`,
`R分類`,
`F分類`,
`M分類`,
CONCAT(`R分類`, '-', `F分類`, '-', `M分類`) AS `RFM分類`
FROM RFM_database
LEFT JOIN R_table USING(`會員編號`)
LEFT JOIN F_table USING(`會員編號`)
LEFT JOIN M_table USING(`會員編號`)
) --幫所有用戶貼上RFM標籤
SELECT
`RFM分類`,
COUNT(DISTINCT`會員編號`)AS`會員數` --算出每一個RFM分群有多少人
FROM RFM_tag
GROUP BY `RFM分類`恭喜你! 如此一來，你就可以輕鬆回答"高活躍高頻率高貢獻"，這種三高用戶到底是哪些人了! 下一篇會再繼續介紹作者提到三個顧客變數的後兩者 4P-TCC!