📊

GA4 + BigQueryでユーザーの離脱ページを探ろう

に公開
Google Cloud
BigQuery
Google Sheets
GA4
tech

はじめに

今回はGoogle Analytics4で収集したデータをより使いこなしてみよう、ということで訪問ユーザーがどのページで離脱したのかを探ります！
日ごとに特定ページの離脱ユーザー数をダッシュボードで可視化してみようと思います。
日ごとに特定ページの離脱数から派生した全体ページから見た離脱率がわかれば改修含め、次の策を練る一助となりそうですよね！
なお、最近はGoogle Cloudばかり使用しているので、記事で使用する技術もGoogleサービスに寄っていますがご了承ください笑

※BigQueryは課金対象のため作業後はリソース削除等、適宜対応してください

使用するサービスは大きく分けて3種類になります。

  • Google Analytics4（以下、GA4）
  • BigQuery
  • Googleスプレッドシート（以下、スプシ）

前提

以下の作業が完了しているものとして話を進めていきます。

  • Googleアカウント作成
  • GA4のアカウント、プロパティ作成
  • Google Cloudのプロジェクト作成、IAMにロール付与
    • ロールは「BigQuery データ編集者」あれば問題ないはず

GA4 + BigQueryの連携

GA4側の設定のみになります。

GA4_1.png

GA4のコンソールにログインして管理ボタンを押します。

GA4_2.png

BigQueryのリンクボタンを押します。

GA4_3.png
上記のようになっているかと思いますので、リンクボタンを押します。

GA4_4.png

以下の内容で順次設定を進めていきます。

  1. BigQuery プロジェクトを選択する
    1. 作成したプロジェクトを選択してください
  2. 構成の設定
    1. イベントデータ
      1. エクスポートタイプ：毎日
  3. 確認して送信

ここまで正常に完了したらあとはBigQueryにデータ連携されるのを待ちましょう。
※自分は連携されるまでに1日くらいかかった記憶あり

GA4のデータセット・テーブル確認

bq1.jpg

以下のデータセット、テーブルがGA4で設定したプロジェクトに作成されていれば成功です！

  • データセット
    • analytics_xxxxxxxxx ※数字の部分は自動採番
  • テーブル
    • events_(テーブル数)
    • pseudonymous_users_(テーブル数)

後ほどでも説明しますがシャーディングテーブルと言って、日付区切りのテーブルがそれぞれ1日1回データ連携されていきます。
簡単にそれぞれのテーブルの役割は以下のようになっています。

  • events_*：各ユーザーの動作したイベントログ
  • pseudonymous_users_*：ユーザーログ、ユーザーIDは自動採番で誰かは分からない

ページ離脱数用クエリ作成

events_*からデータを成形したVIEWを作成します。
以下のクエリでいくつか{文字列}の部分を変更していただく箇所があります。
このクエリを実行するとGA4のデータセット内にuser_exit_pagesというVIEWが作成されます。

CREATE VIEW {GA4データセット}.user_exit_pages AS
WITH _list AS (
  SELECT
    user_pseudo_id
    , DATETIME(DATETIME_TRUNC(TIMESTAMP_MICROS(event_timestamp), second), 'Asia/Tokyo') AS date_time
    , event_name
    , REPLACE(event_params.value.string_value, 'https://{GA4設定しているドメイン}', '') AS path
    , event_params.value.int_value AS event_int
  FROM
    `{プロジェクトID}.{GA4データセット}.events_*`
    , UNNEST(event_params) as event_params
  WHERE
    event_params.key IN ('page_location', 'ga_session_number')
)
SELECT
  DATE(date_time) AS date
  , user_pseudo_id
  , FIRST_VALUE(MAX(path)) OVER(PARTITION BY user_pseudo_id, DATE(date_time) ORDER BY date_time) AS first_path
  , FIRST_VALUE(MAX(path)) OVER(PARTITION BY user_pseudo_id, DATE(date_time) ORDER BY date_time DESC) AS last_path
  , ROW_NUMBER() OVER(PARTITION BY user_pseudo_id, DATE(date_time) ORDER BY date_time) AS row_num
FROM
  _list
GROUP BY
  user_pseudo_id
  , date_time
QUALIFY
  row_num = 1
ORDER BY
  date_time;

せっかくなのでBigQueryで使う記法について解説しようと思います。
あとでゆっくり確認したい方は読み飛ばしても問題ありません。

解説①

DATETIME(DATETIME_TRUNC(TIMESTAMP_MICROS(event_timestamp), second), 'Asia/Tokyo') AS date_time

こちらはタイムスタンプをDATETIME型に変換しています。
event_timestampカラムがミリ秒まで保持していたので、DATETIME_TRUNCで秒までを切り出しています。

解説②

  FROM
    `{プロジェクトID}.{GA4データセット}.events_*`
    , UNNEST(event_params) as event_params

events_*の部分ですがGoogle Cloudにはシャーディングテーブルといって、日付で区切ってテーブルを管理することができます。
例）：events_20241221, events_20241222 …
GUIで見るとテーブル群といった形でまとめられています。

WHERE
	_TABLE_SUFFIX  BETWEEN '20241201' AND  '20241231'

ちなみにevents_*は全てのテーブル群を参照する状態になっているため、期間を指定したい場合はWHERE句で上記のように追記してください。

解説③

  FROM
    `{プロジェクトID}.{GA4データセット}.events_*`
    , UNNEST(event_params) as event_params

ネストされた繰り返し列 と言って階層を持つカラムがBigQueryにはあります。
GA4で得られるデータにはこのネストされた列が複数存在し、それらは単純にSELECT文で記載するだけでは取り出すことができません。
SELECTする前にUNNESTすることで、ようやくSELECT文で値を取り出すことができます。

解説④

QUALIFY
  row_num = 1

ウィンドウ関数を使用して日ごとの初めに見たページのパスと最後に見たパス、日別でユーザーごとにデータに行番号を取得しています。
WITH句をさらに書く必要があるのですが、今回はQUALIFYを使うことで短縮しています。

BigQueryとスプシの連携

ここではBigQueryで作成したVIEWとスプシの連携と独自に記述したクエリで取得したいデータを取得するまでの手順を記載します。

スプシ_データコネクタ.png

スプシを新規で開きます。
データ > データコネクタ > BigQueryに接続 で先に進みます。

スプシ_データコネクタ2.png

データ接続の追加でVIEWを作成したプロジェクトを選択してください。

スプシ_データコネクタ3.png

次に進むとデータセットを選択できる画面に代わります。
ただ、今回は下の方にある保存したクエリとクエリエディタを選択してください。

スプシ_データコネクタ4.png

するとクエリエディタが開きます。

SELECT
  date
  , COUNT(last_path) AS exit_cnt
FROM
  `{プロジェクトID}.{GA4データセット}.user_exit_pages`
WHERE
  last_path = "{見たいページのパス}"
GROUP BY
  date
ORDER BY
  date

上記クエリを適宜変更していただきクエリエディタに追加します。
画像では「❗️」が出ているところが「✅」になるかと思いますので、接続ボタンを押すとBigQueryとの連携が完了します。

スプシ_データコネクタ5.jpg

完成版のデータ接続したシートはこんな感じで表示されます。
ちなみにシート内の赤枠で囲った矢印を押すとデータ更新されるのでとても便利です！

スプシでグラフ作成

グラフ作成して日ごとの特定ページ離脱数を可視化してみます。

スプシ_データコネクタ6.jpg

グラフボタンを押して新規でグラフ用シートを作成します。

スプシ_データコネクタ7.jpg

以下の設定でグラフ設定することで、上記のように日ごとの特定ページ離脱数を可視化します。

  • グラフの種類：縦棒グラフ
  • X軸：date
  • 系列：exit_cnt
  • 並べ替え：date

作業としては以上になります、お疲れ様でした！
今回は特定ページに絞ったクエリにしていますが、全ページで積み上げの棒グラフを作成したら、より実務で使えるグラフになっていきそうですね！

おまけ

Google Cloudのサービスでダッシュボード作成するならLooker Studioをおすすめします。

設定もスプシのデータコネクタのように設定が簡単です。
※初期設定時はユーザーアカウントの権限でBigQueryと連携されます
※適宜サービスアカウントに切り替えを推奨します
※集計データがTiBまでいくとダッシュボードの動作が重くなります

おわりに

いかがでしたでしょうか！
GA4を導入したは良いがどうデータを見たら良いのかわからず、放置してしまっている方々は多いと思いこの記事を作成しました。
普段はWEBアプリ、データ基盤、ダッシュボード作成を一気通貫で構築しておりますので、今回の内容やそれに付随する内容も気軽にお問い合わせください！
※BigQueryは課金対象のため作業後はリソース削除等、適宜対応してください

Discussion