【面試題】下面是2020年1月份某電商平臺(tái)的用戶(hù)行為記錄表。

表中的數(shù)據(jù)如下：

業(yè)務(wù)問(wèn)題：店鋪在對(duì)用戶(hù)進(jìn)行盤(pán)點(diǎn)時(shí)發(fā)現(xiàn)，用戶(hù)運(yùn)營(yíng)過(guò)于粗放，沒(méi)能做到用戶(hù)分類(lèi)運(yùn)營(yíng)。老板想在下一個(gè)月對(duì)不同的用戶(hù)進(jìn)行有針對(duì)性的營(yíng)銷(xiāo)，達(dá)到降低成本提高收入，精細(xì)化運(yùn)營(yíng)的效果。怎么辦？

【分析思路】

要想對(duì)用戶(hù)進(jìn)行精細(xì)化運(yùn)營(yíng)，就要對(duì)用戶(hù)分類(lèi)，然后對(duì)不同的用戶(hù)采取不同的運(yùn)營(yíng)策略。

涉及到用戶(hù)分類(lèi)，可以用RFM分析方法。

一、什么是RFM分析方法？

RFM分析方法是根據(jù)用戶(hù)的最近一次消費(fèi)時(shí)間間隔(R)、消費(fèi)頻率(F)、消費(fèi)金額(M)來(lái)對(duì)用戶(hù)進(jìn)行打分，進(jìn)而將用戶(hù)按分?jǐn)?shù)劃分為不同的類(lèi)型，然后對(duì)不同的用戶(hù)使用不同的運(yùn)營(yíng)策略，從而實(shí)現(xiàn)輔助精準(zhǔn)運(yùn)營(yíng)。

RFM分析方法將用戶(hù)分為以下8類(lèi)：

本文重點(diǎn)是介紹如何用SQL實(shí)現(xiàn)RFM的案例應(yīng)用，所以在看下面內(nèi)容之前，確保你已經(jīng)學(xué)會(huì)了《RFM分析法原理：如何對(duì)用戶(hù)按價(jià)值分類(lèi)？》

二、如何用SQL實(shí)現(xiàn)RFM分析方法？

1.定義指標(biāo)R、F、M

將指標(biāo)定義中的“一段時(shí)間“定義為1月份（30天）。

最近一次消費(fèi)時(shí)間間隔(R)：用戶(hù)最近一次消費(fèi)距離現(xiàn)在（1月30日）多長(zhǎng)時(shí)間了。

消費(fèi)頻率(F)：用戶(hù)一段時(shí)間內(nèi)（1月份）消費(fèi)了多少次。

消費(fèi)金額(M)：用戶(hù)一段時(shí)間內(nèi)（1月份）的消費(fèi)金額，對(duì)應(yīng)表中的“消費(fèi)金額“。

2.計(jì)算R、F、M值

根據(jù)RFM分析方法指標(biāo)的定義，只需要“用戶(hù)行為類(lèi)型”中的“購(gòu)買(mǎi)”值，可以先篩選(where)出用戶(hù)的所有“購(gòu)買(mǎi)”記錄（下圖紅框的地方），做為臨時(shí)表a。

查詢(xún)結(jié)果：

（1）最近一次消費(fèi)時(shí)間間隔(R)

R值：用戶(hù)最近一次消費(fèi)距離現(xiàn)在（1月30日）多長(zhǎng)時(shí)間了。

R值=現(xiàn)在（2020-01-30）-?每個(gè)用戶(hù)最后一次購(gòu)物日期

每個(gè)用戶(hù)最后一次購(gòu)物日期，涉及到“每個(gè)問(wèn)題“要想到《猴子 從零學(xué)會(huì)SQL》里講過(guò)的用分組匯總來(lái)實(shí)現(xiàn)。

根據(jù)“用戶(hù)編號(hào)”分組，然后匯總（最后一次購(gòu)買(mǎi)日期，就是找到日期的最大值，用匯總函數(shù)max）

將該查詢(xún)結(jié)果表示為臨時(shí)表b。

然后就可以分析出R值=現(xiàn)在（2020-01-30）-?每個(gè)用戶(hù)最后一次購(gòu)物日期

（2）消費(fèi)頻率(F)

消費(fèi)頻率(F)：用戶(hù)一段時(shí)間內(nèi)（1月份）消費(fèi)了多少次。

先篩選（where）出30天內(nèi)所有的購(gòu)買(mǎi)記錄。

然后需要分析出“每位用戶(hù)的購(gòu)買(mǎi)次數(shù)?！懊课挥脩?hù)”按用戶(hù)編號(hào)分組（group by），然后用匯總函數(shù)（count）求出購(gòu)買(mǎi)次數(shù)。

（3）消費(fèi)金額(M)

消費(fèi)金額(M)：用戶(hù)一段時(shí)間內(nèi)（1月份）的消費(fèi)金額，對(duì)應(yīng)表中的“消費(fèi)金額“。

先篩選（where）出30天內(nèi)所有的購(gòu)買(mǎi)記錄。

然后，分析出“每位用戶(hù)的總消費(fèi)金額”，按用戶(hù)編號(hào)分組（group by），用匯總函數(shù)（sum）求出總消費(fèi)金額。

?

至此，我們已經(jīng)獲得了每一個(gè)用戶(hù)的R、F、M值，將其合并起來(lái)設(shè)為表c，以便后續(xù)查詢(xún)：

2.?給R、F、M按價(jià)值打分

本案例的R、F、M打分規(guī)則定義如下：

打分過(guò)程及預(yù)期打分結(jié)果：

為了實(shí)現(xiàn)上面的打分，可以使用SQL語(yǔ)句的條件判斷（case）對(duì)查詢(xún)條件做條件限制。其語(yǔ)法規(guī)則是:

先來(lái)看如何用條件判斷（case）實(shí)現(xiàn)R值打分

用條件判斷（case）實(shí)現(xiàn)F值打分

用條件判斷（case）實(shí)現(xiàn)M值打分

將上面的R、F、M值打分sql合并在一起：

將評(píng)分結(jié)果表示為臨時(shí)表d。

3.?求出所有用戶(hù)R、F、M評(píng)分的平均值

求平均值使用匯總函數(shù)（avg）

4.?將每個(gè)用戶(hù)的R、F、M值與相應(yīng)指標(biāo)的平均值對(duì)比，確認(rèn)用戶(hù)分類(lèi)

目前我們已經(jīng)得到了每個(gè)用戶(hù)的R分、F分和M分，也獲得了R、F、M這三個(gè)指標(biāo)各自的平均值。

接下來(lái)需要將每一個(gè)用戶(hù)的【R分、F分、M分】與【R分平均、F分平均、M分平均值】做比較，就可知道每一個(gè)用戶(hù)在【最近一次消費(fèi)時(shí)間間隔(R)、消費(fèi)頻率(F)、消費(fèi)金額(M)】三個(gè)指標(biāo)中是否高于平均水平，從而幫助后續(xù)對(duì)用戶(hù)進(jìn)行分類(lèi)。

先利用每個(gè)的R、F、M值與相應(yīng)指標(biāo)平均分作比較，將大于平均分的記為高，否則記為低：

5.用戶(hù)分類(lèi)

根據(jù)上述分值比較情況，結(jié)合下面的分類(lèi)規(guī)則對(duì)用戶(hù)分類(lèi)：

至此，我們就完成了用RFM分析方法、SQL對(duì)用戶(hù)分類(lèi)。然后根據(jù)分析結(jié)果，就可以對(duì)用戶(hù)進(jìn)行精細(xì)化運(yùn)營(yíng)啦。

三、如何進(jìn)行精細(xì)化運(yùn)營(yíng)？

用戶(hù)分類(lèi)后，如何精細(xì)化運(yùn)營(yíng)呢？

對(duì)用戶(hù)分類(lèi)以后要做什么呢？那就是針對(duì)每類(lèi)用戶(hù)如何制定運(yùn)營(yíng)策略，這個(gè)具體公司業(yè)務(wù)不同，方法也不一樣。這里舉例說(shuō)明前4類(lèi)用戶(hù)。

1）重要價(jià)值用戶(hù)，RFM三個(gè)值都很高，要提供vip服務(wù)

2）重要發(fā)展用戶(hù)，消費(fèi)頻率低，但是其他兩個(gè)值很高，就要想辦法提高他的消費(fèi)頻率

3） 重要保持用戶(hù)，最近消費(fèi)距離現(xiàn)在時(shí)間較遠(yuǎn)，也就是F值低，但是消費(fèi)頻次和消費(fèi)金額高。這種用戶(hù)，是一段時(shí)間沒(méi)來(lái)的忠實(shí)客戶(hù)。應(yīng)該主動(dòng)和他保持聯(lián)系，提高復(fù)購(gòu)率

4） 重要挽留客戶(hù)，最近消費(fèi)時(shí)間距離現(xiàn)在較遠(yuǎn)、消費(fèi)頻率低，但消費(fèi)金額高。這種用戶(hù)，即將流失，要主動(dòng)聯(lián)系用戶(hù)，調(diào)查清楚哪里出了問(wèn)題，并想辦法挽回。

這樣通過(guò)RFM分析方法來(lái)分析用戶(hù)，對(duì)用戶(hù)進(jìn)行精細(xì)化運(yùn)營(yíng)。不斷將用戶(hù)轉(zhuǎn)化為重要價(jià)值用戶(hù)。