「連携はできたのに、SQLで止まっていませんか？」GA4×BigQueryの実務テンプレート20本をまとめました

はじめに

GA4とBigQueryの連携設定のお手伝いをしていると、設定が終わったあとに同じところで止まってしまう方をよく見かけます。

「データはBigQueryに入っている。でも、欲しい数字を出すSQLが書けない」

「書き方を調べるだけで半日終わってしまう」

連携はあくまでスタート地点で、その先の「分析する時間」が確保できないままの方も少なくありません。そこで、自分の実務と技術ブログ（Zenn）で使ってきた分析SQLから使い回しの効く20本を選び、テンプレートとして1冊のPDFにまとめました。この記事では、その中身をご紹介します。

収録している20本について

テンプレートは「基礎編」「売上編」「顧客編」「応用編」の4編構成です。

基礎編（5本）は、すべての分析の土台になる部分です。イベントパラメータを取り出すUNNESTの展開パターン、セッションIDの正しい定義、GA4定義の直帰率の集計、デバイス別・地域別のセグメント分析、カスタムパラメータの抽出などを収録しています。

売上編（6本）は、ECの数字を見るためのものです。購入ファネルの集計、注文データとGA4を結合した売上アトリビューション、チャネル別のCAC（顧客獲得単価）算出、メルマガのROI測定、Google広告のキーワード別CVR、ファーストタッチ／ラストタッチの取得などです。

顧客編（5本）には、LTVの算出、初回購入からリピートまでのファネル、定期購入の継続率とコホート分析、ポイント施策の効果検証などを入れました。

応用編（4本）は、サイト内検索の分析、Search ConsoleとGA4の連携分析、クエリコストの監視、そして日次テーブルとintradayテーブルの使い分けです。コスト監視は「気づいたらBigQueryの料金が増えていた」を防ぐために、個人的にも入れておきたかった1本です。

使い方

各テンプレートは、プロジェクトID・データセット名・集計期間の3か所を置き換えれば、そのまま実行できる形にしています。「用途」「置換箇所」「想定結果」「変更ポイント」を付けているので、標準イベントと違う設計でも、どこを直せばいいか分かるようにしました。SQLはBigQueryの標準SQLで、全テンプレートとも構文チェックを行っています。

こんな方に向いています／向いていない方もいます

向いているのは、連携は済んでいるものの分析SQLを書く時間が取れていない方、中小ECやWebサイトの担当者、駆け出しのデータアナリストの方です。

逆に、BigQueryへのエクスポート自体がまだの方は、先に連携が必要です。SQLの文法から学びたい方には入門書のほうが合います。この資料は「学ぶ」より「実務で使う」ことを優先して作りました。

正直にお伝えしたいこと

収録したSQLは、もともとZennやブログで公開してきた記事のものを、パラメータ化と表記統一をして実務用に整形したものです。記事で読める内容を、探し回らずに手元で使える形にしたものとお考えください。自社データでの実行結果を保証するものではありません。イベント設計が標準と異なる場合は、各テンプレートの「変更ポイント」を参考に調整をお願いします。

おわりに

ご自身の環境に合わせた調整や、分析基盤そのものの構築から相談したい場合は、スキルマーケットのサービスもご用意しています。

まずは手元のデータで分析を回してみたい方は、この記事の下に表示されている『GA4×BigQuery 分析SQLテンプレート集』をのぞいてみてください。置き換えるだけで今日から実行できます。