「日別のページごとの離脱数」を集計するSQLを書いてLooker Studioで可視化する──GA4×BigQuery

BigQuery連携したGA4の「日別のページごとの離脱数」を集計するSQLクエリを書いてLooker Studioででビジュアライズしてみましょう。

今回も「Googleが公開しているBigQuery連携したGA4デモデータに接続する｜GA4×BigQuery」で紹介したデモデータに接続します。

BigQueryのコンソールで新しいエディタを開いたら、まずは下のクエリをコピペして「実行」してみてください。

WITH
prep1 AS (
SELECT
event_date,
CONCAT(user_pseudo_id, (
SELECT
value.int_value
FROM
UNNEST(event_params)
WHERE
KEY = 'ga_session_id')) AS ga_session_id,
(
SELECT
value.string_value
FROM
UNNEST(event_params)
WHERE KEY = 'page_location') AS page_location,
event_timestamp,
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
event_name = 'page_view' ),
prep2 AS (
SELECT
event_date,
ga_session_id,
page_location,
event_timestamp,
FIRST_VALUE(CONCAT(page_location, event_timestamp)) OVER(PARTITION BY ga_session_id ORDER BY event_timestamp DESC) AS exit_page_location,
FROM
prep1 )
SELECT
event_date,
CASE
WHEN
CONCAT(page_location, event_timestamp) = exit_page_location THEN page_location
ELSE
NULL
END
AS exit_page_location,
COUNT(DISTINCT ga_session_id) AS exit_count
FROM
prep2
GROUP BY
event_date,
exit_page_location
HAVING
exit_page_location IS NOT NULL

こんな感じに「日別のページごとの離脱数」が集計できました。

※できるだけシンプルに解説するために「日付を日本時間に変更する」といったことは今回はやりません。

このデータをLooker Studioに接続してシンプルな「ページごとの離脱数の表」にするとこんな感じになります。

日付のデータもあるので、日ごとにページによって離脱数に変化があるのかみてみましょう。

ある日付で離脱数が通常の２〜３倍にスパイクしている箇所があります。この日はなにかあったのでしょうか？

ページごとの離脱数の推移を見てみましょう。ピボットテーブルを使います。

だいたい同じページが離脱数が多いことが読み取れます。

今回はGoogleのECサイトのでもデータを利用していますが、これをご自身のサイトで実施してみると、また違った傾向が読み取れると思います。

以上、日別のページごとの離脱数を集計するSQLクエリを書いてLooker Studioで可視化する方法の解説でした。

GA4やBigQuery連携したGA4のレポーティングなどご相談がありましたら、お気軽にお問い合わせください。