mediba Adventカレンダー 5日目です。
ビジネス5Gの苅部からはGA4向けのSQLについて書いていきます。

背景

GoogleAnalytics4（GA4）のデータをBigQuery経由で取り出す際、イベントパラメータ周りの処理が煩雑になる印象がありました。例えば、Universal Analytics 360ではページURLとページタイトルの抽出に以下のようなクエリを使用します。

SELECT
  hits.page.pagePath,
  hits.page.pageTitle
FROM
  `projectId.analytics_propertyId.events_*`

この方法ではフィールド名を覚えていれば、簡単にデータを取り出せます。

しかし、GA4で同じデータを抽出しようとすると、以下のようにUNNESTを使う必要があります。

SELECT
(
  SELECT
    value.string_value -- string_valueを指定
  FROM
    UNNEST(event_params)
  WHERE
    KEY = 'page_location') AS page_location,
(
  SELECT
    value.string_value -- string_valueを指定
  FROM
    UNNEST(event_params)
  WHERE
    KEY = 'page_title') AS page_title
FROM
	`projectId.analytics_propertyId.events_*`

この方法は見づらく、記述も面倒です。UAでは2行のSELECT文で済んでいましたが、GA4ではサブクエリでイベントパラメータをUNNESTして取り出す必要があり、コードが冗長になります。

また、この方法ではvalueがint型の場合、以下のように明示的にint_valueを指定する必要があります。

SELECT
(
  SELECT
    value.int_value -- int_valueを指定
  FROM
    UNNEST(event_params)
  WHERE
    KEY = 'ga_session_id') AS ga_session_id
FROM
	`projectId.analytics_propertyId.events_*`

もちろんfloatであればfloat_valueを指定することとなります。

SELECT
(
  SELECT
    value.float_value -- float_valueを指定
  FROM
    UNNEST(event_params)
  WHERE
    KEY = 'hogehoge') AS ga_session_id
FROM
	`projectId.analytics_propertyId.events_*`

さらに、GA4では値の型が自動的に判定されるため、同じイベントパラメータであっても値によってはstring_valueやint_valueに格納されることがあります。これにより、事前に値の型が分からない場合、上記の方法ではデータの取りこぼしが発生する可能性があります。

そこで、GA4でのイベントパラメータの取得における「型指定の問題」と「コードの可読性の問題」の2点を解決する方法を考えます。

COALESCEを使って型指定を回避

型指定の問題を解決するためには、COALESCE関数が役立ちます。この関数は、与えられた引数の中で最初のNULLでない値を返します。

例1： NULLが無い場合

SELECT COALESCE('A', 'B', 'C') as result

/*--------*
 | result |
 +--------+
 | A      |
 *--------*/

例2： NULLがある場合

SELECT COALESCE(NULL, 'B', 'C') as result

/*--------*
 | result |
 +--------+
 | B      |
 *--------*

つまり以下のような形にすれば、string_valueがあれば、string_valueを、string_valueがなければ（NULLであれば）int_valueを、と続く形でNULLではない引数を返します。

SELECT
  (SELECT
    COALESCE(
      value.string_value,
      CAST(value.int_value AS string),
      CAST(value.float_value AS string),
      CAST(value.double_value AS string)
    )
  FROM
    UNNEST(event_params) AS x
  WHERE
    x.key = "page_location")
FROM
  `projectId.analytics_propertyId.events_*`

NULLではないいずれかの値が返却されるため、事前に型を意識する必要がなくなります。

ただ型指定の問題は解決したとしても、毎回イベントパラメータごとにCOALESCEを記述していたらコードがより冗長的になってしまいます。

UDFとして関数定義

そこでUDF（ユーザー定義変数）を使って一時的な関数として定義します。

定義

CREATE TEMPORARY FUNCTION get_event_value(params ANY TYPE,name STRING) AS (
  ( 
    SELECT
      COALESCE( 
        value.string_value,
        cast(value.int_value AS string),
        cast(value.float_value AS string),
        cast(value.double_value AS string) 
      ) 
    FROM
      UNNEST(params) AS x 
    WHERE 
      x.key = name 
  )
);

このように事前に定義することで、同一SQL文の中で関数として利用することができます。

呼び出し

関数化することでUAのように簡潔になり可読性も高まりました。

SELECT
  get_event_value(event_params, 'page_location') AS page_location,
  get_event_value(event_params, 'page_title') AS page_title
FROM
  `projectId.analytics_propertyId.events_*`

ただこの方法の場合クエリを記述する度に、先頭で関数定義（CREATE TEMPORARY FUNCTION）する必要がありちょっと面倒です。どうせならグローバルに定義したいですよね。

Persistent UDFとして関数定義

BigQueryには永続的な関数定義としてPersistentUDFが用意されていますので、これを利用することにします。

定義

プロジェクトIDとデータセット名、関数名を明示的に指定します。
OPTIONSでは関数の説明を指定できます。

CREATE OR REPLACE FUNCTION
  `projectId.datasetName.get_event_value` (params ANY TYPE, name STRING) AS (
  (
    SELECT
      COALESCE(
		value.string_value,
    	CAST(value.int_value AS string),
		CAST(value.float_value AS string),
		CAST(value.double_value AS string)
	  )
    FROM
      UNNEST(params) AS x
    WHERE
      x.key = name
  )
)OPTIONS(description = "GA4のイベントパラメータの抽出")

上記のクエリを実行するとCloudConsole側でもデータセットの中のルーティン配下に関数が定義されていることが確認できます。

呼び出し

SELECT
  `projectId.datasetName.get_event_value`(event_params, "page_location") AS page_location,
  `projectId.datasetName.get_event_value`(event_params, "page_title") AS page_title
FROM
  `projectId.analytics_propertyId.events_*`

一時的なUDFよりもわずかに長いコードになってしまいましたが、関数自体は再利用可能な形となりシンプルになりました。

以下のコードをIMEか何かに登録しておけばイベントパラメータの取り出しも楽になりそうですね。

`projectId.datasetName.get_event_value`(event_params, "") AS

※備考

PersistentUDFは任意のデータセットの中で定義しているので、データセットのリージョンの影響を受けます。
つまり、定義された関数とクエリ実行がクロスリージョンの場合にはエラーとなります。

そのためBigQueryを複数リージョンで運用している場合には、 region_tokyo、region_usのような形でUDF用のデータセットを用意してもいいかもしれません。
※もし他に良い方法があれば教えてください。

まとめ

GA4のイベントパラメータにおける型の問題とコードの冗長性の問題は、COALESCEによる条件式と永続的UDFを組み合わせることで解決できました。

参考URL