Looker Studioに離脱数が出ない！代わりにBigQueryを使って表現する方法

Looker StudioをGA4と連携すると、GA4でもおなじみのディメンションと指標、そしてLooker Studio固有のディメンションが多く利用できるようになるが、「離脱数」に関してはいつまで経っても使えるようにならない。

離脱数は離脱率を算出するためにも必要で、サイト分析でも欠かせない指標の1つであるが、このおかしな状況が今後変わる気配もなさそうだ。

そこで本記事では、GA4ではなくBigQueryとの連携から、Looker Studioで離脱数および離脱率を可視化する方法について解説する。

本記事を読む前に…

前提として、BigQueryを導入していて、GA4のデータがBigQueryへエクスポートされている必要があるので、それらの設定がまだの方はまずそちらからご対応お願いします。

BigQueryで離脱数・離脱率を抽出する
1. ページごとの離脱数・離脱率を抽出するクエリ
2. 月次で離脱数・離脱率を抽出するクエリ
Looker Studioと連携して可視化する
おわりに

BigQueryで離脱数・離脱率を抽出する

以下の2パターンでサンプルクエリを提示する。

ページごとの離脱数・離脱率を抽出するクエリ
月次で離脱数・離脱率を抽出するクエリ

クエリに関しての注意事項

コメントアウトしているところに要注意
サブクエリは思考回路がバグりやすいので、あえてWITH句を用いて、上から順番にテーブルの形成が理解できるようなクエリにしている
SELECT句やWHERE句のように、SQLの節は大文字で表されることが多いが、僕の好み上、すべて小文字で記述している。結果自体に問題はない。

ページごとの離脱数・離脱率を抽出するクエリ

with predata1 as (
  select
    event_timestamp,
    (select value.string_value from unnest(event_params) where key = "page_location") as page_url,
    concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = "ga_session_id")) as session_id
  from `analytics_280494569.events_*` -- 各人固有の文字列になっているはずなので、ご自身のを確認し、変更いただく必要あり!
  where event_name = "page_view" and _table_suffix between '20241101' and '20241107' -- ご自身で抽出したい期間があれば、それにあわせて柔軟に変更いただいて良い
), predata2 as (
  select
    page_url,
    session_id,
    case
      when row_number() over (partition by session_id order by event_timestamp desc) = 1 then 1
      else 0
    end as exit
  from predata1
)

select
  page_url,
  sum(exit) as exit,
  count(session_id) as sessions,
  round(safe_divide(sum(exit), count(session_id)), 3) as exit_rate
from predata2
group by page_url
order by sessions desc;

集計結果は以下のようになる。

月次で離脱数・離脱率を抽出するクエリ

with predata1 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_*` -- 各人固有の文字列になっているはずなので、ご自身のを確認し、変更いただく必要あり!
  where event_name = "page_view" and _table_suffix between '20240901' and '20241130' -- ご自身で抽出したい期間があれば、それにあわせて柔軟に変更いただいて良い
), predata2 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
  from predata1
)

select
  year_month,
  sum(exit) as exit,
  count(session_id) as sessions,
  round(safe_divide(sum(exit), count(session_id)), 3) as exit_rate
from predata2
group by year_month
order by year_month;

集計結果は以下のようになる。