빅쿼리에서의 GA4 데이터 활용 현실: 기회와 치명적인 문제점

GA4 데이터를 빅쿼리로 보내면 뭐든 다 해결될 것 같은 이야기들이 많지만 실상은 그렇지 못합니다. GA4 인터페이스와 차이가 많고 많은 데이터 용량으로 ETL 과정으로 생각보다 리소스가 많이 듭니다. 그럼에도 불구하고 빅쿼리를 쓰는 이유가 뭘까요?

GA4 빅쿼리네 데이터 Export 하는 기능

GA4 데이터를 빅쿼리로 활용하면 좋은 점

• 데이터 샘플링(GA4 인터페이스에서의 데이터는 샘플링이 되어있습니다)
• 일부 측정항목과 측정기준은 시스템에서 허용하지 않아서 결합해서 보기 어려움
• 데이터가 너무 많아서 상품 이름 같은 많은 측정 기준들이 (기타)라는 명칭으로 숨겨짐
• 유저 아이디별로 추출할 수 없고 집계된 계산만 추출 가능
• 데이터 카디널리티
  • GA4 보고서를 보면 분석 범위에 행이 많으면 데이터 카디널리티 발생
  • 이로 인해서 동일한 차원을 사용하는 표준 보고서와 다른 값이 나올 수 있음

데이터 카디널리티란?
카디널리티는 측정 기준 고유값의 개수.
하나의 측정기준에 얼마나 많은 고유값이 있는지를 의미함
예를 들어, 성별 측정기준은 남성, 여성으로 두 가지 값만 가지고 있으므로 카디널리티가 낮음, 반면 마케팅 데이터를 기준으로 캠페인차원(utm_campaign) 을 기준으로 측정기준을 가져가면 카디널리티가 높은 것

 

GA4 데이터를 빅쿼리로 사용한다면 판도라의 상자를 여는 것

일단 구글 애널리틱스 인터페이스와 데이터는 안 맞습니다. 아래는 공식문서상에서도 불일치가 있다는 것을 언급하고 있습니다.

[GA4] Compare Analytics reports and data exported to BigQuery(링크)

ga4 bigquery discrepency

 

GA4와 Bigquery의 불일치를 검색하면 상당히 많은 문서들이 나옵니다

차원데이터를 Break Down 하면 할수록(카디널리티가 높으면) (other)로 집계되기도 합니다. 

 

특히 GA4 빅쿼리 데이터를 가공하다 보면 어? 뭐지? 하는 현상들이 있는데 대표적인 하나의 케이스를 공유해볼까 합니다. 바로! 빅쿼리에 저장되는 GA4 데이터에는 구글 관련 트래픽에 가장 큰 문제가 있다는 것입니다ㅜㅜ

 

gclid 파라미터(구글 애즈 캠페인으로 들어온 트래픽이라는 의미)가 있는 세션이 시작되었을 때 빅쿼리는 utm_medium, utm_campaign 파라미터를 (organic)으로 기록해 버립니다

다시 말하면 구글 애즈로 들어왔다는 데이터라고 식별은 되지만 캠페인 명이 증발해 버리는 현상입니다.(아래 쿼리 참고)

소스 = google
매체 = Organic
캠페인 = (Organic)

 

빅쿼리 RAWDATA 에 구글 캠페인명이 없음

with events as (
      select
        -- DATE(datetime(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) as date,
        cast(event_date as date format 'YYYYMMDD') as date,
        -- unique session id
        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'
        -- starting from 20231102 the sessions_start and first_visit events don't need to be excluded anymore
        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 을통해 이런 현상을 해결 해볼 수 있습니다.

결론

유저 레벨 분석이 아닌 마케팅 성과를 집계하기 위한 목적(ROAS, CPA 등과 같은 성과지표)으로 GA4 데이터를 활용하고자 한다면 Bigquery Export 보다는 Google Analytics Data API 를 활용해서 일별로 (날짜, 소스, 매체, 캠페인 차원과 필요 지표들) 내부 DataWarehouse에 저장해서 활용하는 방식이 더 효율적일 수 있습니다.

 

참고로 Google Analytics Data API 를 활용한다면 집계된 데이터를 받아오기 때문에 Total Users와 같은 유니크한 집계값 (Count Distinct)를 해야 되는 지표는 제외하고 이벤트 단위(구매, 회원가입 이벤트 수)의 지표만 활용해야 합니다. 

※ GA4 API 데이터 적재하여 사용할 때 user 집계 measure를 사용하면 안되는 이유

예를 들어 4명의 유저가 우리 사이트에 2024-01-01 ~ 2024-01-03 기간 동안 방문했습니다.(로우 데이터)

event_time	user_id
2024-01-01	U1
2024-01-01	U2
2024-01-01	U3
2024-01-02	U1
2024-01-02	U2
2024-01-03	U1
2024-01-03	U3
2024-01-03	U4

 

일별 Total User를 집계해 보면

event_time	user_id
2024-01-01	3
2024-01-02	2
2024-01-03	3

 

전체 기간의 Total User를 집계해 보면

event_time_range	unique user count
2024-01-01 to 2024-01-03	4

 

일별, 기간별 집계했을 때 유니크 사용자수는 완전히 다릅니다. (DAU, MAU 개념과 동일) 일별 테이블을 다 더해서 집계를 해서 사용하시면 쓸모없는 지표가 되어버리기 때문입니다.

 

그래도 유저레벨 단위로 혹은 GA4 인터페이스에서 처리 못하는 방식의 데이터 가공이 필요하다면 위의 케이스를 반드시 고려해야 될 뿐 아니라 분석 단위(dimension) 별 유저수, source, medium별 attribution의 차이를 염두하고 빅쿼리를 사용하셔야 합니다. 

 

 

 

 

풀스택 마케팅 컨설팅펌 마티니아이오

Martinee

 

마티니 링크드인

https://www.linkedin.com/company/martinee

Martinee (마티니아이오) | LinkedIn