GA4×BigQueryの使い方｜SQLでイベント・流入元・コンバージョンを分析する方法

GA4のBigQueryデータをSQLで分析する前に知っておくこと

SQLを書く前に、GA4のエクスポートデータがどういう形をしているかを押さえます。ここを飛ばすと、この後のすべてのSQLが「おまじない」になってしまいます。最低限、次の5つだけ理解してください。

用語	意味
events_YYYYMMDD	1日分のイベントが入る日次テーブル。日付ごとに別テーブルとして作られる
events_*	日次テーブルをまとめて参照するワイルドカード指定。_TABLE_SUFFIXと組み合わせて期間を絞る
event_name	イベントの種類（page_view、session_start、cta_clickなど）
event_params	イベントに付随するパラメータの集まり。1つの列の中に複数のkey / valueが入っている
user_pseudo_id	ブラウザ／アプリ単位で割り当てられる仮名のID

大原則はひとつです。GA4のエクスポートデータは、基本的に「1イベント＝1行」で記録されています。誰かがページを1回見ればpage_viewが1行、CTAを1回クリックすればcta_clickが1行。GA4管理画面で見ている「セッション数」や「ユーザー数」のような集計値は存在せず、集計はすべて自分のSQLで行います。

user_pseudo_idについて補足すると、これは氏名やメールアドレスのような個人を直接特定できる情報ではありません。ブラウザやアプリのインスタンスに割り当てられる仮名化された識別子で、同じ人でもブラウザを変えたりCookieを削除したりすれば別のIDになります。「同じブラウザからのアクセスをまとめるための札」程度に捉えてください。

もうひとつ、セッションを扱ううえで重要なのがga_session_idです。これは独立した列ではなくevent_paramsの中に入っているパラメータで、user_pseudo_idとga_session_idの組み合わせがセッションの識別子になります。この組み合わせが、後半の「CVまでの導線追跡」で効いてきます。

まずevent_nameごとの件数を集計する

最初に実行すべきSQLは決まっています。「このサイトでは、どのイベントが、どれだけ記録されているか」の棚卸しです。分析の対象になるイベントが揃っているかを確認しないまま凝ったSQLを書いても、土台がありません。

SELECT
  event_name,
  COUNT(*) AS event_count
FROM
  `YOUR_PROJECT.analytics_XXXXXXXXX.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260824' AND '20260828'
GROUP BY
  event_name
ORDER BY
  event_count DESC;

ポイントは_TABLE_SUFFIXです。events_*で日次テーブルをまとめて参照しつつ、_TABLE_SUFFIXで対象日を'20260824'〜'20260828'に絞っています。この絞り込みがそのままスキャン量（＝料金）の節約になります。

SEGOの実データでは、次の結果になりました。

event_name	件数
page_view	418
session_start	274
user_engagement	194
first_visit	153
scroll	117
click	21
cta_click	3
form_start	2
generate_lead	1
sample_report_open	1

この1本で分かることは多く、page_viewやscrollのような自動計測イベントに加えて、GTMで仕込んだcta_click（3件）、form_start（2件）、generate_lead（1件）がBigQueryまで届いていることが確認できます。逆に、ここに出てこないイベントは以降のどんなSQLでも分析できません。計測の抜けはこの段階で発見するのが一番安上がりです。

event_paramsをUNNESTして中身を見る

次は、GA4のBigQuery分析で初心者が最初につまずくポイントです。「cta_clickのcta_idを見たい」と思ってSELECT cta_idと書いても、そんな列は存在せずエラーになります。

理由はデータ構造にあります。cta_idのようなパラメータは、event_paramsという列の中に「key（パラメータ名）とvalue（値）のペアの繰り返し」として格納されています。BigQueryの型で言うとREPEATED RECORD——1行の中に小さな表が入れ子になっているイメージです。そのため、普通の列のようには参照できず、UNNEST()で展開してから取り出します。

まずは「cta_clickにはどんなパラメータが入っているのか」を一覧にしてみます。

SELECT
  ep.key AS parameter_name,
  COUNT(*) AS occurrences
FROM
  `YOUR_PROJECT.analytics_XXXXXXXXX.events_*`,
  UNNEST(event_params) AS ep
WHERE
  _TABLE_SUFFIX BETWEEN '20260824' AND '20260828'
  AND event_name = 'cta_click'
GROUP BY
  ep.key
ORDER BY
  occurrences DESC;

FROM句のUNNEST(event_params) AS epが展開の指定です。SEGOの実データでこれを実行すると、cta_click 3件に対して13種類のパラメータが確認できました。

内訳を見ると、GTMで独自に設定したcta_id・link_urlと、GA4が自動で付与するpage_location・page_title・ga_session_id・ga_session_numberなどが並んでいます。自分で送ったパラメータと自動付与のパラメータが同じevent_paramsに同居している——この構造が分かると、GA4のエクスポートデータの見通しが一気に良くなります。

なお、valueの取り出しには型の指定が必要です。文字列ならvalue.string_value、整数（ga_session_idなど）ならvalue.int_valueを使います。次のセクションから実際に使っていきます。

特定のCTAクリックをSQLで確認する

パラメータの構造が分かったので、cta_clickの中身を1件ずつ確認します。「どのページの、どのCTAが、いつクリックされ、どこへ遷移させたか」を並べるSQLです。

SELECT
  TIMESTAMP_MICROS(event_timestamp) AS event_time,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'cta_id') AS cta_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_url') AS link_url
FROM
  `YOUR_PROJECT.analytics_XXXXXXXXX.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260824' AND '20260828'
  AND event_name = 'cta_click'
ORDER BY
  event_timestamp;

ここでは(SELECT ... FROM UNNEST(event_params) WHERE key = '...')という書き方を使っています。パラメータを1つずつ列として取り出す定番の型で、GA4のBigQuery分析ではこの形を何度も書くことになります。event_timestampはUTCのマイクロ秒なので、TIMESTAMP_MICROS()で時刻に変換しています（日本時間で見たい場合はDATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo')）。

SEGOの実データでは、トップページ（https://sego.jp/）に設置したcta_id = contact_footer_topのCTAがクリックされ、link_urlが/consultingを指していることが確認できました。つまり「トップページのフッターCTAから相談ページへ」という動きが、GA4管理画面を開かずにSQLだけで特定できています。

コンバージョンイベントgenerate_leadを確認する

次に、コンバージョン側を確認します。SEGOでは問い合わせフォームの送信完了をgenerate_leadイベントとして計測しています。棚卸しで1件あることは分かっているので、その1件の中身を見ます。

SELECT
  TIMESTAMP_MICROS(event_timestamp) AS event_time,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'form_location') AS form_location,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
FROM
  `YOUR_PROJECT.analytics_XXXXXXXXX.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20260824' AND '20260828'
  AND event_name = 'generate_lead';

実データでは、form_location = consulting_page、page_location = /consulting/thanks（送信完了ページ）、そしてga_session_id（セッションの識別番号）が確認できました。ga_session_idは整数型のパラメータなので、ここだけvalue.int_valueで取り出している点に注意してください。

このga_session_idが手に入ったことで、次のセクションの主役——「このコンバージョンに至るまで、このセッションで何が起きたのか」の追跡が可能になります。

CTAクリックからコンバージョンまで同一セッションで追跡する

ここがこの記事の中心です。GA4管理画面の探索レポートでも経路の分析はある程度できますが、「CV したセッションの全イベントを、発生順に1行ずつ全部見る」という素朴で強力な確認は、BigQueryの生データが最も得意とするところです。

考え方はシンプルで、user_pseudo_id と ga_session_id の組み合わせが同じイベントは同一セッションなので、（1）generate_leadからこの組み合わせを取得し、（2）同じ組み合わせを持つ全イベントを時系列に並べます。特定のIDをSQLに直書きする必要はなく、CTE（WITH句）でつなげば汎用的に書けます。

WITH conversion_sessions AS (
  -- (1) CVイベントからセッションの識別子を取得
  SELECT DISTINCT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
  FROM `YOUR_PROJECT.analytics_XXXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260824' AND '20260828'
    AND event_name = 'generate_lead'
),
session_events AS (
  -- (2) 全イベントに識別子と主要パラメータを付けて展開
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_timestamp,
    event_name,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'cta_id') AS cta_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'form_location') AS form_location
  FROM `YOUR_PROJECT.analytics_XXXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260824' AND '20260828'
)
-- (3) CVセッションのイベントだけを時系列に並べる
SELECT
  TIMESTAMP_MICROS(s.event_timestamp) AS event_time,
  s.event_name,
  s.page_location,
  s.cta_id,
  s.form_location
FROM session_events AS s
JOIN conversion_sessions AS c
  ON s.user_pseudo_id = c.user_pseudo_id
  AND s.ga_session_id = c.ga_session_id
ORDER BY
  s.event_timestamp;

SEGOの実データでこれを実行した結果が次の画面です。

並んだイベントを読むと、このセッションの行動がそのまま見えます。

1. session_start / page_view：トップページ（https://sego.jp/）に到着
2. cta_click：cta_id = contact_footer_topのCTAをクリック
3. page_view：/consulting（相談ページ）へ遷移
4. form_start：フォームの入力を開始
5. generate_lead：/consulting/thanksで送信完了（form_location = consulting_page）

面白いのは、実データでは「トップに戻って再度CTAをクリック→もう一度フォーム入力」という往復が1回挟まっていたことです。form_startは2回記録されており、1回目は送信に至らず、2回目でgenerate_leadに到達しています。管理画面の集計値では「CV 1件」としか見えない裏に、こうした迷いの動きが記録されている——これが1イベント1行の生データを見る価値です。

コンバージョンしたセッションの流入元を確認する

導線が見えたら、最後の問いは「このセッションはどこから来たのか」です。セッション単位の流入元は、エクスポートデータのsession_traffic_source_last_clickという列で確認できます。セッションの参照元・メディア・キャンペーンが格納されている列で、UTMパラメータ由来の値も、自然検索のように自動判定された値もここに入ります。

先ほどのCTEを流用して、CVセッションを「流入元＋ランディングページ＋クリックしたCTA＋CV時刻」の1行にまとめます。

WITH conversion_sessions AS (
  SELECT DISTINCT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
  FROM `YOUR_PROJECT.analytics_XXXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260824' AND '20260828'
    AND event_name = 'generate_lead'
),
session_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_timestamp,
    event_name,
    session_traffic_source_last_click.manual_campaign.source AS source,
    session_traffic_source_last_click.manual_campaign.medium AS medium,
    session_traffic_source_last_click.manual_campaign.campaign_name AS campaign,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'cta_id') AS cta_id
  FROM `YOUR_PROJECT.analytics_XXXXXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260824' AND '20260828'
)
SELECT
  MIN(TIMESTAMP_MICROS(s.event_timestamp)) AS session_start_time,
  ANY_VALUE(s.source) AS source,
  ANY_VALUE(s.medium) AS medium,
  ANY_VALUE(s.campaign) AS campaign,
  ARRAY_AGG(
    IF(s.event_name = 'page_view', s.page_location, NULL) IGNORE NULLS
    ORDER BY s.event_timestamp LIMIT 1
  )[OFFSET(0)] AS landing_page,
  MAX(IF(s.event_name = 'cta_click', s.cta_id, NULL)) AS cta_id,
  MAX(IF(s.event_name = 'generate_lead',
    TIMESTAMP_MICROS(s.event_timestamp), NULL)) AS conversion_time
FROM session_events AS s
JOIN conversion_sessions AS c
  ON s.user_pseudo_id = c.user_pseudo_id
  AND s.ga_session_id = c.ga_session_id
GROUP BY
  s.user_pseudo_id, s.ga_session_id;

少し長く見えますが、やっていることは「セッション内の最初のpage_viewをランディングページとして拾い、cta_idとCV時刻を1行に畳む」だけです。SEGOの実データでの結果がこちらです。

結果はこう読めます。Googleの自然検索（google / organic）で流入し、https://sego.jp/にランディングし、フッターのCTA（contact_footer_top）をクリックして、約10分後にコンバージョンした——「流入 → 行動 → CTA → CV」が1本のSQLでつながりました。

今回は自然検索の1件でしたが、この形はそのまま施策の評価に使えます。メルマガや広告など、URLにUTMパラメータを付けて運用している流入は、source / medium / campaignにその値が入ってくるため、「どの施策経由のセッションがCVしたか」を同じSQLで比較できます。計測を仕込む側（UTM・GTM）と読み取る側（BigQuery）は、ここでつながります。

GA4管理画面とBigQueryの数字が合わないことがある理由

SQLで集計を始めると、ほぼ確実に「GA4管理画面の数字と微妙に違う」場面に出会います。第1回でも触れましたが、実際に集計するようになった今、もう一段だけ踏み込んでおきます。

Googleは公式ヘルプ「アナリティクスのレポートとBigQueryにエクスポートされたデータの比較」で、両者の差が生じる要因を説明しています。主なものを挙げると次の通りです。

• 集計方法の違い：管理画面のユーザー数やセッション数は、大規模データを高速に集計するための近似アルゴリズム（HyperLogLog++）による推計値。BigQueryでのCOUNT(DISTINCT ...)は実数のため、桁が大きくなるほどズレやすい
• しきい値：管理画面ではプライバシー保護のため少数データが非表示になることがある。BigQueryの生データにはこの処理がない
• モデリングデータ：Googleシグナルや同意モードによる推計・補完データは、BigQueryにはエクスポートされない
• レポート用識別子：BigQueryへのエクスポートはデバイスID基準。管理画面が別の識別方法（ユーザーIDやモデリングを含む設定）を使っている場合、ユーザーやセッションの数え方自体が変わる
• 遅延イベント：オフラインになっていた端末のイベントなどが遅れて届き、日次テーブルの内容が後から更新されることがある

要するに、管理画面は「推計を含む見やすいレポート」、BigQueryは「推計を含まない生データ」で、どちらかが壊れているわけではありません。両者を機械的に一致させようとするより、「管理画面はトレンドの把握、BigQueryは条件を厳密に定義した検証」と使い分けるのが実務的です。

BigQueryでGA4を分析するときの注意点

最後に、運用上の注意点を整理します。SQLそのものより、この習慣の方が長期的には重要です。

• _TABLE_SUFFIXで必ず対象日を絞る：events_*を無条件でスキャンすると、蓄積が増えるほど処理量（＝課金対象）が膨らみます。分析対象の期間指定を習慣にしてください
• 大期間へのSELECT * を多用しない：必要な列・必要なイベントに絞るだけでスキャン量は大きく減ります。「とりあえず全部SELECT」は生データ確認の初回だけで十分です
• 実行前に処理量を確認する：BigQueryのエディタは、実行前に「このクエリで何KB／何GB処理されるか」を右上に表示します。今回の記事のSQLはすべて数百KB以下で、無料枠（毎月1TiB）のごく一部でした。実行前に見る癖をつけておけば、課金の不安はほぼなくなります
• user_pseudo_idを個人の特定に使わない：仮名IDであっても、他の情報と突き合わせて個人を特定するような使い方はプライバシーポリシーや規約に抵触するリスクがあります。あくまでセッション・行動をまとめる分析用の識別子として扱ってください
• GA4管理画面との完全一致を目指さない：前のセクションの通り、両者は仕様として一致しません。「なぜ違うのか」を説明できる状態にしておけば十分です

まず覚えておきたいGA4 BigQuery SQLパターン

この記事で使ったSQLは、そのままGA4分析の基本パターン集になっています。順番にも意味があり、上から順に実行していくと「棚卸し→構造理解→個別確認→導線分析」と自然に深掘りできる構成です。

プロジェクトIDと日付、イベント名（cta_click・generate_leadの部分）を自分のサイトのものに置き換えれば、そのまま使えます。まずはパターン1と2だけでも実行してみてください。自分のサイトのデータで動かすことが、どんな解説記事より早い理解につながります。

GA4×BigQueryのSQL分析でよくある質問

GA4のBigQuery分析にはSQLが必須ですか？

テーブルのプレビューまでなら不要ですが、この記事で扱ったような集計・抽出にはSQLが必要です。ただしGA4分析で使うSQLは定型パターンの繰り返しが多く、この記事の6パターンを置き換えて使うところから始められます。ゼロから構文を学ぶより、動くSQLを自分のデータで実行して改変していく方が早く身につきます。

event_paramsとは何ですか？

イベントに付随するパラメータ（page_locationやcta_idなど）が、key / valueのペアの繰り返しとして1つの列にまとめて格納されたものです。普通の列のようには参照できないため、UNNEST()で展開するか、(SELECT value.string_value FROM UNNEST(event_params) WHERE key = '...')の形で取り出します。文字列はstring_value、整数はint_valueと、値の型に応じたフィールドを指定します。

GA4管理画面とBigQueryの数値が違うのはなぜですか？

仕様上、一致しないことがあります。管理画面のユーザー数・セッション数は近似アルゴリズムによる推計値で、しきい値やGoogleシグナル・同意モードによるモデリングデータも含まれます。一方BigQueryは推計を含まない生データです。故障ではなく前提の違いなので、機械的な完全一致を目指す必要はありません。

BigQueryでコンバージョンまで追跡できますか？

できます。user_pseudo_idとga_session_idの組み合わせで同一セッションのイベントを時系列に並べれば、流入からCVまでの導線を1行ずつ確認できます。実際にSEGOでは、自然検索での流入からCTAクリック、フォーム送信完了までを1本のSQLで追跡できました。ただしuser_pseudo_idは仮名IDであり、個人を特定する用途には使えません。

BigQueryのSQL実行には料金がかかりますか？

クエリの処理量が毎月1TiBの無料枠を超えると、超過分に料金がかかります。ただし_TABLE_SUFFIXで期間を絞っていれば、小規模サイトの分析で無料枠を超えることはまれです。今回の記事のSQLは、5日分のデータに対していずれも数百KB以下の処理量でした。実行前にエディタ右上の処理量表示を確認する習慣をつけておくと安心です。

まとめ：生データを「つなげて読める」ことがBigQueryの価値

第1回の連携設定と今回のSQL分析で、「設定 → データ確認 → SQL分析」の一連の流れがひと通り完結しました。

今回の検証で確認できたのは、GA4のBigQuery Exportが「データの置き場所」ではないということです。イベント件数の棚卸しから始めて、event_paramsを展開し、user_pseudo_id + ga_session_idでイベントをつなげば、「google / organicで流入 → トップページ → contact_footer_topをクリック → /consulting → フォーム送信完了」という導線を、自分の定義した条件で、1行ずつ検証できます。管理画面の集計値を眺めるのとは別次元の解像度です。

BigQueryの価値は、GA4のデータを保存できることではなく、「流入 → 行動 → CTA → CV」を自分の分析条件でつなげられることにあります。そしてそれは、CVが月1件の小さなサイトでも——今回のSEGOの実例がまさにそうであるように——十分に意味を持ちます。1件のCVの裏にある行動を具体的に知っていることが、次の施策の精度を変えるからです。

なお、流入元の分析精度は計測の仕込みで決まります。UTMパラメータの命名やCTA計測の設計を整えたうえで、自社サイトのSEO課題を洗い出したい方は、SEGOの無料診断もあわせてご利用ください。

SEGOで無料診断を試す