GoogleAnalyticsページごとのエンゲージメント時間をBigQueryで集計する (滞在時間)

GoogleAnalyticsのエンゲージメント時間をBigQueryで集計する (滞在時間)

ネットで調べたら、少し違うものが上位にでてきてなかなか回答にたどりつかなかったので、こちらで記載します。

滞在時間を求めてください。

最初このように依頼がきました。

普通の感覚で滞在時間をとらえてしまうと、GA4の概念とは離れてしまいます。

普通の感覚の滞在時間はUAに近いようでGA4では別の概念になります。

具体的には

エンゲージメントという考えが必要です。

以下のサイトがわかりやすかったです。

小川卓さんのGA4（Google Analytics 4）連載 第11回 基本的な指標「滞在時間」「コンバージョン」を理解する | パソコン工房 NEXMAG

エンゲージメント時間を求める

以下のサイトを参考にSQLを作成しました。(二つとも海外のサイト)

https://www.vakulski-group.com/blog/post/complete-guide-to-user-engagement-in-google-analytics-4/

sql - How to calculate the "average engagement time" of GA4 using Big Query? - Stack Overflow

ページごとのエンゲージメント時間を集計するSQLです。

ポイント

時間はengagement_time_msecを集計

session_engaged = '1'　エンゲージメントのあるものを集計

～
WITH base_data AS (
    SELECT
        event_date,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'page_location') AS page_location,
        user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'ga_session_id') AS session_id,
        (SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'session_engaged') AS session_engaged,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE KEY = 'engagement_time_msec')/1000 AS engagement_time_seconds
        
    FROM
        `プロジェクト46302.analytics_377777777.events_*`
    WHERE
        _TABLE_SUFFIX BETWEEN '20230904' AND '20230904'
),

-- ステップ2: 各セッションにおけるページごとのエンゲージメント時間を計算
session_engagement AS (
    SELECT
        event_date,
        user_pseudo_id,
        session_id,
        page_location,
        SUM(engagement_time_seconds) AS session_engagement_time
    FROM
        base_data
    WHERE
        session_engaged = '1'
    GROUP BY
        event_date, user_pseudo_id, session_id, page_location
)

select 
event_date,
page_location,
sum(session_engagement_time)

 from session_engagement
 group by event_date,page_location
～

以上です。
数値は探索レポートで確認しました。
ピタッと一緒ではないけれど、ほぼ同じでした。