Design an Ad Click Aggregation System

Ad click aggregation is the system design question that looks like "just count things" and turns out to be about correctness of streaming data. Advertisers are billed per click, and budgets are paced minute by minute. Over-counting overcharges customers; under-counting loses revenue; showing numbers late lets campaigns overspend. Every hard topic in stream processing (event time, late data, exactly-once, replay) appears with real money attached.

Ad platforms such as Google Ads, Meta, Amazon Ads and Indian players like Flipkart Ads or InMobi run some version of this pipeline. This guide designs one from scratch, with a runnable windowing simulation and a dry run of the trickiest part: what happens to a click that arrives late.

Step 1: Requirements

Functional

• Record every ad click and redirect the user to the advertiser's landing page.
• Return the number of clicks for an ad (and roll-ups by campaign and advertiser) over the last M minutes, at 1-minute granularity.
• Return the top N most-clicked ads in the last M minutes, overall and per category.
• Support filtering by dimensions such as country, device type and placement.
• Filter invalid (fraudulent) clicks before counting for billing.

Non-functional

• Accuracy: billing-grade. Small temporary discrepancies on dashboards are tolerable; final billed counts must be correct.
• Freshness: dashboard and budget pacing data within about a minute or two.
• Handle delayed and duplicated events: mobile clients go offline, retries happen, and services restart.
• Fault tolerance: a crashed processor must not lose or double-count clicks.
• Scale: billions of clicks per day with large spikes during sales events and cricket matches.

Step 2: Capacity Estimates

Two takeaways. Ingestion at ~58k events/s is comfortably within a modest Kafka cluster. The aggregated output is about 30x smaller than the raw data, which is the whole reason to pre-aggregate rather than scanning raw clicks for every dashboard query. Impressions are usually 10–100x more numerous than clicks and use the same pipeline design, which is worth saying even if you only design for clicks.

Step 3: API Design

GET /c?t=<signed_click_token>
    -> logs click event, 302 Location: https://advertiser.example/landing

GET /v1/ads/{ad_id}/clicks?from=2026-10-10T09:00Z&to=2026-10-10T10:00Z
                         &granularity=1m&filter=country:IN,device:android
    -> { "ad_id": 98213, "series": [ {"ts": "09:00", "clicks": 412}, ... ],
         "status": "provisional" }      // "final" after reconciliation

GET /v1/ads/top?window=5m&n=100&category=electronics
    -> { "window_end": "...", "ads": [ {"ad_id": 1, "clicks": 52012}, ... ] }

The click token is generated by the ad server when it serves the impression and signed with an HMAC. It contains impression_id, ad_id, campaign_id, placement and the impression timestamp. Signing prevents anyone from forging clicks on arbitrary ads by editing URL parameters. The status field tells the dashboard whether numbers are provisional (streaming) or final (reconciled), which is the honest way to expose two levels of accuracy.

Step 4: Data Model

Raw click event

{
  "click_id": "01J9ZK3Q8B6W...",      // unique per click, generated at the edge
  "impression_id": "imp-7f2c...",
  "ad_id": 98213, "campaign_id": 4410, "advertiser_id": 77,
  "event_time": 1791623702123,          // ms, when the click happened
  "ingest_time": 1791623702410,         // ms, when the click service received it
  "user_hash": "u-3a9...", "ip": "49.36.x.x", "user_agent": "...",
  "country": "IN", "device": "android", "placement": "search_top"
}

Aggregated table

CREATE TABLE ad_clicks_1m (
    ad_id          BIGINT,
    window_start   TIMESTAMP,          -- minute bucket, event time
    country        VARCHAR(2),
    device         VARCHAR(16),
    valid_clicks   BIGINT,
    invalid_clicks BIGINT,
    version        INT,                -- bumps on late-data corrections
    PRIMARY KEY (ad_id, window_start, country, device)
);

The store for aggregates should be an OLAP database built for time-series rollups and fast filtered group-bys: Apache Druid, Apache Pinot or ClickHouse are typical choices. Pinot and Druid support upserts or segment replacement, which matters for corrections. Raw events go to object storage as Parquet partitioned by hour, which is the source of truth for reconciliation and for replays.

Step 5: High-Level Architecture

Everything flows through Kafka twice: raw clicks in, aggregated counts out. That double log gives two replay points: re-run the aggregation from raw events, or rebuild the OLAP store from aggregates. If you need a refresher on why Kafka works well here, see how a distributed message queue like Kafka is built.

Deep Dive 1: Click Ingestion and the Redirect

The click service sits on the user's critical path: the user is waiting for the advertiser page. It must be fast and must not lose clicks.

1. Verify the token (HMAC, expiry). Invalid tokens still redirect, so the user is not punished, but the click is marked invalid.
2. Assign click_id (a UUID v7 or similar) so every later stage can deduplicate.
3. Produce to Kafka with acks=all and the idempotent producer enabled, so broker-side retries do not create duplicates.
4. Redirect with 302. If Kafka is briefly unavailable, write the event to a local disk buffer and redirect anyway; a sidecar drains the buffer later. Losing the redirect is worse than delaying the count.

Partition clicks.raw by ad_id so all clicks for an ad reach the same Flink subtask, which keeps window state local. Hot ads complicate this; we fix that below.

Deep Dive 2: Event-Time Windows and Watermarks

"Clicks per ad per minute" is a tumbling window: fixed, non-overlapping 1-minute buckets. Other window types matter for follow-up questions:

The crucial choice is event time vs processing time. Processing-time windows are easy (count whatever arrives during this wall-clock minute) but wrong: if Kafka consumers lag by 10 minutes during a deploy, clicks from 09:00 get counted as 09:10. Billing and pacing must use the time the click happened.

With event time, a window cannot know when it is complete. A watermark is the processor's assertion "I don't expect any more events with event time earlier than W". The common strategy is bounded out-of-orderness: watermark = (maximum event time seen) − (allowed delay, say 20 seconds). When the watermark passes a window's end, the window fires.

WatermarkStrategy<Click> wm = WatermarkStrategy
        .<Click>forBoundedOutOfOrderness(Duration.ofSeconds(20))
        .withTimestampAssigner((click, ts) -> click.getEventTime())
        .withIdleness(Duration.ofMinutes(1));       // idle partitions must not stall time

OutputTag<Click> tooLate = new OutputTag<Click>("too-late") {};

SingleOutputStreamOperator<AdCount> counts = env
        .fromSource(kafkaSource, wm, "clicks.raw")
        .keyBy(Click::getClickId)
        .process(new DedupFunction())               // keyed state with 24h TTL
        .filter(new FraudRules())
        .keyBy(Click::getAdId)
        .window(TumblingEventTimeWindows.of(Duration.ofMinutes(1)))
        .allowedLateness(Duration.ofMinutes(5))     // late clicks re-fire the window
        .sideOutputLateData(tooLate)
        .aggregate(new CountAgg(), new ToAdCount()); // incremental count, then add window info

counts.sinkTo(transactionalKafkaSink);              // exactly-once to clicks.agg
counts.getSideOutput(tooLate).sinkTo(lateClicksSink);

The sketch omits class definitions, but the structure is the point: dedup, filter, key by ad, event-time tumbling window with allowed lateness and a side output. withIdleness matters in practice: a Kafka partition that receives no clicks would otherwise hold the global watermark back forever, and no window would fire.

Deep Dive 3: Late Events, With a Dry Run

Here is a runnable simulation of the same semantics in plain Python: 1-minute windows, a 20-second watermark delay, 60 seconds of allowed lateness, and deduplication by click_id.

from collections import defaultdict

WINDOW = 60          # 1-minute tumbling windows (seconds)
MAX_DELAY = 20       # watermark lags max event time by 20 s
LATENESS = 60        # keep window state 60 s after it fires


class ClickAggregator:
    def __init__(self):
        self.counts = defaultdict(int)       # (ad_id, window_start) -> count
        self.fired = set()                   # windows already emitted once
        self.seen = set()                    # click_ids (TTL state or bloom filter in prod)
        self.max_ts = 0
        self.watermark = float("-inf")
        self.dead_letter = []                # too late even for corrections

    def on_click(self, click_id, ad_id, ts):
        if click_id in self.seen:            # duplicate delivery or double click
            return
        self.seen.add(click_id)
        start = ts - ts % WINDOW
        if start + WINDOW + LATENESS <= self.watermark:
            self.dead_letter.append((click_id, ad_id, ts))
            return
        self.counts[(ad_id, start)] += 1
        if (ad_id, start) in self.fired:     # late but allowed: emit a correction
            print(f"  UPDATE ad={ad_id} window={start} count={self.counts[(ad_id, start)]}")
        self.max_ts = max(self.max_ts, ts)
        self._advance(self.max_ts - MAX_DELAY)

    def _advance(self, wm):
        if wm <= self.watermark:
            return
        self.watermark = wm
        for (ad, start), c in sorted(self.counts.items(), key=lambda kv: kv[0][1]):
            if start + WINDOW <= wm and (ad, start) not in self.fired:
                self.fired.add((ad, start))
                print(f"  EMIT   ad={ad} window={start} count={c}")
        for key in [k for k in self.counts if k[1] + WINDOW + LATENESS <= wm]:
            del self.counts[key]             # free state after allowed lateness


agg = ClickAggregator()
events = [("c1", "ad7", 5), ("c2", "ad7", 42), ("c3", "ad9", 58), ("c2", "ad7", 42),
          ("c4", "ad7", 75), ("c5", "ad7", 83), ("c6", "ad7", 50), ("c7", "ad9", 150),
          ("c8", "ad7", 30), ("c9", "ad7", 190)]
for e in events:
    print("click", e)
    agg.on_click(*e)
print("dead letter:", agg.dead_letter)

This dry run shows the three-tier treatment of lateness that interviewers want to hear:

• On time: counted before the window fires.
• Late within allowed lateness: the window re-fires with a corrected count. Downstream must treat outputs as upserts keyed by (ad, window), not appends, or the dashboard would show 2 + 3 = 5.
• Too late: sent to a side output, stored in object storage and picked up by batch reconciliation. Not lost, just not real-time.

Choosing the numbers is a business trade-off. A longer watermark delay makes first results more complete but later; a longer allowed lateness means more state held in memory. Measure the actual distribution of ingest_time − event_time and pick a delay that covers, say, the 99th percentile; the rest is handled by corrections and reconciliation.

Deep Dive 4: Exactly-Once Processing

"Exactly-once" in streaming really means effectively once: the effect on state and outputs is as if each event were processed once, even though internally events may be replayed after failures. It needs every link of the chain to cooperate.

1. Replayable source. Kafka retains events; the consumer position is just an offset that can be rewound.
2. Consistent checkpoints. Flink periodically injects checkpoint barriers into the stream (a variant of the Chandy-Lamport snapshot algorithm). Each operator snapshots its state when the barrier passes, and the snapshot includes the Kafka offsets. On failure, Flink restores all operator state and rewinds Kafka to the matching offsets, so every event is reflected in state exactly once.
3. Output that tolerates replay. Either a transactional sink (Flink's Kafka sink writes inside a Kafka transaction and commits only when the checkpoint completes, a two-phase commit; downstream consumers use isolation.level=read_committed) or an idempotent sink (upsert into the OLAP store keyed by (ad_id, window_start, dims); writing the same result twice changes nothing).
4. Deduplicate upstream duplicates. Checkpointing cannot help with duplicates that were already in Kafka, such as a client retrying a click request. Dedup by click_id with keyed state and a TTL (24 hours) handles that; a Bloom filter can reduce memory at a small false-positive cost (details in Bloom filters explained).

Deep Dive 5: Hot Ads and Top-N Queries

Hot keys

Keying by ad_id sends every click for a mega-campaign (say, a smartphone launch during a sale) to one Flink subtask. That subtask becomes the bottleneck and its Kafka partition lags. Fix with two-stage aggregation:

1. Stage 1: key by (ad_id, salt) where salt = hash(click_id) mod 16, and count per window. Load spreads across 16 subtasks.
2. Stage 2: key by ad_id and sum the 16 partial counts per window. Stage 2 sees only 16 records per ad per minute, which is trivial.

Apply salting only to known hot ads (from a periodically refreshed list) to avoid doubling work for the long tail.

Top N most clicked ads

For "top 100 ads in the last 5 minutes", run a second windowed job over the per-minute counts: a sliding 5-minute window that sums per ad, then a per-window min-heap of size N. Each parallel subtask keeps a local top N, and a final single task merges them. That is the same heavy-hitter pattern covered in Top-K / trending topics system design, including approximate variants like Count-Min Sketch when the key space is huge.

Deep Dive 6: Lambda vs Kappa Architecture

Lambda was popular when stream processors could not guarantee correctness. Modern engines with checkpointing and event-time semantics make Kappa the usual default. But for billing, many teams keep a lightweight batch path anyway, not as a second serving layer, but as an independent audit. That hybrid is the strongest interview answer.

Deep Dive 7: Reconciliation for Billing

Streaming numbers are provisional. The daily reconciliation job produces the numbers that invoices are generated from.

1. Recompute from raw. A Spark job reads the day's raw Parquet clicks (including the too-late side output), deduplicates by click_id, applies the latest fraud verdicts and computes counts per ad per minute.
2. Compare. Join with the streaming aggregates; compute the absolute and relative difference per ad and per advertiser.
3. Alert on drift. Differences above a threshold (for example 0.1% for an advertiser) page the on-call team, because they usually indicate a bug, data loss or a misconfigured watermark.
4. Finalize. Batch results overwrite the OLAP rows for that day and mark them final. Billing reads only final data.

SELECT b.advertiser_id,
       SUM(b.valid_clicks)                         AS batch_clicks,
       SUM(COALESCE(s.valid_clicks, 0))            AS stream_clicks,
       SUM(b.valid_clicks) - SUM(COALESCE(s.valid_clicks, 0)) AS diff
FROM   batch_ad_clicks_1m b
LEFT JOIN ad_clicks_1m s
       ON s.ad_id = b.ad_id AND s.window_start = b.window_start
      AND s.country = b.country AND s.device = b.device
WHERE  b.window_start >= DATE '2026-10-09' AND b.window_start < DATE '2026-10-10'
GROUP BY b.advertiser_id
HAVING ABS(SUM(b.valid_clicks) - SUM(COALESCE(s.valid_clicks, 0)))
       > 0.001 * SUM(b.valid_clicks);

(The left join from the batch side misses ad-minutes that exist only in the streaming table; a production check runs a full outer join or a second query for those.) This design is a form of event sourcing: raw immutable events are the truth and every aggregate is a derived, rebuildable view. The pattern is explained in event sourcing and CQRS.

Deep Dive 8: Fraud and Invalid Click Filtering

Advertisers only pay for valid clicks. The industry (Media Rating Council guidelines) distinguishes general invalid traffic (known bots, data-centre traffic, duplicates, easy to detect with lists and rules) from sophisticated invalid traffic (click farms, malware, hijacked devices, needing deeper analysis).

Real-time rules must be cheap and stateful per key, which is again rate-limiting logic; the algorithms overlap with rate limiter design. Count invalid clicks separately rather than dropping them silently: advertisers and auditors want to see how many clicks were filtered and why. When offline models invalidate clicks retroactively, the reconciliation job removes them from final counts and the billing system issues credits.

How Interviewers Evaluate This Design

Common mistakes

• Writing every click to a relational database and running COUNT(*) GROUP BY for dashboards.
• Using processing-time windows, so a consumer lag silently shifts clicks between minutes.
• Saying "Kafka gives exactly-once" without explaining how the sink and the duplicates are handled.
• Appending late corrections instead of upserting, which double counts.
• Dropping late events entirely instead of routing them to reconciliation.
• Treating fraud as an afterthought; in an ads interview it is a core requirement.

Follow-ups to prepare

• "How do you support a new dimension like city without reprocessing?" Add it to the event schema now; replay from raw Parquet to backfill if the history is needed.
• "How do you pace budgets?" A pacing service reads near-real-time spend per campaign and throttles ad serving when spend runs ahead of plan.
• "What if the Flink job has a bug that over-counted for 6 hours?" Fix the code, start a new job from the offsets 6 hours back (or rerun batch), write to a new table version, then swap.

Frequently Asked Questions

Pranjal Jain

Ex-Microsoft SDE · IIT Kanpur · Founder of Prepflix. Helps engineers crack startup and product company interviews across India.