GA4

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

2024.06.29 2024.06.30

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

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

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

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

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

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

サイト全体のエンゲージメント率だけを見ても具体的なアクションを起こすことは難しいため、通常は流入元などと組み合わせて確認する。実務においては参照元やランディングページとの組み合わせてモニタリングする必要があるが、ここではまず、基本的なエンゲージメントセッションの集計値を出すことを目的としているためそこまで踏み込まない。

エンゲージメントセッション数を集計するクエリ
- ChatGPTによるクエリ解説
エンゲージメント率を集計するクエリ

エンゲージメントセッション数を集計するクエリ

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

エンゲージメント率を集計するクエリ

エンゲージメント率は母数にセッション数が使われるためセッション数も合わせて集計し割ることでエンゲージメント率が求められる。ただし、実際は率の計算はBigQuery上では行わずBI側で計算することになる。理由として「率」は計算結果であり、日次から週次・月次へとドリルアップした際に再度計算が必要になるため。その際、日次での「率」の指標は不要である。これは「率」だけでなく「累計」など計算結果を伴うものに当てはまる。また月次での集計も、日次で集計しておけば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