GA4: BigQuery でリファラー URL を抽出

投稿日 by

 
Google Analytics 4 の UI に慣れるつもりはないので BigQuery で抽出したのでメモ。

SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS referrer,
  COUNT(1) AS visits
FROM
  `analytics_1234567.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20210501' and '20210531'
  AND event_name = 'page_view'
  -- 参照除外 / referrer exclusion
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer')
      not like '%www.kwonline.org%'
GROUP BY 1
ORDER BY 2 DESC

BigQuery だとほんと楽だわ。