用Excel實(shí)現RFM模型用到的公式

0 評論 2384 瀏覽 13 收藏 6 分鐘

很多文章都是講解RFM模型的理論知識或者是流程,但在實(shí)際操作上涉及不多。本文分享啦一個(gè)臨時(shí)計算,通過(guò)excel工具計算RFM模型的相關(guān)數據,分享給大家。

注意,此方法僅僅是一次性的臨時(shí)計算,比較笨但是能快速實(shí)現。

背景:我想知道一年之內,所有會(huì )員的RFM情況,按照下面圖中的方法進(jìn)行分類(lèi),并計算數量。

一、會(huì )員的關(guān)鍵數據計算

第一步,獲取一年內所有訂單明細,此明細可以通過(guò)某參數(如會(huì )員ID)區分具體會(huì )員,用于之后計算總和。

第二步,計算R:最近一次消費的時(shí)間,今天-訂單日期,則為時(shí)間間隔,該表格個(gè)值可調整為“數值”。

公式:=TODAY()-A2

因為同一個(gè)會(huì )員ID可能有多個(gè)訂單,所以咱們要找到最近一次消費的時(shí)間間隔,才是真正的R:

公式:=MINIFS(F:F,C:C,C2)

第三步,計算F:一年內消費次數,這個(gè)比較好算,直接計算當前會(huì )員ID出現過(guò)幾次則是下了幾次單。

公式:=COUNTIF(C:C,C2)

第四步,計算M:一年內消費金額,直接計算當前會(huì )員ID每次訂單的金額總和。

公式:=SUMIF(C:C,C2,E:E)

第五步,以上幾個(gè)公式,把鼠標移動(dòng)到表格右下角,等光標變成黑色十字,雙擊,整列就都自動(dòng)計算出來(lái)了。

第六步,去重,同一個(gè)會(huì )員現在只需要保留一行就可以了,因為這幾個(gè)關(guān)鍵值是一樣的。

全選-數據-刪除重復項-只選擇能代表會(huì )員身份的唯一值(我這里就是ID了)-確定。(你要是怕數據會(huì )亂,可以復制到新的表里面再去重)

二、判斷RMF是1還是0

從這里開(kāi)始,判斷方法和大家自己的業(yè)務(wù)息息相關(guān),我這里隨便舉例子,大家可以根據實(shí)際情況改變公式。

第一步,判斷R。這里我根據最近一次消費小于30天來(lái)判斷他是一個(gè)R為1的會(huì )員。

公式:=IF(C2<=30,1,0)

第二步,判斷F。這里我希望前20%的會(huì )員才可以F為1,所以我要先計算他的排名。這里可以用百分比排位函數直接算出來(lái)(Excel大法好?。。?。

公式:=PERCENTRANK(D:D,D2)

排位出來(lái),F就很好判斷了,按照大小,80%~100%的才是排名前20%的會(huì )員(消費次數數值越大,越靠前)。

公式:=IF(I2>=0.8,1,0)

第三步,判斷M,這里我取大于1500元的總金額,代表M為1的會(huì )員。

公式:=IF(E2>=1500,1,0)

三、匯總各分類(lèi)人群數量

為了方便枚舉,我直接把三個(gè)值合到一起了。

公式:=C2&D2&E2

最后,直接用數據透視表:

全選數據-插入-數據透視表,默認都是放到新工作表里,直接點(diǎn)確定就行了。

字段可以參考我下圖選擇的,至此,我們已經(jīng)獲得了每種會(huì )員的數量,以及明細。

本文由 @PhoebeFeng 原創(chuàng )發(fā)布于人人都是產(chǎn)品經(jīng)理,未經(jīng)許可,禁止轉載

題圖來(lái)自 Unsplash,基于 CC0 協(xié)議

該文觀(guān)點(diǎn)僅代表作者本人,人人都是產(chǎn)品經(jīng)理平臺僅提供信息存儲空間服務(wù)。

更多精彩內容,請關(guān)注人人都是產(chǎn)品經(jīng)理微信公眾號或下載App
評論
評論請登錄
  1. 目前還沒(méi)評論,等你發(fā)揮!