GA4

[GA4 SQL] エンゲージメントセッション数をBigQueryから集計

2024.06.29 2024.12.31

エンゲージメントセッションとは
【SQL】エンゲージのあったセッション数
- ChatGPTによるクエリ解説
【SQL】エンゲージメント率
【SQL】ランディングページのエンゲージのあったセッション数

エンゲージメントセッションとは

「エンゲージメントセッション」（エンゲージメントのあったセッション数）とはユーザーのエンゲージメントを測定するGA4の中でも重要な指標となる。エンゲージメントとなる定義は以下の通り。

10秒を超えて継続したセッション
コンバージョンイベントが発生したセッション
2回以上のページビューが発生したセッション

参考：エンゲージメントセッション -アナリティクスヘルプ

エンゲージメント率（エンゲージのあったセッションの割合）はセッションの中でエンゲージメントセッションが発生した割合で計算式は以下の通りとなる。

エンゲージメント率 = エンゲージメントセッション数 / セッション数

参考：[GA4] エンゲージメント率と直帰率 -アナリティクスヘルプ

サイト全体のエンゲージメント率だけを見ても具体的なアクションを起こすことは難しいため、通常は流入元などと組み合わせて確認する。実務においては参照元やランディングページとの組み合わせて確認する必要がある。また、エンゲージメントセッションは「session_engagedパラメータ」を元に集計している。session_engagedパラメータについての解説は以下を参照。

2024.09.28

【詳解】session_engaged パラメーター

GA4からBigQueryにエクスポートされるイベントのパラメータであるsession_engagedについての検証メモ session_engagedパラメーターとは session_engagedパラメーターとは、ユーザーのセッション行動に対して、「エンゲージメントセッショ...

【SQL】エンゲージのあったセッション数

WITH
  EngagementSessions_01 AS (
    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))
      AND
        (SELECT COALESCE(e.value.string_value, SAFE_CAST(e.value.int_value AS STRING)) FROM UNNEST(event_params) e WHERE e.key = 'session_engaged' ) = '1'
      AND
        event_name = 'session_start'  
  ),
  EngagementSessions_02 AS (
    SELECT
      ymd,
      CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING)) AS ssid
    FROM
      EngagementSessions_01
  ),
  EngagementSessions_03 AS (
    SELECT
      ymd,
      COUNT(DISTINCT ssid) AS _eng_ss
    FROM
      EngagementSessions_02
    GROUP BY
      ymd
    ORDER BY
      ymd      
  )

SELECT
  *
FROM
  EngagementSessions_03

ChatGPTによるクエリ解説

1. 概要
このクエリは、Google Analytics 4（GA4）のデータから、日次のエンゲージメントセッション数を集計するためのものです。複数のサブクエリを使用して、特定の条件に基づいてデータを抽出し、ユニークなセッション識別子を作成して集計します。

2. データ抽出
まず、イベントデータの中からエンゲージメントされたセッションであり、かつセッション開始イベントであるデータを抽出します。この段階では、イベントの日付を日付形式に変換し（PARSE_DATE関数を使用）、ユーザー識別子（user_pseudo_id）とセッションID（ga_session_id）を取得します。抽出対象のデータは、指定された日付範囲内に限定されます。

3. セッション識別子の作成
次に、抽出したデータを元に、ユーザー識別子とセッションIDを組み合わせたユニークなセッション識別子（ssid）を作成します。この識別子は、ユーザーとセッションの一意の組み合わせを表します。

4. セッションの集計
ユニークなセッション識別子を作成した後、これを日付ごとに集計します。この段階では、各日付に対してユニークなセッション識別子の数をカウントし、日次のエンゲージメントセッション数として集計します。

5. 最終結果
最後に、集計結果を日付順に並べて取得します。これにより、各日付ごとのユニークなエンゲージメントセッション数を確認することができます。このクエリ全体のプロセスにより、日次のエンゲージメントセッション数を効率的に集計することが可能となります。

【SQL】エンゲージメント率

エンゲージメント率は母数にセッション数が使われる。率の計算はBigQuery上では行わずBI側で計算することになるが集計結果としてBigQuery上で例として載せている。率の計算がBigQueryで不要な理由として「率」は、日次から週次・月次へとドリルアップした際に再度計算が必要になるため。これは「率」だけでなく「累計」など計算結果も同様に当てはまる。また月次での集計も、日次で集計しておけばBI側で週次・月次へと再集計することができるため、BigQuery側では最小の日次での集計結果を出しておけば問題ない。

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
  ),
  EngagementSessions AS (
    SELECT
      ymd,
      COUNT(DISTINCT ssid) AS _eng_ss
    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))
          AND
            (SELECT COALESCE(e.value.string_value, SAFE_CAST(e.value.int_value AS STRING)) FROM UNNEST(event_params) e WHERE e.key = 'session_engaged' ) = '1'
          AND
            event_name = 'session_start'  
      )
    )
    GROUP BY
      ymd
  ),
  Join_SS_EngSS AS (
    SELECT
      s.ymd,
      _ssuu,
      COALESCE(_eng_ss, 0) AS _eng_ss,
      ROUND(_eng_ss / _ssuu,2) AS _eng_rate_ss
    FROM
      DailySession AS s
    LEFT JOIN
      EngagementSessions AS e
    ON
      s.ymd = e.ymd
    ORDER BY
      ymd ASC
  )

SELECT
  *
FROM
  Join_SS_EngSS

【SQL】ランディングページのエンゲージのあったセッション数

ランディングページのエンゲージのあったセッション数とはエンゲージメントの評価をランディングページに割り当てた評価方法である。ここで注意するべき点として「エンゲージメント」はセッションスコープであるため、ディメンションをイベントスコープであるページとエンゲージメントを組み合わせることはできないということである。ランディングページはセッションスコープであるため、エンゲージメントの指標と組み合わせることに問題はない。

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

WITH
  LP_Session 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) WHERE key = 'entrances') AS entrances
      FROM
        `<project>.<dataset>.events_*`
      WHERE
        _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND
        event_name = 'page_view'
    )
    GROUP BY
      ymd,
      page_location
  ),
  SessionFirstEventTime AS (
    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,
      MIN(event_timestamp) AS first_event_timestamp
    FROM
      `<project>.<dataset>.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND
      event_name = 'page_view'
    GROUP BY
      ymd,
      user_pseudo_id,
      ga_session_id
  ),
  LandingPages_01 AS (
    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
      _TABLE_SUFFIX BETWEEN date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND
      event_name = 'page_view'
  ),
  LandingPages_02 AS (
    SELECT
      f.ymd,
      f.user_pseudo_id,
      f.ga_session_id,
      l.page_location AS landing_page
    FROM
      SessionFirstEventTime AS f
    INNER JOIN
      LandingPages_01 AS l
    ON
      f.ymd = l.ymd
    AND
      f.user_pseudo_id = l.user_pseudo_id
    AND
      f.ga_session_id = l.ga_session_id
    AND
      f.first_event_timestamp = l.event_timestamp
  ),
  EngagementSessions_01 AS (
    SELECT
      ymd,
      user_pseudo_id,
      ga_session_id,
      COUNT(*) AS _engaged_sessions
    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 date_from() AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
        AND
          (SELECT COALESCE(value.string_value, SAFE_CAST(value.int_value AS STRING)) FROM UNNEST(event_params) WHERE key = 'session_engaged' ) = '1'
        AND
          event_name = 'session_start'
    )
    GROUP BY
      ymd,
      user_pseudo_id,
      ga_session_id
  ),
  LP_engaged_sessions  AS (
    SELECT
      lp2.ymd,
      lp2.landing_page,
      SUM(_engaged_sessions) AS _engaged_sessions
    FROM
      LandingPages_02 AS lp2
    INNER JOIN
      EngagementSessions_01 AS e
    ON
      lp2.ymd = e.ymd
    AND    
      lp2.user_pseudo_id = e.user_pseudo_id
    AND
      lp2.ga_session_id = e.ga_session_id
    GROUP BY
      lp2.ymd,
      lp2.landing_page
  ),
  LP_ss_engaged_sessions AS (
    SELECT
      s.ymd,
      s.landing_page,
      COALESCE(_ssuu, 0) AS _ssuu,
      COALESCE(_engaged_sessions, 0) AS _engaged_sessions,
      COALESCE(ROUND(_engaged_sessions / _ssuu,2), 0) AS _rate_engaged_sessions
    FROM
      LP_Session AS s
    LEFT JOIN  
      LP_engaged_sessions AS e
    ON 
      s.ymd = e.ymd
    AND
      s.landing_page = e.landing_page
  )

SELECT
  *
FROM
  LP_ss_engaged_sessions
ORDER BY
 ymd ASC