本記事は
GA4 Week
3日目の記事です。
📈
2日目
▶▶ 本記事 ▶▶
4日目
🔍
はじめまして、GA4 Week 3日目を担当します荒川と申します。 普段はGoogle アナリティクス(GA)の導入やGAデータ活用支援などを行っております。
はじめに
今回は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`
「sample_dimension」に値が入っている項目を1つのカラムで取得できるようになりました。
クエリ結果
おわりに
今回はGA4のBigQueryエクスポートにて複数のデータの型が存在するときのデータ取得方法についてお話してきました。 GA4は日々情報がアップデートされておりますので、最新情報は公式ヘルプをご確認ください。
Google アナリティクス(GA)の導入やGAデータ活用支援などを行っております。