GA4 × BigQuery連携で
リピーター分析を深掘りする
管理画面の限界を超え、SQLで顧客を理解する

GA4のBigQueryエクスポートとは

GA4には、収集したイベントデータをGoogle BigQueryへ自動的にエクスポートする機能が標準搭載されています。BigQueryはGoogleが提供するサーバーレスのデータウェアハウスで、大量データに対してSQLクエリを高速に実行できます。

エクスポートには「日次エクスポート」と「ストリーミングエクスポート」の2種類があります。日次エクスポートは無料で利用でき、前日分のデータが毎日自動的にBigQueryのテーブルに追加されます。ストリーミングエクスポートはほぼリアルタイムでデータが反映されますが、BigQuery側のストレージ・クエリコストが増加するため、要件に応じて選択しましょう。

設定はGA4の管理画面から「BigQueryのリンク」を選び、GCPプロジェクトを指定するだけで完了します。データは analytics_XXXXXXXXX.events_YYYYMMDD という命名規則のテーブルに格納されます。

なぜBigQueryが必要か（GA4管理画面の限界）

GA4の管理画面だけでリピーター分析を行おうとすると、いくつかの壁にぶつかります。

• データ保持期間の制限：GA4の探索レポートで利用できるデータは最大14ヶ月。年単位でのリピート傾向を分析したい場合、これでは不十分です。BigQueryにエクスポートすればデータは半永久的に保持できます。
• セグメントの柔軟性不足：GA4のセグメント機能は便利ですが、「3回以上購入かつ最終購入が90日以内」のような複合条件の組み合わせには限界があります。SQLなら自由に定義できます。
• サンプリングの問題：大規模サイトでは、GA4の探索レポートにサンプリング（推定値）がかかります。BigQueryではイベントデータの全量に対してクエリを実行できるため、正確な数値が得られます。
• 他データとの結合：CRMの会員データや購買DBと突合するには、生データへのアクセスが不可欠です。BigQueryなら他のテーブルとJOINするだけで実現できます。

リピーター分析に使えるSQLクエリ例

BigQueryにエクスポートされたGA4データを使って、リピーター分析を行う基本的なSQLクエリを紹介します。

ユーザーごとのセッション回数を集計する

まずは基本となる、ユーザーごとの訪問回数を集計するクエリです。

SELECT
  user_pseudo_id,
  COUNT(DISTINCT CONCAT(
    user_pseudo_id,
    CAST(
      (SELECT value.int_value
       FROM UNNEST(event_params)
       WHERE key = 'ga_session_id') AS STRING)
  )) AS session_count
FROM
  `project.analytics_XXXXXXXXX.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20250101' AND '20250331'
GROUP BY
  user_pseudo_id
ORDER BY
  session_count DESC

このクエリにより、期間内のユーザーごとのセッション数が分かります。session_count が2以上であればリピーターです。

購入回数別のユーザー数を集計する

SELECT
  purchase_count,
  COUNT(*) AS user_count
FROM (
  SELECT
    user_pseudo_id,
    COUNT(DISTINCT ecommerce.transaction_id) AS purchase_count
  FROM
    `project.analytics_XXXXXXXXX.events_*`
  WHERE
    event_name = 'purchase'
    AND _TABLE_SUFFIX BETWEEN '20250101' AND '20250331'
  GROUP BY
    user_pseudo_id
)
GROUP BY
  purchase_count
ORDER BY
  purchase_count

1回購入・2回購入・3回以上と、購入回数のヒストグラムを描けます。F2転換率やロイヤルカスタマーの割合を把握する第一歩です。

ユーザーの購買回数別セグメント分析

購入回数だけでなく、セグメントごとの行動特性を深掘りすることで、施策の精度が上がります。

たとえば、以下のようなセグメント分けが有効です。

• 1回購入（F1）：全体の何%か？どの流入経路からの初回購入が多いか？
• 2回購入（F2）：F1からF2への転換率は？初回購入から2回目までの平均日数は？
• 3回以上購入（ロイヤル）：LTV（顧客生涯価値）はF1の何倍か？どの商品カテゴリを購入しているか？

BigQueryでは、これらを1つのクエリ内でWITH句（CTE）を使って段階的に計算できます。たとえば、初回購入から2回目購入までの平均日数を算出し、それが30日以内かどうかで「転換の速い層」「転換の遅い層」を分けると、フォローアップ施策のタイミング設計に直結します。

セグメントごとの平均注文単価（AOV）や購入カテゴリの傾向を出せば、「F1層には初回購入カテゴリの関連商品をレコメンド」「ロイヤル層には新商品を先行案内」といった具体的なアクションにつなげられます。

コホート分析をBigQueryで実現する方法

コホート分析とは、「同じ時期に初回行動を起こしたユーザー群」をグループ化し、その後の行動推移を追跡する分析手法です。GA4にもコホート探索レポートがありますが、期間やメトリクスの自由度に限界があります。

BigQueryでは、SQLを使って完全にカスタマイズしたコホート分析が可能です。基本的な手順は以下のとおりです。

1. コホート定義：各ユーザーの初回購入月（または初回訪問月）を算出する
2. 活動月の計算：各イベントが初回から何ヶ月後かを算出する
3. リテンション率の集計：コホート月 × 経過月のマトリクスで、アクティブユーザー数を集計する

こうして出来上がったリテンション表を見れば、「2024年1月に初回購入したユーザーの3ヶ月後の再購入率は12%だが、4月コホートは18%に改善している」といった時系列での施策効果測定が可能になります。

さらに、コホートを流入チャネル別（自然検索、広告、SNSなど）に分けることで、「どのチャネルから獲得した顧客が長期的に定着するか」という広告投資判断にも活用できます。

BigQuery × RFM分析の連携パターン

BigQueryに蓄積されたGA4データを使えば、RFM分析を自動化できます。RFMとは、Recency（最終購入日）、Frequency（購入頻度）、Monetary（購入金額）の3軸で顧客をスコアリングする手法です。

BigQueryでのRFM分析は、以下のステップで進めます。

1. R・F・Mの算出：ユーザーごとに最終購入日からの経過日数、購入回数、合計金額をSQLで計算
2. ランク付け：NTILE(5)関数を使い、各指標を5段階にスコアリング
3. セグメント割当：R・F・Mの組み合わせに基づき「優良顧客」「休眠リスク」「新規有望」などのラベルを付与

この結果をGoogle スプレッドシートやLooker Studio（旧Data Studio）に接続すれば、ダッシュボードとして日次で更新されるRFMレポートが完成します。

ただし、BigQueryでRFM分析を運用するには、SQLの知識やGCPの管理が必要です。「分析はしたいが、SQLを書くリソースがない」「もっと手軽にRFM分析を回したい」という場合は、専用のCRM分析ツールの活用も選択肢に入ります。

コスト管理とデータ保持期間の設計

BigQueryは従量課金制のため、使い方次第ではコストが膨らむ可能性があります。以下のポイントを押さえましょう。

• ストレージコスト：アクティブストレージは1GBあたり月額約$0.02。90日間変更がないテーブルは長期保存ストレージに自動移行され、料金が半額になります。中規模ECサイト（月間100万PV程度）であれば、年間のストレージ費用は数百円〜数千円程度です。
• クエリコスト：オンデマンド料金では処理データ1TBあたり$6.25。日常的な分析クエリであれば月額数百円〜数千円に収まりますが、SELECT * で全カラムをスキャンすると費用が跳ね上がります。必要なカラムだけを指定し、パーティションフィルタ（_TABLE_SUFFIX）で日付範囲を絞ることがコスト削減の鉄則です。
• データ保持ポリシー：GA4のBigQueryエクスポートは日次テーブルで蓄積されるため、古いデータを月次テーブルに集約するバッチ処理を組むと、ストレージ効率が向上します。生データは2年分を保持し、それ以前は集約テーブルのみ残す、といった運用設計がおすすめです。

無料枠（毎月1TBまでのクエリと10GBまでのストレージ）を活用すれば、多くのECサイトでは実質無料、もしくはごく低コストでBigQuery分析を始められます。