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単体でもできることが増え、より便利になっていくでしょう。

まとめ

  • BigQuery Utilsは、Google社がメインで管理しているオープンソース・プログラムの1つ。
  • BigQuey Utilsの中のGA4 BigQueryを使う際に便利な関数には、「get_value関数」「url_trim_query関数」「ts_session_group関数」などがある。

実は今回紹介した関数のうち「url_trim_query」関数は、BigQuery Utilsのプロジェクトを知った筆者が実装を行い、このプロジェクトに取り込まれたものとなります。このように、一般の開発者であっても、開発に貢献することができるのもオープンソースの良い点といえます。

また、自身で開発できない場合であっても、BigQuery Utilsのプロジェクトに対し、「どういった機能が欲しいか」「その機能が欲しい理由」「同様に多くのユーザーがその機能を望んでいるか」を伝える、というのもオープンソースの1つの貢献の方法です。

オープンソースなど過去の資産をうまく活用して、複雑な実装にかける時間を減らし、より問題の本質に時間を費やしていきましょう。

もしお困りのことがございましたら、お気軽にお問い合わせ下さい。

お気軽にご質問、ご相談ください

関連タグ

山田良太

テクノロジー開発室長。チーフテクノロジーマネージャー。10年以上のプログラミング経験を活かして、Webマーケティングのテクノロジー領域(APIを使ったシステム開発や、タグ実装など)を中心に取り組む。

関連資料

GA4【GA4最前線 2022年版】GA4移行において押さえるべきポイント! 〜データ収集後の可視化方法も解説〜

公開日:2022年06月

【GA4最前線 2022年版】GA4移行において押さえるべきポイント! 〜データ収集後の可視化方法も解説〜

GA4×CRMGA4を活用したECサイトの優良顧客獲得手法とは?GA4 × CRM 統合分析によるペルソナ点検のすすめ

公開日:2021年02月

GA4を活用したECサイトの優良顧客獲得手法とは?GA4 × CRM 統合分析によるペルソナ点検のすすめ

Web解析定量調査と定性調査で コンバージョンを最大化させる施策を探る! ハイブリッド診断とは?

公開日:2021年02月

定量調査と定性調査で コンバージョンを最大化させる施策を探る! ハイブリッド診断とは?

関連ブログ