BigQueryでGA4データのユーザーエクスプローラ

GA4では探索でユーザーエクスプローラの機能がありますが、イベント名が並んでいるだけで、どのページかを確認するためには、いちいち当該のイベントをクリックしてパラメータを見なければいけません。

いずれ改善されるかもしれませんが、今の段階では非常に面倒なので、BigQueryでユーザーがサイトをどのように使っているか軌跡を可視化してみます。

SELECT

user_pseudo_id,

(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,

event_name,

(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page,

datetime(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo') AS event_time,

lead(datetime(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo'))

over(partition by (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') order by datetime

(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo')) as next_event_time,

datetime_diff(lead(datetime(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo'))

over(partition by (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') order by datetime

(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo')),

datetime(TIMESTAMP_MICROS(event_timestamp),'Asia/Tokyo'), second) as margin

FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131` 

where event_name = 'page_view' or event_name = 'scroll'

クエリがやや長くなっていますが、ユーザーid、セッションid、イベント名、イベントが発生したページ、イベントが発生した時間、次のイベントが発生した時間、その差を集計しています。

イベントをpage_viewとscrollにしていますが、必要に応じてほかのイベントを表示させてもいいと思います。

クエリを実行すると下図のようになります。

セッションの最後のページ（離脱ページ）は次のページの時間が取得できませんので、nullになります。

お試しください。

 

twitter
@knjnakamura