RFM分析とは？
RFM分析とは、「最新購入日（Recency）」「購入回数（Frequency）」「購入金額合計（Monetary）」を元にしてユーザーを3次元でグループ化してランク付けを行う分析手法です。ユーザーグループに応じて、マーケティング施策を打っていこうというやつです。
| 指標名 | 指標説明 |
|---|---|
| 最新購入日（Recency） | 最近購入に至ったユーザーほど優良顧客として扱う |
| 購入回数（Frequency） | 購入した回数が多いユーザーほど優良顧客として扱う |
| 購入金額合計（Monetary） | 購入金額の多いユーザーほど優良顧客として扱う |
今回やること
BigQueryのサンプルデータでGA4のEコマースのアクセスログデータセット「ga4_obfuscated_sample_ecommerce」を用いて、各ユーザーランクにどれくらいのユーザーがいるのかを集計する。なお、あまり込み入った分析には踏み込まず、クエリ紹介を目的とする。
注意点
-
サンプルデータのテーブルは2020-11-01 〜 2021-01-31の3ヶ月分のデータであるため、2021-02-01からみた最新購入日との日数を評価する。
-
サンプルデータのevent_nameに「purchase」という購入を示すイベントがあるが、このレコードに購入金額を示す「ecommerce.purchase_revenue」が必ずしも入ってるわけではなかった。今回はそれが入っているもののみを購入とみなした。
-
RFM各指標は5グループに分割し、点数を振る必要があるが、今回は意図的にランク付けするのはやや面倒なので、NTILE関数を用いて各指標点数を分割し点数を付けた。
※ランクを定義する場合、例えば最新購入日からの経過日数であれば、14日以内：5点、28日以内：4点、60日以内：3点のように振り分ける。
※NTILE関数は例えば最新購入日からの経過日数を昇順で並び替えて、同じユーザー数になるように5等分し、ユーザーを振り分ける関数である。一般にデシル分析でよく用いられる。
クエリ
WITH mst_purchase_log AS (
SELECT
PARSE_DATE('%Y%m%d', event_date) AS purchase_date
, user_pseudo_id AS user_id
, ecommerce.purchase_revenue AS purchase_revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE ecommerce.purchase_revenue IS NOT NULL
)
, mst_user_rfm AS (
SELECT
user_id
, MAX(purchase_date) AS recent_date
, DATE_DIFF('2021-02-01', MAX(purchase_date), DAY) AS recency
, COUNT(*) AS frequency
, SUM(purchase_revenue) AS monetary
FROM mst_purchase_log
GROUP BY 1
)
, mst_user_point AS (
SELECT
*
, 6 - NTILE(5) OVER (
ORDER BY recency
) AS recency_point
, 6 - NTILE(5) OVER (
ORDER BY frequency DESC
) AS frequency_point
, 6 - NTILE(5) OVER (
ORDER BY monetary DESC
) AS monetary_point
FROM mst_user_rfm
)
SELECT
recency_point + frequency_point + monetary_point AS total_point
, recency_point
, frequency_point
, monetary_point
, COUNT(DISTINCT user_id) AS uu
, COUNT(DISTINCT user_id) / (SELECT COUNT(DISTINCT user_id) FROM mst_user_point) AS ratio
FROM mst_user_point
GROUP BY 1, 2, 3, 4
ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC
アウトプット
結果
ここからの分析は三次元のバブルチャートでマッピング、もしくは適当な2つの指標を組み合わせて2次元で表現して、ユーザーを「優良顧客」「安定顧客」「離反離脱」「非優良顧客」等に分類していきます。
そこで
- 安定顧客を常連にするには？
- 常連離脱予兆のあるユーザーを常連に戻すには？
- 新規離脱予兆のあるユーザーを安定ユーザーにするには？
等々考えて施策を打ってきます。
ですが、今回はサンプルデータですし、そこまで踏み込みません。
分析についての詳細は NECさんのブログ記事に詳しく書かれておりましたので、ぜひご覧ください。
一応、合計ポイントごとのユーザー数の表を乗っけておきます。
