👩‍💻

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

2021/09/25に公開

Google Cloud

Terraform

BigQuery

tech

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

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

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

理由は以下です。

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

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

GA4のpage_view

この記事では 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句で絞り込むためにも、次のようなものを準備します。

JSON構造から特定のキーを探してバリューを取得するためのユーザー定義関数（UDF）
1をつかってpage_titleを抽出するユーザー定義関数
1をつかってpage_locationを抽出するユーザー定義関数
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関数が用意されています。

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

実現するためには 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とも組み合わせて再現可能な状態にもできる（実際そうしています）

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

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

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