GA4

【GA4 SQL】ページ離脱数・離脱率をBigQueryから取得

2024.06.16

GA4では現状、離脱数を探索で並べることはできるものの、離脱率の指標はまだ存在しないため、離脱率を探索・標準レポートで可視化することはできない。カスタム定義においても離脱数の指標が選択できないためカスタマイズして離脱率を作成することも現時点は不可となっている。

このようにGA4の管理画面だけで分析を終始一貫させることは無理なわけで、探索に離脱数とセッションを並べてデータをエクスポートしてExcelでデータを加工してなど手間がかかる手順を踏むよりも、BigQueryからデータを抽出して離脱率を求めるレポートを作成することを推奨する。

BigQueryとLooker Studioを連携させると、このようにページごとの離脱率をデータビジュアライズすることが可能になる。

GA4における離脱率の計算式
離脱数・離脱率を算出するクエリ
探索で離脱数を確認する方法
BigQuery × Looker Studioでデータビジュアライズ

GA4における離脱率の計算式

離脱率 = 離脱数 ÷ セッション

ページ単位の離脱率は、離脱率を算出したいページを元に、そのページ数で離脱した数に対して、そのページを含むセッション数を割ることで算出する。

参考：[GA4] 閲覧開始数と離脱数 -アナリティクスヘルプ

離脱数・離脱率を算出するクエリ

ページごとの離脱数を取得するクエリ

SELECT
  page_location,  
  COUNT(*) AS _exit
FROM (
  SELECT
    user_pseudo_id AS l_user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS l_ga_session_id,
    MAX(event_timestamp) AS last_event_timestamp
  FROM
    `<project>.<dataset>.events_*`
  WHERE
      event_name = 'page_view'
    AND
      _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  GROUP BY
    l_user_pseudo_id,
    l_ga_session_id
)
INNER JOIN (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
    event_timestamp
  FROM
    `<project>.<dataset>.events_*`
  WHERE
    event_name = 'page_view'
) AS e
ON
    l_user_pseudo_id = e.user_pseudo_id
  AND
    l_ga_session_id = e.ga_session_id
  AND
    last_event_timestamp = e.event_timestamp
GROUP BY
  page_location
ORDER BY
  _exit DESC

日次のページごとの離脱数を取得するクエリ

SELECT
  ymd,
  page_location,  
  COUNT(*) AS _exit
FROM (
  SELECT
    PARSE_DATE("%Y%m%d", event_date) AS l_ymd,
    user_pseudo_id AS l_user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS l_ga_session_id,
    MAX(event_timestamp) AS last_event_timestamp
  FROM
    `<project>.<dataset>.events_*`
  WHERE
      event_name = 'page_view'
    AND
      _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  GROUP BY
    l_ymd,
    l_user_pseudo_id,
    l_ga_session_id
)
INNER JOIN (
  SELECT
    PARSE_DATE("%Y%m%d", event_date) AS ymd,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
    event_timestamp
  FROM
    `<project>.<dataset>.events_*`
  WHERE
    event_name = 'page_view'
) AS e
ON
    l_ymd = e.ymd
  AND
    l_user_pseudo_id = e.user_pseudo_id
  AND
    l_ga_session_id = e.ga_session_id
  AND
    last_event_timestamp = e.event_timestamp
GROUP BY
  ymd,
  page_location
ORDER BY
  ymd ASC,
  _exit DESC

ページごとの「離脱率」を取得するクエリ

WITH
  DailyPageSession AS (
    SELECT
      ymd,
      page_location,
      COUNT(DISTINCT ssid) AS _ssuu
    FROM (
      SELECT
        ymd,
        page_location,
        CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
      FROM (
        SELECT
          PARSE_DATE("%Y%m%d", event_date) AS ymd,
          REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
          user_pseudo_id,
          (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
        FROM
          `<project>.<dataset>.events_*`
        WHERE
            _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY))
      )
    )
    GROUP BY
      ymd,
      page_location
  ),
  DailyExit AS (
    SELECT
      ymd,
      page_location,  
      COUNT(*) AS _exit
    FROM (
      SELECT
        PARSE_DATE("%Y%m%d", event_date) AS l_ymd,
        user_pseudo_id AS l_user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS l_ga_session_id,
        MAX(event_timestamp) AS last_event_timestamp
      FROM
        `<project>.<dataset>.events_*`
      WHERE
          event_name = 'page_view'
        AND
          _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      GROUP BY
        l_ymd,
        l_user_pseudo_id,
        l_ga_session_id
    )
    INNER JOIN (
      SELECT
        PARSE_DATE("%Y%m%d", event_date) AS ymd,
        user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
        REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
        event_timestamp
      FROM
        `<project>.<dataset>.events_*`
      WHERE
        event_name = 'page_view'
    ) AS e
    ON
        l_ymd = e.ymd
      AND
        l_user_pseudo_id = e.user_pseudo_id
      AND
        l_ga_session_id = e.ga_session_id
      AND
        last_event_timestamp = e.event_timestamp
    GROUP BY
      ymd,
      page_location
  ),
  JoinSesionExit AS (
    SELECT
      s.ymd,
      s.page_location,
      COALESCE(_ssuu, 0) AS _ssuu,
      COALESCE(_exit, 0) AS _exit,
      CASE
        WHEN COALESCE(_ssuu, 0) > 0 THEN ROUND(COALESCE(_exit, 0) / COALESCE(_ssuu, 0), 2)
      ELSE NULL
  END AS exit_per_ssuu
    FROM
      DailyPageSession AS s
    LEFT JOIN
      DailyExit AS e
    ON 
        s.ymd = e.ymd
      AND
        s.page_location = e.page_location
  )

SELECT
  *
FROM
  JoinSesionExit
ORDER BY
  page_location ASC,
  ymd ASC