MySQLを使ったRFM分析

# 前提

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

# 手順

> 顧客ごとの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](https://seleck.cc/614)を使ってみたい...

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

```
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分析を具体的な施策へ落とし込む

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

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