BigQuery×GA4 データ の理解 event_params のネスト構造
はじめに
GA4 のデータを BigQuery で扱う際、最初に戸惑うのがデータ構造、スキーマです。
bigquery で ga4 データをプレビューで見た際に、event_params フィールドが複数のカラムに分割されていることに気がつくと思います。これはネスト構造という配列でデータが格納されているためです。
この記事では、GA4 データの構造、ネストされたデータの取り出し方法を解説していきます。
GA4 データの確認
まずは BigQuery のブラウザインターフェースで GA4 のデータを見てみましょう。テーブルをクリックすると、スキーマ(データ構造)が表示されます。
BigQuery の GA4 データスキーマ表示画面
よく使うカラムは以下の通り:
-
event_date
: イベントが発生した日付 -
event_name
: イベントの種類(page_view, click など) -
event_params
: イベントに関する詳細情報(配列形式) -
user_properties
: ユーザーに関する情報(配列形式)
GA4 のデータは「イベント」を基準に記録されています。ユーザーがページを閲覧したり(page_view)、クリックしたり(click)するたびに、1 つのイベントとして記録され、そのイベントに関する詳細情報がevent_params
に格納されます。
スキーマを見ると、このevent_params
の型がRECORD
でREPEATED
となっていることに気づくと思います。
-
RECORD
: 1 つのデータの中に複数の項目がセットになっている構造です。GA4 では、各パラメータがkey
とvalue
という 2 つの項目をセットで持ちます。 -
REPEATED
: そのデータが複数回繰り返されることを示します。GA4 では、1 つのイベントに対して複数のパラメータ(page_location や page_title など)が記録されます。
実際のデータを見てみましょう:
{
"event_name": "page_view",
"event_params": [
// これがREPEATED(配列)
{
// これがRECORD(keyとvalueのセット)
"key": "page_location",
"value": {
"string_value": "https://example.com/products"
}
},
{
// 2つ目のRECORD
"key": "page_title",
"value": {
"string_value": "商品一覧"
}
},
{
// 3つ目のRECORD
"key": "ga_session_id",
"value": {
"int_value": "1234567890"
}
}
]
}
このように、event_params
は:
- 各パラメータが
key
とvalue
のセット(RECORD)になっていて - それが配列として複数存在する(REPEATED)という
入れ子構造(ネスト構造)になっています。
このネスト構造は、GA4 が柔軟にさまざまなイベントデータを記録できる利点がありますが、そのままでは分析に使いづらいという特徴があります。実際の BigQuery での表示を見てみましょう:
BigQuery の GA4 データプレビュー画面
プレビュー画面を見ると、データの構造が少し複雑に見えるかもしれません。例えば:
-
event_params.key
という列が複数存在する -
event_params.value
の後に.string_value
や.int_value
といった型の指定がある - 同じイベントに対して複数の行が存在する
これは先ほど説明したネスト構造が、実際のデータとしてどのように格納されているかを示していま
す。
GA4 のブラウザの画面だと、見やすいように配列が展開されて表示されています。
プレビューが実際のテーブルの構造だと思ってクエリを書くと、思った通りの結果は得られません。
次のセクションではUNNEST
関数を使って、このネストされたデータを効率的に取り出す方法を見ていきましょう。
データの取り出し方:基本編
まずは簡単なところから始めましょう。event_date
やevent_name
など、ネストされていない基本的なカラムは、通常の SQL と同じように取り出すことができます:
SELECT
event_date,
event_name,
user_pseudo_id
FROM `your_project.your_dataset.events_*`
WHERE event_date = '20231125'
LIMIT 5;
また、どのようなイベントが記録されているのか、以下のクエリで確認できます:
SELECT
event_name,
COUNT(*) as event_count
FROM `your_project.your_dataset.events_*`
WHERE event_date = '20231125'
GROUP BY event_name
ORDER BY event_count DESC;
ここまでは簡単ですね。しかし、実際の GA4 データ分析で必要となる情報の多くは、event_params
の中に格納されています:
- ページの URL(
page_location
) - ページタイトル(
page_title
) - セッション ID(
ga_session_id
) - スクロール深度(
percent_scrolled
) - 商品情報(
items
)
など
これらの情報は先ほど見たネスト構造の中に格納されているため、単純なSELECT
文では取り出せません。「URL だけ取り出したいのに、なぜこんなに複雑なんだ...」と感じる方も多いのではないでしょうか。
実は、BigQuery には配列データを上手く扱うためのUNNEST
という関数が用意されています。次のセクションでは、このUNNEST
関数を使って、ネストされたデータを効率的に取り出す方法を見ていきましょう。
ネストデータの展開
では、いよいよネストされたデータの展開方法を見ていきましょう。GA4 データでは、event_params
やuser_properties
などの重要な情報が配列形式で格納されています。
UNNEST の 2 つの使い方
-
FROM 句での展開
- すべてのパラメータを行として展開
- データの探索や、どんなパラメータがあるか確認する際に便利
- 1 つのイベントが複数行に展開されるため、集計時は注意が必要
-
SELECT 句での展開
- 必要なパラメータのみを列として取得
- 分析時によく使用する方法
- 1 イベント 1 行で取得できるため、集計が容易
それぞれの方法を詳しく見ていきましょう。
FROM 句での展開
UNNEST
関数は、配列を行に展開するための関数です。以下のような方法で使用できます:
SELECT
event_date,
event_name,
-- パラメータの種類(page_locationなど)
ep.key as param_key,
-- パラメータの値(文字列型)
ep.value.string_value,
-- パラメータの値(数値型:整数)
ep.value.int_value,
-- パラメータの値(数値型:浮動小数点)
ep.value.float_value,
-- パラメータの値(数値型:倍精度浮動小数点)
ep.value.double_value
FROM `your_project.your_dataset.events_*`,
UNNEST(event_params) as ep
WHERE event_date = '20231125'
AND event_name = 'page_view'
LIMIT 10;
このクエリを実行すると、1 つのイベントに対して複数のevent_params
の行が生成されます。
配列が一つずつレコードになるイメージです。
event_date | event_name | param_key | string_value | int_value | float_value | double_value |
---|---|---|---|---|---|---|
20231125 | page_view | page_location | https://example.com/ | null | null | null |
20231125 | page_view | page_title | ホームページ | null | null | null |
20231125 | page_view | ga_session_id | null | 1234567890 | null | null |
20231125 | page_view | page_referrer | https://www.google.com/ | null | null | null |
20231125 | page_view | campaign | (organic) | null | null | null |
20231125 | page_view | entrances | null | 1 | null | null |
... | ... | ... | ... | ... | ... | ... |
この方法は:
- データの探索時に便利(どんなパラメータがあるか確認できる)
- すべてのパラメータを確認できる
- 集計時は行数が増えることに注意が必要
SELECT 句での展開
普段 event_params を展開する時はこちらの方法をよく使います。
SELECT
event_date,
event_name,
-- URLを取得
(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 = 'page_title') as page_title,
-- セッションIDを取得(数値型)
(SELECT value.int_value
FROM UNNEST(event_params)
WHERE key = 'ga_session_id') as session_id
FROM `your_project.your_dataset.events_*`
WHERE event_date = '20231125'
AND event_name = 'page_view'
LIMIT 5;
この方法のメリット:
- 必要なパラメータのみを列として取得できる
- 1 イベント 1 行で取得できる
- 集計が容易
結果例:
event_date | event_name | page_location | page_title | session_id |
---|---|---|---|---|
20231125 | page_view | https://example.com/ | ホームページ | 1234567890 |
20231125 | page_view | https://example.com/products | 商品一覧 | 1234567890 |
20231125 | page_view | https://example.com/cart | カート | 1234567891 |
20231126 | page_view | https://example.com/ | ホームページ | 1234567892 |
20231126 | page_view | https://example.com/about | 会社概要 | 1234567892 |
... | ... | ... | ... | ... |
実践:ページビューの時系列分析
よくある分析パターンを見ていきましょう。
- 特定ページの PV 数集計
- 流入元(リファラー)分析
- スクロール率の計算
ここでは 1 つの例として、特定ページの PV 数を集計するクエリを詳しく解説します。
WITH PageViews AS (
SELECT
event_date,
-- URLを取得
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'page_location') AS page_location
FROM `your_project.your_dataset.events_*`
WHERE
-- 効率的な日付範囲の指定
_TABLE_SUFFIX BETWEEN '20231101' AND '20231130'
-- ページビューイベントのみを対象に
AND event_name = 'page_view'
)
SELECT
event_date,
COUNT(*) as page_views
FROM PageViews
WHERE page_location = 'https://example.com/specific-page'
GROUP BY event_date
ORDER BY event_date;
このクエリのポイント:
-
WITH
句(CTE)の活用- 必要なデータを一時テーブルとして作成
- クエリを読みやすく整理
- 中間データの再利用が可能
-
効率的な日付範囲の指定
-
_TABLE_SUFFIX
を使用して必要な日付のテーブルのみを参照 - GA4 のテーブルは日付ごとにデータが分かれているため、この方法が効率的
※詳しく知りたい方はパーティションテーブルで調べてみてください。
-
-
必要最小限のデータ取得
- 必要なカラムのみを選択
- 特定のイベントタイプのみを抽出
実行結果:
event_date | page_views |
---|---|
20231101 | 245 |
20231102 | 312 |
20231103 | 289 |
... | ... |
クエリ最適化のポイント
-
日付範囲の指定
-- 非効率な例(すべてのテーブルをスキャン) WHERE event_date BETWEEN '20231101' AND '20231130' -- 効率的な例(必要なテーブルのみをスキャン) WHERE _TABLE_SUFFIX BETWEEN '20231101' AND '20231130'
-
必要なカラムのみの抽出
-- 非効率な例(すべてのパラメータを展開) SELECT * FROM UNNEST(event_params) -- 効率的な例(必要なパラメータのみを展開) SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location')
-
WITH 句の活用
- 複雑なクエリを分かりやすく整理
- 中間データの再利用による効率化
- メンテナンス性の向上
まとめ
この記事で学んだこと:
- GA4 のネスト構造の基本
- UNNEST を使った 2 つの展開方法
- FROM 句での展開(データ探索向き)
- SELECT 句での展開(分析向き)
- 実践的なクエリの書き方と最適化
次のステップ:
- セッション分析
- コンバージョン計測
- カスタムディメンションの活用
よくある課題と解決策:
- パフォーマンスの改善
- 適切な日付範囲の指定
- 必要最小限のデータ取得
- 複雑なイベントの取り扱い
- WITH 句を使った整理
- 適切なデータ型の使用
- 大規模データの効率的な処理
- パーティション分割の活用
- クエリの最適化
次の記事では、このネスト構造の理解を活かして、セッション、PV 数、ユーザー数などの重要なメトリクスの取得方法について解説します。
Discussion