BigQuery での GA4 データの理解とネスト構造
BigQuery での GA4 データの理解と活用
GA4 のデータを BigQuery で分析しようとすると、最初に戸惑うのが独特のデータ構造です。「なぜこんなに複雑なの？」「どうやってデータを取り出せばいいの？」という声をよく聞きます。
このデータ構造には理由があります。GA4 は様々なイベントやパラメータを柔軟に記録できるように設計されており、そのために BigQuery では「ネスト構造」という特殊な形式でデータを保存しています。
この記事では、GA4 データの構造を理解し、効率的にデータを取り出す方法を、例を交えながら解説します。
BigQuery ブラウザでの GA4 データの確認方法
まずはBigQuery のブラウザインターフェースで GA4 のデータを見てみましょう。テーブルをクリックすると、スキーマ（データ構造）が表示されます。
BigQuery の GA4 データスキーマ表示画面
特に重要なカラムは以下の通りです：
-
event_date: イベントが発生した日付 -
event_name: イベントの種類（page_view, click など） -
event_params: イベントに関する詳細情報（配列形式） -
user_properties: ユーザーに関する情報（配列形式）
スキーマを見ると、event_paramsの型がRECORDでREPEATEDとなっていることに気づくと思います：
-
RECORD: 複数のフィールドを持つ構造体であることを示します -
REPEATED: そのレコードが配列（複数の要素）として存在することを示します
つまり、event_paramsは「複数のフィールドを持つデータ（RECORD）」が「配列として繰り返し現れる（REPEATED）」という入れ子構造になっています。このような構造を「ネスト構造」と呼び、GA4 データの特徴的な部分です。
実際のデータがどのように格納されているか、プレビューで確認してみましょう：
BigQuery の GA4 データプレビュー画面
プレビュー画面を見ると、データの構造が少し複雑に見えるかもしれません。例えば：
-
event_params.keyという列が複数存在する -
event_params.valueの後に.string_valueや.int_valueといった型の指定がある - 同じイベントに対して複数の行が存在する
これは先ほど説明したネスト構造が、実際のデータとしてどのように格納されているかを示しています。このような構造になっているからこそ、GA4 は柔軟にさまざまなイベントデータを記録できるのです。しかし一方で単純な SQL クエリでは目的のデータを取り出すことが難しくなっています。
GA4 データの基本構造
では、効率的なデータの取り出し方を学ぶ前に, GA4 のデータがどのように構成されているのか、もう少し詳しく見ていきましょう。
GA4 のデータは「イベント」を基準に記録されています。ユーザーがページを閲覧したり（page_view）、クリックしたり（click）するたびに、1 つのイベントとして記録されます。
各イベントには、以下のような情報が含まれています：
- イベントの基本情報（日時、イベント名など）
- イベントに関する詳細情報（
event_params） - ユーザーに関する情報（
user_properties）
特に重要なのが event_params で、これがネスト構造になっています。
ネストされたデータ構造とは
ネスト（入れ子）構造とは、データの中にさらにデータが含まれている状態を指します。GA4 データの場合、event_params は配列（Array）として格納されており、その中に複数のパラメータが含まれています。
例えば、ページビュー（page_view）イベントの場合：
{
"event_name": "page_view",
"event_params": [
{
"key": "page_location",
"value": {
"string_value": "https://example.com/products"
}
},
{
"key": "page_title",
"value": {
"string_value": "商品一覧"
}
},
{
"key": "ga_session_id",
"value": {
"int_value": "1234567890"
}
}
]
}
このように、1 つのイベントに対して複数のパラメータ情報が配列として格納されています。各パラメータは：
-
key: パラメータの名前 -
value: パラメータの値（型によってstring_value、int_valueなどが異なる）
という構造になっています。
このネスト構造は、データを柔軟に保存できる利点がありますが、そのままでは分析に使いづらいという特徴があります。そこで、次のセクションで説明する 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 関数による展開
UNNEST関数は、配列を行に展開するための関数です。以下のような方法で使用できます：
SELECT
event_date,
event_name,
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'
LIMIT 10;
このクエリを実行すると、1 つのイベントに対して複数のevent_paramsの行が生成されます。例えば：
| event_date | event_name | param_key | string_value | int_value |
|---|---|---|---|---|
| 20231125 | page_view | page_location | /products | null |
| 20231125 | page_view | page_title | 商品一覧 | null |
| 20231125 | page_view | ga_session_id | null | 1234567890 |
よく使う展開パターン
実務でよく使用する展開パターンを紹介します：
- 特定のパラメータだけを取り出す場合：
SELECT
event_date,
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 = 'page_title') as page_title
FROM `your_project.your_dataset.events_*`
WHERE event_date = '20231125'
LIMIT 5;
このパターンは、必要なパラメータだけを列として取り出 たい場合に便利です。
実践例：ページビューの時系列分析
ここまでの知識を活用して、実際のユースケースを見ていきましょう。例えば、特定のページのページビュー数を 1 ヶ月間、日付ごとに集計するケースを考えてみます。
WITH PageViews AS (
SELECT
event_date,
(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 の作成）- 必要なデータを一時テーブルとして作成
-
event_paramsからpage_locationを抽出
-
日付範囲の指定
-
_TABLE_SUFFIXを使用して対象期間を指定 - GA4 のテーブルは日付ごとに分かれているため、この方法が効率的
-
-
イベントの絞り込み
-
event_name = 'page_view'でページビューイベントのみを抽出
-
-
集計とソート
- 日付ごとにカウント
- 日付順に並び替え
実行結果は以下のようになります：
| event_date | page_views |
|---|---|
| 20231101 | 245 |
| 20231102 | 312 |
| 20231103 | 289 |
| ... | ... |
このように、ネストされたデータも適切に展開することで、必要な分析を行うことができます。
クエリ最適化のポイント
-
日付範囲の指定
-
_TABLE_SUFFIXを使うことで、必要な日付のテーブルのみを参照 - パーティション分割されたテーブルの場合は
event_dateでの絞り込みも効果的
-
-
必要なカラムのみの抽出
-
SELECTで必要最小限のカラムのみを指定 - 特に
UNNESTでは必要なパラメータのみを展開
-
-
WITH 句の活用
- 複雑なクエリを分かりやすく整理
- 中間データの再利用が可能
まとめ
この記事では、GA4 データのネスト構造とその取り扱い方について解説してきました。ポイントを整理しましょう：
データ構造の理解
- GA4 のデータは「イベント」を基準に記録される
- 重要な情報の多くはネスト構造で格納されている
データの取り出し方
- 基本的なカラム（
event_date、event_nameなど）は通常の SQL で取得可能 - ネストされたデータは
UNNEST関数を使って展開する - 展開方法は主に 2 つ：
-
SELECT内での展開：特定のパラメータを列として取得 -
FROM句での展開：すべてのパラメータを行として展開
-
クエリ作成のベストプラクティス
- 必要な期間のテーブルのみを参照（
_TABLE_SUFFIXの活用） - 必要なパラメータのみを展開（パフォーマンスの考慮）
-
WITH句を使って複雑なクエリを整理
次の記事では、このネスト構造の理解を活かして、セッション、PV 数、ユーザー数などの重要なメトリクスの取得方法について解説します。
Discussion