GA4 + BigQueryでユーザーの離脱ページを探ろう
アドベントカレンダー2024！
はじめに
今回はGoogle Analytics4で収集したデータをより使いこなしてみよう、ということで訪問ユーザーがどのページで離脱したのかを探ります！
日ごとに特定ページの離脱ユーザー数をダッシュボードで可視化してみようと思います。
日ごとに特定ページの離脱数から派生した全体ページから見た離脱率がわかれば改修含め、次の策を練る一助となりそうですよね！
なお、最近はGoogle Cloudばかり使用しているので、記事で使用する技術もGoogleサービスに寄っていますがご了承ください笑
※BigQueryは課金対象のため作業後はリソース削除等、適宜対応してください
使用するサービスは大きく分けて3種類になります。
- Google Analytics4（以下、GA4）
- BigQuery
- Googleスプレッドシート（以下、スプシ）
前提
以下の作業が完了しているものとして話を進めていきます。
- Googleアカウント作成
- GA4のアカウント、プロパティ作成
- Google Cloudのプロジェクト作成、IAMにロール付与
- ロールは「BigQuery データ編集者」あれば問題ないはず
GA4 + BigQueryの連携
GA4側の設定のみになります。
GA4のコンソールにログインして管理ボタンを押します。
BigQueryのリンクボタンを押します。
上記のようになっているかと思いますので、リンクボタンを押します。
以下の内容で順次設定を進めていきます。
- BigQuery プロジェクトを選択する
- 作成したプロジェクトを選択してください
- 構成の設定
- イベントデータ
- エクスポートタイプ：毎日
- 確認して送信
ここまで正常に完了したらあとはBigQueryにデータ連携されるのを待ちましょう。
※自分は連携されるまでに1日くらいかかった記憶あり
GA4のデータセット・テーブル確認
以下のデータセット、テーブルがGA4で設定したプロジェクトに作成されていれば成功です！
- データセット
- analytics_xxxxxxxxx ※数字の部分は自動採番
- テーブル
- events_(テーブル数)
- pseudonymous_users_(テーブル数)
後ほどでも説明しますがシャーディングテーブルと言って、日付区切りのテーブルがそれぞれ1日1回データ連携されていきます。
簡単にそれぞれのテーブルの役割は以下のようになっています。
- events_*：各ユーザーの動作したイベントログ
- pseudonymous_users_*：ユーザーログ、ユーザーIDは自動採番で誰かは分からない
ページ離脱数用クエリ作成
events_*からデータを成形したVIEWを作成します。
以下のクエリでいくつか{文字列}の部分を変更していただく箇所があります。
このクエリを実行するとGA4のデータセット内にuser_exit_pagesというVIEWが作成されます。
せっかくなのでBigQueryで使う記法について解説しようと思います。
あとでゆっくり確認したい方は読み飛ばしても問題ありません。
解説①
こちらはタイムスタンプをDATETIME型に変換しています。
event_timestampカラムがミリ秒まで保持していたので、DATETIME_TRUNCで秒までを切り出しています。
解説②
events_*の部分ですがGoogle Cloudにはシャーディングテーブルといって、日付で区切ってテーブルを管理することができます。
例）：events_20241221, events_20241222 …
GUIで見るとテーブル群といった形でまとめられています。
ちなみにevents_*は全てのテーブル群を参照する状態になっているため、期間を指定したい場合はWHERE句で上記のように追記してください。
解説③
ネストされた繰り返し列 と言って階層を持つカラムがBigQueryにはあります。
GA4で得られるデータにはこのネストされた列が複数存在し、それらは単純にSELECT文で記載するだけでは取り出すことができません。
SELECTする前にUNNESTすることで、ようやくSELECT文で値を取り出すことができます。
解説④
ウィンドウ関数を使用して日ごとの初めに見たページのパスと最後に見たパス、日別でユーザーごとにデータに行番号を取得しています。
WITH句をさらに書く必要があるのですが、今回はQUALIFYを使うことで短縮しています。
BigQueryとスプシの連携
ここではBigQueryで作成したVIEWとスプシの連携と独自に記述したクエリで取得したいデータを取得するまでの手順を記載します。
スプシを新規で開きます。
データ > データコネクタ > BigQueryに接続 で先に進みます。
データ接続の追加でVIEWを作成したプロジェクトを選択してください。
次に進むとデータセットを選択できる画面に代わります。
ただ、今回は下の方にある保存したクエリとクエリエディタを選択してください。
するとクエリエディタが開きます。
上記クエリを適宜変更していただきクエリエディタに追加します。
画像では「❗️」が出ているところが「✅」になるかと思いますので、接続ボタンを押すとBigQueryとの連携が完了します。
完成版のデータ接続したシートはこんな感じで表示されます。
ちなみにシート内の赤枠で囲った矢印を押すとデータ更新されるのでとても便利です！
スプシでグラフ作成
グラフ作成して日ごとの特定ページ離脱数を可視化してみます。
グラフボタンを押して新規でグラフ用シートを作成します。
以下の設定でグラフ設定することで、上記のように日ごとの特定ページ離脱数を可視化します。
- グラフの種類：縦棒グラフ
- X軸：date
- 系列：exit_cnt
- 並べ替え：date
作業としては以上になります、お疲れ様でした！
今回は特定ページに絞ったクエリにしていますが、全ページで積み上げの棒グラフを作成したら、より実務で使えるグラフになっていきそうですね！
おまけ
設定もスプシのデータコネクタのように設定が簡単です。
※初期設定時はユーザーアカウントの権限でBigQueryと連携されます
※適宜サービスアカウントに切り替えを推奨します
※集計データがTiBまでいくとダッシュボードの動作が重くなります
おわりに
いかがでしたでしょうか！
GA4を導入したは良いがどうデータを見たら良いのかわからず、放置してしまっている方々は多いと思いこの記事を作成しました。
普段はWEBアプリ、データ基盤、ダッシュボード作成を一気通貫で構築しておりますので、今回の内容やそれに付随する内容も気軽にお問い合わせください！
※BigQueryは課金対象のため作業後はリソース削除等、適宜対応してください
明日のアドベントカレンダー
もう少しでクリスマスですねぇ。