GA4×BigQueryのevent_paramsをUNNESTで取り出す方法【初心者向けSQL】
GA4のデータをBigQueryにエクスポートして、いざSQLを書こうとすると、最初につまずくのが event_params です。ページのURLもタイトルも、そのままの列としては入っておらず、SELECT page_location と書いてもエラーになってしまいます。
この記事では、event_paramsの構造と、UNNEST を使って値を取り出す定番の書き方を、実行結果付きで解説します。GA4×BigQueryの分析は、ここさえ押さえれば一気に楽になります。
参考記事
BigQueryの導入方法【初心者ガイド】
BigQueryに連携したGA4データ、何時に更新か確認する方法は?テーブル更新時間を調べる手法
SQL JOINの使い方【INNER・LEFT・FULL OUTERの違いを実行結果で解説】
※この記事の実行結果は、GA4のエクスポートと同じ構造で作ったサンプルデータ(架空のサイト、2026年3月1日〜7日)で集計したものです。
GA4のエクスポートデータの形
GA4のデータは、events_20260301 のように1日1テーブルで保存されます。1行が1つのイベント(page_view、session_start など)です。
| 列 | 内容 |
|---|---|
| event_date | イベントの日付('20260301' のような文字列) |
| event_timestamp | イベントの日時(マイクロ秒) |
| event_name | イベント名(page_view、session_start、generate_lead など) |
| event_params | イベントのパラメータ(URL、タイトル、セッションIDなど)が入った配列 |
| user_pseudo_id | ユーザーを識別するID(Cookieごと) |
| device / geo / traffic_source | 端末・地域・最初の流入元などの情報 |
問題の event_params は、1行の中に「key(名前)」と「value(値)」の組が複数入った配列になっています。1つのpage_viewイベントの中身を開いてみると、次のようになっています。
| key | value.string_value | value.int_value |
|---|---|---|
| ga_session_id | NULL | 1970044206 |
| ga_session_number | NULL | 1 |
| page_location | https://www.example.com/ | NULL |
| page_title | トップ | NULL |
| engagement_time_msec | NULL | 71621 |
| source | NULL | |
| medium | organic | NULL |
値は、文字なら string_value、整数なら int_value というように、型ごとに別の場所に入っています。どこに入っているかは、パラメータによって決まっています。
UNNESTとは
UNNEST は、配列を「1要素=1行」に展開する関数です。上の表は、実は次のSQLで1つのイベントのevent_paramsを展開したものです。
SELECT
e.event_name
, ep.key
, ep.value.string_value
, ep.value.int_value
FROM
`プロジェクトID.analytics_123456789.events_20260301` AS e
CROSS JOIN UNNEST(e.event_params) AS ep
WHERE
e.event_name = 'page_view'
AND e.user_pseudo_id = '1755797020.1936924823'
AND e.event_timestamp = 1772319756000000
CROSS JOIN UNNEST(e.event_params) AS ep で、イベント1行を、パラメータの数(この例では7つ)だけの行に増やしています。CROSS JOIN は、FROM テーブル AS e, UNNEST(e.event_params) AS ep のようにカンマで書くこともできます。
※プロジェクトID と analytics_123456789 は、ご自身の環境のものに置き換えてください。
定番の書き方:サブクエリで1つずつ取り出す
実務で最もよく使うのが、SELECTの中にサブクエリを書いて、必要なパラメータだけを列として取り出す方法です。
SELECT
event_date
, user_pseudo_id
, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec
FROM
`プロジェクトID.analytics_123456789.events_20260301`
WHERE
event_name = 'page_view'
ORDER BY
event_timestamp
LIMIT 5
実行結果
| event_date | user_pseudo_id | ga_session_id | page_title | engagement_time_msec |
|---|---|---|---|---|
| 20260301 | 1755797020.1936924823 | 1970044206 | トップ | 71621 |
| 20260301 | 1997525838.1807308348 | 1641180590 | Rの基本 | 74641 |
| 20260301 | 1924866553.1919087451 | 1897677788 | SQLの基本 | 54565 |
| 20260301 | 1924866553.1919087451 | 1897677788 | サービス | 9685 |
| 20260301 | 1924866553.1919087451 | 1897677788 | SQLの基本 | 51672 |
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') は、「このイベントのevent_paramsを展開して、keyが page_title の行の string_value を取ってくる」という意味です。行が増えないので、イベントの件数がそのまま保たれるのが、この書き方のいちばんのメリットです。
よく使うパラメータと値の型
どの value.〇〇_value から取り出すかを間違えると、結果がすべてNULLになります。サンプルデータに入っているパラメータを、型ごとに数えてみました。
SELECT
ep.key
, COUNT(*) AS n
, COUNTIF(ep.value.string_value IS NOT NULL) AS string_cnt
, COUNTIF(ep.value.int_value IS NOT NULL) AS int_cnt
FROM
`プロジェクトID.analytics_123456789.events_*` AS e
, UNNEST(e.event_params) AS ep
WHERE
_TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
GROUP BY
ep.key
ORDER BY
n DESC
実行結果
| key | n | string_cnt | int_cnt |
|---|---|---|---|
| ga_session_id | 2114 | 0 | 2114 |
| page_location | 2114 | 2114 | 0 |
| ga_session_number | 2114 | 0 | 2114 |
| page_title | 2114 | 2114 | 0 |
| engagement_time_msec | 1117 | 0 | 1117 |
| session_engaged | 965 | 965 | 0 |
| source | 450 | 450 | 0 |
| medium | 450 | 450 | 0 |
| campaign | 184 | 184 | 0 |
まとめると、次のとおりです。
| パラメータ | 内容 | 取り出す場所 |
|---|---|---|
| page_location | ページのURL | string_value |
| page_title | ページのタイトル | string_value |
| page_referrer | 前のページのURL | string_value |
| ga_session_id | セッションID | int_value |
| ga_session_number | そのユーザーの何回目のセッションか | int_value |
| engagement_time_msec | エンゲージメント時間(ミリ秒) | int_value |
| session_engaged | エンゲージのあったセッションか | string_value('1') |
| source / medium / campaign | 流入元(参照元・メディア・キャンペーン) | string_value |
自分のデータで、どのパラメータがどの型で入っているかわからないときは、上の「型ごとに数える」SQLを実行してみるのが確実です。カスタムイベントで送っている独自のパラメータも、ここに表示されます。
型がわからないときの書き方
値が文字か数値かわからないパラメータは、COALESCE で「入っている方」を取り出す方法もあります。
(SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING), CAST(value.double_value AS STRING))
FROM UNNEST(event_params) WHERE key = 'value') AS value
複数日のテーブルをまとめて集計する
events_* のように *(ワイルドカード)を使うと、複数日のテーブルをまとめて読めます。期間は _TABLE_SUFFIX(* の部分に当たる文字列)で指定します。ページタイトル別のページビュー数を、1週間分で集計してみましょう。
SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
, COUNT(*) AS page_views
FROM
`プロジェクトID.analytics_123456789.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
AND event_name = 'page_view'
GROUP BY
page_title
ORDER BY
page_views DESC
実行結果
| page_title | page_views |
|---|---|
| トップ | 311 |
| Rの基本 | 292 |
| SQLの基本 | 197 |
| サービス | 182 |
| お問い合わせ | 120 |
| 送信完了 | 15 |
_TABLE_SUFFIX で期間を絞ると、読み込むテーブル自体が減るので、BigQueryの料金(スキャン量)も抑えられます。期間の指定は必ず付けるようにしましょう。
よくある失敗:CROSS JOINのまま件数を数える
CROSS JOIN UNNEST で展開したまま COUNT(*) をすると、イベントがパラメータの数だけ重複して数えられてしまいます。
-- 間違い:パラメータの数だけ水増しされる
SELECT
COUNT(*) AS page_views
FROM
`プロジェクトID.analytics_123456789.events_*` AS e
, UNNEST(e.event_params) AS ep
WHERE
_TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
AND e.event_name = 'page_view'
| 書き方 | page_viewsの結果 |
|---|---|
| CROSS JOINで展開したまま数える(間違い) | 7634 |
| 展開せずに数える(正しい) | 1117 |
正しい数の約7倍になってしまいました。CROSS JOINを使う場合は、WHERE ep.key = 'page_location' のように取り出すkeyを1つに絞るか、この記事で紹介したサブクエリの書き方を使いましょう。迷ったら、サブクエリで取り出す書き方に統一するのがおすすめです。
まとめ
- GA4のevent_paramsは、「key」と「value」の組が入った配列
- 値は型ごとに string_value / int_value などに分かれて入っている
- 定番は
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = '〇〇')で列として取り出す書き方 events_*と_TABLE_SUFFIXで期間を指定し、スキャン量を抑える- CROSS JOINで展開したまま数えると、件数が水増しされる
event_paramsを取り出せるようになれば、GA4の画面ではできない細かい集計も、SQLで自由にできるようになります。ユーザー数・セッション数の集計方法は、続きの記事で解説します。
NEWSLETTER
Data Schoolの新着記事をメールで受け取る
新しい記事が公開されたら、メールでお知らせします。配信はいつでも停止できます。
