GA4 데이터를 빅쿼리로 보내면 뭐든 다 해결될 것 같은 이야기들이 많지만 실상은 그렇지 못합니다. GA4 인터페이스와 차이가 많고 많은 데이터 용량으로 ETL 과정으로 생각보다 리소스가 많이 듭니다. 그럼에도 불구하고 빅쿼리를 쓰는 이유가 뭘까요?
GA4 데이터를 빅쿼리로 활용하면 좋은점
- 데이터 샘플링(GA4 인터페이스에서의 데이터는 샘플링이 되어있습니다)
- 일부 측정항목과 측정기준은 시스템에서 허용하지 않아서 결합해서 보기 힘듬
- 데이터가 너무 많아서 상품 이름 같은 많은 측정 기준들이 (기타)라는 명칭으로 숨겨짐
- 유저 아이디별로 추출 할 수 없고 집계된 계산만 추출 가능
- 데이터 카디널리티
- GA4 보고서를 보면 분석 범위에 행이 많으면 데이터 카디널리티 발생
- 이로 인해서 동일한 차원을 사용하는 표준 보고서와 다른 값이 나올 수 있음
GA4 데이터를 빅쿼리로 사용한다면 판도라의 상자를 여는것
GA4 빅쿼리 데이터를 가공하다보면 어? 뭐지? 하는 현상들이 있는데 대표적인 하나의 케이스를 공유해볼까합니다. 바로! 빅쿼리에 저장되는 GA4 데이터에는 구글 관련 트래픽에 가장 큰 문제가 있다는 것입니다ㅜㅜ
gclid 파라미터(구글 애즈 캠페인으로 들어온 트래픽이라는 의미)가 있는 세션이 시작 되었을 때 빅쿼리는 utm_medium, utm_campaign 파라미터를 (organic)으로 기록해버립니다
다시 말하면 구글 애즈로 들어왔다는 데이터라고 식별은 되지만 캠페인 명이 증발해버리는 현상입니다.
with events as (
select
cast(event_date as date format 'YYYYMMDD') as date,
concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id,
user_pseudo_id,
(select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_start,
if(
(select value.string_value from unnest(event_params) where key in ('campaign_id', 'campaign', 'source', 'medium', 'term', 'content', 'gclid', 'dclid', 'srsltid') and value.string_value is not null limit 1) is not null,
(
select as struct
(select value.string_value from unnest(event_params) where key = 'campaign_id') as manual_campaign_id,
(select value.string_value from unnest(event_params) where key = 'campaign') as manual_campaign_name,
(select value.string_value from unnest(event_params) where key = 'source') as manual_source,
(select value.string_value from unnest(event_params) where key = 'medium') as manual_medium,
(select value.string_value from unnest(event_params) where key = 'term') as manual_term,
(select value.string_value from unnest(event_params) where key = 'content') as manual_content,
(select value.string_value from unnest(event_params) where key = 'gclid') as gclid,
(select value.string_value from unnest(event_params) where key = 'dclid') as dclid,
(select value.string_value from unnest(event_params) where key = 'srsltid') as srsltid
),
null
) as collected_traffic_source,
event_timestamp,
stream_id,
(select value.string_value from unnest(event_params) where key = 'device_category') device_category,
event_name,
ecommerce.purchase_revenue_in_usd as purchase_revenue_in_usd,
case
when event_name = 'purchase'
then 1 else 0 end AS purchase
from
`테이블명`
where
_TABLE_SUFFIX >= '20231001' AND _TABLE_SUFFIX <= '20231001'
and (select value.string_value from unnest(event_params) where key = 'source') = 'google'
)
select * from events
order by session_id
이 현상을 해결하려면 UTM 파라미터를 자동 태깅(gclid)를 대신해서 사용하거나 URL에 커스텀 파라미터를 붙여서 해결할 수 있긴 합니다.
- Page Referrer 에 담긴 gclid 를 모아서 빅쿼리의 데이터를 수정해줘야 됩니다.
- 여기서 한가지 조건이 있는데
- 유저가 세션안에서 2개의 페이지 뷰 이벤트를 가지고 있어야 합니다.
- 왜냐하면 사용자가 광고 클릭 후 랜딩 페이지에 도착하고 이후 추가 페이지를 탐색할 경우, 첫 번째 페이지(랜딩 페이지)에서의 referrer에 gclid가 포함되어 기록될 가능성이 높기 때문입니다.
- 따라서 GA4 Custom Dimensions 을 통해서 ga_session_id, gclid 를 구글 태그매니저(GTM)로 잡는 방법을 사용하면 됩니다.
- 이렇게 셋팅을 했다면 GA4 API 를 통해서 sessionCampaignName, sessionSourceMedium, customEvent:ga_session_id, customEvent:gclid 를 구글 스프레드시트나 빅쿼리에 테이블을 만들거나 혹은 GCF(Google Cloud Function)을 활용해서 빅쿼리 테이블 만들어서 GA4 빅쿼리 테이블과 JOIN 을통해 이런 현상을 해결 해볼 수 있습니다.
결론!
유저 레벨 분석이 아닌 마케팅 성과를 집계하기 위한 목적으로 GA4 데이터를 활용하고자 한다면 Bigquery Export 보다는 Google Analytics Data API 를 활용해서 일별로 내부 DataWarehouse에 저장하는 방식이 더 효율적일 것 같습니다.
참고로 Google Analytics Data API 를 활용한다면 집계된 데이터를 받아오기 때문에 Total Users 와 같은 유니크한 집계값 (Count Distinct)를 해야되는 지표는 제외하고 이벤트 단위의 지표만 활용해야합니다. 이유는 아래와 같습니다.
4명의 유저가 2024-01-01 ~ 2024-01-03 에 방문했습니다.(로우 데이터)
일별 Total User 를 집계해보면
전체 기간의 Total User 를 집계해보면
일별, 기간별 집계 했을 때 유니크 사용자수는 완전히 다릅니다. (DAU, MAU 개념과 동일)일별 테이블을 다 더해서 집계를 해서 사용하시면 쓸모없는 지표가 되어버리기 때문입니다.
그래도 유저레벨 단위로 혹은 GA4 인터페이스에서 처리 못하는 방식의 데이터 가공이 필요하다면 위의 케이스를 반드시 고려해서 빅쿼리를 사용하시는 걸 권장드립니다.
Reference
Why your BigQuery results don't (exactly) match with Google Analytics reports (GA4)
