Google Analytics4 から連携されたイベントデータを BigQuery のユーザー定義関数やビューで扱いやすくする

Google Analytics4 と BigQuery の連携について調べる機会がありました。

https://zenn.dev/waddy/scraps/940ac10e7c3f94

構成としては Google Tag Manager を経由する以下の形を検討中です。

Google Analytics4 と BigQuery を連携してどうするの？という部分なのですが、Webアプリの商品（コンテンツ）の"質"を計測する実験を行っています。せっかくBigQueryと連携できるし、GA4で取得できるデータを使おうかなとやや手段先行な部分はありつつもGA4+BigQueryを使うところまでは決定。で、最後まで悩んだのが、「Google Tag Manager/Google Analytics4 でどこまで設定して、どこからBigQueryのクエリに任せるか」といったことです。正直なところまだ結論は出ていませんが、いまのところ、GTM/GA4の設定は最小限にして、取得できたイベントから計測に都合の良いデータをクエリするとしています。

理由は以下です。

1. GTM/GA4の設定画面で複雑な設定を行うと、メンテナンスが大変
2. GTMで設定を再現するための手順書の作成が難しい（画面や仕様変更への追従）
3. BigQuery のクエリであれば、クエリを保存して再現することが可能

特に3番目のメリットを重視しました。

GA4のpage_view

特に断りがない限り、この記事のBigQueryはスタンダードSQLを利用しています。

この記事では Google Analytics4 から連携される基本イベントのひとつ、page_viewをBigQueryで変形する方法を例にとります。ん？わざわざVIEWを作る必要があるの？という話ですが、Google Analytics4 のpage_viewイベントは以下のようなデータになっています。

JSONだと：

[
  {
    "event_date": "20210924",
    "event_timestamp": "1632477063761534",
    "event_name": "page_view",
    "event_params": [
      {
        "key": "page_title",
        "value": {
          "string_value": "iPad mini6 アクセサリ",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "page_location",
        "value": {
          "string_value": "http://localhost/waddy/products/nwerwewewfadfa",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      },
      {
        "key": "page_referrer",
        "value": {
          "string_value": "http://localhost:3000/products/nwerwewewfadfa/edit",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }
      // ... 省略
    ],
    "event_previous_timestamp": null,
  }
]

各パラメータが event_params という配列に、しかも KeyValue 形式で入っていますね。これはひとつのPVイベントデータです。このイベントを複数SELECTして、アプリケーション側でパースしてフィルタするのは…大変ですし、わざわざBigQueryに連携するメリットが薄いですね。ほぼGA4の生データを使っていることになります。WHERE句で絞り込むためにも、次のようなものを準備します。

1. JSON構造から特定のキーを探してバリューを取得するためのユーザー定義関数（UDF）
2. 1をつかってpage_titleを抽出する ユーザー定義関数
3. 1をつかってpage_locationを抽出する ユーザー定義関数
4. 2,3を使って「あるページに対するPVイベントを集めたテーブル」を作るためのVIEW

最終的に下図のようなテーブルにしたいです。

生データに比べてずいぶんSQLで扱いやすい形になりました。このVIEWを作りましょう。順番に見ていきます。

1. JSON配列構造からキーを探すユーザー定義関数

このdataですね。ここから page_title の string_value を抽出したい。

    "event_params": [
      {
        "key": "page_title",
        "value": {
          "string_value": "iPad mini6 アクセサリ",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }
    ]

アプリでパースしても少し面倒そうなのに、SQLでできるのでしょうか。調べてみると、BigQueryではJSONデータが扱いやすいよう、JSON関数が用意されています。

https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions

JSON_EXTRACTはjson_pathを引数として受け取るようなので、まさにやりたいことができそうです。いやぁありがたいですね…といいたいところなのですが、このプリセット関数では完結できません。なぜなら今回は配列JSONから特定のキーを探すという行為をやらねばならず、この操作はJSON_EXTRACTではサポートされていません。

https://stackoverflow.com/questions/51673083/jsonpath-in-bigquery-doesnt-support-for-filter-suggestions-for-alternatives

実現するためには JavaScript の ユーザー定義関数を使います。もはやなんでもアリですね。

CREATE FUNCTION `project-name.dataset_name.CUSTOM_JSON_EXTRACT`(json STRING, json_path STRING)
RETURNS STRING
LANGUAGE js AS """
try {
      var parsed = JSON.parse(json);
      var res =  jsonpath.query(parsed, json_path);
      return res.length ? res[0] : null
    } catch (e) { return null }
"""
OPTIONS (
    library="https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/jsonpath/jsonpath-1.1.js"
);

JavaScript 本体の記述に加え、OPTIONS で外部ライブラリを指定しています。この関数を作る時点で、Google Cloud Stroage（この場合）へアップロードしておく必要があります。JSを挟めるなら、いろいろなことができそうですね。この関数は以下のように利用します。

SELECT `CUSTOM_JSON_EXTRACT_SCALAR`("""
    [
      {
        "key": "page_title",
        "value": {
          "string_value": "iPad mini6 アクセサリ",
          "int_value": null,
          "float_value": null,
          "double_value": null
        }
      }
    ]
""", '$[?(@.key=="page_title")].value.string_value');

json_path（2番目の引数）に注目してください。keyがpage_titleのものを配列から探して、そのvalue.string_valueを取得しています。page_titleがstringであることを我々は知っているので、string_value決め打ちで取得していることは問題ありません。少し大変でしたが、これでGA4のイベントパラメータを好きなように抽出できるようになりました。

2. page_title を抽出するユーザー定義関数

CUSTOM_JSON_EXTRACT_SCALAR が使えればそれほど難しくありません。

CREATE FUNCTION `project-name.dataset_name.EXTRACT_PAGE_TITLE`(event_params_json STRING)
RETURNS STRING
AS (
`project-name.dataset_name.CUSTOM_JSON_EXTRACT_SCALAR`(event_params_json,'$[?(@.key=="page_title")].value.string_value')
)

これは VIEW で使います。

3. page_location を抽出するユーザー定義関数

page_titleと同じです。

CREATE FUNCTION `project-name.dataset_name.EXTRACT_PAGE_LOCATION`(event_params_json STRING)
RETURNS STRING
AS (
`project-name.dataset_name.CUSTOM_JSON_EXTRACT_SCALAR`(event_params_json,'$[?(@.key=="page_location")].value.string_value')
)

これも VIEW で使います。

4. PVイベントを集めたテーブルをVIEWで

まずは普通に目的のテーブル表示になるよう、クエリとして打ちましょう。2と3で作ったユーザー定義関数を使います。

SELECT
  `project-name.dataset-name.EXTRACT_PAGE_TITLE`(TO_JSON_STRING(event_params)) AS page_title,
  `project-name.dataset-name.EXTRACT_PAGE_LOCATION`(TO_JSON_STRING(event_params)) AS page_location,
  event_name
FROM
  `project-name.dataset-name.events_intraday*`
WHERE
  event_name='page_view'
  AND REGEXP_CONTAINS(`project-name.dataset-name.EXTRACT_PAGE_LOCATION`(TO_JSON_STRING(event_params)), r'https?://.+?/(.+?)/products/[\w-]+.*$')

このような結果が得られるはずです。

目指す形になったら、保存＞ビューの保存 とします。

わかりやすいテーブル名をつけて完了です。

これで、作ったVIEWをテーブルとして、絞り込んだり、集計できるようになりました。JSONの配列もなくなり、カラムに展開されているので WHERE句なども使いやすいですね。

VIEWを使ったクエリ例

SELECT * FROM `my-project.analytics_999999.pv_intraday_view` LIMIT 1000

追加でデータが必要になったら、event_parameterから抽出してビューに追加することで対応できます。

まとめ

Google Analytics4 から連携されるデータを BigQuery のUDFを用い、分析しやすいテーブルになるようVIEWを作成しました。このやり方には次のようなメリットがあります：

• 集計アプリケーションは作成したVIEWさえ見ればよく、JSONをパースしたりを考える必要がない
• ユーザー定義関数とVIEWの実態はFUNCTIONまたはクエリ定義であり、GitHub などのエコシステムに載せてバージョン管理することができる
• さらに、Terraform などのIaCとも組み合わせて再現可能な状態にもできる（実際そうしています）

現時点では正解かどうかわかりませんが、商品の質を計測するために以下の方針にしました：

1. GA4で採取できる基本データのみを応用する
2. 計測用データへの変換ははBigQueryのユーザー定義関数およびVIEWで行う（本記事の内容）
3. それでも足りないデータが出てきた場合、GTM/GA4でカスタムイベント・カスタムパラメータの追加を検討する

TerrafromとBigQueryとの連携については、別の記事で紹介します。