皆さんこんにちは
今回はフラットにしたGA4のテーブルを使って一般的なPVやSS数の計算を行ってみます。
（UNNEST化しないほうが楽な場合もあったりします、、）
【PV数を計算する】
日付でGROUP BYする
SELECT
DATE,
SUM(IF(event_name = “page_view”, 1, 0)) AS PV
FROM {UNNESTしたGA4テーブル}
GROUP BY DATE
【SS数を計算する】
日付でGROUP BYする
user_pseudo_idとga_session_idのint_valueをCONCATして、ユニーク数をカウントしたのがセッション数。
WITH T0 AS(
SELECT *,
CONCAT(CAST(user_pseudo_id AS STRING),“_”,CAST(IF(event_params.key = “ga_session_id”,) AS SS_KEY
FROM {UNNESTしたGA4テーブル}
)
SELECT
DATE,
COUNT(DISTINCT SS_KEY) AS SS
FROM T0
GROUP BY DATE
各ページ毎のセッション数を算出する場合は以前作成したpage_locationををいれてGROUP BYしましょう。
【UU数を計算する】
user_pseudo_idを使って計算をします。
SELECT
DATE,
COUNT(DISTINCT user_pseudo_id) AS UU
FROM {UNNESTしたGA4テーブル}
GROUP BY DATE
【セッション中の行動順を付ける】
GAUAでいうところのhitnumberを付けるのですが、GAUAではpageviewと任意で作成したイベントで
hitnumberを計算すればよかったですがGA4では自動収集されるイベントが多数存在しているため
本項ではPage_viewの時のみhitnumberを加算していくという定義で作成します。
SELECT
SUM(IF(event_name = “page_view”,1,0)) OVER (PARTITION SS_KEY ORDER BY date_timestamp_jst) AS HITS_NUM_PV
FROM {UNNESTしたGA4テーブル}
GROUP BY DATE
◆応用
上記で作成したhitnumberの最大値を離脱ポイントとして算出する場合は
IF(HITS_NUM_PV = (MAX(HITS_NUM_PV) OVER (PARTITION BY SS_KEY)),true,null) AS is_exit
さらにそのページで直帰したかはHITS_NUM_PVが1かつis_exitがtrueの場合とすればわかりやすいですね。
基本的な使い方については以上となります。
まだまだ使い勝手が良いとまではいかないGA4ですがいずれGAUAが終了する事を考えると
徐々に慣れていく必要があるかと存じます。
コメントを残す