みなさん、こんにちは！
今回の記事は今田が担当します。
 
日ごろはGoogle BigQueryでデータ抽出や、Tableauを活用したデータ分析・ビジュアライズ作成などをしています。
 
この記事では、サンプルのGA4生ログをもとに配列データをフラットテーブルに変換する方法を解説します。
GA4（Google Analytics 4）の生ログは、配列データを含む構造化されたデータです。配列操作のリファレンスとサンプルデータセットは以下リンクより参照ください。

※配列操作のリファレンス

※サンプルデータセット

1．サブクエリを使用したフラット化の解説

以下のサンプルクエリを使って、サブクエリを使用したフラット化を解説します。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS URL,
  COUNT(DISTINCT user_pseudo_id) AS n_left
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_date BETWEEN '20201201' AND '20201231'
GROUP BY
  1

event_paramsは、イベントパラメーター名のkeyとパラメーターの値のValueが格納されています。
この例では、event_paramsという配列データからkeyがpage_locationである要素を抽出しています。
Valueはkeyにあったデータ型を指定する必要があります。データ型を間違えるとNULLになるので注意が必要です。

2．CROSS JOINを使用したフラット化の解説

以下のサンプルクエリを使って、CROSS JOINを使用したフラット化を解説します。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS URL,
  COUNT(DISTINCT user_pseudo_id) AS n_cross
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
CROSS JOIN
  UNNEST(items) AS items
WHERE
  event_date BETWEEN '20201201' AND '20201231'
GROUP BY
  1

CROSS JOINを使用して配列データをフラット化する場合は、上記のような記述をします。
上記の例ではCROSS JOINを明示的にしていますが、カンマ演算子（,）で CROSS JOINを暗黙的に実行することも可能です。
 
要素の重複が発生する場合があるので注意が必要です。
 
CROSS JOINを使用してフラット化すると、空の配列または NULL 配列を含む行は除外されます。このような行を入れる場合には、次で説明するLEFT JOINを使用します。

3．LEFT JOINを使用したフラット化の解説

以下のサンプルクエリを使って、LEFT JOINを使用したフラット化を解説します。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS URL,
  COUNT(DISTINCT user_pseudo_id) AS n_left
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
LEFT JOIN
  UNNEST(items) AS items
WHERE
  event_date BETWEEN '20201201' AND '20201231'
GROUP BY
  1

LEFT JOINを使用して配列データをフラット化する場合は、上記のような記述をします。
LEFT JOINを使用したフラット化は元のデータを保持しながら、配列データを結合します。CROSS JOINとは異なり要素が一致しない場合はNULL値が設定されます。
 

4．CROSS JOINとLEFT JOINの差分をサンプルデータで確認

以下のクエリを実行して結合の違いを確認します。

WITH l AS (
SELECT
  (select value.string_value from unnest(event_params) where key = 'page_location') URL,
  COUNT(distinct user_pseudo_id) user_left,
  COUNT(items.item_id) item_left
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  LEFT JOIN
    UNNEST(items) AS items
WHERE
  event_date BETWEEN '20201201'
  AND '20201231'
GROUP BY
  1
)
, c AS (
SELECT
  (select value.string_value from unnest(event_params) where key = 'page_location') URL,
  COUNT(distinct user_pseudo_id) user_cross,
  COUNT(items.item_id) item_cross
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  CROSS JOIN
    UNNEST(items) AS items
WHERE
  event_date BETWEEN '20201201'
  AND '20201231'
GROUP BY
  1
)
SELECT
  l.URL,
  user_left,
  user_cross,
  item_left,
  item_cross
FROM
  l 
  INNER JOIN c ON l.URL = c.URL
ORDER BY
  2 desc
LIMIT 10

以下の実行結果のように、CROSS JOINはitemsがNULLの場合は除外されるため、LEFT JOINよりもユーザー数が少なくなることが確認できます。

| URL                                                           | user_left | user_cross | item_left | item_cross |
|---------------------------------------------------------------|-----------|------------|-----------|------------|
| https://shop.googlemerchandisestore.com/                       | 32527     | 25719      | 42640     | 42640      |
| https://shop.googlemerchandisestore.com/Google+Redesign/Apparel | 21343     | 2230       | 100536    | 100536     |
| https://shop.googlemerchandisestore.com/store.html             | 13202     | 6336       | 189821    | 189821     |
| https://googlemerchandisestore.com/                             | 12705     | 1357       | 1963      | 1963       |
| https://shop.googlemerchandisestore.com/Google+Redesign/Shop+by+Brand/YouTube | 10495     | 1910       | 67560     | 67560      |
| https://shop.googlemerchandisestore.com/Google+Redesign/Apparel/Mens | 8590      | 4032       | 306204    | 306204     |
| https://shop.googlemerchandisestore.com/signin.html            | 8533      | 2          | 6         | 6          |
| https://shop.googlemerchandisestore.com/Google+Redesign/Clearance | 8183      | 2882       | 202524    | 202524     |
| https://www.googlemerchandisestore.com/                         | 7104      | 396        | 519       | 519        |
| https://shop.googlemerchandisestore.com/Google+Redesign/New    | 6975      | 1685       | 60384     | 60384      |

5．個人的なおすすめの使い分け

個人には、一つのイベントに対して複数のデータが生成される場合（例：一度に複数の商品を購入する場合）はLEFT JOINで取り出します。
それ以外はサブクエリで記述するのが可読性が高いと感じています。
 
使用方法の違いによって得られるデータの内容が異なるため、使用目的やデータの構造に応じて適切な方法を選択ください。

6．最後に

GA4の生ログからフラットテーブルを作成するための配列操作について解説しました。
サブクエリを使用したフラット化、CROSS JOINを使用したフラット化、LEFT JOINを使用したフラット化のそれぞれの方法について具体的なクエリを示し、得られるデータの違いも説明しました。少しでも参考になる点があれば幸いです。

●●●

SMKT事業部では、データエンジニアを募集しています。是非こちらもご覧ください。

▽その他募集職種こちらから▽

●●●