[數據分析#13] (RFM/NES/Cohort) Google Sheet/Excel CRM 模型範本

之前在上一篇文章 CRM 案例實作中提到幾個常見顧客分群使用的模型，包括 RFM、NES、Cohort Analysis，這三個主要的數據分析模型都可以利用 Google Sheet/Excel 來完成，這邊會帶大家從了解模型為我們帶來的資訊、準備數據，一直到使用我做好的 Google Sheet 範本來實作一次！(底下有提供範本下載喔！)

如何利用數據分析，為顧客分群？

當我們要快速了解顧客的時候，有一個比較快的方式就是利用指標 (量化的指標) 來定義每個客戶，然後再把指標數字相似的顧客分成一群，接著再去深入瞭解業務上每個執行的策略是如何影響每一群人。

因此，這些模型並沒有辦法為我們得到問題的解答 (問題：如何讓顧客的消費頻次變高？如何讓客戶的留存率降低？)，但是模型可以帶我們用很快的速度了解現況。

雖然這些模型沒辦法為我們帶來答案，但是可以當作驗證策略的工具。例如說，我們可以利用自己對業務的了解，把指標變多，或者依照不同渠道、策略、產品，都製作一個模型，以便對每個策略進行驗證 (驗證哪個銷售渠道比較有效？驗證哪個產品流存率比較高？)

顧客分群 模型一：RFM

RFM 的關鍵指標組成？

RFM 透過三個關鍵指標 (R – 最近互動時間/F – 購買頻次/M – 購買金額) 為每個用戶衡量他對我們的價值。所以每個用戶都會拿到三個分數，最後可以加起來變成總分，或者是將不同指標乘上不同權重 (依照在乎程度)，變成總分。這是喬治·卡利南 (George Cullinan) 在實驗中發現，有利於評估顧客價值的三大指標，於 1961 年所提出，到現在還是能夠使用，只要稍微變形一下，基本上不論零售、電商、軟體業都能使用。

RFM 模型分析步驟 (以電商資料為例)

1. Step1 – 準備四個欄位：時間、訂單編號、用戶編號、每張訂單金額

2. Step 2 – 將數據處理成模型所需的維度：用戶、第一次下單時間、最後一次下單時間、購買訂單數、訂單總金額

3. Step 3 – 衡量每個人的 R、F、M 分別可以得到幾分：可以利用分數的分佈，來決定 1-5 分的標準應該如何給？-> 這個步驟是模型中的精髓，也是一個可以得知用戶分佈的好機會 (可以知道用戶都是高單價居多？還是低單價？等等)

4. Step 4 – 根據 Step 3. 中的分數來為每個買家貼標，貼標後，可以利用簡單的統計函數 (不同分數的人數占比) 來快速了解顧客的行為

5. Step 5 – 可以利用簡單的統計函數 (不同分數的人數占比) 來快速了解顧客的行為

顧客分群 模型二：NES

如何利用購買週期來定義 NES 用戶？

NES 模型的核心價值是以用戶的活躍度為用戶分群。NES 是由：New 新用戶、Existing 活躍用戶、Sleeping 流失用戶組成。

• 新用戶 (N)：第一次購買，或是只夠買過一次的用戶
• 活躍用戶 (E)：在正常購買週期內的用戶
• 流失用戶 (S)：在購買週期 1 倍以上的用戶

NES 模型可以運用在名單型行銷：例如電子報行銷、推播通知上。我們可以在流失用戶 1 倍購買週期時就提醒他們回來，避免流失的時間越長越難叫回來。

NES 模型分析步驟 (以電商為例)

1. Step 1 – 準備欄位：用戶、每次下單時間
2. Step 2 – 將數據處理成模型所需要的維度：用戶、下單次數、平均下單週期、最後一次下單離現在的天數、最後一次下單離現在的天數/平均下單週期 (會得到一個倍數)
3. Step 3 – 將用戶依照自己的平均購買週期分群：
   • 下單次數 = 1 -> 新用戶 (應該盡快讓他達到 aha moment)
   • 最後一次下單離現在的天數/平均下單週期 < 0 -> 活躍用戶
   • 最後一次下單離現在的天數/平均下單週期 > 1 -> 流失用戶
     • 流失用戶中又可以分倍數小到大
4. Step 4 – 計算活躍用戶的下次購買時間
   • 計算活躍用戶(E)/新用戶(N) 的下次購買時間 -> 如果沒買，可以對他激活
   • 計算流失用戶(S) 的流失倍數，在倍數還小的時候對他做激活

• NES 模型步驟一中需要準備用戶、下單時間，接下來的資料處理可以用 Google Sheet 公式算出每一次下單的下單週期 (後一次減前一次的天數)

• 從步驟二開始，就要把步驟一中準備好的資料轉成我們需要的用戶維度：包括用戶、下單次數、平均購買週期 (將每一次的週期做平均)、最後一次購買離現在的天數，還有倍數 (最後一次購買離現在的天數/平均購買週期)
• 接著依據倍數幫每個用戶貼標 (N or E or S？)
• 然後可以用最後一次購買時間跟平均購買週期算出下一次可能的下單時間

顧客分群 模型三：Cohort Analysis

利用 Cohort Analysis 找到產品最終的留存率？

Cohort Analysis 的核心是了解用戶的留存狀況，透過對每個時期新進的用戶做每個月的留存分析，也可以看出當時間拉長了，最後留下來的人會有多少，就可以看出產品的健康度。

Cohort Analysis 分析步驟 (以電商為例)

1. Step 1 – 準備欄位：這邊需要先將數據處理過變成，依照加入月份先幫客戶分群，最後會呈現：加入月份、月份、活躍人數、初期人數

• (補充教學) 也可以準備用戶、下單時間，在 Google Sheet 中整理成上面所需欄位 (這部分我會在 Google Sheet 中說明如何處理)

1. Step 2 – 利用樞紐分析表，做出直欄為每個月加入的人數，橫欄為依據每個月加入的人數再去看他後面逐月的活躍人數，就可以看出來每個期間帶來的新戶是否是有價值且健康的用戶

3. Step 3 – 尋找幾個關鍵數據：

• 找到產品最終的留存率
• 每個月份進來的新用戶的留存狀況
• 也可以將模型拆成不同渠道、不同產品屬性有不同的 cohort 圖表

Google Sheet 範例

點我拷貝 Google Sheet

這三種是在做用戶分析的時候常用的輔助模型，分別從不同面向去定義用戶。RFM 從活躍、頻次跟花費來定義個別用戶價值，NES 從時間的角度來尋找即將要流失的用戶，Cohort 從一個群體的留存率來看整個產品的健康度。各有各有的用途，大家可以都看了之後 (至少對現況了解後)，再選擇最急迫的問題來解決！

如果你對於 CRM 案例有興趣，可以看看我的經歷，我有實際分享我做過的 CRM 專案！<延伸閱讀：[無私分享] CRM 案例實作 – 4個模型搭配科學化實驗方法實現用戶經營>

Goole Sheet 延伸學習

• <延伸閱讀：所有與資料分析相關的教學>
• <延伸閱讀：Google Sheet 相關教學>：由於這次的模型裡面會用到比較多進階的公式，包括Query、Filter、Arrayformula，因此附上詳細的教學文
  • Google Sheet Query 用法
  • Google Sheet Filter 用法
  • Google Sheet Arrayformula 用法