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さんのブログ記事に詳しく書かれておりましたので、ぜひご覧ください。

一応、合計ポイントごとのユーザー数の表を乗っけておきます。