ブログ

GA4

【GA4 SQL】ランディングページのセッション数を集計

GA4におけるランディングページのセッション数とはセッションの開始となった最初の訪問ページのカウント数となる。つまり探索で選択可能な「閲覧開始数」と理論上は同値になるが、ランディングページの「閲覧開始数」と「セッション数」は同値とはならない。この理由の1つとしてはユーザーの閲覧画面が非アクティブ状態になり、再度アクティブ状態になった際に、セッションが変わり「閲覧開始数」と「セッション数」ではセッション数の方が多く集計されることになるため。

ランディングページのセッション数をどのように集計するのか？

ランディングページのセッション数を集計するには、ランディングページのセッション数を単純に集計すればよいだけなのであるが、今のところランディングページの「セッション数」を集計するクエリの作成に私が成功していない。そのため、別の方法として「閲覧開始数」を集計することで、ランディングページのセッション数と同じ意味になるため、この方法を現時点では採用している。

閲覧開始数とは何か？

閲覧開始数とはセッションの最初に閲覧したページのpage_viewイベントのentrancesパラメータであるINT型の1を集計したものであり、要はランディングページを「指標」化したものが閲覧開始数といえる。そのため、閲覧開始数を集計することで、ランディングページのセッション数と同じ意味になる。ただ、上述した通りセッションIDが同一ページでも変わることがあり、これが主な原因でランディングページの閲覧開始数とセッション数で乖離が生まれる。

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

2024.08.09

【検証】GA4 entrancesパラメータ

BigQueryへエクスポートされるGA4のentrancesパラメータについて検証したことをメモ entrancesパラメータの概要 entrancesパラメータは入口ページであることのフラグであり、セッションの最初のpage_viewイベントのentrancesパラメータに数値の1が付...

閲覧開始数とセッション数ではどれくらい乖離するのか？

探索で以下のディメンションと指標を並べて検証したところ、ランディングページの(not set)を除けば、一致率は99.7％であった。つまり、(not set)はpage_viewが付いていないセッションであり、それは同一ページであるため集計上でも(not set)は除外しても問題ないといえる。したがって、閲覧開始数をランディングページのセッション数として集計することが可能になる。というか、「セッション」それ自体の定義が最初のページに限定するものではなく「ランディングページのセッション」とすることで集計範囲を限定している。であれば「セッションあたりの平均エンゲージメント時間」などの母数はセッションではなく閲覧開始数にしてほしいとも思ったりする。

ディメンション
　・ランディングページ
指標
　・閲覧開始数

　・セッション

ランディングページのセッション数を集計するクエリ

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20241101');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20241130');

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 date_from() AND date_to()
      AND
        event_name = 'page_view'
    )
    GROUP BY
      ymd,
      landing_page
    ORDER BY
      ymd ASC,
      _ssuu DESC
)

SELECT
  *
FROM
  LP_Session_Daily

参照元付きのランディングページのセッション数を集計するクエリ

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20241101');
CREATE TEMP FUNCTION date_to() RETURNS STRING AS ('20241130');

CREATE OR REPLACE TEMP TABLE SMC_ssid AS (
  SELECT
    ssid,
    -- manual_source_medium AS source_medium,
    -- manual_source AS source,
    -- manual_medium AS medium,
    -- manual_campaign AS campaign,
    cross_source_medium AS source_medium,
    cross_source AS source,
    cross_medium AS medium,
    cross_campaign AS campaign    
  FROM (
    SELECT
      ssid,
      -- セッションの手動参照元
      CONCAT(manual_source,' / ', manual_medium) AS manual_source_medium,
      manual_source,
      manual_medium,
      manual_campaign,
      -- セッションの参照元
      CONCAT(cross_source,' / ', cross_medium) AS cross_source_medium,
      cross_source,
      cross_medium,
      cross_campaign
    FROM (
      SELECT
        CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS ssid,
        -- セッションの手動参照元
        session_traffic_source_last_click.manual_campaign.source AS manual_source,
        session_traffic_source_last_click.manual_campaign.medium AS manual_medium,
        session_traffic_source_last_click.manual_campaign.campaign_name AS manual_campaign,
        -- セッションの参照元
        session_traffic_source_last_click.cross_channel_campaign.source AS cross_source,
        session_traffic_source_last_click.cross_channel_campaign.medium AS cross_medium,
        session_traffic_source_last_click.cross_channel_campaign.campaign_name AS cross_campaign
      FROM
        `<project>.<dataset>.events_*`
      WHERE
        _TABLE_SUFFIX BETWEEN date_from() AND date_to()
      AND
        event_name = 'session_start'
    )
  )
);

WITH
  LP_Session_SMC AS (
    SELECT
      ymd,
      page_location AS landing_page,
      source_medium,
      source,
      medium,
      campaign,
      SUM(entrances) AS _ssuu
    FROM (
      SELECT
        ymd,
        page_location,
        source_medium,
        source,
        medium,
        campaign,
        entrances
      FROM (
        SELECT
          ymd,
          page_location,
          entrances,
          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,
            (SELECT value.int_value FROM UNNEST(event_params) p WHERE p.key = 'entrances') AS entrances,
            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 date_from() AND date_to()
          AND
            event_name = 'page_view'
        )
      ) AS id
      LEFT JOIN (
        SELECT
          *
        FROM
          _SESSION.SMC_ssid
      ) AS SMC_id
      ON id.ssid = SMC_id.ssid
    )
    GROUP BY
      ymd,
      landing_page,
      source_medium,
      source,
      medium,
      campaign
    ORDER BY
      ymd ASC,
      _ssuu DESC
)

SELECT
  *
FROM
  LP_Session_SMC

関連記事

【GA4SQL】ページ/セッションをGA4で確認する方法

【GA4 SQL】ページ単位のエンゲージメント分析

【GA4 SQL】ページ再訪問率をBigQueryから集計
PAGE TOP