Design an Ad Click Aggregator

An ad network shows ads across thousands of websites and apps. When someone clicks an ad, the network records the click and sends the person on to the advertiser's landing page. Advertisers pay per click, so these counts end up on invoices.

Advertisers want a dashboard that shows clicks per ad, campaign and country by the minute, fresh to within about a minute, and they want to filter over the last hour or the last 90 days. Finance wants the monthly invoice to be exact: no click counted twice because a browser retried, no click lost because a server restarted, and bot traffic removed.

Clicks arrive out of order from phones that were offline, and a single viral ad can produce a large share of all traffic.

In 45 minutes, cover requirements and scale, the click path, how you aggregate in near real time, and how you make the numbers trustworthy enough to bill on.

Sketch on the HLD board, write your answer in the Design doc, and size it with the Estimator. Start the mock interview from the timer for a 45-minute run.

Design Path · Commerce & Payments · problem 11 of 18

Ask before you design. Try answering each yourself, then compare.

1. What volume of clicks should we expect?
   Typical answer

   About 1 billion clicks a day, with peaks around 50,000 per second, across roughly 10 million active ads.

2. How fresh must the dashboard be?
   Typical answer

   Within about a minute for live views. Billing totals can be finalised hours later.

3. What dimensions do advertisers query by?
   Typical answer

   Ad, campaign, advertiser, country and device, at minute granularity for recent data and hourly or daily for older data.

4. How late can a click arrive?
   Typical answer

   Most within seconds; mobile SDKs may flush clicks that are up to a day old. Those must still count toward billing.

5. Who decides whether a click is a duplicate or a bot?
   Typical answer

   Each click carries a unique signed click ID from the ad server. Duplicates by ID are dropped; bot filtering rules exist and we apply them, but building them is out of scope.

6. Can the redirect wait for the click to be recorded?
   Typical answer

   It must be durably logged, but the redirect should add only a few milliseconds.

Requirements

Functional

• Record each ad click and redirect the user to the advertiser's landing page.
• Aggregate clicks per ad per minute, rolled up by campaign, advertiser, country and device.
• Let advertisers query counts over arbitrary time ranges with filters.
• Produce final, deduplicated, bot-filtered counts for billing.

Non-functional

• Redirect p99 under 50 ms; recording adds no more than 5 ms.
• Dashboard freshness under 60 seconds; dashboard queries p95 under 1 second.
• Peak ingest of 50,000 clicks per second with headroom to 3x.
• Billing counts exact: no loss, no double counting, reproducible from raw data; raw clicks kept 13 months.

Out of scope

• Impression counting and ad serving or auction.
• Designing the bot-detection models themselves.

Back-of-the-envelope estimates

• Ingest: 1 B clicks a day ≈ 11,600 per second on average; plan for 50,000 per second at peak.
• Raw storage: about 300 bytes per click ≈ 300 GB a day uncompressed, roughly 60 GB compressed in columnar files, around 22 TB for 13 months.
• Stream throughput: 50,000 × 300 B = 15 MB/s at peak, easily handled by a Kafka topic with 64 to 128 partitions.
• Aggregates: 10 M ads, but only about 1 M receive clicks in a given minute; with country and device splits that is a few million rows a minute before rollup, collapsing to tens of millions of hourly rows a day.
• Query load: tens of thousands of advertisers refreshing dashboards ≈ a few hundred queries per second, all over pre-aggregated data.

The ingest rate and the need for exact billing numbers drive the design; query volume is small.

API

GET  /c?t=<signed click token>          -> 302 Location: landing page
     token = {clickId, adId, campaignId, publisherId, ts, sig}
GET  /v1/stats?advertiser=&from=&to=&granularity=minute|hour|day
         &groupBy=ad,country&filter=device:mobile -> {rows[], final: bool}
GET  /v1/billing/{advertiserId}/{month}      -> {clicks, invalidClicks, amount}

The click ID is minted when the ad is served and signed into the click URL, so dedupe uses a key the browser cannot forge, and every downstream stage can be idempotent on it.

Data model

• Raw click topic (Kafka, keyed by ad_id, 7-day retention): the immutable source of truth for the stream.
• Raw click lake (Parquet in object storage, partitioned by event_date/hour): click_id, ad_id, campaign_id, publisher_id, country, device, ip_hash, event_ts, ingest_ts. Cheap, replayable, used for billing recounts.
• Aggregates (columnar OLAP store, partitioned by hour, sorted by advertiser_id, ad_id, minute): (ad_id, minute, country, device) -> clicks, invalid_clicks, version. Columnar compression and pre-aggregation make range scans over 90 days fast.
• Billing totals (relational): (advertiser_id, day) -> final_clicks, run_id, written only by the batch job.

High-level design

A user clicks an ad and the browser hits the load balancer, which forwards to a stateless Click Service. It verifies the token signature, appends the click to the Raw Clicks topic with producer acknowledgement from in-sync replicas, and returns a 302 to the landing page. If Kafka is slow, a small local disk buffer absorbs the spike so the redirect is never blocked for long.

The Stream Aggregator (Flink-style) consumes the topic by ad partition. It drops duplicate click IDs using keyed state with a TTL, tags suspected bots, assigns each click to a one-minute event-time window, and emits window results once the watermark passes, plus updates for late events. Results are upserted into the OLAP store keyed by ad and minute, so replays overwrite rather than add.

In parallel, a connector archives the raw topic into the Raw Click Lake. Every hour a Batch Recount job recomputes the previous hours from the lake, with full dedupe and the latest bot rules, and overwrites the matching OLAP rows, marking them final. Billing reads only final rows.

Advertisers read through the Query Service, which picks minute, hourly or daily tables based on the time range and caches popular queries for a few seconds.

Open the HLD board and press Reference to see the architecture diagram.

Deep dives

Event time, watermarks and late clicks

Count each click in the minute it happened, not the minute it arrived. The aggregator tracks a watermark, roughly the oldest event time still expected, and closes a window when the watermark passes its end plus an allowed lateness of a few minutes. Clicks arriving after that are written to a side output instead of being dropped. They still reach the lake, so the hourly or daily batch recount includes them, and the dashboard shows a small correction later. This keeps state bounded in the stream while billing stays complete.

Exactly-once counting end to end

Duplicates come from browser retries, double taps and producer retries. Dedupe on the signed click ID in keyed state for, say, 10 minutes catches nearly all of them. Inside the aggregator, checkpointed state plus committed offsets mean a restart replays from the last checkpoint without double counting. At the sink, write each window as an idempotent upsert keyed by (ad_id, minute, dims) with the full count, never an increment, so replays converge. The batch recount is the final guard: it deduplicates over the whole day, which catches retries that arrived hours apart.

Hot ads and partition skew

Keying the topic by ad ID keeps one ad's clicks on one partition, which makes dedupe and windowing local. A viral ad can then overload one consumer. Detect hot keys from per-key rates and salt them: route clicks for a hot ad to ad_id#0..15, aggregate each sub-key separately, and merge the partial counts in a second, much smaller stage. Dedupe still works because the salt is derived from the click ID hash, so retries of the same click land on the same sub-key.

Two paths, one answer

The streaming path is fast but approximate at the edges: late data, bot rules that change, and rare state loss. The batch path over the raw lake is slow but authoritative and reproducible. Both write to the same OLAP rows with a version field, and batch results always win. Expose a final flag per row so the dashboard can show provisional numbers and billing never reads them. Keeping a single code library for the counting logic used by both jobs avoids the two paths drifting apart.

Failure modes

• If Kafka is unavailable, click servers buffer to local disk for minutes and keep redirecting; if the buffer fills, they still redirect and log the gap so billing can credit advertisers rather than overcharge.
• If a stream worker dies, the job restarts from the last checkpoint and replays offsets; idempotent upserts keep counts correct, and the dashboard lags by a minute or two.
• If the OLAP store is down, the aggregator backs off and Kafka retains the data; dashboards show stale data with a banner.
• If a bad bot rule ships, rerun the batch job for the affected hours from the lake and overwrite the aggregates.
• If a region is lost, its click servers stop taking traffic, DNS sends users elsewhere, and replicated topics let another region's job take over.

Core algorithm in code

from collections import defaultdict

WINDOW = 60            # seconds per tumbling window
ALLOWED_LATENESS = 120  # keep windows open this long after the watermark passes them
DEDUPE_TTL = 600


class ClickAggregator:
    """Event-time per-minute click counts per ad with dedupe and a watermark."""
    def __init__(self):
        self.open = defaultdict(int)     # (ad_id, window_start) -> count
        self.seen = {}                   # click_id -> event_ts (keyed state with TTL)
        self.watermark = 0
        self.late = []                   # side output for the batch correction job
        self.duplicates = 0

    def on_click(self, click_id, ad_id, event_ts):
        if click_id in self.seen:
            self.duplicates += 1
            return
        self.seen[click_id] = event_ts
        start = event_ts - event_ts % WINDOW
        if start + WINDOW + ALLOWED_LATENESS <= self.watermark:
            self.late.append((click_id, ad_id, event_ts))
            return
        self.open[(ad_id, start)] += 1

    def advance_watermark(self, ts):
        self.watermark = max(self.watermark, ts)
        closed = []
        for key in sorted(self.open):
            ad_id, start = key
            if start + WINDOW + ALLOWED_LATENESS <= self.watermark:
                closed.append((ad_id, start, self.open.pop(key)))
        cutoff = self.watermark - DEDUPE_TTL
        self.seen = {c: t for c, t in self.seen.items() if t >= cutoff}
        return closed


agg = ClickAggregator()
events = [
    ("c1", "ad-7", 1000), ("c2", "ad-7", 1010), ("c1", "ad-7", 1000),   # c1 retried by the browser
    ("c3", "ad-9", 1030), ("c4", "ad-7", 1075), ("c5", "ad-9", 1140),
    ("c6", "ad-7", 1020),                                               # arrives late but within lateness
]
for e in events:
    agg.on_click(*e)
print("emitted at wm=1150:", agg.advance_watermark(1150))
agg.on_click("c7", "ad-9", 1090)
print("emitted at wm=1300:", agg.advance_watermark(1300))
agg.on_click("c8", "ad-7", 1001)                                        # far too late for streaming
print("still open:", dict(agg.open))
print("duplicates dropped:", agg.duplicates, "late side output:", agg.late)

Trade-offs

• A streaming plus batch design gives fresh dashboards and exact bills but costs two pipelines to run and keep consistent.
• Longer allowed lateness captures more clicks in real time but holds more state and delays final minute results.
• Dedupe state with a short TTL is cheap but misses retries hours apart, which the batch job must catch.
• Pre-aggregating by fixed dimensions makes queries fast but makes ad hoc slices impossible without returning to raw data.
• Waiting for replicated Kafka acks protects billing data but adds a few milliseconds to every redirect.

What a strong answer shows

• Decouples the redirect from aggregation with a durable log, keeping the click path fast.
• Uses event time with watermarks and has a plan for late data rather than dropping it.
• Explains where duplicates come from and makes each stage idempotent on a click ID.
• Separates provisional real-time numbers from authoritative billing numbers, with a replayable raw store.
• Addresses hot-key skew with a concrete technique.
• Chooses a columnar store with pre-aggregation for the query side and justifies it.

Mistakes to avoid

• Incrementing counters in a database on every click, which melts under peak load and double counts on retry.
• Windowing by processing time, so outages and offline phones shift clicks into the wrong minutes.
• Claiming the stream processor alone gives exactly-once billing without idempotent sinks or a reconciliation path.
• Throwing away raw clicks after aggregation, leaving no way to recompute after a bug.
• Ignoring that one hot ad can saturate a single partition.

Follow-ups to expect

• How would you support unique-user counts per campaign? (HyperLogLog sketches per window, merged across rollups.)
• An advertiser disputes an invoice. What do you show them? (Replay their raw clicks from the lake with the rule version used.)
• How do you add a new dimension, like browser? (Add to raw schema; backfill aggregates from the lake.)
• How would you handle clicks from multiple regions? (Regional ingest and aggregation; global rollup of per-region partials.)

Practice next