【GA4】GA4のローデータをunnestしてさらにデータ整形する

GA4とは次世代のGoogle AnalyticsというWebとアプリが統合されたアクセス解析ツールのことで、このユーザーのアクセスデータをBigQueryに吐き出すことができます。
この吐き出されたデータは何も加工されていないローデータで、中を見てみると以下のようにnestされたデータが入っています。
以下の画像の赤枠がGA4で言うイベントであり、イベントごとのデータがこのローデータには入っています。
 

 
赤枠を見ると1レコードではあるが、中身を見ると「event_...key」（正確にはevent_params.keyといいます）と記載しているところから、複数のレコードがさらに入っているように見えます。
これがいわゆる配列でネストされたデータと呼ばれます。
1つのイベントレコードの中に、複数のレコードがさらに入っているような入れ子の形ですね。

このようなデータ形式だと通常のSQLでうまく実行できそうにないイメージが浮かびそうですね！
そこでこのネストされたデータを、通常のSQLで扱えるようにするのがアンネスト(unnest)と言います。
 
ここではアンネストについて取り扱いつつ、そのアンネストしたデータをより扱いやすくするためのデータ整形について考えたいと思います。
 

そもそもアンネスト(unnest)とは

イメージとしては、以下のようになっていて、
1つのイベントには様々な情報があります。イベントが起きたところのページ情報や、ページタイトル、、そういった情報などが紐づき、それらの情報はイベントパラメータに入ったりするので、
以下のようにローデータを見ると、1イベントにイベントパラメータが複数紐づく形となり、こういったデータ形式になります。
他にも1つのイベントに複数ぶら下がるものとして、ユーザープロパティなどがあります。

 

実際のデータを見ると以下のように配列のような形になっています。

 

 

これを取り使いやすくするために、通常アンネストという作業を行います。
このnestされたデータをunnestつまりnestをなくす処理のことを、巷ではunnestすると言います。
 

 

こうすることで、ネストされた箇所もなくなり、普通のテーブル形式で扱えるようになります。
アンネストをすると以下のようにテーブルが平準化します。

 
こうすることで、みなさんご存知の通りのROW、COLUMNのテーブルになります。
 

GA4のローデータをunnestする

SELECT
  event_date,
  event_timestamp,
  event_name,
  event.key,
  event.value.string_value,
  event.value.int_value,
  event.value.float_value,
  event.value.double_value
FROM
  `analytics_123456789.events_*`
  ,unnest(event_params) as event
WHERE
  _TABLE_SUFFIX = "20220409"

アンネストする対象としては、スキーマの中のRECORD型となっているものです。
event_paramsが今回RECORD型になっているので、このevent_paramsでunnestをするということになります。
unnestするクエリは、
,unnest(event_params) as event
の部分になります。
こうすることで、「event_paramsでunnestする」と宣言し、アンネストした結果へのアクセスはeventでアクセスするということになります。

なのでunnest後は、event.keyやevent.value.string_valueなどでアクセスをするということになります。

 
event_paramsはRECORD型で、REPETABLEつまり複数作成できるということです。
 

BigQuery特有のユーザー定義関数を用いたデータ整形

UDFとはBigQueryで使用できるユーザー定義関数と呼ばれるBigQuery内で関数を独自定義してデータ整形をする機能です。
その関数の中にはSQLだけではなく、javascriptなどの言語を用いてデータ整形をすることもできます。
以下でUDFは扱ってますので、もしよければ是非。

 

イベントパラメータはイベントごとに発生するので、
イベント粒度のレコードにするのであれば、イベントパラメータを1レコードに置きたいですよね。
なのでその変換SQLは以下となります。

# イベントパラメータの値がstringかintなどあるので、どれかには入っているので、入ってるものを取得するためcase when

SELECT
  event_name,
  (SELECT MAX(IF(event.key='page_location', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_location,
  (SELECT MAX(IF(event.key='page_referrer', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_referrer,
  (SELECT MAX(IF(event.key='page_title', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_title,
  (SELECT MAX(IF(event.key='ga_session_id', event.value.int_value, NULL)) FROM UNNEST(event_params)) AS ga_session_id,
  (SELECT MAX(IF(event.key='ga_session_number', event.value.int_value, NULL)) FROM UNNEST(event_params)) AS ga_session_number
FROM
  `analytics_123456789.events_*`
  ,unnest(event_params) as event
WHERE
  _TABLE_SUFFIX = "20220409"

record型がいわゆるアンネストに指定するもの。
UDFで配列渡してstringの値があればそれ返す関数を作成する。intやstringなど汎用性を高くするために、値が入っていればで判定

BigQueryのUDFを用いると以下のように書くことができるが、
javascriptのlist型をBigQueryはサポートしていないので、以下のようにARRAYなど配列の中に入る値の型でいちいち指定しないといけなくなる。

#standardSQL
CREATE TEMP FUNCTION getArrayString(json_row ARRAY)
RETURNS STRING
LANGUAGE js
AS """
  for(i = 0 ; i < json_row.length ; i++){
    if(json_row[i] != null){
      return json_row[i];
    }
  }
""";

CREATE TEMP FUNCTION getArrayInt(json_row ARRAY)
RETURNS INT64
LANGUAGE js
AS """
  for(i = 0 ; i < json_row.length ; i++){
    if(json_row[i] != null){
      return json_row[i];
    }
  }
""";

With a as(
  SELECT
    event_timestamp,
    event_name,
    (SELECT MAX(IF(event.key='page_location', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_location,
    (SELECT MAX(IF(event.key='page_referrer', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_referrer,
    (SELECT MAX(IF(event.key='page_title', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_title,
    (SELECT MAX(IF(event.key='ga_session_id', event.value.int_value, NULL)) FROM UNNEST(event_params)) AS ga_session_id,
    (SELECT MAX(IF(event.key='ga_session_number', event.value.int_value, NULL)) FROM UNNEST(event_params)) AS ga_session_number
  FROM
    `analytics_123456789.events_*`
    ,unnest(event_params) as event
  WHERE
    event_name = "page_view"
    and 
    _TABLE_SUFFIX = "20220409"
)

SELECT 
  event_timestamp,
  event_name,
  getArrayString(ARRAY_AGG(page_location)) as page_location,
  getArrayString(ARRAY_AGG(page_referrer)) as page_referrer,
  getArrayString(ARRAY_AGG(page_title)) as page_title,
  getArrayInt(ARRAY_AGG(ga_session_id)) as ga_session_id,
  getArrayInt(ARRAY_AGG(ga_session_number)) as ga_session_number
 FROM a GROUP BY event_timestamp, event_name

BigQuery特有のユーザー定義関数を用いないデータ整形

いやでも待てよ、、
Group Byすると、page_locationはnullか正式な値の2種類で、その中でMAXを取れば、その正式な値を取ることは明確だから、
MAXだけでいいじゃん、、ということで、MAXにして書き換えました。
これでも取得できます。

With ga4_unnest_from_raw as(
  SELECT
    event_timestamp,
    event_name,
    (SELECT MAX(IF(event.key='page_location', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_location,
    (SELECT MAX(IF(event.key='page_referrer', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_referrer,
    (SELECT MAX(IF(event.key='page_title', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS page_title,
    (SELECT MAX(IF(event.key='ga_session_id', event.value.int_value, NULL)) FROM UNNEST(event_params)) AS ga_session_id,
    (SELECT MAX(IF(event.key='ga_session_number', event.value.int_value, NULL)) FROM UNNEST(event_params)) AS ga_session_number,
    (SELECT MAX(IF(event.key='source', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS source,
    (SELECT MAX(IF(event.key='term', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS term,
    (SELECT MAX(IF(event.key='medium', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS medium,
    (SELECT MAX(IF(event.key='campaign', event.value.string_value, NULL)) FROM UNNEST(event_params)) AS campaign,
    (SELECT MAX(IF(up.key='yoshida_user_property', up.value.string_value, NULL)) FROM UNNEST(user_properties)) AS yoshida_user_property
  FROM
    `analytics_123456789.events_*`
    ,unnest(event_params) as event
    ,unnest(user_properties) as up
  WHERE
    event_name = "page_view"
    and 
    _TABLE_SUFFIX = "20220409"
)
SELECT 
  event_timestamp,
  event_name,
  MAX(page_location) as page_location,
  MAX(page_referrer) as page_referrer,
  MAX(page_title) as page_title,
  MAX(ga_session_id) as ga_session_id,
  MAX(ga_session_number) as ga_session_number,
  MAX(source) as source,
  MAX(term) as term,
  MAX(medium) as medium,
  MAX(campaign) as campaign,
  MAX(yoshida_user_property) as yoshida_user_property
 FROM ga4_unnest_from_raw GROUP BY event_timestamp, event_name

そもそも、Groupbyすると、page_locationは複数の値を持つことになり、その中でMAXを取ればnullと値で、必ず値の方がmaxになるから、maxで問題ない
UDFが便利って思い始めてから、UDFの頭になっていたせいで普通のSQLを忘れかけていた、、、、

 

イベントパラメータに入っているもの全てを出すことはできるのか？
要はイベントパラメータを検知して、自動的にsqlに含めることができるか？
これは厳しそう、、
ただできてもいきなり知らないイベントパラメータが紛れ込んで、それを取得して、その結果を別テーブルなどに吐き出す際にスキーマが増えて、インサートできないみたいなことでエラーになる可能性もあるので、
やはり要件定義をしっかりした上で、自動化は避けた方が良さそうですね、、やってみたかった。
 

イベントパラメータなどをカラムとして追加するメリット・デメリット

今回は上でイベントパラメータやユーザープロパティはカラムとして追加していくような形を取りましたが、GA4でイベントパラメータが追加されるたびに、上記SQLに追加していく必要があります。
となると運用上あまりよろしくないと思います。
GA4を計測設定する人と、GCPにて処理をする人で情報をちゃんと共有できるような組織体制でないと、集計漏れが発生してしまうからです。
それならば、カラム「key」とカラム「value」のような2つのカラムを追加していくのでも良いのでは？そうすれば別にイベントパラメータが新しく追加されても、別にSQLに追加しなくてもいいよね！となると思います。
 

そこでここではメリデメを考えてみたいと思います。

	keyとvalueカラム	イベントパラメータごとにカラム追加
メリット	今後イベパラが追加された際に、自動的に計測されるようになる。	イベパラはそもそもイベントの属性という概念だから、1イベントに対して属性としてカラムを作るのがベスト。なので、1イベントに対して複数の属性、複数のイベパラが紐づくのが良いので、1レコードにイベパラを繋いで行くのが良い。 すぐイベパラの値を見たい時などに、1イベント（1レコード）を見れば、どんな値が入るのかをBQ上で簡単に確認ができる。（上のだと、順序が決められてないので、あるイベパラがどんな値入ってるか確認したい時にSQLを叩かないといけないなどが発生する） BQの列思考型にそもそも思想があっている。 カラムを指定しなければ全て読むこともないので、スキャン量を抑えれる。
デメリット	スキャン量が多くなる。orderbyなどの並び替えの際にスキャン内のレコードの移動が多くなり処理が重くなり、メモリが枯渇する可能性がある。 あとテーブル内の順序は保証されないので、各イベントごとのイベントパラメータ情報を見たい場合に、すぐ確認が難しい。いちいち、SQL書いて確認するなどが必要になる。 レコード数が最低5倍は増える。（イベパラでも自動的に補足するイベパラは5つあるので、最低でも5倍のレコード数が増える計算となる） 1つのカラムに入れるためには文字列型など1つのカラムに統一させないといけなくなる。（keyカラムとvalueカラムを作って確かに1対1だけど、valueカラムに指定する型が決まらない。数値や日付方、文字列方などがあるから。その分型分カラム作ればいいってなるが、そうなったら元々のGA4のローデータの形式とそんな変わんない。unnestしただけ。）	イベパラを作成すると、改めて1からunnest処理に追加をする必要がある。（忘れたらアウト）

自分なりにまとめてみましたが、どうでしょうか？
他にもありそうですが、これらのことから、基本的には横にカラムを追加していく形が良いのではと想定してます！