GA4 + BigQuery로 ROAS 파이프라인 직접 만들기 — 플랫폼 대시보드 못 믿을 때

“Meta는 ROAS 5.2라는데, GA4는 3.8이고, BigQuery 우리 자체 집계는 4.5예요. 어느 게 맞는 거죠?” — 데이터 분석가가 회의에서 이런 답을 내놓으면 마케터는 멘붕이에요. 사실 셋 다 다른 데이터·다른 정의로 답하고 있어서 모두 틀리지 않아요. 이 글은 그 혼란을 끝내는 가장 깔끔한 답 — GA4 → BigQuery 익스포트로 직접 ROAS 파이프라인을 만드는 4단계 SQL입니다.

왜 직접 만들어야 하나

플랫폼·도구별 ROAS의 한계:

자체 파이프라인의 진짜 가치는 “플랫폼이 안 보여주는 정의”를 자유롭게 만들 수 있다는 거예요.

• “브랜드 검색 제외 ROAS”
• “신규 vs 기존 분리 ROAS”
• “베이지안 신뢰구간 ROAS”
• “incrementality 보정된 ROAS”

플랫폼 UI에서는 절대 안 나오는 뷰들. 한 번 만들어두면 매주 자동으로 돌아갑니다.

1단계 — GA4 → BigQuery 익스포트 활성화

GA4 속성 설정 → BigQuery 연결 → “매일 익스포트” 옵션 켜기. 다음 날부터 다음 형식의 테이블이 자동 생성됩니다:

project.analytics_<property_id>.events_YYYYMMDD

각 row가 한 이벤트(page_view, purchase, click 등). 가장 자주 쓰는 컬럼들:

가장 헷갈리는 게 event_params예요. 배열이라 SQL에서 펼쳐야(UNNEST) 합니다. “4월 구매 이벤트의 utm_source 분포는?” 같은 단순 질문도 결국 UNNEST 한 번 거쳐야 답이 나와요.

2단계 — 광고비 + 매출 일별 join

광고비는 별도로 적재해야 합니다. Google Ads는 BigQuery Data Transfer Service를 켜면 자동, 다른 채널(Meta/TikTok/Naver 등)은 매일 한 번 API로 가져와 ads_spend(date, channel, campaign_id, campaign_name, spend) 테이블에 적재.

이제 GA4 raw event에서 일별 매출을 채널·캠페인 단위로 집계해 광고비와 join하면 됩니다. SQL의 큰 흐름은 세 단계예요.

-- 단계 1: GA4 purchase 이벤트에서 utm + 매출 추출
WITH purchases AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    ecommerce.transaction_id,
    ecommerce.purchase_revenue AS revenue,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='source')   AS utm_source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='medium')   AS utm_medium,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='campaign') AS utm_campaign
  FROM `project.analytics_<id>.events_*`
  WHERE event_name='purchase'
    AND _TABLE_SUFFIX BETWEEN '20260401' AND '20260430'
)
-- 단계 2: utm을 채널로 매핑 (CASE WHEN으로 통일)
-- 단계 3: ads_spend와 (date, channel, campaign_name) 키로 LEFT JOIN
-- 결과: date | channel | campaign_name | spend | revenue | orders | roas

이 한 쿼리로 “우리만의 ROAS”가 나옵니다. 정의는 마케터가 정한 last-touch utm 매칭이고, 매출은 GA4가 잡은 transaction_id 기반. 플랫폼별 임의 정의가 아니라 우리 데이터팀이 합의한 정의가 들어간 숫자예요.

3단계 — 베이지안 신뢰구간 한 칸 추가

전 글에서 다룬 Beta-Binomial을 BigQuery에서도 직접 계산할 수 있어요. 단순한 푸아송 근사(주문수가 많을 때 정규분포로 근사)로 ROAS 신뢰구간을 한 줄에:

• $n_{\text{orders}}$ = 주문 수 (푸아송으로 근사)
• $\overline{\text{AOV}}$ = 평균 객단가 (revenue / orders)

결과 예시:

이 표 한 장이 마케터의 진짜 무기가 됩니다. ROAS 평균뿐 아니라 “얼마나 신뢰할 만한 추정인가”까지 한 줄에. 회의에서 “틱톡 ROAS 3”이 아니라 “틱톡 ROAS 3 (신뢰구간 1.85~4.15, 데이터 부족, 의사결정 보류)“가 나오면 토론의 차원이 달라져요.

4단계 — 일별·세그먼트별 뷰 자동화

매일 새벽 자동 실행되는 BigQuery scheduled query로 결과 테이블을 만들어두면, 마케터가 SQL 모르고도 쓸 수 있는 대시보드 테이블이 됩니다. 컬럼 구성은 다음 정도가 베이스:

이 테이블을 Looker Studio·Tableau·내부 BI로 연결하면, 마케터는 그저 필터를 바꿔가며 자기 채널·자기 캠페인 ROAS를 신뢰구간과 함께 봅니다. 이 한 테이블이 “우리 회사의 진실”이 되고, 회의에서 더 이상 “플랫폼 ROAS 5인데 GA4는 3.8” 같은 충돌이 사라져요.

잘 빠지는 함정 — 직접 만들 때만 발생하는 것들

1) Cross-day attribution 손실

GA4의 default attribution은 “last-click” 모델인데, 사용자가 4월 30일에 클릭하고 5월 1일에 결제하면 매출이 5월에 잡힙니다. 광고비는 4월 30일 spent. 일별 ROAS 계산 시 이 mismatch가 노이즈가 돼요. 해결: 월간 합계로 보거나, 7일 lag join.

2) Cross-device 이슈

같은 사람이 모바일에서 클릭하고 데스크탑에서 결제하면 user_pseudo_id가 달라져 별개 사용자로 잡힙니다. GA4 user_id(로그인 ID) 매칭을 켜뒀으면 일부 회복.

3) UTM 스펠링 차이

utm_source=facebook vs Facebook vs fb 같은 케이스. 채널 매핑 CASE WHEN을 오랜 시간 정비해야 해요. 광고팀과 “UTM 표기 가이드” 한 장 합의해두는 게 결국 가장 큰 절약.

4) 환불·캐시백 빠뜨리기

GA4 purchase 이벤트는 결제 시점이고, 환불·캐시백·할인 쿠폰은 별개 이벤트. 진짜 매출은 “purchase - refund”를 일별로 차감해야 정확.

운영 팁 — 마케터 데이터 팀이 자주 묻는 것

1) BigQuery 비용

GA4 export는 무료고, 쿼리 비용은 데이터량 기준. 한 달치 events 테이블 풀스캔 대신 _TABLE_SUFFIX BETWEEN으로 날짜 필터를 거는 게 가장 큰 절약. scheduled query 결과 테이블만 BI에 연결하고, raw events는 분석 시에만 쿼리.

2) 1차 모델은 단순하게

처음부터 “멀티터치 + 베이지안 + incrementality 보정” 다 넣으려고 하면 6개월 걸려요. 1차는 last-click + 신뢰구간 한 가지만. 2~3개월 데이터 쌓이고 마케터가 익숙해지면 그 다음 정의 추가.

3) 정의를 한 페이지 wiki로

“우리 회사의 ROAS 정의: utm_medium = (cpc|sa) 한정, 결제 후 7일 환불 차감, 채널 매칭은 ___표 따름” — 이 한 페이지가 가장 큰 운영 자산이에요. 분기마다 갱신.

4) 실험 분석에도 같은 파이프라인

A/B 테스트나 geo-lift 결과도 BigQuery raw event에서 직접 분석하면, 플랫폼 도구별 결과 차이로 시간 낭비할 일이 없어요. 한 분석가가 만든 SQL을 모두가 재사용.

마치며

플랫폼 대시보드 ROAS는 “플랫폼이 자기 광고에 좋게 보이도록 정의한 숫자”예요. 그 정의를 받아들이는 한, 마케터의 분석 능력은 항상 플랫폼에 종속됩니다. GA4 raw event + BigQuery + SQL 세 가지로 자체 파이프라인을 만들면, 정의의 주도권이 마케터·데이터팀으로 돌아옵니다. 첫 SQL 200줄을 짜는 일주일이 그다음 1년의 모든 분석을 좌우해요.

이 글로 “마케터를 위한 데이터 시리즈” 10편이 마무리됩니다. 베이지안 어트리뷰션부터 GA4 자체 파이프라인까지, 광고·실험·CRM·LLM·BI를 관통하는 마케터의 데이터 무기들을 같이 정리해봤어요.

참고

• GA4 BigQuery Export 공식 문서 — 익스포트 활성화·스키마
• BigQuery — events_* 테이블 스키마 — 모든 컬럼 정의
• GA4 dimensions and metrics — UNNEST 패턴 — event_params 펼치기
• Looker Studio + BigQuery 연결 — BI 시각화
• The Data Warehouse Toolkit — Kimball — 마케팅 데이터 모델링 표준서