用Excel實現(xiàn)RFM模型用到的公式
很多文章都是講解RFM模型的理論知識或者是流程,但在實際操作上涉及不多。本文分享啦一個臨時計算,通過excel工具計算RFM模型的相關(guān)數(shù)據(jù),分享給大家。
注意,此方法僅僅是一次性的臨時計算,比較笨但是能快速實現(xiàn)。
背景:我想知道一年之內(nèi),所有會員的RFM情況,按照下面圖中的方法進(jìn)行分類,并計算數(shù)量。
一、會員的關(guān)鍵數(shù)據(jù)計算
第一步,獲取一年內(nèi)所有訂單明細(xì),此明細(xì)可以通過某參數(shù)(如會員ID)區(qū)分具體會員,用于之后計算總和。
第二步,計算R:最近一次消費的時間,今天-訂單日期,則為時間間隔,該表格個值可調(diào)整為“數(shù)值”。
公式:=TODAY()-A2
因為同一個會員ID可能有多個訂單,所以咱們要找到最近一次消費的時間間隔,才是真正的R:
公式:=MINIFS(F:F,C:C,C2)
第三步,計算F:一年內(nèi)消費次數(shù),這個比較好算,直接計算當(dāng)前會員ID出現(xiàn)過幾次則是下了幾次單。
公式:=COUNTIF(C:C,C2)
第四步,計算M:一年內(nèi)消費金額,直接計算當(dāng)前會員ID每次訂單的金額總和。
公式:=SUMIF(C:C,C2,E:E)
第五步,以上幾個公式,把鼠標(biāo)移動到表格右下角,等光標(biāo)變成黑色十字,雙擊,整列就都自動計算出來了。
第六步,去重,同一個會員現(xiàn)在只需要保留一行就可以了,因為這幾個關(guān)鍵值是一樣的。
全選-數(shù)據(jù)-刪除重復(fù)項-只選擇能代表會員身份的唯一值(我這里就是ID了)-確定。(你要是怕數(shù)據(jù)會亂,可以復(fù)制到新的表里面再去重)
二、判斷RMF是1還是0
從這里開始,判斷方法和大家自己的業(yè)務(wù)息息相關(guān),我這里隨便舉例子,大家可以根據(jù)實際情況改變公式。
第一步,判斷R。這里我根據(jù)最近一次消費小于30天來判斷他是一個R為1的會員。
公式:=IF(C2<=30,1,0)
第二步,判斷F。這里我希望前20%的會員才可以F為1,所以我要先計算他的排名。這里可以用百分比排位函數(shù)直接算出來(Excel大法好?。。?/p>
公式:=PERCENTRANK(D:D,D2)
排位出來,F(xiàn)就很好判斷了,按照大小,80%~100%的才是排名前20%的會員(消費次數(shù)數(shù)值越大,越靠前)。
公式:=IF(I2>=0.8,1,0)
第三步,判斷M,這里我取大于1500元的總金額,代表M為1的會員。
公式:=IF(E2>=1500,1,0)
三、匯總各分類人群數(shù)量
為了方便枚舉,我直接把三個值合到一起了。
公式:=C2&D2&E2
最后,直接用數(shù)據(jù)透視表:
全選數(shù)據(jù)-插入-數(shù)據(jù)透視表,默認(rèn)都是放到新工作表里,直接點確定就行了。
字段可以參考我下圖選擇的,至此,我們已經(jīng)獲得了每種會員的數(shù)量,以及明細(xì)。
本文由 @PhoebeFeng 原創(chuàng)發(fā)布于人人都是產(chǎn)品經(jīng)理,未經(jīng)許可,禁止轉(zhuǎn)載
題圖來自 Unsplash,基于 CC0 協(xié)議
該文觀點僅代表作者本人,人人都是產(chǎn)品經(jīng)理平臺僅提供信息存儲空間服務(wù)。
- 目前還沒評論,等你發(fā)揮!