BigQuery RFM分析基盤設計ガイド 2026：システム構成・SQLスコアリング・パーソナライズ戦略

購買データをRFM分析に落とし込み、BigQueryで基盤を設計。顧客セグメントに応じたパーソナライズ施策で、マーケティング効果を最大化する実践的手法を詳解。

更新：2026年6月17日
この記事をシェア：
目次 クリックで開く

現代のマーケティングにおいて、単なる「全件一括配信」はリソースの浪費だけでなく、顧客体験（CX）の低下を招きます。本ガイドでは、Google Cloudのデータウェアハウス「BigQuery」を活用し、購買データから顧客の熱量を可視化するRFM分析基盤の構築手順を、実務レベルで詳解します。

1. データ基盤におけるRFM分析の再定義

RFM分析とは、Recency（最新購買日）、Frequency（累計購買回数）、Monetary（累計購買金額）の3指標で顧客をランク付けする手法です。これをExcelではなくBigQueryで行う最大の理由は、数百万行を超えるトランザクションデータのリアルタイム処理と、他システムへの自動連携にあります。

1.1 1st Party Data活用とCookieless対策

サードパーティCookieの利用制限が進む中、自社で保有する「1st Party Data（購買履歴・行動ログ）」の価値が高まっています。BigQueryにデータを集約し、独自のアルゴリズムで顧客セグメンテーションを行うことは、プライバシー保護と高精度なターゲティングを両立する唯一の解決策です。

2. RFM分析基盤のシステム構成とツール選定

効率的なデータパイプラインを構築するためには、適切なツールの選定が不可欠です。以下に、現代的な「モダンデータスタック」における主要ツールの比較を示します。

データ基盤関連ツールの比較
ツール名 役割 主な特徴・スペック 公式URL / 導入事例
Google BigQuery DWH（蓄積・分析） 毎秒数GBのクエリ処理、ストレージ$0.02/GB〜 公式サイト

事例：株式会社ZOZO
dbt (data build tool) データ変換（SQL管理） バージョン管理、テスト自動化、ドキュメント生成 公式サイト

事例
Hightouch リバースETL（データ連携） 200以上のSaaS（Salesforce等）と連携可能 公式サイト

事例

関連記事：高額なCDPは不要？BigQuery・dbt・リバースETLで構築する「モダンデータスタック」ツール選定と公式事例

3. 【実践】BigQuery SQLによるRFMスコアリング実装

実務でそのまま利用できる、SQLを用いたRFMスコアの算出ステップを解説します。

ステップ1：指標の集計（Raw Data to Metrics）

まず、購買履歴テーブル（orders）から顧客ごとのR・F・Mを計算します。基準日を 2026-04-12 と想定します。

SQL実装例：


SELECT

  user_id,

  DATE_DIFF('2026-04-12', MAX(order_date), DAY) AS recency,

  COUNT(order_id) AS frequency,

  SUM(amount) AS monetary

FROM project.dataset.orders

GROUP BY user_id

ステップ2：5段階スコアリング（NTILE関数）

次に、算出した数値を相対的な5段階評価に変換します。BigQueryの NTILE(5) 関数を使用することで、全顧客を均等な数で5つのグループに分割できます。

よくあるエラー：不正確なNTILEの順序

Recencyは「値が小さいほど高評価（1）」、Frequency/Monetaryは「値が大きいほど高評価（5）」とする必要があります。ORDER BY句の方向に注意してください。

RFM分析を仕組み化したいなら、BigQuery基盤の設計から始めましょうAurant のデータ分析・BI支援は、Looker Studio・BigQuery・Tableau によるダッシュボード構築からデータ基盤の整備、運用定着までを支援します。✓ ダッシュボード設計・構築✓ BigQuery等の基盤整備✓ 運用定着とKPI設計データ分析・BI支援を見る →数字を集める作業から、使う仕事へ散在データBI構築意思決定基盤整備・可視化・定着

4. 分析結果をアクションに繋げるパーソナライズ戦略

スコアリング完了後、特定のセグメントに対して具体的な施策を実行します。例えば、RFMすべてが「5」の優良顧客には、限定の先行案内をLINEで送付する等の自動化が考えられます。

関連記事：高額MAツールは不要。BigQueryとリバースETLで構築する「行動トリガー型LINE配信」の完全アーキテクチャ

4.1 リバースETLによる現場へのデータ還元

BigQueryで計算されたRFMスコアは、CRM（Salesforce）や広告プラットフォーム（Google Ads）に自動で同期する必要があります。これにより、現場の営業担当者が顧客の最新ランクをSalesforce上で即座に確認できるようになります。

  • Salesforce連携: API制限を考慮し、バッチ処理で夜間に同期。
  • Google 広告連携: カスタムオーディエンスとして利用し、優良顧客と類似したユーザーに配信を拡張。

RFMセグメント別パーソナライズ施策：スコア基準・ビジネス解釈・推奨アクション

「スコアリングが完成した」ことはゴールではなく、スタートです。RFMスコアを算出しても「その後どのセグメントに何を送るか」の施策設計がなければ、データは可視化されただけで終わります。特に「離脱懸念（元・優良顧客）」と「休眠顧客」の扱いを事前に決めておかないと、コストをかけてWIN-BACKメッセージを送っても反応がなく費用対効果が悪化するケースがあります。下表は代表的な5セグメントのスコア基準・ビジネス上の意味・推奨アクション・リバースETLでの連携先をまとめたものです。

セグメント名 RFMスコア目安 ビジネス上の解釈 推奨アクション リバースETL連携先
優良顧客（VIP） R4〜5, F4〜5, M4〜5 最も価値の高い顧客。直近購買があり購買頻度・金額ともに高い。ブランドへのエンゲージメントが最大 ロイヤリティプログラムへの招待・新商品先行案内・テスター依頼。「あなただけへ」という特別感のあるパーソナルメッセージが有効 LINE（VIPセグメントタグ付与）・Salesforce（優先顧客フラグ）
離脱懸念（元・優良） R1〜2, F4〜5, M4〜5 かつての優良顧客で直近購買がない。放置すれば完全休眠になるため、最優先でWIN-BACKアクションが必要 個人名入りの「お久しぶりです」メッセージ・購買頻度を確認するアンケート・期間限定の特別クーポン（通常より高い割引率） Salesforce（WIN-BACKリスト）・Google広告（類似拡張で獲得コスト削減）
新規・育成対象 R4〜5, F1〜2, M1〜2 直近購買はあるが頻度・金額が低い。ブランドとの関係性が浅い段階。2回目購買への誘導が成功率を左右する ブランドストーリーの訴求・活用ガイドや使用事例の送付・初回購買後の自動フォローアップメール（7日・30日後）・2回目購買クーポン LINE/MA（ステップ配信シナリオ）
平均的顧客 R3, F2〜3, M2〜3 安定的に購買しているが突出した特徴がない。施策の設計次第で優良顧客への育成が最も費用対効果高い 関連商品のクロスセル提案・セット購買キャンペーン・レコメンデーション広告（閲覧商品に基づく動的広告） Google広告・Meta広告（カスタムオーディエンス）・MA配信
休眠顧客 R1〜2, F1〜2, M1〜2 購買頻度も直近購買も低い。WIN-BACKコストが高く、費用対効果の検証が必須。配信停止も選択肢に含める 費用対効果の計算（獲得コスト vs LTV）を行った上でWIN-BACKキャンペーン実施を判断。反応なければ配信停止で通数コストを削減 配信前に費用対効果評価。配信停止の場合はLINEブロック解除訴求のみに絞る

この表で特に注意すべきは「休眠顧客への一律配信は費用を増やすだけになりうる」点です。LINEの送信通数課金は2023年の料金改定以降コスト意識が高まっており、反応率が極めて低い休眠顧客への配信を継続すると、通数コストと配信リソースを消費し続けます。BigQueryで「配信コスト対ROI」を定期的に算出し、セグメントごとに配信の継続・停止を意思決定できる仕組みを構築することが、RFM基盤の最大の価値です。

5. 運用のベストプラクティスとコスト管理

大規模データを扱う場合、クエリコストの最適化は必須です。BigQueryでは order_date による「パーティショニング」を設定することで、スキャン範囲を限定し、料金を大幅に抑えることが可能です。

関連記事：SaaSコストとオンプレ負債を断つ。バックオフィス＆インフラの「標的」と現実的剥がし方（事例付）

トラブルシューティング：データ型の不一致

ETL過程で amount が文字列（STRING）として取り込まれている場合、SUM関数でエラーが発生します。集計前に SAFE_CAST(amount AS FLOAT64) を通すことで、異常値によるパイプライン停止を回避できます。

以上のように、BigQueryを中心としたRFM分析基盤を設計することで、データは単なる「記録」から、現場を動かす「資産」へと変わります。まずはスモールスタートとして、主要な購買指標の可視化から着手することをお勧めします。

実務で差がつくRFM運用のチェックポイント

分析基盤を構築しても、ビジネス成果に繋がらないケースの多くは「指標の定義」と「データ更新頻度」に課題があります。実装前に以下の3点を現場のマーケターと合意しておくことが重要です。

1. Recency（最新購買日）の起点をどこにするか

「発送完了日」か「注文日」か。キャンセルや返品をどう除外するかでスコアが変動します。BigQuery上でこれらを正規化するロジックを共通化しましょう。

2. 購買頻度のライフサイクル設定

商材によってリピートサイクルは異なります。日用品なら週単位、家電なら年単位など、分析対象期間（Lookback Period）の適切性を検証してください。

3. 「Monetary」の集計範囲

送料や手数料を含めるか、ポイント利用分を差し引くか。会計上の売上とマーケティング用のM値を一致させる必要があります。

RFMセグメント別のアクションマップ

スコアリングされたデータをどのように具体的な施策へ振り分けるか、代表的な活用例をまとめました。特に、獲得したデータの鮮度を落とさずに広告やCRMへ戻す「データ基盤の駆動」が肝となります。

セグメント RFM定義の例 推奨アクション 連携先システム
優良顧客 R5, F5, M5 ロイヤリティプログラム招待・先行予約 LINE / Salesforce
離脱懸念（元・優良） R1~2, F5, M5 新商品の案内・アンケート・特別クーポン Google / Meta広告（CAPI）
新規・育成対象 R5, F1, M1~2 ブランドストーリーの訴求・使い方ガイド MA / LINE

さらなるデータ活用の拡張

RFM分析で可視化した「顧客の熱量」は、単なるメール配信だけでなく、広告の学習効率を最大化するためにも活用できます。例えば、優良顧客のデータを広告プラットフォームへフィードバックすることで、よりLTV（顧客生涯価値）の高いユーザーをターゲットにする最適化が可能です。

本稿で紹介したBigQueryベースの分析手法は、企業のデータ成熟度に応じて柔軟に拡張可能です。まずは最小限のSQLで「今、誰が最もブランドを愛してくれているか」を可視化することから始めてみてください。

📚 関連資料

このトピックについて、より詳しく学びたい方は以下の無料資料をご参照ください：

システム導入・失敗回避チェックリスト PDF

DX推進・システム導入で陥りがちな落とし穴を徹底解説。選定から運用まで安全に進めるためのチェックリスト付き。

📥 資料をダウンロード →


よくある質問（FAQ）

Q. BigQueryでRFM分析基盤を設計するには何が必要ですか？

BigQuery RFM分析基盤の構成要素：①購買履歴データの集約（Shopify・EC独自DB・実店舗POSのトランザクションデータをBigQueryに集める。Fivetranや独自ETLで定期同期）、②RFMスコア計算のSQLモデル（Recency：最終購買日からの経過日数、Frequency：期間内の購買回数、Monetary：期間内の累計購買金額を顧客別に集計してdbtモデルで定義する）、③スコアリングと分類（R・F・M各指標を1〜5のスコアに変換してRFMセグメント（Champions・Loyal Customers・At Risk等）に分類するSQL）、④セグメントのMA/CRMへの反映（HightouchやCensus等のリバースETLでBigQueryのRFMセグメントをKlaviyo・LINE・Salesforceに同期して配信施策に活用）の4層構成です。

Q. BigQuery RFM分析の「SQLスコアリング」はどのように実装しますか？

SQLスコアリングの実装例：①各顧客のR・F・M値を計算するベーステーブルを作成：
`SELECT customer_id, DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency, COUNT(DISTINCT order_id) AS frequency, SUM(order_amount) AS monetary FROM orders GROUP BY customer_id`
②NTILE関数でスコアを1〜5に変換：
`SELECT *, NTILE(5) OVER (ORDER BY recency ASC) AS r_score, NTILE(5) OVER (ORDER BY frequency DESC) AS f_score, NTILE(5) OVER (ORDER BY monetary DESC) AS m_score`
③複合スコアでセグメント分類（CASE WHEN r_score >= 4 AND f_score >= 4 THEN ‘Champions’ WHEN r_score >= 3 AND f_score >= 3 THEN ‘Loyal’ WHEN r_score <= 2 AND f_score <= 2 THEN 'AtRisk' ELSE 'Regular' END）の3ステップです。NTILEは顧客数が少ない場合に精度が落ちるため、顧客数100人以上の場合に適用してください。

Q. RFM分析に基づく「パーソナライズ戦略」はどう設計すればいいですか？

セグメント別の配信戦略例：①Champions（R・F・M全て高スコア）→VIPプログラムへの招待・先行販売案内・アンバサダー依頼、②Loyal Customers（FとMが高いがRが少し低下）→「最近お会いしていませんが」系の再エンゲージメントメッセージ・新商品先行告知、③At Risk（過去は優良顧客だが最近購入なし）→「特別割引クーポン」「在庫残り少ない商品のリマインド」で復帰を促す、④Lost（全スコアが低く長期未購入）→大幅割引またはウィンバック専用キャンペーン・復帰しない場合はリスト除外を検討。セグメント別の施策を設計してLINE・メール・広告リターゲティングの3チャネルで実施するオムニチャネルRFM戦略が効果的です。

データ分析・予実可視化とダッシュボード構築のご相談

散在するデータの集約から、予実管理やKPIをひと目で追えるダッシュボードの構築までを支援します。何をどの指標で見える化すべきかという設計段階から、貴社の状況に合わせてご一緒します。

データ分析・可視化支援を見る →