🤖

BigQuery×GA4 データ の理解 event_params のネスト構造

2024/11/25に公開

はじめに

GA4 のデータを BigQuery で扱う際、最初に戸惑うのがデータ構造、スキーマです。
bigquery で ga4 データをプレビューで見た際に、event_params フィールドが複数のカラムに分割されていることに気がつくと思います。これはネスト構造という配列でデータが格納されているためです。

この記事では、GA4 データの構造、ネストされたデータの取り出し方法を解説していきます。

GA4 データの確認

まずは BigQuery のブラウザインターフェースで GA4 のデータを見てみましょう。テーブルをクリックすると、スキーマ(データ構造)が表示されます。

GA4データのスキーマ|800x400
BigQuery の GA4 データスキーマ表示画面

よく使うカラムは以下の通り:

  • event_date: イベントが発生した日付
  • event_name: イベントの種類(page_view, click など)
  • event_params: イベントに関する詳細情報(配列形式)
  • user_properties: ユーザーに関する情報(配列形式)

GA4 のデータは「イベント」を基準に記録されています。ユーザーがページを閲覧したり(page_view)、クリックしたり(click)するたびに、1 つのイベントとして記録され、そのイベントに関する詳細情報がevent_paramsに格納されます。

スキーマを見ると、このevent_paramsの型がRECORDREPEATEDとなっていることに気づくと思います。

  • RECORD: 1 つのデータの中に複数の項目がセットになっている構造です。GA4 では、各パラメータがkeyvalueという 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は:

  1. 各パラメータがkeyvalueのセット(RECORD)になっていて
  2. それが配列として複数存在する(REPEATED)という
    入れ子構造(ネスト構造)になっています。

このネスト構造は、GA4 が柔軟にさまざまなイベントデータを記録できる利点がありますが、そのままでは分析に使いづらいという特徴があります。実際の BigQuery での表示を見てみましょう:

GA4データのプレビュー|800x300
BigQuery の GA4 データプレビュー画面

プレビュー画面を見ると、データの構造が少し複雑に見えるかもしれません。例えば:

  • event_params.key という列が複数存在する
  • event_params.value の後に .string_value.int_value といった型の指定がある
  • 同じイベントに対して複数の行が存在する

これは先ほど説明したネスト構造が、実際のデータとしてどのように格納されているかを示していま
す。
GA4 のブラウザの画面だと、見やすいように配列が展開されて表示されています。
プレビューが実際のテーブルの構造だと思ってクエリを書くと、思った通りの結果は得られません。
次のセクションではUNNEST関数を使って、このネストされたデータを効率的に取り出す方法を見ていきましょう。

データの取り出し方:基本編

まずは簡単なところから始めましょう。event_dateevent_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_paramsuser_propertiesなどの重要な情報が配列形式で格納されています。

UNNEST の 2 つの使い方

  1. FROM 句での展開

    • すべてのパラメータを行として展開
    • データの探索や、どんなパラメータがあるか確認する際に便利
    • 1 つのイベントが複数行に展開されるため、集計時は注意が必要
  2. 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
... ... ... ... ...

実践:ページビューの時系列分析

よくある分析パターンを見ていきましょう。

  1. 特定ページの PV 数集計
  2. 流入元(リファラー)分析
  3. スクロール率の計算

ここでは 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;

このクエリのポイント:

  1. WITH句(CTE)の活用

    • 必要なデータを一時テーブルとして作成
    • クエリを読みやすく整理
    • 中間データの再利用が可能
  2. 効率的な日付範囲の指定

    • _TABLE_SUFFIXを使用して必要な日付のテーブルのみを参照
    • GA4 のテーブルは日付ごとにデータが分かれているため、この方法が効率的
       ※詳しく知りたい方はパーティションテーブルで調べてみてください。
  3. 必要最小限のデータ取得

    • 必要なカラムのみを選択
    • 特定のイベントタイプのみを抽出

実行結果:

event_date page_views
20231101 245
20231102 312
20231103 289
... ...

クエリ最適化のポイント

  1. 日付範囲の指定

    -- 非効率な例(すべてのテーブルをスキャン)
    WHERE event_date BETWEEN '20231101' AND '20231130'
    
    -- 効率的な例(必要なテーブルのみをスキャン)
    WHERE _TABLE_SUFFIX BETWEEN '20231101' AND '20231130'
    
  2. 必要なカラムのみの抽出

    -- 非効率な例(すべてのパラメータを展開)
    SELECT * FROM UNNEST(event_params)
    
    -- 効率的な例(必要なパラメータのみを展開)
    SELECT
      (SELECT value.string_value
       FROM UNNEST(event_params)
       WHERE key = 'page_location')
    
  3. WITH 句の活用

    • 複雑なクエリを分かりやすく整理
    • 中間データの再利用による効率化
    • メンテナンス性の向上

まとめ

この記事で学んだこと:

  • GA4 のネスト構造の基本
  • UNNEST を使った 2 つの展開方法
    • FROM 句での展開(データ探索向き)
    • SELECT 句での展開(分析向き)
  • 実践的なクエリの書き方と最適化

次のステップ:

  • セッション分析
  • コンバージョン計測
  • カスタムディメンションの活用

よくある課題と解決策:

  • パフォーマンスの改善
    • 適切な日付範囲の指定
    • 必要最小限のデータ取得
  • 複雑なイベントの取り扱い
    • WITH 句を使った整理
    • 適切なデータ型の使用
  • 大規模データの効率的な処理
    • パーティション分割の活用
    • クエリの最適化

次の記事では、このネスト構造の理解を活かして、セッション、PV 数、ユーザー数などの重要なメトリクスの取得方法について解説します。

参考文献

Discussion