NRIネットコム Blog

NRIネットコム社員が様々な視点で、日々の気づきやナレッジを発信するメディアです

BigQueryにエクスポートしたGoogle アナリティクス 4 (GA4) のデータを取り出そう!

f:id:ankjm0:20211215170020j:plain

本記事はNRIネットコム Advent Calendar 2021 16日目の記事です。
🎅 15日目 ▶▶ 本記事 ▶▶ 17日目 🐶

はじめまして、中島と申します。
普段はGoogle アナリティクスの導入支援やコンサルティング、データ活用に向けたデータ分析業務などを行っております。

はじめに

本記事では、「次世代のGoogle アナリティクス『Google アナリティクス 4』(GA4)を活用するにあたって、BigQueryを活用していきたい!」という思いを抱いている方々にとっての第一歩を踏み出すことを目的としております。

GA4のデータをBigQueryにエクスポートすることにより、以下のようなメリットがあります。
* GA4の「データ探索」レポートでは確認できない、より詳細なデータ分析が可能。
* GA4ではない他のデータ(例えば、CRMデータなど)との結合ができ、幅広い視野でデータを活用可能。
* 機械学習やAI技術を活用したデータ分析が可能。

「そもそも、GA4のデータはBigQueryにどのように格納されているのか?」
「BigQueryでGA4のデータを扱ったことがない」
「BigQueryでデータを扱うにあたってまず気を付けるべきことは何なのか?」
という疑問や不安にスポットを当てて、本記事では『実際にページビューを取得する』までを目標とします。

BigQuery のデータ構造

BigQuery とは、Googleが提供するクラウド上のデータウェアハウスです。
BigQuery: Cloud Data Warehouse  |  Google Cloud

BigQueryは「プロジェクト」「データセット」「テーブル、ビュー」の3階層で成り立っています。
f:id:ankjm0:20211215144310p:plain

GA4プロパティとBigQueryをリンクしてエクスポートする方法については、公式ヘルプにまとまっております。
公式ヘルプ:[GA4] Set up BigQuery Export - Analytics Help

BigQuery にエクスポートされるテーブルについて

GA4のデータをエクスポートした際、BigQueryに作成されるテーブルの内容は、頻度が「毎日」か「ストリーミング」によって異なります。
f:id:ankjm0:20211215144934p:plain
「ストリーミング」を選択した場合は毎日新しいテーブル(events_intraday_YYYYMMDD)が作成されます。
「ストリーミング」と「毎日」を両方選択した場合には、日次テーブル(events_YYYYMMDD)も作成され、下記のような内容のテーブルが作成されます。
f:id:ankjm0:20211215145035p:plain
① プロジェクト ID
② データセット名:エクスポート元として選択したプロパティの「analytics_{プロパティID}」が表示されます。
③ テーブル名:events_{日付(YYYYMMDD)}
  このテーブルには、日次データが1日1回エクスポートされます。
④ テーブル名:event_intraday_{日付(YYYYMMDD)}
  このテーブルには、その日に発生したデータが継続的にエクスポートされます。

GA4のテーブルは日単位で分かれていますので、年月日またぎのデータを取得したい場合は、FROM句とWHERE句を下記のように指定する必要があります。
f:id:ankjm0:20211215175113p:plain

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

GA4プロパティとBigQueryをリンクしてエクスポートして生成されたテーブルには、アップロードされたイベント毎にレコードが作成されます。
f:id:ankjm0:20211215145719p:plain
上表のような入れ子構造に対応するために、Google アナリティクスのテーブルは、RECORD 型というデータ型を含んでいます。
公式ヘルプ:[GA4] BigQuery Export スキーマ - アナリティクス ヘルプ
f:id:ankjm0:20211215145847p:plain
注意点として、入れ子構造になっているフィールドに対して通常どおりクエリを実行しようとすると、下図のようなエラーが発生します。
f:id:ankjm0:20211215175517p:plain
上記のようなエラー回避するためには、UNNEST 演算子を用いて RECORD 型をフラットにすることが必要です。
f:id:ankjm0:20211215175409p:plain UNNEST 演算子について詳しい説明:Query syntax  |  BigQuery  |  Google Cloud

page_viewイベント一覧を取得してみよう!

それでは実際にpage_viewイベントを取得してみたいと思います。
今回はデータセットとして、BigQueryの一般公開データセット「ga4_obfuscated_sample_ecommerce」を活用します。
Google アナリティクス 4 e コマースウェブ実装向けの BigQuery サンプル データセット  |  Google アナリティクスの BigQuery Export  |  Google Developers

page_viewイベント一覧を取得するクエリは以下になります。

SELECT
  DATE(timestamp_micros(event_timestamp), 'Asia/Tokyo') AS eventstartdate,
  event_name,
  user_pseudo_id,
  device.mobile_model_name, 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location , 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE 
  event_name = 'page_view'

クエリの構造としては以下のようになります。
f:id:ankjm0:20211215151141p:plain
更に詳しくクエリの内容を見ていくと、以下のようになります。
f:id:ankjm0:20211215151451p:plain
①:イベント発生日時では任意のタイムゾーンを指定できます。今回は「Asia/Tokyo」を指定しています。
②:「event_params」は先述した入れ子構造からなるRECORD型なので、UNNEST演算子でフラットにします。
③:「page_location」 は「event_params.key」が「page_location」となっている行の値「event_params.value.string_value」になります。
④:「page_title」は「event_params.key」が「page_title」となっている行の値「event_params.value.string_value」になります。
⑤:WHERE句によりイベント「page_view」のみを指定します。

クエリの実行結果は以下のようになります。結果から、「page_view」イベントのみを抽出できていることが分かります。
f:id:ankjm0:20211215151830p:plain
今回はBigQueryの一般公開データセット「ga4_obfuscated_sample_ecommerce」を使用しましたが、上記クエリのデータセット名をご自身でお持ちのデータセット名に変更することで、簡単に「page_view」イベントが取得できるのでお試しください。

執筆者:中島彩花

Google アナリティクスの導入支援やコンサルティング、データ活用に向けたデータ分析業務などを行っております。

NRIネットコム Advent Calendar 2021
🎅 15日目 ▶▶ 本記事 ▶▶ 17日目 🐶