【BigQuery】BigQuery UtilsでGA4のデータ抽出

みなさんこんにちは
今回は、Googleアナリティクス4（GA4）のSQLが長ったらしくてイヤ複雑で困っているという人向けに便利なライブラリ「BigQuery Utils」を紹介します。

BigQuery Utils

bigquery-utils

README（一部抜粋）

This repository provides useful utilities to assist you in migration and usage of BigQuery.

（BQの複雑なデータ構造に悩む全人類の救世主！個人的にはusefulの前にveryを10個くらい追加したい！）

この中のbigquery-utils/udfs/community/に便利なUDFがたくさん置かれています。

今回は、特に使用頻度が高い関数get_value()とurl_parse()を見ていきます。

get_value()

構造体の配列を扱うときに便利な関数

閲覧されたページのurlを知りたい場合、GA4のevent_paramsフィールドからkey=page_locationのvalueのstring_valueをみるために、こんなSQLを書いていると思います

SELECT 
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") AS page_location
FROM 
    `project-***.analytics_*****.events_YYYYMMDD`

これが、BigQuery Utilsを使うと次のように書けます。

SELECT
    bqutil.fn.get_value("page_location", event_params).string_value AS page_location
FROM 
    `project-***.analytics_*****.events_YYYYMMDD`

url_parse()

URLから指定した部分を返す関数

例えば取得したpage_location

https://qiita.com/hirakuma/items/06aa9784073609d425de?key=value#anchor

このままでは使いにくいので、HOSTやPATHを取得したいときは

bqutil.fn.url_parse(url, 'HOST'), -- => qiita.com
bqutil.fn.url_parse(url, 'PATH'), -- => hirakuma/items/06aa9784073609d425de
bqutil.fn.url_parse(url, 'QUERY'), -- => key=value#anchor
bqutil.fn.url_parse(url, 'REF'), -- => anchor
bqutil.fn.url_parse(url, 'PROTOCOL') -- => https

こうします。QUERY部分をもっと詳しく見たいときはurl_keys()やurl_parse()を使うと良いです
（正規表現などでごにょごにょ書く必要が無く、とても重宝しています！）

まとめ

・BigQuery Utilsはとても良い
・BigQuery Utilsはとても良い（大事なことなので２回言いました）

bqutil.fnには他にも便利な関数がたくさんあるので、暇なときに覗いてみるといいかもしれません

次回予告

データポータル連携について書く、、、かも（UAからGA4への移行で困っている人たくさんいますよね。きっと）