GA4(Google Analytics)의 활성사용자를 BigQuery 데이터를 이용하여 뽑아보자.

결론부터 기록하자면,

GA4의 탐색이나 Data Report API를 통해서 구할 수 있는 “총 사용자, 활성 사용자, 조회(PageView)”를 BigQuery로 구하면 아래와 같습니다.

select
  event_date
  , count(distinct user_pseudo_id) as users
  , count(distinct
      (
       select 
         distinct user_pseudo_id 
       from 
         unnest(event_params) ep 
       where 
         event_name in ('first_visit') 
         or 
        (ep.key='engagement_time_msec' and ep.value.int_value > 0))
       ) as active_users
  , sum(case when event_name = 'page_view' then 1 else 0 end) as pv
from
  `analytics_xxxxxxxxx.events_*`
where
  _table_suffix between '20230214' and '20230221'
group by 1
order by 1 desc

GA에서는 주어진 기간 동안 최초 방문이 발생했거나 engagement_time_msec의 값이 존재하는 고객을 활성 고객이라고 언급을 하고 있어 간단하게 SQL을 구성해 봤더니 Data API 나 탐색 그리고 Looker GA Connector에서 제공하는 집계값과 동일한 값을 구할 수 있었습니다.

혹시나 GA데이터를 BigQuery를 통해 구성하고 분석하시는 분들께 도움이 되었으면 좋겠네요.

BigQuery에서 일자별 사용자, 활성사용자, 조회수 질의 결과
GA4 탐색에서 일자별 사용자, 활성 사용자, 조회수 구성 결과