TOP会社情報お知らせブログお問い合わせ
TOP会社情報お知らせブログお問い合わせ

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

icon
カテゴリ
Google Cloud
icon
作成日
2024/12/21
著者
中田健太

アドベントカレンダー2024！

 
この記事は ミライトデザイン Advent Calendar 2024 の21日目の記事です。
 
前回は ブッチ さんの記事「データ活用から始めるエンジニアの視野の広げ方」でした。
 

はじめに

 
今回はGoogle Analytics4で収集したデータをより使いこなしてみよう、ということで訪問ユーザーがどのページで離脱したのかを探ります！
 
日ごとに特定ページの離脱ユーザー数をダッシュボードで可視化してみようと思います。
日ごとに特定ページの離脱数から派生した全体ページから見た離脱率がわかれば改修含め、次の策を練る一助となりそうですよね！
なお、最近はGoogle Cloudばかり使用しているので、記事で使用する技術もGoogleサービスに寄っていますがご了承ください笑
※BigQueryは課金対象のため作業後はリソース削除等、適宜対応してください
 
使用するサービスは大きく分けて3種類になります。
  • Google Analytics4（以下、GA4）
  • BigQuery
  • Googleスプレッドシート（以下、スプシ）
 

前提

 
以下の作業が完了しているものとして話を進めていきます。
  • Googleアカウント作成
  • GA4のアカウント、プロパティ作成
  • Google Cloudのプロジェクト作成、IAMにロール付与
    • ロールは「BigQuery データ編集者」あれば問題ないはず
 

GA4 + BigQueryの連携

 
GA4側の設定のみになります。
GA4のコンソールにログインして管理ボタンを押します。
 
BigQueryのリンクボタンを押します。
 
上記のようになっているかと思いますので、リンクボタンを押します。
 
以下の内容で順次設定を進めていきます。
  1. BigQuery プロジェクトを選択する
    1. 作成したプロジェクトを選択してください
  1. 構成の設定
    1. イベントデータ
      1. エクスポートタイプ：毎日
  1. 確認して送信
ここまで正常に完了したらあとはBigQueryにデータ連携されるのを待ちましょう。
※自分は連携されるまでに1日くらいかかった記憶あり
 

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

 
以下のデータセット、テーブルが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;
SQL
せっかくなのでBigQueryで使う記法について解説しようと思います。
あとでゆっくり確認したい方は読み飛ばしても問題ありません。
 
解説①
DATETIME(DATETIME_TRUNC(TIMESTAMP_MICROS(event_timestamp), second), 'Asia/Tokyo') AS date_time
SQL
こちらはタイムスタンプをDATETIME型に変換しています。
event_timestampカラムがミリ秒まで保持していたので、DATETIME_TRUNCで秒までを切り出しています。
 
解説②
  FROM
    `{プロジェクトID}.{GA4データセット}.events_*`
    , UNNEST(event_params) as event_params
SQL
events_*の部分ですがGoogle Cloudにはシャーディングテーブルといって、日付で区切ってテーブルを管理することができます。
例）：events_20241221, events_20241222 …
GUIで見るとテーブル群といった形でまとめられています。
 
WHERE
	_TABLE_SUFFIX  BETWEEN '20241201' AND  '20241231'
SQL
ちなみにevents_*は全てのテーブル群を参照する状態になっているため、期間を指定したい場合はWHERE句で上記のように追記してください。
 
解説③
  FROM
    `{プロジェクトID}.{GA4データセット}.events_*`
    , UNNEST(event_params) as event_params
SQL
ネストされた繰り返し列 と言って階層を持つカラムがBigQueryにはあります。
GA4で得られるデータにはこのネストされた列が複数存在し、それらは単純にSELECT文で記載するだけでは取り出すことができません。
SELECTする前にUNNESTすることで、ようやくSELECT文で値を取り出すことができます。
 
解説④
QUALIFY
  row_num = 1
SQL
ウィンドウ関数を使用して日ごとの初めに見たページのパスと最後に見たパス、日別でユーザーごとにデータに行番号を取得しています。
WITH句をさらに書く必要があるのですが、今回はQUALIFYを使うことで短縮しています。
 

BigQueryとスプシの連携

 
ここではBigQueryで作成したVIEWとスプシの連携と独自に記述したクエリで取得したいデータを取得するまでの手順を記載します。
スプシを新規で開きます。
データ > データコネクタ > BigQueryに接続 で先に進みます。
 
データ接続の追加でVIEWを作成したプロジェクトを選択してください。
 
次に進むとデータセットを選択できる画面に代わります。
ただ、今回は下の方にある保存したクエリとクエリエディタを選択してください。
 
するとクエリエディタが開きます。
 
SELECT
  date
  , COUNT(last_path) AS exit_cnt
FROM
  `{プロジェクトID}.{GA4データセット}.user_exit_pages`
WHERE
  last_path = "{見たいページのパス}"
GROUP BY
  date
ORDER BY
  date
SQL
上記クエリを適宜変更していただきクエリエディタに追加します。
画像では「❗️」が出ているところが「✅」になるかと思いますので、接続ボタンを押すとBigQueryとの連携が完了します。
 
完成版のデータ接続したシートはこんな感じで表示されます。
ちなみにシート内の赤枠で囲った矢印を押すとデータ更新されるのでとても便利です！
 

スプシでグラフ作成

 
グラフ作成して日ごとの特定ページ離脱数を可視化してみます。
グラフボタンを押して新規でグラフ用シートを作成します。
 
以下の設定でグラフ設定することで、上記のように日ごとの特定ページ離脱数を可視化します。
  • グラフの種類：縦棒グラフ
  • X軸：date
  • 系列：exit_cnt
  • 並べ替え：date
 
作業としては以上になります、お疲れ様でした！
今回は特定ページに絞ったクエリにしていますが、全ページで積み上げの棒グラフを作成したら、より実務で使えるグラフになっていきそうですね！
 

おまけ

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

おわりに

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

明日のアドベントカレンダー

 
明日はNyokkiさんの記事になります！お楽しみに！
もう少しでクリスマスですねぇ。
 

関連記事