はじめに

今回はGoogleアナリティクス4(GA4)データのBigQueryエクスポートデータの利用について書いていきます。 BigQueryでGA4のデータにはじめて触れるという方はお先にこちらの記事をご覧ください。 tech.nri-net.com

BigQuery にエクスポートされるデータの構造

データの構造について

BigQueryにエクスポートされたGA4のデータはイベント毎にレコードが作成されます。

上記の記事でも触れられている通り、BigQueryにエクスポートされたデータはRECORD型という入れ子構造になっています。 RECORD型をフラットにするためにはUNNEST演算子を利用します。

たとえば、閲覧されたページURL（page_location）の一覧を取得したいとき、SQLは下記のようになります。

SELECT
  event_date,
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `project_id.dataset_id.events_yyyymmdd`
WHERE event_name = 'page_view'

クエリ結果

このようにイベントパラメータ(event_params)はRECORD型のため、特定の項目を取得するときはkeyとvalueを指定する必要があります。

公式ヘルプクエリ構文 | BigQuery | Google Cloud UNNEST演算子
UNNESTについてさらに詳しく知りたい方はこちら Google Developers Japan: BigQuery 活用術: UNNEST 関数

イベントパラメータのデータ型について

イベントパラメータを取得するときに気を付けなければならないのはvalueのデータ型です。 valueはデータ型ごとにフィールドが分かれており、取得したいデータ型のフィールドを指定する必要があります。「page_location」は文字列型のため、先ほどのSQLでは「value.string_value」を取得しました。 GA4のBigQueryエクスポートのスキーマでは4つのデータ型のフィールドが用意されています。

※執筆時点では、「value.float_value」は利用されていません。

公式ヘルプ [GA4] BigQuery Export スキーマ - アナリティクスヘルプ

同一keyのイベントパラメータに複数のデータ型がある場合について

ここまではBigQueryのデータ構造についてお話してきました。ここからはデータを取得するときに気を付けたい点についてお話していきたいと思います。カスタムディメンションなどサイト側で計測値を設定している場合、データの型はGA4側にて自動で検出され決定されます。データの計測値によっては、同じカスタムディメンションであってもレコードごとに異なるデータ型として計測値が格納される可能性があります。

ここでは整数と文字列が計測されているカスタムディメンション「sample_dimension」の取得方法を考えていきたいと思います。 1レコード目は「value.int_value」、2レコード目「value.string_value」に値が格納されます。

GA4では計測するときに文字列型でデータを送信しても、データが数値のみの場合は整数型として計測される場合があるためBigQueryデータ抽出時には注意が必要です。

計測値	計測されるフィールド
12345	event_params.value.int_value
abcde	event_params.value.string_value

複数のフィールドで値が計測されるときのデータ取得方法

フィールドが一意に定まらない値の取得について解決方法を2つ提示します。

解決方法1 クエリ結果のカラムを分けて取得する

データの型ごとにカラムを分けて取得する方法です。この方法はデータを利用するシステムや分析を行う側でカラムを1つにまとめるなどデータ加工を行う必要があります。

SELECT
  event_date ,
  event_name ,
  event_timestamp ,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'sample_dimension') AS sample_dimension_int , -- 整数型
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'sample_dimension') AS sample_dimension_str --文字列型
FROM `project_id.dataset_id.events_yyyymmdd`

クエリ結果

解決方法2 UDFを利用する

クエリ結果を1つのカラムで取得したい場合はユーザ定義関数（UDF）を利用します。

UDFとは

言語が標準で用意している関数とは異なり、ユーザが定義する関数です。 JavaScriptやSQLを使用して関数を作成可能です。今回はJavaSctiptを利用しています。

公式ヘルプユーザー定義の関数

--- 引数で渡された値のうち、NULLではない値を返却します。
CREATE TEMPORARY FUNCTION  select_not_null_value(str_value STRING, int_value INT64)
RETURNS STRING LANGUAGE js AS """
    if(str_value !== null){
       return str_value;
    }
  return int_value;
""";

SELECT
  event_date ,
  event_name ,
  event_timestamp ,
  (SELECT select_not_null_value(value.string_value,value.int_value) FROM UNNEST(event_params) WHERE key = 'sample_dimension') AS sample_dimension
FROM
    `project_id.dataset_id.events_yyyymmdd`