GA4のデータをBigQueryへエクスポートしLooker Studioで可視化する

こんにちは、香田です。

今回はGA4のデータをBigQueryへエクスポートし、Looker Studioで可視化する方法について紹介していきます。

GA4(Google アナリティクス 4)、Looker Studioは基本的に無料で利用可能ですが、BigQueryもBigQuery サンドボックスを利用することで無料で利用可能です。

BigQueryを利用したことがない場合は、下記を参考に有効化してみてください。

BigQuery サンドボックスを有効にする

はじめに

今回紹介する構成の全体像は下記になります。

GA4(Google アナリティクス 4)のデータをBigQueryへ定期的に自動エクスポートします。

エクスポートしたデータをLooker StudioよりBigQueryへ接続し、データを可視化する流れになります。

本記事では最終的にLooker Studioで下記のようなレポートを作成していきます。

GA4 BigQuery Export 設定

はじめにGA4のデータをBigQueryへエクスポートしてきます。

GA4のデータをBigQueryへエクスポートする手順は、下記公式ドキュメントのセットアップ手順を参考にしてみてください。

[GA4] BigQuery Export のセットアップ

BigQueryでのデータ管理

GA4のデータがBigQueryへ正常にエクスポートされると、analytics_<GA4プロパティID>.events_<YYYYMMDD>という名前のテーブルが作成されます。

GA4よりエクスポートされたデータに対して、クエリを実行してみます。

※下記SQLのFROMで指定するプロジェクト名、データセット名や_TABLE_SUFFIX BETWEENで指定する日付範囲は適宜修正してください。

SELECT
  -- イベントのタイムスタンプを日本時間（JST）に変換し、年月日と時分秒の形式で表示
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), SECOND), "Asia/Tokyo") AS event_time,
  -- ページのタイトルを取得
  page_title.value.string_value AS page_title,
  -- ページのURLを取得
  page_location.value.string_value AS page_location,
  -- 訪問者の国を取得
  geo.country AS country,
  -- 訪問者の地域を取得
  geo.region AS region,
  -- 訪問者の都市を取得
  geo.city AS city,
  -- 訪問者のデバイスカテゴリ（モバイル、デスクトップなど）を取得
  device.category AS device_category,
  -- 訪問者のブラウザ情報を取得
  device.web_info.browser AS device_browser,
  -- 訪問者のオペレーティングシステムを取得
  device.operating_system AS device_os,
  -- ユーザーの疑似IDを取得
  user_pseudo_id,
  -- トラフィックソース（例：Google、Facebookなど）を取得
  traffic_source.source AS traffic_source,
  -- トラフィック媒体（例：organic、referralなど）を取得
  traffic_source.medium AS medium,
  -- GA4のデータセットとテーブルを指定
FROM
  `<project_id>.<analytics_property_id>.events_*`
  -- イベントパラメータからページタイトルを取り出す
  UNNEST(event_params) AS page_title,
  -- イベントパラメータからページのURLを取り出す
  UNNEST(event_params) AS page_location
  -- クエリの条件を指定
WHERE
  -- 指定された日付範囲内のデータを選択
  _TABLE_SUFFIX BETWEEN '20231220'
  AND '20240117'
  -- イベントタイプがページビューのものに限定
  AND event_name = 'page_view'
  -- ページタイトルとページURLのキーを指定
  AND page_title.key = "page_title"
  AND page_location.key = "page_location"
  -- 結果を日本時間で降順に並べ替え
ORDER BY
  event_time DESC

クエリ実行すると、下記のように指定した期間のデータが表示されるはずです。

Looker StudioからBigQueryへの接続設定

BigQueryでクエリ実行できることが確認できたら、次にLooker StudioよりBigQueryへ接続設定をしていきます。

Looker Studioの[作成]より[レポート]を選択し、[データのレポートへの追加]にて[BigQuery]を選択します。

[カスタムクエリ]を選択し下記のSQLを入力していきます。

※下記SQLのFROMで指定するプロジェクト名、データセット名は適宜修正してください。

SELECT
  -- イベントのタイムスタンプを日本時間（JST）に変換し、年月日と時分秒の形式で表示
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), SECOND), "Asia/Tokyo") AS event_time,
  -- ページのタイトルを取得
  page_title.value.string_value AS page_title,
  -- ページのURLを取得
  page_location.value.string_value AS page_location,
  -- 訪問者の国を取得
  geo.country AS country,
  -- 訪問者の地域を取得
  geo.region AS region,
  -- 訪問者の都市を取得
  geo.city AS city,
  -- 訪問者のデバイスカテゴリ（モバイル、デスクトップなど）を取得
  device.category AS device_category,
  -- 訪問者のブラウザ情報を取得
  device.web_info.browser AS device_browser,
  -- 訪問者のオペレーティングシステムを取得
  device.operating_system AS device_os,
  -- ユーザーの疑似IDを取得
  user_pseudo_id,
  -- トラフィックソース（例：Google、Facebookなど）を取得
  traffic_source.source AS traffic_source,
  -- トラフィック媒体（例：organic、referralなど）を取得
  traffic_source.medium AS medium,
  -- GA4のデータセットとテーブルを指定
FROM
 `<project_id>.<analytics_property_id>.events_*`
  -- イベントパラメータからページタイトルを取り出す
  UNNEST(event_params) AS page_title,
  -- イベントパラメータからページのURLを取り出す
  UNNEST(event_params) AS page_location
  -- クエリの条件を指定
WHERE
  -- 指定された日付範囲内のデータを選択
  _TABLE_SUFFIX BETWEEN @DS_START_DATE AND @DS_END_DATE
  -- イベントタイプがページビューのものに限定
  AND event_name = 'page_view'
  -- ページタイトルとページURLのキーを指定
  AND page_title.key = "page_title"
  AND page_location.key = "page_location"
  -- 結果を日本時間で降順に並べ替え
ORDER BY
  event_time DESC

パラメータの[期間パラメータを有効にする]にチェックを入れ、[追加]をクリックします。

下記のように無題のレポートが作成されるはずです。

デフォルトで作成された表は不要なので削除しておきます。

Looker Studio レポートタイトルの作成

ページ上部にレポートのタイトルを作成していきます。

[図形]より[長方形]を選択し、右側の[長方形のプロパティ]パネルで、背景色を選択します。

追加した長方形内にテキストボックスを追加します。

[テキスト]を選択し、テキストボックスに[Looker Studio サンプルレポート]と入力し、右側の[テキストのプロパティ]パネルで、フォントを調整します。

Looker Studio 期間設定の作成

指定した期間でデータをフィルタリングできるように期間設定を作成していきます。

[コントロールを追加]より[期間設定]を選択します。

Looker Studio ドーナツグラフの作成

ブラウザ、参照元、地域、デバイス情報の割合を参照できるようにドーナツグラフを作成していきます。

[グラフを追加]より円の[ドーナツグラフ]を選択します。

右側のプロパティパネルの[設定]より[指標]に[Record Count]を指定します。

下記情報を元に[ディメンション]を指定し、ドーナツグラフをそれぞれ追加していきます。

ブラウザ: device_browser
参照元: traffic_source
地域: region
デバイス情報: device_category

[テキスト]を選択し、テキストボックスにブラウザ、参照元、地域、デバイス情報とそれぞれドーナツグラフへ名前を入力していきます。

右側のプロパティパネルの[スタイル]より[枠線に影を付ける]をチェックすることで、下記のようにグラフを立体的に見せることが可能です。

Looker Studio スコアカードの作成

ページビュー数、ユーザー数を参照できるようにスコアカードを作成していきます。

[グラフを追加]より[スコアカード]を選択します。

右側のプロパティパネルの[設定]より[指標]に[page_location]を指定します。

下記のように名前に[ページビュー数]と入力し、集計方法は[件数]を選択します。

同様にスコアカードを追加し、右側のプロパティパネルの[設定]より[指標]に[user_pseudo_id]を指定します。

下記のように名前に[ユーザー数]と入力し、集計方法は[個別件数]を選択します。

ドーナツグラフと同様に[スタイル]の[枠線に影を付ける]をチェックし、グラフのスタイルを調整します。

Looker Studio 表の作成

ページタイトル毎の件数と時系列にデータ参照できるように表を作成していきます。

ページタイトル毎の件数として[グラフを追加]より[表]を選択します。

右側のプロパティパネルの[設定]より[ディメンション]に[page_title]を指定し、[指標]に[Record Count]を指定します。

時系列にデータ参照できるように同様に表を作成します。

右側のプロパティパネルの[設定]より[ディメンション]に下記を設定し[指標]を削除します。

event_time
device_category
traffic_source
medium
country
region
city
page_title
page_location

下記のような表が追加されればレポート作成は完了です。

さいごに

GA4のデータをBigQueryへエクスポートし、Looker Studioで可視化する方法いかがでしょうか？

GA4のデータをBigQueryへエクスポートすることで、BigQuery上に保管している他のデータソース（例えばCRMデータ、販売データなど）と結合し、Looker Studioで包括的なレポート作成も可能になるのではないでしょうか。

最後までご覧いただきありがとうございます。

GA4のデータをBigQueryへエクスポートしLooker Studioで可視化する