IT web開発

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

2022年9月3日

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のセッション数

以下にある通り、GA4のセッション数は少しずれるらしいですね。
https://support.google.com/analytics/answer/9191807?hl=ja

▼ お問合せはこちら

-IT, web開発
-,

© 2023 Yosshi Labo. Powered by AFFINGER5