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

같은 raw event로 만든 두 ROAS가 다르다면 데이터 자체를 탓하기 전에 JOIN grain·window·timezone·환율·dedup 계약이 SQL에 어떻게 들어갔는지부터 비교해야 합니다.

광고 데이터에서는 한 사용자가 여러 광고·세션·디바이스를 거쳐 구매할 수 있습니다. 이 경로를 표 한 장으로 줄일 때 SQL의 집계 규칙이 결과를 결정합니다.

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

이 다섯 가지가 동시에 들어오면 작은 grain 오류도 비용과 매출을 크게 왜곡합니다. 그래서 결과값뿐 아니라 어떤 데이터 계약과 품질 검사를 통과했는지를 같이 봐야 합니다.

예시는 BigQuery 기준이지만 grain·window·timezone·dedup 원칙은 다른 warehouse에도 적용됩니다.

함정 1 — Fan-out 중복 조인

가장 흔하고 큰 오류를 만드는 패턴입니다. 서로 다른 grain을 단순 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행에 붙이는 one-to-many fan-out을 만듭니다. SUM(s.spend)가 50배가 되어 ROAS가 1/50로 찍히죠. 반대쪽 grain이 더 잘게 쪼개지면 매출이 부풀 수도 있습니다.

해결은 두 갈래입니다.

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

결과가 그럴듯해 보여도 join 전후의 row 수·distinct key·금액 합계를 비교하면 fan-out을 기계적으로 잡을 수 있습니다.

함정 2 — Attribution window 누락

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

흔한 잘못된 패턴은 click과 purchase를 user_id로 묶을 때 click.date = purchase.date 같은 등치 조건을 같이 넣는 것입니다. 이렇게 짜면 클릭한 당일에 구매한 케이스만 매칭됩니다. purchase_time BETWEEN click_time AND TIMESTAMP_ADD(click_time, INTERVAL N DAY)처럼 비교 대상까지 명시해야 합니다.

attribution window는 매체 이름만으로 고정할 수 없습니다. Google Ads도 conversion action별 설정이 다르고, Meta·TikTok은 ad set 또는 ad group의 선택값과 보고 요청 파라미터가 결과에 영향을 줍니다. 그래서 수집 시점에 실제 설정을 별도 테이블로 보존해야 합니다.

모든 채널을 임의로 7일이나 30일로 덮으면 특정 매체의 전환이 누락되거나 더 많이 귀속됩니다. 매체 원본 보고서와 조직 공통 비교 보고서는 서로 다른 계약으로 저장하는 편이 안전합니다.

해결 패턴:

• 채널·설정 단위별 window를 유효 기간이 있는 설정 테이블로 분리합니다.
• 매칭 조건은 purchase_time BETWEEN click_time AND TIMESTAMP_ADD(click_time, INTERVAL window DAY)로 쓰고, 한 전환에 여러 touchpoint가 들어오면 last-touch·first-touch·가중치 중 합의한 규칙으로 한 번 더 순위를 매깁니다.
• view-through는 별도 컬럼으로 분리해 두고, 보고서에서 켜고 끄게 합니다.

함정 3 — Timezone 미스매치

매체의 일별 보고서는 광고 계정 시간대로 집계될 수 있고, GA4 BigQuery의 event_timestamp는 UTC 기준 마이크로초인 반면 event_date는 속성의 보고 시간대를 따릅니다. 사내 주문 DB가 KST라면 같은 “5월 16일”도 서로 다른 24시간을 가리킬 수 있습니다.

자정 부근 전환이 빠지거나 요일 경계에서 광고비와 매출의 움직임이 어긋나면 원본 일자와 시간대부터 비교합니다.

이벤트 timestamp는 UTC로 보존하고 보고 레이어에서 합의한 시간대로 날짜를 만듭니다. GA4 마이크로초 값은 BigQuery에서 DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')처럼 변환합니다. 반면 매체가 이미 광고 계정 시간대로 집계한 일별 행은 임의로 UTC 날짜로 바꾸지 말고 source_date와 source_timezone을 함께 보존한 뒤 주문 집계와 경계를 맞춥니다.

정책은 “모든 값을 억지로 UTC 날짜로 바꾼다”가 아니라 원본 timestamp·원본 일자·원본 시간대를 잃지 않고, 보고 경계만 명시적으로 변환한다입니다. dbt 같은 변환 도구를 쓴다면 변환 매크로와 DST 회귀 테스트를 같이 둡니다.

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

가상 예시로 어제 ROAS를 오늘 집계했을 때 2.3이었는데 다음 주 재집계에서 3.1이 됐다고 해봅시다. 원본이 변조된 것이 아니라 conversion lag로 늦게 귀속된 전환이 추가됐을 수 있습니다.

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

시작점으로 검토할 운영 정책 예시 3가지입니다. T+3이나 D+7은 보편 표준이 아니라, 실제 lag 분포로 조정해야 합니다.

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

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

보고서에는 provisional·frozen 상태와 기준일을 표시해, 같은 기간의 숫자가 왜 갱신됐는지 설명할 수 있게 합니다.

함정 5 — 환율과 통화 단위

다국가 캠페인을 운영하면 광고비와 매출에 USD·KRW·EUR 같은 통화가 섞일 수 있습니다. 이를 한 컬럼에 그대로 합치면 서로 다른 통화 1단위를 같은 값으로 더해 ROAS가 자릿수부터 틀어집니다.

흔한 잘못된 패턴:

• 환율을 적용하지 않은 채 서로 다른 통화를 SUM하기
• 환율을 적용할 때 보고서 출력 시점 환율로 일괄 적용하기
• 환율을 적용할 때 transaction 시점이 아니라 batch가 도는 시점으로 적용하기

권장 패턴:

• raw layer에 항상 source currency 컬럼을 같이 적재. spend_amount + spend_currency.
• 환율 테이블은 일별 + 통화별로 별도. fx_rate(date, from_currency, to_currency, rate).
• fx_rate를 “원본 통화 1단위당 KRW”로 정의한 뒤 보고 layer에서 spend_amount * krw_per_source_unit로 일자별 환율을 매칭.

의사결정용 당시 환율과 회계 정산용 기준일 환율은 별도 열로 두고 어떤 환율 표를 썼는지 남깁니다.

통화 키는 USD·EUR·JPY·KRW 같은 ISO 4217 코드로 통일해 환율 join의 누락을 막습니다.

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

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

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

GA4 UI에서는 channel group 규칙으로 일부 통합되지만 BigQuery export에서는 수집된 값을 직접 다뤄야 합니다. 채널 정규화 lookup이 없으면 같은 Meta가 여러 행으로 쪼개지고 보고서마다 서로 다른 합계를 선택하기 쉽습니다.

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

권장 정책:

• 채널 정규화 테이블을 1번 소스로 만들고 SQL은 항상 LEFT JOIN으로 정규화 후 사용.
• utm 표기 규칙(소문자, 하이픈, 캠페인 코드 prefix)을 마케팅팀과 합의해 거버넌스 문서로.
• gclid·fbclid와 UTM이 함께 있을 때 어느 소스를 우선할지 제품·연동 방식에 맞춰 명시.
• 정규화 실패 케이스(매핑에 없는 새 utm)는 매주 모니터링 쿼리로 점검.

미매핑 utm_source 상위 값을 정기 알림으로 보내면 새 매체와 표기 흔들림을 정규화 테이블에 반영할 수 있습니다.

함정 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. 인증된 내부 사용자 ID 또는 적법하게 처리한 email_hash
2. 동의 상태와 플랫폼 정책을 만족하는 디바이스 식별자
3. 목적·보관 기간이 명시된 first-party cookie_id

특히 FIRST_VALUE(email_hash)를 전체 기간에 적용해 미래의 로그인 정보를 과거 익명 이벤트에 소급 연결하는 쿼리는 피합니다. 분석 목적과 동의 근거 없이 과거 행동을 합칠 수 있고, 한 번의 잘못된 연결을 전체 이력에 전파하기 때문입니다. 대신 identity_link(subject_id, source_id, valid_from, valid_to, consent_scope, confidence)처럼 유효 기간이 있는 연결 테이블을 만들고, event timestamp가 그 기간 안에 있을 때만 join합니다. 삭제·동의 철회 요청이 들어오면 연결과 파생 테이블에도 반영되는지 테스트합니다.

ID 연결 계약은 cdp-id-graph와 server-side-tagging-capi에서 더 자세히 이어집니다.

이 API로 실제 무엇을 만들 수 있는지

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

광고 API·GA4 export·주문 DB 위에 다음 도구를 만들 수 있습니다.

• JOIN 전후 row 수·distinct key·금액 합계를 비교하는 fan-out 검사기
• source별 window·timezone·currency 계약이 비어 있으면 배포를 막는 schema test
• T+3·D+7·D+30 스냅샷을 나란히 보여주는 conversion lag 관제판
• 미매핑 UTM과 새 metric ID를 자동 격리하는 data quality inbox
• 주문 수·매출·광고비를 플랫폼 보고서와 대조하고 차이 원인을 분류하는 reconciliation 표

BI팀과 마케팅팀의 ROAS가 안 맞을 때 “데이터가 틀린 것 같아요”보다 “원본·정의·변환 중 어디서 달라졌을까요”로 시작하면 원인을 더 빨리 좁힐 수 있습니다. 원본 수집도, 설정도, SQL도 모두 오류 후보이므로 각 경계의 합계와 계약을 증거로 확인합니다.

참고

• GA4 BigQuery export schema 공식 문서
• dbt 공식 — Jinja functions
• GA4 BigQuery export schema
• Google Ads — conversion windows
• TikTok Ads Manager — attribution windows
• Meta Marketing API — attribution windows
• 사내 글: GA4 + BigQuery로 ROAS 파이프라인 직접 만들기
• 사내 글: 광고 측정 데이터 흐름 — impression부터 BI까지
• 사내 글: 베이지안 어트리뷰션 — 적은 데이터로 더 안정적인 이유
• 사내 글: CDP 시대의 ID 그래프