More than 5 years have passed since last update.

MySQLを使ったRFM分析

Last updated at 2019-05-08Posted at 2019-05-08

前提

モノからヒト+モノへのマーケティング活動のための事前準備のための分析。

手順

顧客ごとのRFMの集計とランク付け

まず、顧客ごとにRFMそれぞれの指標値を集計し、ランク１〜5を割り振ります。

（1）顧客ごとのRFM値の算出

（a）「顧客ごと」で集計するため、group by句でcustomer_idを指定しグルーピングします。
（b）直近購買日recencyとして、顧客の最も新しい購買日と現在の日付の差分を算出します。
現在の日付はDATE関数で算出しています。
（c）頻度frequencyを、count関数で顧客の購買の回数を集計することで算出します。
（d）合計金額monetaryを、sum関数で顧客の購買金額の和を集計することで算出します。

（2）ランクの算出

　算出したrecency、frequency、monetaryの値を元に、CASE式を使ってRFMの指標ごとにランク付けしていきます。

ランクの基準

Recency Rank

case when COUNT(customer_id)  >= 20 then "5" 
           when COUNT(customer_id)  >= 10 and COUNT(customer_id)  <= 19 then "4"
           when COUNT(customer_id)  >= 5 and count(customer_id) <= 9 then "3"
           when COUNT(customer_id)  >= 2 and count(customer_id) <= 4 then "2"
           when COUNT(customer_id)  = 1 then "1"
           end

Frequency Rank

WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 2
        THEN
            '5'
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) >= 3
                AND PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 5
        THEN
            '4'
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) >= 6
                AND PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 8
        THEN
            '3'
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) >= 9
                AND PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 11
        THEN
            '2'
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) >= 12
                AND PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 23
        THEN
            '1'
    END

Monetary Rank

今回は割愛。

指標・ランクごとの顧客数の集計

次に、上記で求めた顧客ごとのデータを元に各指標・各ランクでそれぞれ顧客数がどのくらいかを集計します。

ピボットで集計する

オープンソースのre:dashを使ってみたい...

セクションを全てまとめたクエリは以下

SELECT 
    customer_id,
    case when COUNT(customer_id)  >= 20 then "5" 
           when COUNT(customer_id)  >= 10 and COUNT(customer_id)  <= 19 then "4"
           when COUNT(customer_id)  >= 5 and count(customer_id) <= 9 then "3"
           when COUNT(customer_id)  >= 2 and count(customer_id) <= 4 then "2"
           when COUNT(customer_id)  = 1 then "1"
           end,
    SUM(sales),
    CASE
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 2
        THEN
            '5'
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) >= 3
                AND PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 5
        THEN
            '4'
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) >= 6
                AND PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 8
        THEN
            '3'
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) >= 9
                AND PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 11
        THEN
            '2'
        WHEN
            PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) >= 12
                AND PERIOD_DIFF(DATE_FORMAT('20170331', '%Y%m'),
                    DATE_FORMAT(MAX(purchase_date), '%Y%m')) <= 23
        THEN
            '1'
    END
FROM
    t_order
WHERE
    delivery_status != '注文キャンセル'
        AND customer_id != ''
        AND DATE_FORMAT(purchase_date, '%Y%m%d') BETWEEN '20150401' AND '20170331'
GROUP BY customer_id

検証用クエリの作成

概要

SQL上でWHERE句で期間を指定する場合に日付が入っていないとデータに差異が出る。

原因

DATE_FORMAT(purchase_date, '%Y%m') BETWEEN '20161001' AND '20180930'

例えば上記の例であれば、DATE_FORMAT(purchase_date, '%Y%m') のように年月までしか指定していないので、
%dまで入れないと抽出結果がおかしくなる。

検証クエリ

期間が長いと結果がわかりづらくなるため、ひとまず期間を一日にして検証する

WHERE
    delivery_status != '注文キャンセル'
        AND customer_id != ''
        AND DATE_FORMAT(purchase_date, '%Y%m%d') BETWEEN '20190401' AND '20190401'
GROUP BY customer_id

上記で問題なければ、各セクションごとに算出、集計する。

LTVを算出する

手順

該当期間の会員購入回数を出す

SELECT 
    COUNT(purchase_date)
FROM
    t_order
WHERE
    delivery_status != '注文キャンセル'
        AND customer_id != ''
        AND DATE_FORMAT(purchase_date, '%Y%m%d') BETWEEN '20161001' AND '20180930'

該当期間の平均購入回数を出す

購入回数/会員数

LTVを算出する

購入単価*平均購入回数

限界CPAも算出できる

購入単価 * 平均購入回数 * 粗利率(24%)

上記からわかること

2年LTVだとすると、2年で回収できるということ

注意点

※本来は、オンラインとオフラインの顧客IDが統合されたDWH環境を構築したうえで
分析を進めるべきだが、今回は作業手順を含めてまずは「やってみる」こととしている。

RFM分析を具体的な施策へ落とし込む

優良顧客層へ限定クーポンもしくはセールなどの特別な情報　【限定系】
離反顧客や休眠顧客が何を購入していたか？を分析したうえで、リテンションが望めそうなユーザーに絞って特定のアイテムをお知らせしてみて反応を見る　【休眠向け】
Recencyが１カ月以上前で、購入頻度が低い顧客におススメアイテムのレコメンドメールを送り、その1か月後に関連アイテムを再度お知らせする　【まとめ買い系】

他にもあれば、随時追加。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up