UNNESTとCROSS JOINでGA4のevent_paramsをフラット化するSQL

今回は、UNNEST関数とCROSS JOINを使ってevent_paramsをフラット化するSQLクエリについて紹介する。

SQLクエリ概要

GA4からBigQueryへとエクスポートしたデータスキーマの特徴（そして混乱ポイント…）として、発生した各種イベントのパラメータが event_params というカラムに格納されており、データ型が RECORD 、モードが REPEATED に設定されている。

つまり STRUCT 型データの配列ということになる（ややこしい…）

ネストされたデータのある列を作成するには、列のデータ型をスキーマで RECORD に設定します。RECORD には、Google 標準 SQL の STRUCT 型としてアクセスできます。STRUCT は順序付きフィールドのコンテナです。

繰り返しデータが含まれる列を作成するには、スキーマでモードを REPEATED に設定します。繰り返しフィールドには、Google 標準 SQL の ARRAY 型としてアクセスできます。

RECORD 列には REPEATED モードを設定できます。このモードは STRUCT 型の配列として表されます。また、レコード内のフィールドを繰り返すこともできます。これは、ARRAY を含む STRUCT として表されます。

（出典）

ネストされた列と繰り返し列を定義する

https://cloud.google.com/bigquery/docs/nested-repeated?hl=ja#define_nested_and_repeated_columns

これは何を意味しているかというと、以下のように key にパラメータの名前、 value にパラメータの値が格納されたPythonのディクショナリ的なものが配列として複数格納されているようなイメージだ。

[
	{
    "key": "ga_session_number",
    "value": {
      "string_value": null,
      "int_value": "1",
      "float_value": null,
      "double_value": null
    }
  }, 
	{
    "key": "page_title",
    "value": {
      "string_value": "Bags | Lifestyle | Google Merchandise Store",
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }
]

この配列内にある event_params.key や event_params.value の値をそのまま SELECT で指定して取得することはできないので、 UNNEST という演算子を使って配列をフラットニングする必要がある。

ARRAY を一組の行に変換（フラット化）するには、UNNEST 演算子を使用します。UNNEST は ARRAY を取得し、ARRAY に含まれる各要素を 1 行にしたテーブルを返します。

（出典）

配列内の要素をテーブル内の行に変換する

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ja#flattening_arrays

その上で元のテーブルと CROSS JOIN することで、 event_params 内のパラメータにアクセスすることが可能になる。

UNNEST と CROSS JOIN を使用して、STRUCT の ARRAY をフラット化することもできます。

（出典）

ARRAY 内の STRUCT 要素のクエリ

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ja#query_structs_in_an_array

また、CROSS JOIN ではなくスカラーサブクエリを使用することで event_params 内のパラメータの値を取得することも可能である。

そちらの方法に関しては、こちらの記事で解説している。

UNNESTでGA4のevent_params内のパラメータを取り出すSQLクエリ

SQLクエリサンプル

クエリのサンプルは、以下のようになる。

SELECT
  event_date,
  event_name,
  user_pseudo_id,
  key AS event_params_key,
  COALESCE(value.string_value, CAST(value.int_value AS string)) AS event_params_value

FROM
    `technogram-354804.analytics_291973511.events_intraday_202209*`, UNNEST(event_params) -- UNNEST前のカンマはCROSS JOINを意味する
;

（参考）

COALESCE関数

https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions?hl=ja#coalesce

BigQueryでの集計結果

BigQueryでの集計結果を、フラット化する前後で比較してみる。

（フラット化する前）

（フラット化した後）