【BigQuery】GA4 event_paramsのUNNNESTによる展開と分析：ダメなクエリと正しいクエリ

はじめに

GA4をBigQueryにExport設定して、BigQuery上でGA4データを分析する際に、よくつまずくのが、GA4のevent_paramsの解析の仕方。

今回は、こちらに関してまとめる。

event_paramsとは？

GA4のイベントは、特定のユーザーの行動やサイト、アプリの状態変化を記録する。GA4では、イベントのトラッキングと分析にevent_nameとevent_paramsの2つの主要な要素を使用する。

event_name

• 文字列で、特定のイベントを一意に識別する。これはイベントの種類を表し、例えばpage_view、purchase、sign_upなどがあり、ユーザーで設定可能。

event_params

• イベントの各パラメータを格納した繰り返しレコード。

BigQueryにエクスポートされたGA4のイベントテーブルのフィールド（一部）。

event_params列には配列が格納されている

event_paramsはイベントに関連する追加情報を格納するレコード（またはオブジェクト）の配列。各レコードはキー(key)と値(value)のペアを持ち、イベントのコンテキストまたは追加の詳細を提供する。

具体的には、下記のように1つのevent_nameに対して、複数のevent_paramsがkey, valueのペアで格納されている。

{
	"event_name":"session_start",
	"event_params":[
		{"key":"page_referrer", "string_value":"https://www.bing.com/"},
		{"key":"ga_session_id", "int_value":1670474519},
		{"key":"page_location", "string_value":"https://exammple.com/page1"},
		{"key":"page_title", "string_value":"page1"},
		・・・(省略)
	]
}

BigQueryにエクスポートされたテーブルでは下記のように表示される。

よく設定されるevent_params項目

1. page_location（key）: ユーザーが訪れたページのURL。
2. page_referrer（key）: ユーザーが前に訪れたページのURL。
3. event_category（key）: イベントのカテゴリ。UA時代のパラメーター
4. event_label（key）: イベントのラベル。UA時代のパラメーター
5. value（value）: イベントに関連する数値（例：購入金額）。

ちなみに、GA4では、Universal Analytics（UA）時代のevent_categoryやevent_labelといったパラメータは標準では使用されず、任意で設定することが可能になっている。

event_paramsのvalue の4つの異なるタイプ（string, int, float, double）

さらにややこしいのが、event_paramsのvalueには、実際には4つの異なるタイプがある：string_value、int_value、float_value、double_value。

これらはイベントのコンテキストに応じて異なる種類のデータを格納するために使われる。

string_value

• テキスト情報を格納。一番よく使われる。
• ページのURL、ユーザーが実行したアクションの種類、製品名、カテゴリ名など。たとえば、ユーザーがどのボタンをクリックしたか、どの製品を閲覧したかなどの情報が含まれる。

int_value

• 整数を格納。数が大きくない特定のカウントやIDなど、小数点を含まない数値データに適している。
• 具体的なケース: ページビューの数、あるイベントが発生した回数、製品IDやカテゴリIDなど

float_value

• 浮動小数点数を格納。小数点を含む数値や、非常に大きいまたは小さい数値を扱う場合に適している。
• 具体的なケース: 製品のレビュースコアやユーザー評価、ある行動の平均時間（秒）、または特定のイベントに関連する割引率など。

double_value

• 浮動小数点数を格納するが、float_valueよりも大きな範囲と精度を持つ。より精密な計算や、より大きい範囲の数値を必要とする場合に使用される。
• 具体的なケース: 金額や距離、時間など、より高い精度が要求される数値データに使用される。たとえば、高価な商品の価格や、ユーザーがアプリを使用した総時間（ミリ秒単位）、長い距離（メートル単位）など。

event_paramsを送る際のvalueタイプは自動推論される。

GA4イベントを送信する際に、gtag.jsでは値のタイプを明示的に指定する機能は提供されていない。代わりに、渡された値のタイプに基づいて自動的に推論される。つまり、JavaScriptの変数の型がそのまま使用される。

gtag('event', 'purchase', {
  "transaction_id": "T12345", // 文字列
  "value": 9.99, // 数値 (JavaScriptではこれはfloatになるが、GA4はこれを自動的に解釈する)
  "currency": "USD", // 文字列
  "items_sold": 5, // 整数
  "shipping_method": "express", // 文字列
  "purchase_time_seconds": 360.5 // 浮動小数点数
});

下記のようにgtag内で、event_paramsのvalueに送る値の型を明示的に指定する必要はない。

gtag('event', 'purchase', {
		// このように型を明示的に指定する必要はない。
		"transaction_id": String "T12345",
		"items_sold": Int 5 
});

UNNEST演算子を使用してevent_params列の中身を展開して分析

event_paramsの中に、さらにkey-value形式の配列でデータが入れ子になっているので、集計の際にはSQLのUNNEST演算子を使って、データ内の要素を展開して単一行に変換してから集計を行う。

UNNESTは、配列や構造化データ内の要素を展開し、単一の行に変換する関数。

event_params を展開せずにアクセスしようとすると、次のような型エラーが出ることがある。

[ERROR] Cannot access field value on a value with type ARRAY<STRUCT<key INT64, value STRING>>

下記event_params配列をUNNEST関数で展開してみる。

ここでは、event_paramsからkeyとそれに対応するvalueを抽出している。valueが複数の型を持つ可能性があるため、各種のvalue（string_value、int_valueなど）を選択的に取得する。

{
	"event_name":"session_start",
	"event_params":[
		{"key":"page_referrer", "string_value":"https://www.bing.com/"},
		{"key":"ga_session_id", "int_value":1670474519},
		{"key":"page_location", "string_value":"https://exammple.com/page1"},
		{"key":"page_title", "string_value":"page1"},
		・・・(省略)
	]
}

SELECT 
  event_name,
  param.key AS param_key,
  param.value.string_value AS string_value,
  param.value.int_value AS int_value,
  param.value.float_value AS float_value,
  param.value.double_value AS double_value
FROM 
  `your_dataset.your_table`,
  UNNEST(event_params) AS param

このクエリを実行すると、以下のような表形式の結果が得られる。

event_nameで絞り込み、event_paramsでフィルタリング

実際のデータ分析では、まずevent_nameでデータを絞り込み、その後特定のparam_keyとそのstring_value（または他のvalueタイプ）でさらにフィルタリングすることが一般的。

これは、異なるevent_nameが同じparam_keyを持つことがあり、param_keyとvalueだけでフィルタリングすると、関連のないイベントデータも含まれてしまうため。たとえば、session_startイベントとpage_viewイベントが両方ともpage_locationというparam_keyを持っている可能性がありますが、これらのイベントのコンテキストは全く異なるため、まずevent_nameでフィルタリングすることが重要。

また、event_paramsの場合は、クエリの複雑性を管理するために、WITH句を使用してサブクエリを作成することが多い。サブクエリを使用すると、クエリの各部分をモジュール化し、より読みやすく、メンテナンスしやすいSQLクエリを作成できる。

以下は、サブクエリでevent_nameでイベントを特定してフィルタリングし、その後のメインクエリでevent_paramsで特定のparam_keyとstring_valueでさらに絞り込んでいる。

// サブクエリ：event_nameでフィルタリング
WITH EventDetails AS (
  SELECT
    event_name,
    param.key AS param_key,
    param.value.string_value AS string_value
  FROM
    `your_dataset.your_table`,
    UNNEST(event_params) AS param
  WHERE
    event_name = 'session_start'
)

// メインクエリ：event_paramsでさらにフィルタリング
SELECT *
FROM EventDetails
WHERE
  param_key = 'page_location' AND string_value = 'https://exammple.com/page1'

ちなみに、上記クエリは下記のように、param_keyをサブクエリに持ってくることもできる。サブクエリ内でevent_nameがsession_startであり、かつparam_keyがpage_locationであるイベントのみを選択している。その結果、メインクエリでは、page_locationが特定のURLに一致するかどうかをチェックするだけで済む。

サブクエリでparam_keyを使用すると、その後の処理で扱うデータ量が減少し、クエリの実行効率が向上する可能性がある。

WITH EventDetails AS (
  SELECT
    event_name,
    param.key AS param_key,
    param.value.string_value AS string_value
  FROM
    `your_dataset.your_table`,
    UNNEST(event_params) AS param
  WHERE
    event_name = 'session_start'
    AND param.key = 'page_location'
)

SELECT *
FROM EventDetails
WHERE
  string_value = 'https://exammple.com/page1'

event_params内のフィルターの、ダメなクエリと正しいクエリ

ダメなクエリの例

このクエリ例では、同一のparamsエントリに対して、event_categoryとevent_labelによる複数のフィルタを適用しようとしているが、これは期待通りに機能しない。

このクエリの問題点は、params.keyが同時に'event_category'と'event_label'であることを期待していること。これは不可能で、結果的にクエリは0件の結果を返す。

SELECT
  event_name,
  COUNT(*) as event_count
FROM
  `your_project.your_dataset.your_table`,
  UNNEST(event_params) as params
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND event_name = 'show'
  AND (params.key = 'event_category' AND params.value.string_value = 'dummy_category')
  AND (params.key = 'event_label' AND params.value.string_value = 'dummy_label')
GROUP BY
  event_name;

正しいクエリの例

サブクエリを用いてevent_categoryとevent_labelを別々に抽出し、それに基づいてフィルタリングを行う。

このクエリでは、まずWITH句でevent_paramsからカテゴリとラベルを抽出し、それらを個別の列として中間テーブルfiltered_eventsに格納する。その後、この中間テーブルからcategoryが'dummy_category'で、labelが'dummy_label'であるレコードを選択してカウントする。これにより、特定のカテゴリとラベルを持つイベントのみを正確に集計できる。

WITH filtered_events AS (
  SELECT
    event_name,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_category') as category,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_label') as label
  FROM
    `your_project.your_dataset.your_table`
  WHERE
    event_name = 'show'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
)

SELECT
  event_name,
  COUNT(*) as event_count
FROM
  filtered_events
WHERE
  category = 'dummy_category'
  AND label = 'dummy_label'
GROUP BY
  event_name;