GA4→BigQueryエクスポートデータをPV（ページビュー）単位に集約

GA4からBigQueryにエクスポートされたデータを加工する際の工夫のメモ。

GTMなどで独自にイベント（例えば、event_name='sample'）を発火させる場合に、このsampleイベントのデータを取得しつつ、ページビュー単位のテーブルを作成する方法について。

その１．
・全データを取得しつつ、下記のようにpage_viewイベントかどうかのフラグを作成する。
・セッションID（user_pseudo_idとga_session_idを結合して作る、セッションユニークなID）カラムは作成しておく。下記ではunique_session_idと呼ぶ。

CASE
    WHEN event_name = 'page_view' THEN 1 
END AS page_view_flg

ちなみにga_session_idは下記のイメージ

select value.int_value from unnest(event_params) where key = 'ga_session_id'

その２．
その１の結果に対して、下記の処理を行う。
・page_viewイベントのレコードに対して、セッション内でのpage_viewイベントのナンバリングを行う。

CASE
    WHEN page_view_flg = 1 THEN
        ROW_NUMBER() OVER (PARTITION BY unique_session_id, page_view_flg ORDER BY event_timestamp)
END AS page_view_num

その３．
その２の結果に対して、下記の処理を行う。
・page_viewイベント以外のレコードに対しても、直前のpage_viewイベントのレコードのpage_view_numを付与する

LAST_VALUE(page_view_num IGNORE NULLS) OVER (PARTITION BY unique_session_id ORDER BY event_timestamp) AS page_view_num

その４．
その３の結果に対して、下記の処理を行う。
・page_view_num内でカラムの値を共有し、SELECT DISTINCTでデータを取得する
　分析関数は、FIRST_VALUE()、LAST_VALUE()、SUM()なのかはカラムの定義に併せて実装する。
　セッション単位のカラムについては、PARTITION BY でpage_view_numを使わないように。

LAST_VALUE(sample_column IGNORE NULLS) OVER (PARTITION BY unique_session_id, page_view_num ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sample_column

お終い。

【注意点とか】
・ページビューイベント以外のイベント数の集計であれば、その４で、分析関数SUM()を使う。
・同一ページビュー（次のページビューイベントまでに発生したイベントを含む）で、複数のsampleイベントが発火したとして、これらを識別した状態でテーブルを作成することは想定していない。
　→もし識別した状態で作成するなら、上記のその１～その４を、sampleイベントベースでも行い、ページビューイベントベースのテーブルと結合すればOk。
　ただし、同一ページビュー（次のページビューイベントまでに発生したイベントを含む）で複数のsampleイベントが発生しているデータについては、イベント数の集計が重複することに注意。
　 この場合は、ページビュー数はCOUNT(DISTINCT uniuq_session_id, page_view_num)みたいなイメージ。