GA4

【GA4SQL】日次のセッション数をBigQueryから取得するクエリ

2024.04.30 2024.06.29

GA4のセッションの集計では、トラフィックソースがセッション中に変わったとしても新しいセッションとしてカウントされない。これが旧UAと大きく集計方法が異なる点であり、考慮すべき点は単純にデフォルトの30分のセッションタイムが切れたときにセッションが切れるという仕様になった。セッションに関する詳細事項は以下、公式ヘルプを参照したい。

参考：[GA4] アナリティクスのディメンションと指標 – アナリティクスヘルプ
 参考：[GA4] アナリティクスのセッションについて – アナリティクスヘルプ

セッション数の日次カウント
- クエリ解説
探索で日次のセッション数をカウント
ページごとのセッション数カウント
- 日次のページごとのセッション数カウント
ランディングページごとのセッション数をカウント
- 日次のランディングページのセッション数をカウント

セッション数の日次カウント

WITH
  DailySession AS (
    SELECT
      ymd,
      COUNT(DISTINCT ssid) AS _ssuu
    FROM (
      SELECT
        ymd,
        CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
      FROM (
        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
        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
  ORDER BY
    ymd
)

SELECT
  *
FROM
  DailySession

もっと簡略して書くのであれば

WITH
  DailySession AS (
    SELECT
      PARSE_DATE("%Y%m%d", event_date) AS ymd,
      COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS _ssuu
    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
    ORDER BY
      ymd
)

SELECT
  *
FROM
  DailySession

クエリ解説

セッションは”user_pseudo_id”と”session_id”を結合することでユニークなセッションをカウントすることが可能になる。”session_id”のみでは重複することがあるので結合する必要があり、CONCAT関数で両者を結合する。その際に、間に可読性を上げるために’-‘を挟んでいるが、これはあってもなくてもいい。また、”user_pseudo_id”はSTRING型で”session_id”はINTEGER型であるためCASTをしているが、なくとも暗黙的な型変換が行われるため記載しなくても問題ないが、最終的にどちらの型を使用しているのかの可読性を上げるために記載している。

user_id または user_pseudo_id を session_id と結合して、各セッションの一意の識別子を取得することをおすすめします。
https://support.google.com/analytics/answer/9191807?hl=ja

BigQueryでセッション数をカウントした値とGA4の探索におけるセッション数の数値が微妙にズレることがあるが、GA4におけるセッション数のカウントではHyperLogLog++ アルゴリズムが適応されるために完全にBigQueryと一致することはない。検証では上記のクエリで約99.7%の一致率となった。そのため、BigQueryで得られた値を信用しても問題ない。

探索で日次のセッション数をカウント

探索で確認するには、ディメンションに「日付」、指標に「セッション」を指定する。

ページごとのセッション数カウント

‘page_location’パラメータは’page_view’イベント以外でも取得できるため省略してもロジックとしては問題ないが、ページ訪問を元にした集計をする際は基本的には’page_view’イベントで条件を付けた方が、たとえ’page_view’イベントが発生していなかったとしても、整合性を取るという意味でもよいかもしれない。ではあるが、標準レポートでディメンションにページと指標にセッションを置いた場合、event_name = ‘page_view’を省略した数値と一致する。

WITH
  PageSession AS (
    SELECT
      page_location,
      COUNT(DISTINCT ssid) AS _ssuu
    FROM (
      SELECT
        page_location,
        CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
      FROM (
        SELECT
          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
      page_location
    ORDER BY
      _ssuu DESC
)

SELECT
  *
FROM
  PageSession

日次のページごとのセッション数カウント

UAでいう「ページ別訪問数」のことである。ページ別訪問数とは、セッションの中で同じページを何回閲覧しても「1」としてカウントする集計方法で、重複カウントを避けたい場合などによく使われる。

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
    ORDER BY
      ymd ASC,
      _ssuu DESC
)

SELECT
  *
FROM
  DailyPageSession

ランディングページごとのセッション数をカウント

WITH
  LP_Session AS (
    SELECT
      page_location AS landing_page,
      SUM(entrances) AS _ssuu
    FROM (
      SELECT
        REGEXP_REPLACE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'), r"\?.*", "") AS page_location,
        (SELECT value.int_value FROM UNNEST(event_params) p WHERE p.key = 'entrances') AS entrances
      FROM
        `<project>.<dataset>.events_*`
      WHERE
        _TABLE_SUFFIX BETWEEN '20240401' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND
        event_name = 'page_view'
    )
    GROUP BY
      page_location
    ORDER BY
      _ssuu DESC
)

SELECT
  *
FROM
  LP_Session

日次のランディングページのセッション数をカウント

WITH
  LP_Session_Daily AS (
    SELECT
      ymd,
      page_location AS landing_page,
      SUM(entrances) AS _ssuu
    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,
        (SELECT value.int_value FROM UNNEST(event_params) p WHERE p.key = 'entrances') AS entrances
      FROM
        `<project>.<dataset>.events_*`
      WHERE
        _TABLE_SUFFIX BETWEEN '20240315' AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND
        event_name = 'page_view'
    )
    GROUP BY
      ymd,
      landing_page
    ORDER BY
      ymd ASC,
      _ssuu DESC
)

SELECT
  *
FROM
  LP_Session_Daily