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