GA4

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

2024.04.30 2024.05.15

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で得られた値を信用しても問題ない。

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

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

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

ディメンションにURL（ページ）を置く場合は以下の通り。’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

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

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