[數據分析#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 – 準備四個欄位:時間、訂單編號、用戶編號、每張訂單金額

RFM(1)欄位

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

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

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

RFM(4)用戶分數

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(1)欄位轉換

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

NES(2)NES模型計算

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

顧客分群 模型三:Cohort Analysis

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

Cohort留存率(最終趨近穩定)

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

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

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

Cohort(1)欄位
Cohort(1)欄位轉換補充教學

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

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

Cohort(2)Pivot
Cohort(3)Pivot%

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

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

Google Sheet 範例


點我拷貝 Google Sheet

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

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

Goole Sheet 延伸學習

【1 對 1 免費諮詢】數據分析師 0~1 培訓班陪你一路到轉職成功!

hi 我是 Lisa

你也是 0 經驗、沒有相關背景,但也想要轉職數據分析師,或運用數據分析,提升在領域的專業度嗎?

-

你是否也覺得,數據分析的網路學習資源很多,但是多到不知道從哪裡開始?

學了很多工具,但學完了,問題才真正開始,不知道業界怎麼使用?又該如何面臨工作跟面試?

-

我在 2019 年從營運專員的職位轉職到數據分析師

在這之後,因為數據分析,也讓我快速建構不同領域的知識:行銷、營運、商業策略、產品、網頁

轉職過程中,花了很多時間成本摸索

我發現,學數據分析最快的方式是:

  1. 確認自己跟市場需求之間的差距:訂定轉職策略及了解數據分析師的工作方式
  2. 以業界應用的角度學習工具:包括數據工具及分析用思維架構,並且如何高效運用
  3. 建立一個商業上的真實專案,並且得到回饋:打造屬於你及貼合職缺需求的作品集和專案
  4. 打造一個高效履歷跟面談:透過高價值包裝術拿下面試、成功獲得數據行業的門票

當初如果我有這套系統思維,應該可以在短時間內轉職,馬上拿到進入業界的門票

-

現在,我想把這套完整課程分享給你

這是專門為了 0 經驗、沒有相關背景的轉職者所設計

不需要去考研究所、不需要花大錢準備出國讀書

可以用最快的時間準備轉職、拿到門票!

-

如果你正在準備轉職數據分析師,且試圖找過解決辦法卻始終治標不治本!歡迎跟我預約免費 1 對 1 諮詢,我會在諮詢時,了解你的需求並且評估我是否可以協助到你!

電子書 職場人必學的數據分析術

Lisa Wu

Hi 大家好!我是喜歡探索身心靈健康及平衡的數據分析師 Lisa Wu!這個網站會幫助你:利用數據分析 (理性) 及 身心靈平衡 (感性) 的系統,去達成人生目標!如果你對我的人生系統工具有興趣,歡迎聯繫我:couplehonest@gmail.com

This Post Has 3 Comments

Comments are closed.