GA4で使った SQL集 2

GA4で使用したSQLの備忘録です。
GA4のBigQueryエクスポート機能を使ってデータを抽出しています。

１：指定したURLを条件にPVを発生させたユニークなユーザー数

特定のページを表示したユニークユーザーを算出したい時に、利用していたクエリ文です。
クリック数と組み合わせ、CTR算出に用いることが多かったです。
URLの抽出は正規表現で。
イベント名を変更すれば、別イベントでの絞り込みができるかと思います。
期間指定に特にこだわりはありません。

WITH t1 as 
SELECT 
    event_date 
    ,count (distinct user) as top_user 
FROM 
    (SELECT 
        event_date user_pseudo_id as user 
        ,event_name
        ,e.key as page_location 
        ,e.value.string_value as url 
     FROM pmsg-portal-prd.analytics_********.events_** 
    ,unnest (event_params) as e 
     WHERE 
        key = "page_location" and REGEXP_CONTAINS(value.string_value,r"^https:.*") and _TABLE_SUFFIX BETWEEN "20230801" AND "20230808" and event_name = "page_view"
     GROUP BY event_date, user_pseudo_id, event_name,page_location, url) 
GROUP BY 
    event_date, user 
ORDER BY event_date ) 


SELECT 
    t1.event_date
    , sum(top_user) as user 
FROM 
    t1 
GROUP BY 
    t1.event_date, top_user 
ORDER BY 
    t1.event_date

　　

２：アクティブユーザー数

GA4のアクティブユーザーは、エンゲージメントが発生したかが定義となります。
Big Queryではその判断は、
・session_engaged_flagが１
・first_visit　がイベントが発生しているか
・engagement_time_msecが0以上か

上記3つに該当すればアクティブユーザーと判断されるため、
CASE文の条件分岐で抽出しています。
期間指定に特にこだわりはありません。
　
　　

WITH prep AS (
SELECT
    event_date
    ,user_pseudo_id
    ,event_name
    , (SELECT key FROM UNNEST(event_params) WHERE key = 'page_location') AS ur1
    , (SELECT value.string_value FROM UNNEST (event_params) WHERE key = 'page_location') AS page_location
    , (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged_flag
    ,(SELECT value.int_value FROM UNNEST (event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec
FROM
    'pmsg-portal-prd.analytics_***********.events_*'
WHERE
_TABLE_SUFFIX = FORMAT_DATE ("*Y%m%d", DATE_SUB(CURRENT_DATE ('Asia/Tokyo'), INTERVAL 2 DAY))
GROUP BY 
    event_date, user_pseudo_id, event_name, page_location, url, session_engaged_flag, engagement_time_msec
)


SELECT
    event_date,
    COUNT (DISTINCT CASE WHEN session_engaged_flag = '1' THEN user_pseudo_id
    WHEN event_name = 'first_visit THEN user_pseudo_id
    WHEN engagement_time_msec > 0 THEN user_pseudo_id END) AS active_user_count
FROM 
    prep
GROUP BY
    event_date
ORDER BY
    event_date

３：参照元、メディアごとのセッション数

セッションの参照元/メディアを抽出するクエリ文です。
定番のクエリ文ですが、イベント名を変更したり、UIDのカラムを加えたりして、色んな条件での流入先を把握する際に流用していました。

with m1 as 
(
select 
    event_date
    ,concat (traffic_source. source," / ",traffic_source.medium) as source_medium
    ,count (event_name) as sessions
from pmsg-portal-prd.analytics_********.events_*
where
    _TABLE_SUFFIX = FORMAT_DATE ("*Y%m%d", DATE_SUB (CURRENT _DATE (Asia/Tokyo"),INTERVAL 2 DAY)) and event_name = 'session_start'
group by
    event_date, source_medium 
order by
    event_date desc, sessions desc
)

まとめ

今回は、3つのSQLの構文を書かさせて頂きました。
基本的なクエリかも知れませんが、多用、流用することが多かったため備忘録として記載した次第です。

ご参考になれば幸いです。