ClickHouse 기반 광고 성과 분석 큐브 활용

입찰 텔레메트리, ML 랭킹 신호, 비즈니스 KPI를 하나의 넓은 이벤트 테이블에 담고, 임의 차원으로 거의 실시간에 집계하는 방법

디지털 광고 플랫폼을 운영해 보신 분이라면 익숙한 고민이 있으실 겁니다. RTB 경매 로그, 모델이 뱉어낸 예측 점수, 그리고 재무팀이 요구하는 매출·소진액 리포트가 사실은 같은 이벤트 스트림에서 나오는데, 저장·집계 방식은 제각각으로 흩어지곤 합니다. 노출·클릭·전환은 배치로 롤업하고, distinct 사용자 수는 별도 파이프라인에서 세고, 캠페인 메타데이터는 매번 JOIN으로 붙이는 식이지요.

이 글에서는 이 세 가지 관심사를 하나의 append-only 와이드 테이블로 통합하고, ClickHouse의 집계 프리미티브만으로 "광고 성과 분석 큐브"를 만드는 과정을 다룹니다. 합성 데이터 1,000만 행을 기준으로, 스키마 설계부터 실시간 사전 집계까지 실제 실행 가능한 SQL과 함께 살펴보겠습니다.

왜 ClickHouse인가

광고 플랫폼의 워크로드를 한 문장으로 요약하면 이렇습니다.

고빈도 append 스트림 + 임의 차원 OLAP 집계 + 근사 distinct + 반정형 enrichment

이 조합은 사실 ClickHouse가 설계 단계부터 정조준한 영역입니다. 조금 더 풀어 보면, 광고 플랫폼은 보통 하나의 이벤트 흐름에서 세 가지 일을 동시에 처리합니다.

핵심은 이 셋을 위해 각각 다른 테이블을 만들 필요가 없다는 점입니다. 넓은 이벤트 row 하나에 전부 담아 두고, 분석가는 GROUP BY만 바꿔 가며 원하는 차원으로 큐브를 재슬라이스합니다. 새 인덱스도, 새 파이프라인도 필요 없습니다.

데이터셋 개요

이 실습은 특정 회사에 종속되지 않도록 일반적인 광고 플랫폼(애드 네트워크 / 리테일 미디어 / DSP·SSP / 인하우스 광고 서버)을 모델링한 합성 데이터를 사용합니다.

퍼널 분포는 실제 광고 트래픽과 비슷하게 잡았습니다. 노출 85%, 뷰어블 8%, 클릭 3.5%, 인터랙션 1.5%, 직접전환 1.2%, 간접전환 0.8% — 이렇게 하면 현실적인 CTR ≈ 4%, CVR ≈ 57% 수준이 나옵니다.

금액을 부동소수점이 아닌 정수 마이크로 단위로 저장한 점을 짚어 두겠습니다. 광고비처럼 누적 합산이 잦은 값은 float로 두면 미세한 드리프트가 쌓입니다. Int64 마이크로 단위로 저장하고, 리포팅 시점에만 SCALE로 나눠 주는 방식이 안전합니다. 이 SCALE 값과 수수료율은 쿼리에 하드코딩하지 않고 별도 파라미터 테이블(cube_params)에 데이터로 보관합니다.

01 — 스키마 설계

ad_analytics 데이터베이스 아래에 네 개의 객체를 만듭니다.

가장 중요한 것은 ad_events 테이블의 정렬 키와 파티션 설계입니다.

설계 의도는 이렇습니다.

• ORDER BY (channelType, campaignId, eventType, eventTs) — 가장 빈번하게 필터·그룹으로 쓰이는 컬럼을 앞에 배치해 primary index가 효과적으로 동작하도록 합니다.
• PARTITION BY toYYYYMM(eventTs) — 월 단위 파티션으로 날짜 범위 쿼리에서 파티션 프루닝이 걸리게 합니다.
• LowCardinality(String) — eventType, channelType처럼 값 종류가 적은 enum성 컬럼에 적용해 저장·스캔 비용을 낮춥니다.

02 — 합성 데이터 생성

데이터는 numbers(10000000)에서 생성합니다. 모든 필드는 cityHash64(number, 'salt')로 number에서 결정적으로 해시됩니다. 덕분에 같은 스크립트를 다시 돌려도 동일한 데이터가 재현됩니다.

퍼널 단계는 0–999 버킷에서 확률에 따라 선택하고, winPrice는 CLICK 행에만, conversionValue는 전환 행에만 채웁니다. 이렇게 해야 이후 sumIf 기반 지표가 현실적으로 동작합니다.

numbers(N)은 N개를 생성하고 종료하지만, system.numbers는 무한 스트림입니다. 데이터 생성에는 반드시 numbers(N)을 쓰셔야 합니다.

03 — 큐브의 심장: 조건부 집계

이 실습의 핵심입니다. 모든 퍼널 지표를 동일한 테이블에 대한 단일 패스 조건부 집계로 계산합니다. 예를 들어 CTR은 이렇게 나옵니다.

countIf(eventType = 'CLICK') * 100.0
  / nullIf(countIf(eventType = 'IMPRESSION'), 0)   AS ctr_pct

countIf, sumIf, avgIf를 조합하면 노출·클릭·전환 카운트, 소진액·매출·Profit, 그리고 CTR/CVR/CTCVR/CPC/CPM/ROAS를 한 번의 스캔으로 전부 뽑아낼 수 있습니다. nullIf(..., 0)으로 분모가 0일 때의 division-by-zero를 자연스럽게 NULL로 처리하는 패턴도 함께 기억해 두시면 좋습니다.

여기서 진짜 강점이 드러납니다. 동일한 큐브를 채널 × 플랫폼으로 보고 싶으면 GROUP BY channelType, platform, 캠페인 목표별로 보고 싶으면 GROUP BY campaignGoal, 일별로 보고 싶으면 GROUP BY toDate(eventTs) — 새 테이블도 새 인덱스도 없이 GROUP BY만 바꾸면 됩니다.

스케일 규칙 한 가지

금액을 마이크로 단위로 저장했기 때문에, 리포팅 시 SCALE을 어떻게 처리할지 규칙이 필요합니다.

ROAS는 매출/소진액이므로 스케일이 위아래에서 지워집니다. 반면 CPC(클릭당 비용)처럼 실제 통화 단위로 보여줘야 하는 값은 SCALE로 나눠 줘야 합니다. SCALE과 수수료율은 cube_params에서 스칼라 서브쿼리로 주입하므로, 계약 조건이 바뀌어도 쿼리 SQL은 손대지 않습니다.

04 — 근사 고유값 카운트

userId, deviceId, requestId는 distinct 값이 수백만 개입니다. 이걸 매번 정확하게 세면 메모리와 시간이 크게 들지요. ClickHouse는 용도에 맞는 세 가지 선택지를 제공합니다.

uniq는 HyperLogLog 기반이라 메모리를 거의 쓰지 않으면서 1% 미만의 오차로 동작합니다. 여기에 uniqIf를 쓰면 부분집합에 대한 distinct — 예를 들어 "실제 노출이 발생한 캠페인 수"만 골라 셀 수 있고, DAU·라인아이템당 도달·사용자당 노출 수(프리퀀시 캡 분포)까지 같은 방식으로 계산됩니다.

원칙: 탐색과 대시보드에서는 근사 distinct를 쓰시고, 숫자가 곧바로 돈으로 이어지는 지점(과금·정산)에만 uniqExact를 예약해 두세요.

05 — JSON과 사전 Enrichment

와이드 테이블을 JOIN 없이 풍부하게 만드는 두 가지 방법이 있습니다.

첫째, JSONExtract* 계열 함수로 creativeAsset 같은 String 컬럼에서 값을 뽑습니다. 중첩 경로도 지원하므로 JSONExtractString(creativeAsset, 'item', 'id')처럼 파고들거나, 타입을 지정해 숫자로 파싱할 수 있습니다.

둘째, dictGet() 로 campaign_dict에서 캠페인의 name/status/targetRoas를 행별로 RAM에서 해결합니다. 500행짜리 차원 테이블을 사전으로 올려 두면 매번 JOIN하지 않고도 목표 ROAS 대비 실제 ROAS 페이싱을 비교할 수 있습니다.

마지막으로, 자주 읽는 JSON 경로는 MATERIALIZED 컬럼으로 승격시킵니다.

ALTER TABLE ad_events
  ADD COLUMN creativeFormat LowCardinality(String)
  MATERIALIZED JSONExtractString(creativeAsset, 'format');

ALTER TABLE ad_events MATERIALIZE COLUMN creativeFormat;

이렇게 하면 이후 읽기가 JSON을 재파싱하지 않고 LowCardinality 컬럼만 스캔합니다. 다만 MATERIALIZED 컬럼은 계산되는 값이므로 명시적 INSERT 컬럼 리스트에서 빼야 하고, ADD COLUMN만으로는 기존 파트가 채워지지 않으니 MATERIALIZE COLUMN으로 백필해 줘야 합니다.

06 — ML 랭킹 품질 분석

광고 랭킹 모델의 건강 상태도 같은 테이블에서 볼 수 있습니다. 조건부 집계에 quantile 분포를 섞어 모델 신호 컬럼을 분석합니다.

• 점수 분포 — rawPCtr의 avg / median / p95 / p99
• 보정 갭(calibration gap) — 채널별 avg(pCTR) − 실제 CTR. 모델이 과대·과소 예측하는지 한눈에 드러납니다.
• 캡핑 비율 — qualityScore가 rawQualityScore 대비 얼마나 하향/상향/전체 조정되는지
• 랭크 이동 — candidateRank에서 최종 rank로의 변화, 그리고 eCPM 스타일의 품질 × 입찰가 랭킹 점수

quantile류 함수는 대용량에서도 근사 알고리즘으로 빠르게 동작하므로, 모델 점수의 꼬리 분포(p95/p99)를 실시간에 가깝게 모니터링하기에 적합합니다.

07 — 실시간 사전 집계

원본 스캔이 아무리 빨라도, 대시보드가 초당 수백 번 같은 일별 집계를 요청한다면 미리 롤업해 두는 편이 낫습니다. AggregatingMergeTree와 Materialized View를 조합합니다.

일별 (campaign, eventType) 큐브를 이렇게 정의합니다.

CREATE TABLE daily_campaign_cube (
    reportDate Date,
    campaignId String,
    eventType  LowCardinality(String),
    events     AggregateFunction(count),
    spend_raw  AggregateFunction(sum, Int64),
    users      AggregateFunction(uniq, Nullable(String))
) ENGINE = AggregatingMergeTree()
ORDER BY (reportDate, campaignId, eventType);

읽을 때는 countMerge/sumMerge/uniqMerge로 상태를 병합하고, 한 쿼리에서 eventType별로 지표를 쪼개고 싶다면 *MergeIf 콤비네이터를 씁니다. 검증 결과 이 집계 테이블은 원본을 그대로 스캔한 것과 동일한 수치(클릭 349,540건 / 소진액 307,367.09)를 냈고, 라이브 INSERT 테스트에서도 MV가 새 이벤트를 자동으로 롤업했습니다.

*State로 쓴 컬럼은 반드시 대응하는 *Merge로 읽어야 합니다. uniqState로 저장한 AggregateFunction(uniq, …) 컬럼은 uniqMerge로 읽고, *MergeIf는 merge 도중에 필터링을 겁니다.

기억할 함정들

실습을 진행하며 반복해서 마주치는 포인트를 정리합니다.

마치며

광고 성과 분석의 세 가지 관심사 — 경매 텔레메트리, ML 랭킹 신호, 비즈니스 리포팅 — 는 원래 하나의 이벤트 스트림에서 나옵니다. ClickHouse에서는 이걸 굳이 분리하지 않아도 됩니다. 넓은 append-only 테이블 하나에 담아 두고, 조건부 집계로 큐브를 만들고, 근사 distinct로 도달을 세고, JSON과 사전으로 JOIN 없이 enrich하고, AggregatingMergeTree로 실시간 롤업까지 — 전부 같은 엔진 안에서 해결됩니다.

이 실습의 모든 스크립트는 빈 상태의 ClickHouse 26.6.1.778(clickhouse local)에서 오류 없이 end-to-end 검증되었습니다. 직접 돌려 보시면서 GROUP BY 한 줄만 바꿔도 완전히 다른 큐브가 나오는 경험을 해 보시길 권합니다.

추가 자료

• ClickHouse aggregate functions
• Combinators (If, State, Merge)
• JSON functions
• Dictionaries
• Materialized Views

clickhouse-hols/usecase/ads-analytics at main · litkhai/clickhouse-hols

ClickHouse Hands-on Labs . Contribute to litkhai/clickhouse-hols development by creating an account on GitHub.

github.com

Happy Cubing! 📊