本記事では、BigQueryにエクスポートしたGA4データを用いて、エンゲージメントセッション数（エンゲージのあったセッション数）を集計するSQLクエリについて解説をおこなう。

※ 執筆時点においてGoogleのアナリティクスヘルプでは「エンゲージメント セッション」、GA4の標準レポート及び探索画面では「エンゲージのあったセッション」と表記されていて、名称が完全に定まっていないように思われる。

SQLクエリ概要

まずevent_params 内にあるsession_engeged パラメータを使用し、 session_engaged = ‘1’ となった場合にエンゲージメントセッションが発生したとみなす。

ただ、エンゲージメントセッションがあったことを表す「1」は数値ではなく文字列なので注意が必要である（event_params.value.string_value に格納されている）。

セッション数の集計方法に関する詳しい説明はこちらの記事を参照されたい。

また、event_params内のパラメータを取り出す方法に関する詳しい説明はこちらの記事を参照されたい。

SQLクエリサンプル①

今回のクエリサンプルは２つある。まず１つ目のクエリから見ていこう。

WITH prep AS (
  SELECT
    event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged_flag
  FROM
    `technogram-354804.analytics_291973511.events_intraday_202210*`
)

SELECT
  event_date,
  COUNT(DISTINCT CASE WHEN session_engaged_flag = '1' THEN CONCAT(user_pseudo_id, ga_session_id) END) AS engaged_session_count
FROM
  prep
GROUP BY
  event_date
ORDER BY
  event_date
;

SQLクエリサンプル②

続いて２つ目のクエリサンプルを見ていこう。

WITH prep AS (
  SELECT
    event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    MAX((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged')) AS session_engaged_flag
  FROM
    `technogram-354804.analytics_291973511.events_intraday_202210*`
  GROUP BY
    event_date,
    user_pseudo_id,
    ga_session_id
)

SELECT
  event_date,
  COUNTIF(session_engaged_flag = '1') AS engaged_session_count
FROM
  prep
GROUP BY
  event_date
ORDER BY
  event_date
;

■ BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになった。

■ GA4（Googleアナリティクス4）探索画面での集計結果

続いて、GA4での探索画面での集計結果は、以下のようになった。

BigQueryでの集計結果とGA4探索画面での集計結果には微妙な差異が発生していることがわかる。

Googleのアナリティクスへルプにおいて、GA4の探索画面を用いた集計とBigQueryにエクスポートしたデータのSQL集計では結果に差異が生じる可能性があるとの記載があるので、両者の数値が一致しないことに対してそこまで神経質になる必要性はないと思われるが、引き続き調査を継続し、新しい発見があったら本記事の更新を随時おこなっていく予定である。

BigQuery には効率的な計算方法が適用されないため、BigQuery の計算結果と、標準レポート、カスタム レポート、データ探索ツール、Looker Studio の計算結果にはわずかな差異が生じることがあります。

データを確認する方法を選ぶ際は、次のヒントを参考にしてください。

• 元データからより正確な結果を取得したい場合は、BigQuery で結果を確認してください。
• より効率的に結果を取得したい場合は、標準レポート、カスタム レポート、データ探索ツール、Looker Studio で結果を確認してください。

（出典）

https://support.google.com/firebase/answer/9191807

また関連記事として、以下の記事も参考にされたい。