광고 SQL·BI 안티패턴 7가지 — ROAS 보고서를 거짓말로 만드는 SQL 함정

“Meta는 ROAS 5.2라는데, GA4는 3.8이고, BI팀이 BigQuery로 다시 뽑은 건 9.6이에요.” 어느 회의에서나 흔한 풍경입니다. 셋 다 다른 데이터를 봤다면 차이가 나는 게 당연한데, 진짜 무서운 건 같은 raw event를 두 사람이 SQL로 집계했는데 ROAS가 30% 차이 나는 경우입니다. 거의 모든 경우 원인은 데이터가 아니라 SQL 한 줄이에요. 이 글은 광고 데이터를 SQL로 집계할 때 마케터·BI팀이 반복적으로 빠지는 7가지 안티패턴을 정리합니다. 각각 어떻게 ROAS를 왜곡하는지, 어떻게 잡는지까지.

왜 SQL 한 줄이 ROAS 30%를 바꾸나

광고 데이터는 다른 도메인 데이터와 결이 다릅니다. 한 유저가 같은 캠페인을 4번 보고, 그 사이에 3번 사이트를 들렀다가, 마지막에 다른 디바이스로 구매하는 일이 일상입니다. 이걸 표 한 장으로 만들 때 SQL은 마법을 부려야 합니다.

• impression·click·event 테이블이 따로
• attribution window가 채널마다 다름
• 같은 user가 cookie·gaid·email_hash로 흩어져 있음
• 광고비는 매체 통화, 매출은 자사 통화
• 보고 시점마다 conversion lag로 어제 수치가 바뀜

이 다섯 가지가 동시에 들어오면 SQL은 굉장히 미묘해집니다. 운이 나쁘면 한 줄 잘못 짜서 ROAS가 두 배로 나오기도 하고, 운이 좋으면 한 분기 동안 모르고 지나가기도 합니다. 그래서 광고 SQL은 “맞다/틀리다”가 아니라 “어떤 안티패턴을 피했나”의 게임이에요.

이 글은 BigQuery 기준으로 예시를 들지만 Snowflake·Redshift·Postgres 어디서나 동일한 함정이에요. 한 번에 다 외울 필요는 없고, BI팀과 마케팅팀의 숫자가 안 맞을 때 위에서부터 체크리스트로 쓰면 됩니다.

함정 1 — Fan-out 중복 조인

가장 흔하고, 가장 큰 폭의 오류를 만드는 패턴입니다. 광고비 테이블과 전환 테이블을 단순 JOIN으로 붙이면 한 행이 N행으로 부풀어 매출이 N배가 됩니다.

상황을 그려볼게요. 광고비 테이블 ad_spend는 캠페인·날짜 단위로 한 행씩 있습니다. 전환 테이블 conversions는 한 user의 한 구매가 한 행입니다. 한 캠페인의 하루에 구매가 50건 있다면 단순 JOIN의 결과는 어떻게 될까요.

-- ❌ 안티패턴: spend가 50번 복제되어 매출이 정상이지만 spend가 50배가 된다
SELECT
  s.campaign_id,
  s.date,
  SUM(s.spend) AS spend,        -- 50배 부풀려진다
  SUM(c.revenue) AS revenue
FROM ad_spend s
JOIN conversions c
  ON s.campaign_id = c.campaign_id
 AND s.date = DATE(c.event_time)
GROUP BY 1, 2

이 쿼리는 ad_spend의 한 행을 conversions의 50행과 카르테시안 조인합니다. SUM(s.spend)가 50배가 되어 ROAS가 1/50로 찍히죠. 운 좋게 매출 쪽이 부풀려지는 케이스라면 반대로 ROAS가 N배가 됩니다.

해결은 두 갈래입니다.

• 먼저 dedup·집계 → 그 다음 JOIN. 전환 테이블을 먼저 GROUP BY campaign_id, date로 합쳐서 한 행으로 만든 뒤 광고비와 1:1로 붙입니다.
• full-outer + COALESCE 패턴. 전환이 없는 캠페인도 살려야 하니 INNER가 아니라 FULL OUTER로 붙이고 NULL은 0으로.

이 안티패턴이 무서운 이유는 결과가 그럴듯하게 보인다는 점이에요. ROAS 0.04 같은 명백히 이상한 숫자라면 다들 알아차리지만, 1.8쯤 나오면 “광고가 좀 약한가” 하고 넘어갑니다. 한 분기를 그렇게 보내고 나서야 누가 발견하는 일이 흔합니다.

함정 2 — Attribution window 누락

광고는 오늘 클릭하고 내일 구매합니다. 어떤 카테고리는 일주일 뒤에 구매하기도 하죠. SQL을 짤 때 click과 purchase를 같은 날짜로 묶으면 이 lag가 다 빠집니다.

흔한 잘못된 패턴은 click과 purchase를 user_id로 묶을 때 click.date = purchase.date 같은 등치 조건을 같이 넣는 것입니다. 이렇게 짜면 클릭한 당일에 구매한 케이스만 매칭되고 D+1 이후는 전부 attribution에서 빠져요. 단순 등치가 아니라 BETWEEN click.timestamp AND TIMESTAMP_ADD(click.timestamp, INTERVAL N DAY) 같은 윈도우 매칭이 표준입니다.

광고 채널별로 표준이 다른 attribution window가 있습니다.

이 window를 채널마다 다르게 적용해야 같은 보고서가 됩니다. 한 SQL에 모두 7일로 통일하면 Google Ads의 D+10 전환이 다 빠지고, 모두 30일로 통일하면 Meta·TikTok에서 view-through가 7배쯤 부풀려집니다.

해결 패턴:

• 채널별 window를 설정 테이블로 분리합니다. channel_attribution_config(channel, click_window_days, view_window_days) 같은 lookup.
• 매칭 조건은 BETWEEN click_time AND TIMESTAMP_ADD(click_time, INTERVAL window DAY)로 씁니다.
• view-through는 별도 컬럼으로 분리해 두고, 보고서에서 켜고 끄게 합니다.

함정 3 — Timezone 미스매치

Meta API는 광고주 시간대를 기본으로 주지만 일부 endpoint는 UTC, GA4 BigQuery export는 이벤트 시점 UTC, 사내 주문 DB는 KST. 이 네 개를 같은 “5월 16일”로 집계하면 광고비와 매출이 각각 다른 24시간을 보고 있게 됩니다.

흔한 증상.

• 매일 9시에 도는 잡이 어제 ROAS를 뽑는데, KST 자정 직후 1시간의 전환이 사라진다
• 월요일 ROAS가 항상 낮고 일요일 ROAS가 높다 (광고비는 토일, 매출은 일월 카운팅)
• 광고 매체와 BI의 ROAS 차이가 정확히 “하루 정도의 비율”로 일정하다

해결은 단순합니다. 모든 timestamp를 한 timezone(보통 KST)으로 정규화한 다음 그 위에서 날짜를 만들어요. BigQuery라면 DATE(TIMESTAMP(event_timestamp), 'Asia/Seoul') 한 줄, Snowflake라면 CONVERT_TIMEZONE('UTC', 'Asia/Seoul', event_ts)::DATE처럼 표준 함수 한 줄로 처리합니다. 핵심은 raw layer가 항상 UTC라는 약속을 깨지 않는 거예요.

매체 API는 time_increment=1 옵션을 쓸 때 광고주 timezone으로 쪼개주지만, 통화·환율 변환과 섞이면 다시 깨집니다. 정책은 항상 같은 것이 좋아요. raw layer는 UTC로 통일해서 저장, 보고 layer에서 KST로 변환. dbt 같은 변환 도구를 쓴다면 timezone 변환 매크로를 만들어두면 안전합니다.

함정 4 — Conversion lag와 진행 중 데이터

어제 ROAS를 오늘 봤더니 2.3이었는데, 다음 주에 다시 보니 3.1이 됐습니다. 데이터가 바뀐 게 아니라 conversion lag로 늦게 잡힌 전환이 추가된 거예요. 마케터에게는 굉장히 혼란스러운 경험입니다.

이 함정은 SQL 자체보다 운영 정책의 문제입니다. SQL은 “지금 시점의 최선의 추정치”를 매번 다시 계산하니까요. 정책 없이 매일 ROAS를 뽑으면 어제 본 숫자와 오늘 본 숫자가 다를 수밖에 없습니다.

표준 운영 정책 3가지.

• T+3 freeze. 발생 후 3일 지난 데이터만 KPI로 확정. 어제·그제·오늘은 “진행 중”으로 표시.
• D+7 ROAS와 D+30 ROAS를 분리해 본다. 단기 의사결정은 D+7, 분기 회고는 D+30.
• 스냅샷 테이블. 매일 아침 같은 시점의 ROAS를 별도 테이블에 적재해서 시점별 비교 가능하게.

dbt를 쓰면 snapshots나 incremental + invalidation 패턴으로 이걸 처리합니다. SQL만 짠다면 적어도 report_run_date 컬럼을 항상 같이 박아두는 게 안전해요.

ROAS가 시점에 따라 바뀐다는 점을 회의에서 명확히 공유하지 않으면 마케터가 매일 다른 숫자를 보고 부담을 느낍니다. “월요일에 본 지난주 ROAS는 진행 중이고, 이번주 금요일 freeze 이후가 확정”이라는 식의 룰을 팀에 박아둬야 합니다.

함정 5 — 환율과 통화 단위

다국가 캠페인을 운영하면 Meta 광고비는 USD, GA 매출은 KRW, TikTok 광고비는 USD, 일부 매체는 EUR이 섞입니다. 이걸 단순히 한 컬럼으로 합치면 1달러와 1원이 같은 무게로 더해집니다. 그러면 환율 1300배짜리 ROAS 폭탄이 만들어져요.

흔한 잘못된 패턴:

• 환율을 적용 안 한 채 SUM하기 (한 통화로 보면 ROAS가 1300배)
• 환율을 적용할 때 보고서 출력 시점 환율로 일괄 적용하기
• 환율을 적용할 때 transaction 시점이 아니라 batch가 도는 시점으로 적용하기

권장 패턴:

• raw layer에 항상 source currency 컬럼을 같이 적재. spend_amount + spend_currency.
• 환율 테이블은 일별 + 통화별로 별도. fx_rate(date, from_currency, to_currency, rate).
• 보고 layer에서 spend_amount * fx_rate 형태로 일자별 환율을 매칭해서 KRW 변환.

이렇게 짜놓으면 분기 회고에 “당시 환율 기준”과 “기준일 환율 기준” 두 가지 ROAS를 다 볼 수 있어요. 광고비는 당시 환율로 본 값이 의사결정에 더 맞고, 회계 정산은 기준일 환율로 보는 게 맞아서 두 뷰 모두 의미가 있습니다.

함정 6 — 채널 매핑과 UTM 일관성

같은 채널이 raw data에서 네 가지 이름으로 나타납니다.

• utm_source=facebook
• utm_source=Facebook
• utm_source=fb
• utm_source=meta

GA4 UI에서는 자동으로 일부 통합되지만 BigQuery export는 raw 그대로 들어옵니다. 채널 정규화 lookup이 없으면 같은 Meta가 4개 행으로 쪼개지고, 채널별 ROAS는 다 1/4 수준으로 찍힙니다.

문제는 여기서 끝나지 않아요. 더 깊은 함정은 utm을 잘못 박은 캠페인, 마케터가 자율적으로 만든 utm 표기, 매체가 자동으로 추가하는 파라미터(gclid, fbclid)와의 우선순위입니다.

권장 정책:

• 채널 정규화 테이블을 1번 소스로 만들고 SQL은 항상 LEFT JOIN으로 정규화 후 사용.
• utm 표기 규칙(소문자, 하이픈, 캠페인 코드 prefix)을 마케팅팀과 합의해 거버넌스 문서로.
• gclid·fbclid가 있을 때 utm보다 우선하는 fallback 룰 정의.
• 정규화 실패 케이스(매핑에 없는 새 utm)는 매주 모니터링 쿼리로 점검.

이 함정은 한 번 정리하면 한 분기는 평화롭다가, 마케터가 새 매체를 추가하면 다시 깨집니다. 거버넌스가 SQL보다 더 중요해요. 매주 도는 모니터링 쿼리 하나만 두면 안전합니다 — 채널 매핑 테이블에 없는 신규 utm_source TOP 10을 매주 슬랙으로 알려주는 잡 하나. 새 매체가 들어오면 자동으로 잡혀요.

함정 7 — Dedup과 동일 user의 다중 식별자

한 user가 brand search로 한 번 들어왔다가, 7일 뒤 retargeting으로 다시 와서 구매했다고 칩시다. raw data에는 cookie_id 두 개, email_hash 한 개, gaid 한 개로 나타날 수 있어요. SQL에서 user 단위 집계를 할 때 이 식별자들을 어떻게 묶느냐가 결과를 완전히 바꿉니다.

흔한 잘못된 패턴:

• COUNT(DISTINCT cookie_id)로 unique user를 셈 → 한 user가 N명으로 카운팅
• email_hash가 NULL인 비로그인 행을 user 집계에서 누락 → 신규 user가 사라짐
• attribution을 user_id 매칭으로만 처리 → 로그인 전 클릭이 attribution에서 빠짐

해결 패턴은 ID resolution입니다. 사내에 CDP가 있다면 unified user_id를 쓰면 되고, 없다면 다음 우선순위로 식별자를 통합합니다.

1. 로그인된 email_hash (가장 강함)
2. gaid / idfv (모바일 디바이스)
3. cookie_id (가장 약함, 짧은 수명)

window function을 써서 한 cookie_id가 어느 시점에 어느 email_hash로 매칭됐는지 추적합니다. 그 매칭이 잡힌 순간부터 그 cookie_id의 과거 이벤트도 같은 user로 묶어요. SQL로는 FIRST_VALUE(email_hash) OVER (PARTITION BY cookie_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 같은 윈도우 함수 한 줄로 unified_email_hash를 만든 뒤, 그 매핑 테이블을 raw events에 LEFT JOIN으로 붙이는 식이 표준입니다.

이 함정은 SQL 한 줄이 아니라 데이터 모델 자체의 문제라 가장 어렵습니다. 사내 ID 거버넌스가 아직 약하다면 cdp-id-graph와 server-side-tagging-capi를 먼저 보시면 도움이 됩니다.

마치며 — 안티패턴 체크리스트

7가지를 한 번에 다 해결하려고 하지 마세요. 1·2·3은 SQL 표준 정착으로, 4는 운영 정책으로, 5·6은 데이터 거버넌스로, 7은 데이터 모델로 풀어야 합니다. 각각 다른 사람이 책임자라는 뜻이에요.

BI팀과 마케팅팀의 ROAS가 안 맞을 때, 회의에서 “데이터가 틀린 것 같아요”보다 “이 7가지 함정 중 어디서 깨졌을까요”로 시작하면 훨씬 건강한 대화가 됩니다. 데이터는 거의 안 틀려요. 거의 항상 SQL 한 줄이 틀려있습니다.

참고

• GA4 BigQuery export schema 공식 문서
• dbt 공식 — surrogate_key·dedup 매크로
• Meta Marketing API — attribution windows
• Modern Data Stack — attribution modeling 사례 (Fivetran 블로그)
• 사내 글: GA4 + BigQuery로 ROAS 파이프라인 직접 만들기
• 사내 글: 광고 측정 데이터 흐름 — impression부터 BI까지
• 사내 글: 베이지안 어트리뷰션 — 적은 데이터로 더 안정적인 이유
• 사내 글: CDP 시대의 ID 그래프