Ad Performance Analytics Cube with ClickHouse

---

A hands-on, end-to-end laboratory for building an ad-performance analytics "cube" on ClickHouse. It models a generic digital advertising platform (ad network / retail media / DSP·SSP / in-house ad server) where bid telemetry, ML ranking signals, and business KPIs all land in one wide append-only event row, and analysts want to slice it by arbitrary dimensions in near real time.

Built around a synthetic 10 million-row ad_events stream, it walks through every primitive that makes ClickHouse a natural fit for this workload: conditional aggregation (countIf/sumIf/avgIf), approximate distinct counts (uniq/uniqCombined), semi-structured JSON parsing, in-memory dictionary enrichment (dictGet), quantile distributions, and real-time roll-ups via AggregatingMergeTree + Materialized Views.

🎯 Why this lab

A digital advertising platform commonly handles three concerns from one event stream:

1. Ad serving & auction (RTB) telemetry — bid requests, candidate ranking, win price, served rank
2. ML ranking signals — predicted CTR/CVR (pCTR·pCTCVR), quality score, capping behavior
3. Business reporting — spend, revenue, ROAS sliced by any dimension combination

The combination of "high-frequency append-only stream + arbitrary-dimension OLAP aggregation + approximate distinct + semi-structured enrichment" is the textbook ClickHouse sweet spot. This lab makes that fit concrete and measurable.

📊 Dataset

A synthetic generic ad platform: - Events: 10,000,000 (30 days, 2026-05-15 → 2026-06-13) - Funnel: IMPRESSION 85% · VIEWABLE_IMPRESSION 8% · CLICK 3.5% · INTERACTION 1.5% · DIRECT_CONVERSION 1.2% · INDIRECT_CONVERSION 0.8% → realistic CTR ≈ 4%, CVR ≈ 57% - Wide schema: ~50 dimensions per row (ad hierarchy L1–L4, inventory, user/device, RTB, ML signals, 3 JSON fields) - Campaigns: 500 (fed into an in-memory dictionary) - High cardinality: ~1.9M users, ~2.9M devices, ~5M bid requests - Money fields are scaled integers (micro-units, SCALE = 1,000,000); scale factor & take rates live in a cube_params table, never hard-coded

📁 File Structure

ads-analytics/
├── README.md                  # This file
├── 01-schema.sql              # ad_events wide table + campaign_dict + cube_params
├── 02-load.sql                # 10M synthetic events + 500 campaigns (hashed funnel)
├── 03-cube-basics.sql         # countIf/sumIf cube: CTR/CVR/CTCVR/CPC/CPM/ROAS/Profit
├── 04-approx-distinct.sql     # uniq vs uniqExact, DAU, reach, frequency capping
├── 05-json-enrichment.sql     # JSONExtract*, dictGet, MATERIALIZED column promotion
├── 06-model-ranking.sql       # quantile distributions, pCTR–CTR gap, capping ratios
├── 07-materialized-views.sql  # AggregatingMergeTree daily cube + live-insert test
└── 99-cleanup.sql             # Drop everything

🚀 Quick Start

cd usecase/ads-analytics

# Against a running ClickHouse (server or `clickhouse local`)
for f in 01-schema.sql 02-load.sql 03-cube-basics.sql \
         04-approx-distinct.sql 05-json-enrichment.sql \
         06-model-ranking.sql 07-materialized-views.sql; do
    echo "▶ Running $f"
    clickhouse-client --multiquery --queries-file "$f"
done

Or with a stateless clickhouse local (persisting to a local path):

for f in 01-schema.sql 02-load.sql 03-cube-basics.sql \
         04-approx-distinct.sql 05-json-enrichment.sql \
         06-model-ranking.sql 07-materialized-views.sql; do
    clickhouse local --path /tmp/ad_cube --multiquery --queries-file "$f"
done

📖 Lab Walkthrough

01 — Schema (01-schema.sql)

Creates the ad_analytics database and four objects: - ad_events — the single wide event table. ORDER BY (channelType, campaignId, eventType, eventTs) puts the most common filter/group columns first; PARTITION BY toYYYYMM(eventTs) enables date pruning. Enum-like columns use LowCardinality(String); money is Int64 (scaled). - campaign_meta — small dimension table (500 rows) that feeds the dictionary. - campaign_dict — COMPLEX_KEY_HASHED dictionary over campaign_meta for JOIN-free dictGet(). - cube_params — SCALE and take rates as data, so measure expressions stay parameter-driven.

02 — Synthetic data (02-load.sql)

10M events generated from numbers(10000000), every field hashed deterministically from number via cityHash64(number, 'salt'). The funnel stage is picked from a 0–999 bucket; winPrice is populated only on CLICK rows, conversionValue only on conversion rows, so the downstream sumIf measures behave realistically.

03 — The cube (03-cube-basics.sql)

The heart of the lab. Every funnel metric is a conditional aggregate over the same table in a single pass:

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

Covers Impression/Click/Conversion counts, Spending/Revenue/Profit, and CTR/CVR/CTCVR/CPC/CPM/ROAS — then re-slices the same cube by channel × platform, by campaignGoal, and by day. No new table, no new index — just change the GROUP BY. SCALE and take rates are pulled from cube_params as scalar subqueries.

Scale rule: ratio KPIs (CTR, ROAS) cancel the scale factor, so no /SCALE. Absolute-money KPIs (CPC, CPM, Spending) do divide by SCALE.

04 — Approximate distinct (04-approx-distinct.sql)

userId/deviceId/requestId have millions of distinct values:

Function	Use	Error (measured)
uniqExact	billing / settlement	0% (exact, heaviest)
uniq	dashboards (default)	~0.15%
uniqCombined	tunable accuracy/memory	~0.35%

Plus DAU, uniqIf (distinct only over a sub-population — "campaigns that actually got an impression"), reach-per-line-item, per-user economics, and a frequency-capping distribution (impressions per user).

Rule of thumb: approximate distinct for exploration & dashboards; uniqExact only where a number drives money (billing/settlement).

05 — JSON & dictionary enrichment (05-json-enrichment.sql)

Two ways to enrich the wide table without JOINs: - JSONExtract* on the creativeAsset / biddingStrategy / experiment String columns — including nested paths (JSONExtractString(creativeAsset, 'item', 'id')) and typed numeric parsing. - dictGet() on campaign_dict — resolves campaign name/status/targetRoas per row from RAM, then compares actual vs target ROAS for pacing.

Finally, promotes a hot JSON path to a MATERIALIZED column (ALTER TABLE … ADD COLUMN … MATERIALIZED + MATERIALIZE COLUMN backfill) so future reads scan a LowCardinality column instead of re-parsing JSON.

06 — ML ranking quality (06-model-ranking.sql)

Mixes conditional aggregates with quantile distributions on the model-signal columns: - Score distributions (avg / median / p95 / p99 of rawPCtr) - Calibration gap — avg(pCTR) − actual CTR per channel - Capping ratios — lower / higher / total adjustment of qualityScore vs rawQualityScore - Rank movement (candidateRank → rank) and an eCPM-style quality × bid ranking score

07 — Real-time pre-aggregation (07-materialized-views.sql)

A daily (campaign, eventType) cube in an AggregatingMergeTree, fed by a Materialized View:

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);

Reads use countMerge/sumMerge/uniqMerge (and the *MergeIf combinator to split metrics by eventType in one query). The aggregate gives the same numbers as the raw scan (verified: 349,540 clicks / 307,367.09 spend from both), and a live-INSERT test proves the MV auto-rolls-up new events.

🔑 Gotchas worth remembering

	Note
Scaled-integer money	Store currency as Int64 micro-units to avoid float drift. Divide by SCALE only for absolute-money KPIs; ratio KPIs cancel it out.
Parameterize, don't hard-code	SCALE, take rates, tokenizer choices → a params table, read as scalar subqueries. Contract/model changes never touch query SQL.
MATERIALIZED columns aren't INSERTed	Omit them from explicit INSERT column lists — they are computed. ADD COLUMN … MATERIALIZED does not backfill old parts until you run MATERIALIZE COLUMN.
*State ↔ *Merge must pair	An AggregateFunction(uniq, …) column written with uniqState is read with uniqMerge. The *MergeIf combinator filters during the merge.
Approximate vs exact distinct	uniq is HyperLogLog (~1% error, tiny memory); reserve uniqExact for billing.
numbers(N) not system.numbers	numbers(N) terminates; system.numbers is infinite.

🔍 Additional resources

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

📝 Verification

All scripts verified end-to-end against ClickHouse 26.6.1.778 from an empty state (clickhouse local):

Script	Output lines	Errors
01-schema.sql	5	0
02-load.sql	10	0
03-cube-basics.sql	37	0
04-approx-distinct.sql	31	0
05-json-enrichment.sql	29	0
06-model-ranking.sql	16	0
07-materialized-views.sql	15	0

📝 License

MIT — same as the rest of the repository.

👤 Author

Ken Lee (ClickHouse Solution Architect) — ken.lee@clickhouse.com Created: 2026-06-16

---

Happy Cubing! 📊

For questions, see the main clickhouse-hols README.

---

ads-analytics/
├── README.md                  # 이 문서
├── 01-schema.sql              # ad_events 와이드 테이블 + campaign_dict + cube_params
├── 02-load.sql                # 1,000만 합성 이벤트 + 500 캠페인 (해시 퍼널)
├── 03-cube-basics.sql         # countIf/sumIf 큐브: CTR/CVR/CTCVR/CPC/CPM/ROAS/Profit
├── 04-approx-distinct.sql     # uniq vs uniqExact, DAU, 도달, 프리퀀시 캡
├── 05-json-enrichment.sql     # JSONExtract*, dictGet, MATERIALIZED 컬럼 승격
├── 06-model-ranking.sql       # quantile 분포, pCTR–CTR 갭, 캡핑 비율
├── 07-materialized-views.sql  # AggregatingMergeTree 일별 큐브 + 라이브 INSERT 테스트
└── 99-cleanup.sql             # 모든 객체 삭제

cd usecase/ads-analytics

# 실행 중인 ClickHouse (서버 또는 `clickhouse local`)에 대해
for f in 01-schema.sql 02-load.sql 03-cube-basics.sql \
         04-approx-distinct.sql 05-json-enrichment.sql \
         06-model-ranking.sql 07-materialized-views.sql; do
    echo "▶ Running $f"
    clickhouse-client --multiquery --queries-file "$f"
done

for f in 01-schema.sql 02-load.sql 03-cube-basics.sql \
         04-approx-distinct.sql 05-json-enrichment.sql \
         06-model-ranking.sql 07-materialized-views.sql; do
    clickhouse local --path /tmp/ad_cube --multiquery --queries-file "$f"
done

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

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);