[Bigquery] GA4 metrics(수치형) 데이터 추출 쿼리 방법 ex) PV, AU 등

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

결과 수치는 구글 애널리틱스의 보고서의 기준과 다소 차이가 있을 수 있습니다!

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

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

 

조회수(PV, Page_view, 페이지 뷰)

SELECT
sum(case when event_name = 'page_view' and key = 'page_location'  then 1 else 0 end) as PAGE_VIEW
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)) --(까지)

 

사용자수(AU, ActiveUser,  활성사용자수)

SELECT
count(distinct case when is_active_user is true then user_pseudo_id end) as ACTIVE_USER
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)) --(까지)

 

총 사용자수(TotalUser)

SELECT
count(distinct user_pseudo_id) as TOTAL_USER
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)) --(까지)

 

세션수(Sessions)

SELECT
count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) SESSIONS
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)) --(까지)

 

참여세션수(Engaged Sessions)

SELECT
count(distinct case when (select value.string_value from unnest(event_params) where key='session_engaged') = '1' then concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id')) end) ENGAGED_SESSIONS
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)) --(까지)

 

평균세션시간(Average Session Duration)

WITH TEMPORARY AS(
SELECT
  TIMESTAMP_ADD(TIMESTAMP_MICROS(event_timestamp), INTERVAL 9 HOUR) timestamp
, concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) SESSION_ID
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
sum(session_second)/count(distinct SESSION_ID) SESSION_AVG_DUR
FROM
  (
  SELECT
  SESSION_ID,
  (UNIX_MICROS(max(timestamp))-UNIX_MICROS(min(timestamp)))/1000000 session_second
  FROM TEMPORARY
  group by 1
  )

 

평균참여시간(Average Engaged Duration)

WITH TEMPORARY AS (
SELECT
  case when key = 'engagement_time_msec' then value.int_value end ENGAGED_TIME
, case when is_active_user is true then user_pseudo_id end ACTIVE_USER
, concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) SESSION_ID
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
  (sum(ENGAGED_TIME)/1000) / count(distinct ACTIVE_USER) --(활성)사용자 당 평균참여시간
, (sum(ENGAGED_TIME)/1000) / count(distinct SESSION_ID)  --세션 당 평균참여시간 
FROM TEMPORARY