「エンゲージメント セッション」とはユーザーのエンゲージメントを測定するGA4の中でも重要な指標となる。探索の表記では「エンゲージのあったセッション数」となる。エンゲージメントとなる定義は以下の通り。
- 10 秒を超えて継続したセッション
- コンバージョン イベントが発生したセッション
- 2 回以上のページビューが発生したセッション
参考:エンゲージメント セッション -アナリティクス ヘルプ
エンゲージメント率はセッションの中でエンゲージメント セッションが発生した割合で計算式は以下の通りとなる。
参考:[GA4] エンゲージメント率と直帰率 -アナリティクス ヘルプ
サイト全体のエンゲージメント率だけを見ても具体的なアクションを起こすことは難しいため、通常は流入元などと組み合わせて確認する。実務においては参照元やランディングページとの組み合わせてモニタリングする必要があるが、ここではまず、基本的なエンゲージメント セッションの集計値を出すことを目的としているためそこまで踏み込まない。
エンゲージメント セッション数を集計するクエリ
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