BigQueryでGA4データの流入経路単位集計
■メディア単位のセッション数集計
BigQueryにあるGoogleアナリティクス4のデータを使って流入経路単位のsession数を集計します。GA4のサンプルデータが用意されていますので、それを使っています。
[GA4] BigQuery 用の Google アナリティクス 4 プロパティのサンプル データセット
メディア単位で集計するには、メディアとセッションIDを取り出す必要があります。
クエリです----------------------------------------------------------------------
SELECT
traffic_source.medium,
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
------------------------------------------------------------------------------------
ただ、ga_session_idはユーザー間で重複するかもしれませんので、user_pseudo_idとconcatし、メディア単位でユニークにカウントします。また、session_startイベントに絞ります。
クエリです----------------------------------------------------------------------
SELECT
traffic_source.medium,
WHERE key = 'ga_session_id') as STRING))) AS sessions,
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
where event_name = 'session_start'
group by 1
------------------------------------------------------------------------------------
■セッション数集計にsession_startイベントを使う
セッション数の集計は、user_pseudo_idとga_session_idをCONCATして集計しましたが、session_startイベントをそのままカウントする方法もあります。ただ、この場合前者よりも数字が若干多くなっています。organicでは955が956になっています。
尚、原因の深堀まではできていません。。
■参照元/メディアでの集計
参照元/メディアで集計したい場合は、traffic_source.sourceとtraffic_source.mediumをCONCATすればできます。
concat(traffic_source.source,' / ',traffic_source.medium) as source_medium
