[Bigquery] GA4 dimension(속성형) 데이터 추출 쿼리 방법 ex) 지역, 재방문 등

용어는 GA4 REPORT & Bigquery 데이터 용어 사전(설명) 블로그 페이지 참고!

[Bigquery] GA4 Report & Bigquery 데이터 용어 사전(설명)

 

myproject.analytics_123456789 부분은 본인 프로젝트 명으로 변경 필수!

결과문은 예시문입니다.

 

국가, 지역, 도시(country, region, city)

WITH TEMPORARY AS (
SELECT
  geo.country -- 국가
, geo.region  -- 지역
, geo.city    -- 도시
, case when is_active_user is true then user_pseudo_id end AU
, case when event_name = 'page_view' and key = 'page_location' then 1 else 0 end as PV
FROM `myproject.analytics_123456789.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX between '20240629' --(부터)
                    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) --(까지)
)
SELECT
country, region, city, count(distinct AU) AU, SUM(PV) PV
FROM TEMPORARY
GROUP BY country, region, city
ORDER BY AU DESC

country	region	city	AU	PV
South Korea	Seoul	Seoul	1983	6209
South Korea	Busan	Busan	599	1686
South Korea	Jeollanam-do	Gwangju	226	698

 

신규방문자, 재방문자(NewUser, ReturningUser)

WITH TEMPORARY AS (
SELECT
  case when (select value.int_value from unnest(event_params) where key = 'ga_session_number') = 1 then 'New' else 'Returning' end newVsReturning
, case when is_active_user is true then user_pseudo_id end AU
, case when event_name = 'page_view' and key = 'page_location' then 1 else 0 end as PV
FROM `myproject.analytics_123456789.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX between '20240629' --(부터)
                    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) --(까지)
)
SELECT
newVsReturning, count(distinct AU) AU, SUM(PV) PV
FROM TEMPORARY
GROUP BY newVsReturning
ORDER BY AU DESC

NewVsReturning	AU	PV
Returning	466	2033
New	414	1087

 

브라우저(Browser)

WITH TEMPORARY AS (
SELECT
  device.web_info.browser
, device.web_info.browser_version
, case when is_active_user is true then user_pseudo_id end AU
, case when event_name = 'page_view' and key = 'page_location' then 1 else 0 end as PV
FROM `myproject.analytics_123456789.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX between '20240629' --(부터)
                    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) --(까지)
)
SELECT
  browser
, browser_version
, count(distinct AU) AU
, sum(PV) PV
FROM TEMPORARY
GROUP BY browser, browser_version
ORDER BY AU DESC

browser	browser_version	AU	PV
Chrome	124.0.0.0	1888	1888
Samsung Internet	25.0	1045	2950
Chrome	126.0.6478.127	694	1986

 

운영체제(Operating_system)

WITH TEMPORARY AS (
SELECT
  device.operating_system
, device.operating_system_version
, case when is_active_user is true then user_pseudo_id end AU
, case when event_name = 'page_view' and key = 'page_location' then 1 else 0 end as PV
FROM `myproject.analytics_123456789.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX between '20240629' --(부터)
                    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) --(까지)
)
SELECT
operating_system, operating_system_version, count(distinct AU) AU, SUM(PV) PV
FROM TEMPORARY
GROUP BY operating_system, operating_system_version
ORDER BY AU desc

operating_system	operating_system_version	AU	PV
Android	Android 10	419	1980
Windows	Windows 10	54	102
iOS	iOS 17.4.1	40	81

 

유입(SOURCE, MEDIUM)

WITH TEMPORARY AS (
SELECT
  traffic_source.source
, traffic_source.medium
, case when is_active_user is true then user_pseudo_id end AU
, case when event_name = 'page_view' and key = 'page_location' then 1 else 0 end as PV
FROM `myproject.analytics_123456789.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX between '20240629' --(부터)
                    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) --(까지)
)
SELECT
source, medium, count(distinct AU) AU, SUM(PV) PV
FROM TEMPORARY
GROUP BY source, medium
ORDER BY AU desc

source	medium	AU	PV
(direct)	(none)	414	1556
Tcall	display	289	1253
google	organic	49	106

 

방문시간대(VisitingTime)

WITH TEMPORARY AS (
SELECT
  TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 9 HOUR) timestamp
, case when is_active_user is true then user_pseudo_id end AU
, case when event_name = 'page_view' and key = 'page_location' then 1 else 0 end as PV
FROM `skb-biz-ga4.analytics_320354291.events_*`, unnest(event_params)
FROM `myproject.analytics_123456789.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX between '20240624' --(부터)
                    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) --(까지)
)
SELECT
CASE WHEN EXTRACT(DAYOFWEEK from timestamp) = 1 THEN '일'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 2 THEN '월'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 3 THEN '화'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 4 THEN '수'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 5 THEN '목'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 6 THEN '금'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 7 THEN '토' END DayOfweek,
count(distinct AU) AU, SUM(PV) PV
FROM TEMPORARY
GROUP BY DayOfweek
ORDER BY AU desc

Hour	AU	PV
23	30	82
22	35	116
21	45	122
20	60	161

 

방문요일(DayOfWeek)

WITH TEMPORARY AS (
SELECT
  TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 9 HOUR) timestamp
, case when is_active_user is true then user_pseudo_id end AU
, case when event_name = 'page_view' and key = 'page_location' then 1 else 0 end as PV
FROM `myproject.analytics_123456789.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX between '20240624' --(부터)
                    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) --(까지)
)
SELECT
CASE WHEN EXTRACT(DAYOFWEEK from timestamp) = 1 THEN '일'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 2 THEN '월'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 3 THEN '화'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 4 THEN '수'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 5 THEN '목'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 6 THEN '금'
     WHEN EXTRACT(DAYOFWEEK from timestamp) = 7 THEN '토' END DayOfweek,
count(distinct AU) AU, SUM(PV) PV
FROM TEMPORARY
GROUP BY DayOfweek
ORDER BY AU desc

DayOfweek	AU	PV
금	3094	9474
화	3067	9126
목	2858	8841

 

방문 회수(Visit_count)

WITH TEMPORARY AS (
SELECT
  dense_rank() OVER(PARTITION BY case when is_active_user is true then user_pseudo_id end ORDER BY event_date) visit_count
, case when is_active_user is true then user_pseudo_id end AU
, case when event_name = 'page_view' and key = 'page_location' then 1 else 0 end as PV
FROM `myproject.analytics_123456789.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX between '20240624' --(부터)
                    and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) --(까지)
)
SELECT
visit_count,
count(distinct AU) AU, SUM(PV) PV
FROM TEMPORARY
GROUP BY visit_count
ORDER BY AU desc

visit_count	AU	PV
1	10087	29145
2	1039	4556
3	346	1755

 

 

일단 마케팅 분야에서 많이 쓰일 듯한 기본적인 데이터 위주로 정리했는데, 추가로 더 올릴 수 있다. 

일단 여기까지.. 너무 많다