Web Analytics

GA4×BigQueryのevent_paramsをUNNESTで取り出す方法【初心者向けSQL】

酒井 裕麻株式会社Bistro 代表取締役社長

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 google 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で自由にできるようになります。ユーザー数・セッション数の集計方法は、続きの記事で解説します。

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。