BigQueryでRFM分析|NTILEで同じ回数の顧客が別の点になる理由と境目の決め方

BigQueryでRFMの点を付けるとき、1回だけの顧客が2割を超える表をNTILE(5)で5等分すると、同じ1回の人が別の点に分かれます。架空の通販データ(1回が約56%)で試した結果と、回数・日数の境目の決め方をまとめました。

この記事をシェア:
目次 クリックで開く

この記事の要点

  1. RFM の集計は顧客ごとに3つの数を出すだけで、ずれやすいのは1〜5の点を付けるところです。
  2. 架空の通販データで試すと、回数は半分以上が「1回」で、NTILE(5) は同じ1回の人を1・2・3点に分けました。ORDER BY に customer_id を足しても、この分かれ方は直りません。
  3. 回数は境目を決めて点を付けます。日数を5等分した境目(今回は上位2割が54日以内)は顧客の分布で動きます。区分の決め方は、当社が検証のために決めた1つの例です。

回数(F)に NTILE(5) で点を付けたときの分かれ方

3点に1回の人と2回の人が混ざる。4回以上の人は全員5点

1回の人 → 1点5,904人
1回の人 → 2点5,904人
1回の人 → 3点4,599人
2回の人 → 3点1,305人
2回の人 → 4点5,743人
3回の人 → 4点161人
3回の人 → 5点3,058人

当社の検証用 Google Cloud プロジェクトで実測(2026年10月14日、架空の通販データ、基準日 2026年9月30日、返品を除いた 29,519 人)

BigQuery で RFM 分析をするとき、集計の SQL は短く書けます。ずれやすいのは、出した数に1〜5の点を付けるところです。当社が作った架空のデータでは、注文の回数が「1回」の人が約56%でした。このように1回の人が全体の2割(5等分の1つ分)を超える表を NTILE(5) で5等分すると、同じ1回の人が別々の点に入ります。回数は境目を決めて点を付け、日数と金額は分布を見てから決めます。

当社は、BigQuery を中心にしたデータ基盤の設計・構築と、売上・顧客の分析を支援しています。点の付け方で区分の人数がどれだけ変わるかは実際に動かしてみないと分からないので、当社の検証用の Google Cloud プロジェクトに架空の通販データを作って試しました。数字の傾向は、データを作るときに当社が決めたものです。クラスタリング(k-means など)、BigQuery ML、区分ごとの施策の効果は扱いません。

RFM は3つの数を出すだけで、先に決めるのは「何を数えるか」

RFM 分析は、Recency(最後の注文からの日数)、Frequency(注文の回数)、Monetary(金額の合計)で顧客を並べる方法です。注文の明細が BigQuery にあれば、集計から区分までを1本の SQL にできます。スケジュールされたクエリにすれば、決まった間隔で実行し直せます。実行の時刻は UTC に直して扱われます(Google Cloud「クエリのスケジューリング」)。

データをどこに置くか、Excel のままでよい場合との分かれ目を動画で話しています。

SQL を書く前に、3つの数の中身を決めます。ここが違うと、同じ SQL でも点が変わります。

RFM の集計の前に決めること
決めることこの記事の検証での決め方
基準日DATE '2026-09-30' に固定。CURRENT_DATE() はタイムゾーンを指定しないと UTC の日付なので(公式)、日本時間の朝9時前に実行すると前日になる。実行した日を使うなら CURRENT_DATE('Asia/Tokyo') と書く
R の起点注文日(発送完了日にする選び方もある)
返品・キャンセル返品の印が付いた注文を除く。返品だけの人は対象から外れる
F の数え方1注文1行の表なので COUNT(*)。商品ごとの明細の表なら COUNT(DISTINCT order_id)
M の範囲税抜の金額。送料・手数料・ポイント利用分を入れるかもここで決める
期間全期間。何度も買う商品と何年かに一度の商品では、見る期間が違う

金額の列が文字列で取り込まれていると、SUM は数値の型(と INTERVAL)しか受け付けないのでエラーになります。SAFE_CAST で数値に変えればエラーは消えます。ただし、変えられない値は NULL になり(公式)、SUM は NULL を飛ばして足すので(公式)、M が黙って小さくなります。変えたら NULL の行を数えてください。金額の型は、公式が金額の計算に向くとしている NUMERIC にします(公式)。

架空の通販の 29,519 人で試した

当社の検証用プロジェクトに、SQL で架空の通販のデータを作りました(2026年10月14日)。会員は 30,000 人、注文は 55,792 件です(2024年1月〜2026年9月、1注文1行、税抜)。期間の後のほうの月ほど、新しく入る会員が多い店にしました。2回目以降を買う確率や注文の間隔は当社が決めた架空の値です。返品を除いた RFM の対象は 29,519 人で、3つの数は次の SQL で出しました。

WITH base AS (
  SELECT customer_id,
    DATE_DIFF(DATE '2026-09-30', MAX(order_date), DAY) AS recency_days,
    COUNT(*) AS frequency,
    SUM(amount_ex_tax) AS monetary
  FROM blog_demo.ec_orders   -- 自分の表なら your_dataset.orders など
  WHERE NOT is_returned
  GROUP BY customer_id)

今回のデータでは回数(F)の半分以上が「1回」で、NTILE(5) は同じ1回の人を3つの点に分けた

注文の回数ごとの人数

返品を除いた 29,519 人。1回が約56%

1回16,407人
2回7,048人
3回3,219人
4回1,514人
5回705人
6回以上626人

当社の検証用 Google Cloud プロジェクトで実測(2026年10月14日、架空の通販データ、返品を除いた 29,519 人。条件は最初の図と同じ)

この約56%は、データを作るときに当社が決めた条件から出た値です。2回目以降を買う確率は経路ごとに 0.45〜0.70 とし、後の月ほど新しい会員を多くしました。ほかの店の割合を表すものではありません。

この回数に NTILE(5) OVER (ORDER BY frequency) で点を付けると、1回の人は1点 5,904 人、2点 5,904 人、3点 4,599 人に分かれました。2回の人も、1,305 人が3点、5,743 人が4点に入りました(冒頭の図)。同じ買い方の人が別の点に入り、3点には1回と2回の人が混ざります。

これは NTILE の仕様どおりの動きです。公式の説明では、NTILE は並びに沿って行をグループに分け、グループの行数の差は最大1で、余りはグループ1から配ります(Google Cloud の英語の説明「Numbering functions」の NTILE)。29,519 人なら1〜4点が 5,904 人、5点が 5,903 人になり、実測と合います。NTILE は値が同じかどうかを見ずに人数で切ります。公式の例でも、記録時刻が同じ2人が1と2に分かれています。

ORDER BY に customer_id を足しても直らない

同じ値の中で誰が上の点に入るかは、NTILE の説明には書かれていません。同じく並びで番号を振る ROW_NUMBER の説明には、同じ値の中の順番は決まらない(non-deterministic)とあります(公式)。並びに customer_id を足して ORDER BY frequency, customer_id にすると、並びが一意になります。誰がどの点に入るかも決まるはずですが、人数の分かれ方は足す前とまったく同じでした。直るとしても「誰が入るか」だけです。足す前の形で、実行ごとに入る人が変わるかどうかは試していません。

RANK や CUME_DIST は同じ値の行に同じ値を返しますが(公式)、5等分にはなりません。なお、日数は小さいほど高い点にするので、この記事では日数の NTILE を6から引いています(6 - NTILE(5) OVER (ORDER BY recency_days, customer_id))。

回数は境目を決めて点を付ける

当社が検証で決めた境目は、1回=1点、2回=2点、3回=3点、4〜5回=4点、6回以上=5点です。4回以上は人数が少なくなるので、まとめて点を付けました。同じ回数の人は必ず同じ点になります。

CASE WHEN frequency = 1 THEN 1 WHEN frequency = 2 THEN 2
     WHEN frequency = 3 THEN 3 WHEN frequency <= 5 THEN 4
     ELSE 5 END AS f_score

境目で付けた回数の点ごとの人数

回数ごとの人数を足したもの。合計 29,519 人

1点(1回)16,407人
2点(2回)7,048人
3点(3回)3,219人
4点(4〜5回)2,219人
5点(6回以上)626人

当社の検証用 Google Cloud プロジェクトで実測(2026年10月14日、架空の通販データ、返品を除いた 29,519 人。条件は最初の図と同じ)

その代わり、点ごとの人数はそろいません。5等分の点は「全体の何番目の2割か」を、境目の点は「何回買ったか」を表します。施策の条件(「2回目を買っていない人」など)と食い違わないのは後者です。何回からを常連とみなすかは商品の買い替えの間隔で変わるので、事業を知っている人が決めます。

日数(R)を5等分した境目は、顧客の分布で動く

日数(R)は、並びに customer_id を足した NTILE(5) のままで点を付けました。日数の境目は次のとおりです。

日数(R)を NTILE(5) で分けたときの、各点の日数の幅

各点 5,903〜5,904 人。境目の日(54・148・290・480日)の人は隣り合う2つの点に分かれている

5点:0〜54日54日まで
4点:54〜148日148日まで
3点:148〜290日290日まで
2点:290〜480日480日まで
1点:480〜994日994日まで

当社の検証用 Google Cloud プロジェクトで実測(2026年10月14日、架空の通販データ、基準日 2026年9月30日。条件は最初の図と同じ)

上位2割が54日以内になったのは、当社が決めた条件から出た結果です。後の月ほど新しい会員を多くし、注文の間隔は人ごとに25〜120日にしました。会員の増え方が違う店では日数の幅が変わり、同じ店でも実行し直すたびに動くことがあります。なお、境目の日に何人いたかは数えていません。「90日買っていない人に案内を送る」のように施策の条件を日数で決めるなら、R も日数の境目で点を付けるほうが、点の意味が変わりません。境目を決める前の分布は APPROX_QUANTILES(recency_days, 5) で見られます(公式)。

金額(M)は NTILE のままにし、分布は今回は数えていない

金額(M)は、返品を除いた税抜の金額を全期間で合計したものです。日数と同じく、並びに customer_id を足した NTILE(5) で点を付けました。今回は M の分布も、同じ金額の人が何人いて点がどう分かれたかも数えていません。架空データの1注文の金額は10円単位に丸めて作ったので、合計が同じになる人はいる可能性があります。回数と同じく、まず金額ごとの人数を数え、同じ値の人が5等分の1つ分(2割)を超えるほどまとまっていれば、境目を決めて点を付けます。M でずれやすいのは、点を付けるところより数える前です。金額の列が文字列なら M は黙って小さくなり(上の「何を数えるか」の節)、全期間の合計なので昔の注文が多い人ほど大きくなります。R・F・M の点をまとめて付ける SQL は次のとおりです(base は上の集計の SQL)。

, s AS (
  SELECT *,
    6 - NTILE(5) OVER (ORDER BY recency_days, customer_id) AS r_score,
    CASE WHEN frequency = 1 THEN 1 WHEN frequency = 2 THEN 2 WHEN frequency = 3 THEN 3
         WHEN frequency <= 5 THEN 4 ELSE 5 END AS f_score,
    NTILE(5) OVER (ORDER BY monetary, customer_id) AS m_score
  FROM base)

当社が検証で決めた6つの区分と、その結果

点を組み合わせて6つの区分を決めました。R が4点以上の人は、F が3点以上なら「常連」、F が1点なら「新規」、それ以外なら「育成中」です。R が2点以下の人は、F が3点以上なら「離れかけの常連」、それ以外なら「休眠」です。残りの人は「その他」にしました。名前と条件はこの検証のために当社が決めた例で、M は使っていません。上の s に続けて、区分ごとの人数と金額を出した SQL です。

, seg AS (SELECT *, CASE
   WHEN r_score >= 4 AND f_score >= 3 THEN '常連'
   WHEN r_score >= 4 AND f_score = 1 THEN '新規'
   WHEN r_score >= 4 THEN '育成中'
   WHEN r_score <= 2 AND f_score >= 3 THEN '離れかけの常連'
   WHEN r_score <= 2 THEN '休眠'
   ELSE 'その他' END AS segment FROM s)
SELECT segment, COUNT(*) AS customers,
  ROUND(AVG(recency_days)) AS avg_recency_days, ROUND(AVG(frequency), 2) AS avg_frequency,
  ROUND(AVG(monetary)) AS avg_monetary, SUM(monetary) AS total_monetary
FROM seg GROUP BY segment ORDER BY total_monetary DESC;
6つの区分の人数と金額(架空データ、税抜、返品を除く)
区分人数最後の注文からの日数(平均)回数(平均)1人あたりの合計金額の合計
休眠9,861人517日1.28回6,105円60,197,890円
その他5,904人216日1.91回9,393円55,454,990円
常連2,795人58日4.01回19,760円55,228,690円
離れかけの常連1,946人467日3.70回18,171円35,360,090円
新規6,207人62日1.00回4,613円28,629,830円
育成中2,806人61日2.00回9,282円26,045,810円

「離れかけの常連」の 1,946 人は、回数 3.70 回・1人あたり 18,171 円と常連(4.01 回・19,760 円)に近い買い方をしてきた人たちです。それでも、最後の注文からは平均 467 日たっています。回数と日数を別々に点にしたので、この組み合わせが見えます。

読むときの注意が3つあります。金額の合計が最も大きい休眠(60,197,890 円)は、M が全期間の合計なので過去に積み上がった額で、今の売上ではありません。この条件では、R が3点の 5,904 人は回数に関係なく「その他」に入ります。条件の作り方によっては、名前の付かない人が出るということです。そして人数と金額は、当社が決めた架空の傾向から出たもので、ほかの店に当てはまるとは言えません。言えるのは、点の付け方で区分の人数が変わることです。区分ごとの案内とその効果は試していません。獲得した時期をそろえて顧客の価値を比べる話はLTV の記事で書いています。

区分を配信や広告に渡す道具と、処理の料金

区分を外へ渡す道具
道具公式の説明で確かめたこと
dbt区分を出す SQL に、バージョン管理・テスト・ドキュメントなどの作法を当てはめる道具(dbt「What is dbt?」)
リバースETL(Hightouch など)データウェアハウスの表を配信・広告などの道具へ送る。Hightouch は「300以上のツールにつなげる」としている(Hightouch)
Google 広告の顧客リスト(BigQuery を直接接続)[ツール]>[共有ライブラリ]>[オーディエンス マネージャー]>[+](作成)>[顧客リスト]と進む。[データソース]で[新しいデータソースを接続する]を選び、[Google BigQuery]、[直接接続]の順に選ぶ。最初の接続には、データセットに対する GCP オーナー アクセス権と、Google 広告の管理者権限が要る。接続だけを先に作るなら[ツール]>[データ マネージャー]>[+ サービスを接続]から作り、顧客リストを作るときに[既存の接続を使用する]を選ぶ(Google 広告 ヘルプ「BigQuery」)。データソースのフィールドを Google のフィールドにマッピングするので、customer_id のほかに、照合に使うメールアドレスや電話番号などの列が要る(Google 広告 ヘルプ「データファイルをアップロードして、カスタマー マッチ リストを作成する」)

区分を施策に渡す流れ

BigQuery明細 → R・F・M → 点 → 区分を、決まった間隔で実行し直す
渡すリバースETL、Google 広告の顧客リスト(BigQuery を直接接続)
使う配信の道具、広告の顧客リスト

区分ごとに何を送るかと、その効果は、この記事の検証では試していない

出典:Google Cloud、Google 広告 ヘルプ、Hightouch

道具の選び方はBigQuery・dbt・リバースETLのツール選定、LINE 配信へのつなぎ方はリバースETLで作る LINE 配信、広告へ戻す形はCAPI と BigQuery の記事にあります。

処理の料金は、この規模ではほぼかかりません。今回のクエリが読んだのは1本 0.5〜1.5 MB で、課金はどれも最低の 10 MB でした。オンデマンドは参照する表ごと・クエリごとに最低 10 MB で、毎月最初の 1 TiB は無料です(Google Cloud「BigQuery の料金」)。日付でパーティションに分けた表でも、全期間の RFM は全部を読みます。期間を区切り、絞り込みの条件を定数の日付で書けば、範囲の外のパーティションは読み飛ばせます(公式)。ただし、その期間より前にしか買っていない人は対象から消えます。料金の決まり方はデータ統合基盤の費用の記事で実測しています。

まず自分の表で、回数ごとの人数を数える

-- 上の WITH base AS (...) に続けて
SELECT frequency, COUNT(*) AS customers
FROM base GROUP BY frequency ORDER BY frequency;

1回の人が、1点のグループの人数(全体の5分の1で、端数は切り上げ)を超えていれば、NTILE(5) は必ず1回の人を2つ以上の点に分けます。そのときは回数の境目を決め、日数と金額も分布を見てから決めてください。区分の名前と条件を事業の言葉で決めるところから BigQuery での作り込みまでは、当社のデータ分析・BI 支援で一緒に進められます。

関数の仕様と料金は、2026年10月14日に Google Cloud・Google 広告・dbt・Hightouch の公式ページで確かめたものです。数字はすべて当社が作った架空のデータでの結果です。

Google BigQuery 導入支援

GA4・広告・売上のデータを BigQuery に集め、Looker Studio で見られる形にします。スキャン量の課金を読める設計と、社内で運用できる状態までを支援します。

AT
aurant technologies 編集

上場企業からスタートアップまで、数多くのデータ分析基盤構築・AI導入プロジェクトを主導。単なる技術提供にとどまらず、MA/CRM(Salesforce, Hubspot, kintone, LINE)導入によるマーケティング最適化やバックオフィス業務の自動化など、常に「事業数値(売上・利益)」に直結する改善実績多数。

この記事が役に立ったらシェア: