2022年2月14日

GA4のBigQuery SQLを書くときに便利なライブラリ「BigQuery Utils」

Googleアナリティクス4では無償版であっても、BigQueryにエクスポートされたローデータを活用することができます。

このGA4 BigQueryからデータを取得するSQLクエリですが、覚えることが多かったり、煩雑な処理が増えてしまったりで初心者にとっては難しいものです。

本記事では、筆者が見つけた「BigQuery Utils」というライブラリを紹介するとともに、その中で特に「GA4 BigQueryを使うに当たって便利な関数」を紹介したいと思います。

BigQuery Utilsとは？

「BigQuery Utils」は、Google社がメインで管理しているオープンソース・プログラムの1つです。BigQueryをより便利に使うためのプログラムを、様々な開発者が開発しています。そして世界中の誰もが簡単に利用できるように公開しています。

BigQuery Utils – GitHub

この中では、BigQueryの「User Defined Function（ユーザー定義関数）」の開発も行われています。

BigQueryを使ったことのある人の中には、標準関数である「SUM」「COUNT」「EXTRACT」「DATE_ADD」のような関数を使ったことがある人も多いと思います。「User Defined Function」では、標準関数以外の独自の関数を自身で定義し、使うことができます。

BigQuey Utilsの便利な関数

以下ではBigQuey Utilsの中でも、「GA4 BigQueryを使う際に便利な関数」をご紹介します。

get_value関数
url_trim_query関数
ts_session_group関数
その他の統計関数

get_value関数

get_value関数は、BigQuery上にKey-Value形式で格納されたデータを取得するのに適した関数です。

具体的には、GA4で頻繁に発生する「テーブルからページURLやページタイトルなどのパラメーターを取得するケース」で利用できます。

このようなケースでは今までは、

SELECT
 (SELECT p.value.string_value FROM events.event_params as p WHERE p.key = "page_location") as page_location
FROM
 `プロジェクトID.データセットID.events_*` as events

や

SELECT
 (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "page_location") as page_location
FROM
 `プロジェクトID.データセットID.events_*` as events

のように実装していました。

これが、get_value関数を利用すると、

SELECT
 bqutil.fn.get_value("page_location", event_params).string_value as page_location
FROM
 `プロジェクトID.データセットID.events_*`

と書くことができます。

SELECT句を入れ子にする必要がなく、今までよりもスッキリしたクエリを書くことができるようになりました。

また、上の例ではイベントパラメーターから値を取得するサンプルとなっていますが、ユーザー・プロパティから値を取得するときも同様に記述することができます。

url_trim_query関数

GA4のBigQueryには、前処理がほとんど行われていない状態のデータが入ってきます。

特に「page_location」パラメーターのデータ（ページURL）はそのままでは分析できないほどに悲惨な状況になっているケースも多々あります。その原因は、ユーザーがページを閲覧するときにURL上に付与されていたクエリパラメーターの多くがそのまま残った状態でBigQueryに格納されていることです。

例えば、Google広告から遷移した時はURLに「gclid」というクエリパラメーターにランダムな文字列が付与されます。また、Web広告などの外部キャンペーンを行う際に、Googleアナリティクス用のキャンペーンパラメーターも付与されています。

これらのクエリパラメーターは分析を行うに当たって不要な情報なため、事前に前処理を行い不要なクエリパラメーターを削除する必要があります。

全てのクエリパラメーターを一括して削除してしまって問題ないサイトであれば「?」以降を削除するだけなので、標準関数でも実現できますが、「特定のクエリパラメーター」のみを削除したい、というケースには標準関数では実現できません。そのような時はこの「url_trim_query」関数の出番となります。

SELECT bqutil.fn.url_trim_query(
 "https://example.com/index.html?goods_id=123&gclid=xyz987",
 ["gclid"]
)

このようにすると、クエリパラメーター「gclid」を除外したURLである
https://example.com/index.html?goods_id=123
を取得することができます。

除外したいクエリパラメーターが他にもある場合は、[“gclid”]のところを[“gclid”, “fbclid”, “yclid”]のように列挙していくのみです。

自分が使うときは、get_value関数と合わせて

SELECT
 bqutil.fn.url_trim_query(
   bqutil.fn.get_value("page_location", event_params).string_value,
 ["fbclid", "utm_source", "utm_medium", "utm_campaign", "utm_content", "utm_term", "gclid", "yclid","wbraid"]
 ) as page_location
FROM
 `プロジェクトID.データセットID.events_*` as events

のような形で実装することで、既知のよくある不要なクエリパラメーターを削除することができます（実際には、サイト固有のその他のクエリパラメーターが存在しないか、別途チェックする必要があります）。

ts_session_group関数

GA4のBigQueryデータは、GA4の設定に従った条件でセッションが区切られた状態になっています。しかし、アドホック分析などでセッションを区切る条件を変えたい時が稀に出てきます。そのような時、セッションを区切るための処理を自身でゼロから実装するのは難しく、現実的ではありません。

このts_session_group関数とサンプルコードを使うと、自由な条件でセッションを区切り直して分析に利用できるようになります。

その他の統計関数

中央値を表す「median」関数やゼロノルムに正規化するための「zeronorm」関数、その他の検定関数（t検定やFisherの正確検定など）を行うための関数も実装されています。

これらの関数は自身でBigQueryで実装しようとすると、複雑になってしまうので、今まではSQLではなく、RやPythonなどその他のプログラムで実装するケースが多かったのではないでしょうか。BigQuery Utilsにより完全にRやPythonを代替できるわけありませんが、BigQuery単体でもできることが増え、より便利になっていくでしょう。