本記事で紹介している内容は、前提としてGA4のデータがBigQueryへエクスポートされている必要がある。設定がまだの方は、そちらを先に済ませるようにしよう。
Looker StudioをGA4のデータソースに連携すると、GA4でもおなじみのディメンションと指標が利用できるようになるが、「離脱数」をはじめとしたいくつかの指標はなぜか使えないようになっている。
離脱数や離脱率は、サイトの分析において重要な指標の1つであるが、一向に現れる気配がなさそうなので、本記事では、BigQueryと連携してLooker Studio上で離脱数・離脱率を可視化する方法について解説する。
完成イメージは以下だ。
なお、「閲覧開始数」をBigQuery連携で可視化する方法については以下の記事で紹介している。
BigQueryでページごとの離脱数・離脱率を抽出するSQLクエリ
ページごとの離脱数・離脱率を抽出するサンプルクエリは以下である。
WITH table1 AS (
SELECT
event_timestamp,
(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location") AS page,
CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "ga_session_id")) AS session_id
FROM
`analytics_280494569.events_*` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<datASet>.events_*`）
WHERE
event_name = "page_view" AND _table_suffix BETWEEN '20241101' AND '20241107' -- ご自身で抽出したい期間に変更する
),
table2 AS (
SELECT
page,
session_id,
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_timestamp DESC) = 1 THEN 1
ELSE 0
END AS exit_flag
FROM
table1
)
SELECT
page,
SUM(exit_flag) AS exits,
COUNT(session_id) AS sessions,
ROUND(SAFE_DIVIDE(SUM(exit_flag), COUNT(session_id)), 3) AS exit_rate
FROM
table2
GROUP BY
page
ORDER BY
sessions DESC;集計結果は以下のようになる。
クエリ解説
全体の流れとしては、table1とtable2でpage_viewイベントが発生したページとセッションID、離脱があったかどうかのフラグを立てた仮想テーブルを作成し、最終的に、離脱フラグを合計した「離脱数」をセッションで割ってページごとの離脱率を出している。
(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location") AS page上は、ページURLが格納されているイベントパラメータ「page_location」を抽出するクエリ。
CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = "ga_session_id")) AS session_id上は、「user_pseudo_id」と「ga_session_id」をつなげてユーザーごとの固有のセッションIDを抽出している。
WHERE句では「event_name = “page_view”」とすることで、page_viewイベントのみに紐づいたデータに限定することができる。
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_timestamp DESC) = 1 THEN 1
ELSE 0
END AS exit_flag離脱数の定義として、1回の訪問のなかで最後のイベントが発生した回数をカウントしている。
離脱数は、あるページやスクリーンで 1 セッションの最後のイベントが発生した回数を示します。
[GA4] 閲覧開始数と離脱数
そのため上のコードでは、セッションIDをイベント発生時間の遅い順に上から並べ、最初の1行目（つまり最後のイベント発生）であれば「1」、それ以外を「0」としてフラグを立てている。
ちなみに、ROW_NUMBERは行番号を取得するウィンドウ関数である。
SUM(exit_flag) AS exits,
COUNT(session_id) AS sessions,
ROUND(SAFE_DIVIDE(SUM(exit_flag), COUNT(session_id)), 3) AS exit_rate仮想テーブルで作成した離脱フラグの「1」を合計し（離脱数）、session_idの数を数え（セッション数）、それらを割り算することで離脱率を出している。
SAFE_DIVIDEは、割り算の際、0で割ったときにエラーにならないよう「NULL」を返す関数である。
BigQueryで月ごとの離脱数・離脱率を抽出するSQLクエリ
月ごとの離脱数・離脱率を抽出するサンプルクエリは以下である。
WITH table1 AS (
SELECT
FORMAT_DATE("%Y-%m", DATE_TRUNC(PARSE_DATE("%Y%m%d", event_date), MONTH)) AS year_month,
event_timestamp,
CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id")) AS session_id
FROM
`analytics_280494569.events_*` -- ご自身のプロジェクト名・データセット名に応じて変更する（`<project>.<datASet>.events_*`）
WHERE
event_name = "page_view" AND _table_suffix BETWEEN '20240901' AND '20241130' -- ご自身で抽出したい期間に変更する
),
table2 AS (
SELECT
year_month,
session_id,
CASE
WHEN ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_timestamp DESC) = 1 THEN 1
ELSE 0
END AS exit_flag
FROM
table1
)
SELECT
year_month,
SUM(exit_flag) AS exits,
COUNT(session_id) AS sessions,
ROUND(SAFE_DIVIDE(SUM(exit_flag), COUNT(session_id)), 3) AS exit_rate
FROM
table2
GROUP BY
year_month
ORDER BY
year_month;集計結果は以下のようになる。
クエリ解説
基本的には先ほど「BigQueryでページごとの離脱数・離脱率を抽出するSQLクエリ」で紹介したページを抽出するクエリの部分を、年月を抽出するクエリに変更するだけである。
以下がそれにあたる。
FORMAT_DATE("%Y-%m", DATE_TRUNC(PARSE_DATE("%Y%m%d", event_date), MONTH)) AS year_month,PARSE_DATE関数はSTRING型の文字をDATE型に変換する関数で、今回の場合、“YYYYmmdd”のかたちで記録される「event_date」（STRING型）をDATE型に変換している。
DATE_TRUNC関数は日付を丸める関数で、先ほどの「event_date」を月単位に丸めている。
ただしこの時点では、文字列の見た目が“YYYYmm01”のようになり、誤解を生みやすいため、FORMAT_DATE関数を使って“YYYYmm”の表記に変換している。
FORMAT_DATE関数は、日付の表示形式を変更する関数である。
Looker Studioと連携して可視化する
ここからは、前の章で書いたクエリを利用してLooker Studioで離脱数と離脱率を可視化する方法について解説する。
手順は以下の3ステップである。
- BigQueryと接続する
- 可視化させたいグラフ形式を選ぶ
- グラフを整える
1.BigQueryと接続する
まずLooker Studioを開き、編集モードにする。
次に、画面上部のツールバーにある「データを追加」をクリックし、「BigQuery」を選択する。
紐づけ方法だが、今回はBigQuery側での操作が必要ない「カスタムクエリ」を用いた方法で行う。
一番左にある「カスタムクエリ」から該当するプロジェクトを選択すると、右側にカスタムクエリを入力するエリアが表示されるため、枠内に前章で作成したクエリのいずれかをコピペする。
最後に、画面右下の「追加」ボタンをクリックする。
連携がうまくできていれば、画面右側のデータタブにBigQueryのデータソースが出現しているはずだ。
2.可視化させたいグラフ形式を選ぶ
今回は最も基本的な表形式かつ視覚的にわかりやすいヒートマップ付きのものを選択する。
ステップ1で、月ごとの離脱数・離脱率のクエリを採用した場合は、時系列に沿った推移が把握しやすい折れ線グラフを選んでもよいだろう。
3.グラフを整える
まず、データソースが先ほど連携したBigQueryになっているかを確認し、GA4やサーチコンソールなど他のツールになっていれば、「データソース」をクリックしてBigQueryに変更する。
ディメンションに「page」を、指標に「exits」「sessions」「exit_rate」をセットする。
このとき、対策が必要なページの優先順位がわかりやすいよう、並べ替えを「sessions」の降順にし、サブの並び替えを「exit_rate」の降順にするといい。こうすることで、アクセスボリュームも考慮に入れつつ離脱率の高いページを上から並べることができる。
なお、列のタイトルが英語でわかりづらい場合は、ディメンションと指標の左側（マウスオーバーすると鉛筆マークになる）をクリックし、表示名を変更すればよい。
また、離脱率をパーセント表示にしたい場合は、同じように指標の左側をクリックし、「データ型」＞「須知」＞「%」の順でクリックする。
おわりに
本記事では、離脱数の指標が使えないLooker Studioにおいて、BigQuery連携で可視化できるようにする方法について解説した。
BigQueryが絡むと一気にハードルは高くなるが、その分できることが増えて分析の幅が広がるので、興味があればぜひチャレンジしてみよう。
なお、「閲覧開始数」をBigQuery連携で可視化する方法については以下の記事で紹介している。
