← CatZep/Engineering Guides/System Design/Counting & Analytics

Designing an Ad Click / Impression Counter

A system design interview guide to counting billions of ad impressions and clicks per day accurately enough to bill on, while still surfacing fresh numbers on a dashboard within seconds.

Counting sounds like the simplest thing a computer can do, and at small scale it is — increment a number in a database and move on. The problem becomes interesting precisely because of scale and money. An ad platform serves an enormous volume of impressions and clicks, and those counts are not a vanity metric: advertisers are billed against them and the platform's revenue is computed from them. That raises the bar from "roughly right" to "defensibly correct," while the sheer write rate makes the naive "increment a row" approach fall over almost immediately. This guide builds up a design that counts at very high volume, deduplicates so a single click is never billed twice, tolerates events that arrive late or out of order, and reconciles itself to an exact answer that you can stand behind in an invoice.

Concepts to understand first

Each definition includes a concrete example and the condition under which the mechanism is useful. You can read this guide without opening another guide.

1. Requirements and Clarifying Questions

Before drawing any boxes, spend the first few minutes pinning down what "count" means here. The answers change the design, so ask out loud.

• What events? Impressions and clicks only, or also conversions and video views? (Assume impressions and clicks.)
• How fresh must the dashboard be? Seconds, minutes, or next day? (Assume under a minute for dashboards.)
• How exact must billing be? Is "within 0.1%" acceptable, or must every billed click be traceable? (Assume exact and auditable.)
• What query shapes? Per ad per minute? Per campaign per day? Breakdowns by country or device? (Assume per ad and per campaign, minute to day, a few dimensions.)
• How late can events arrive? Mobile apps may upload hours later. (Assume most within minutes, a long tail up to a day.)

2. The Scale of Writes

The first number to establish in an interview is the write rate, because it dictates almost every later decision. A large ad platform might serve on the order of hundreds of thousands to millions of impressions per second at peak, with clicks a couple of orders of magnitude rarer but still very high in absolute terms. Each of those is a discrete event that the counting system must observe. There is no batching at the source — the events stream in continuously, all day, with diurnal peaks and occasional spikes from large campaigns.

At that rate the naive design — "on every event, run UPDATE counts SET n = n + 1 WHERE ad_id = ?" — is dead on arrival. A single row per ad becomes a contention hotspot; the database cannot sustain millions of write transactions per second; and you would be paying transactional durability costs on every single increment. The realization that drives the architecture is that you do not need to durably persist a final count on every event. You need to durably capture the raw event cheaply, and then aggregate it asynchronously. Capture and counting are separate problems with separate performance profiles.

3. Back-of-the-Envelope Estimation

Work the numbers step by step. The goal is not precision; it is to find which parts of the system are big and which are small. Assumptions: 50 billion impressions a day, a 1% click-through rate, peak traffic 3× the average, and about 200 bytes per raw event.

Two lessons fall out of the table. First, the rollup store is small compared with the raw stream; aggregation does most of the work. Second, exact dedup is affordable for clicks but not for impressions, which is why many systems use an exact seen-set for clicks and a Bloom filter (or no streaming dedup at all, relying on batch) for impressions.

4. What We Count

Before designing the plumbing, be precise about the quantities, because their definitions affect both correctness and the data model. Three things are in play, and they are not independent.

• Impressions. An ad was shown to a user. This is the high-volume denominator — the count of times a creative was rendered and surfaced. Definitions matter here (was it viewable? for how long?), and in a real system the client emits a well-defined impression event so the counter does not have to guess.
• Clicks. A user interacted with the ad. Far rarer than impressions, and the event that usually triggers billing in a cost-per-click model. Because each click can carry real money, a click event must be counted exactly once.
• Click-through rate (CTR). The ratio of clicks to impressions for a given ad over a given window. CTR is a derived quantity — you do not store it directly; you store the two counts and divide at query time. Keeping it derived avoids a third number that can drift out of sync with its inputs.

The key design consequence is that you count impressions and clicks as two separate event streams keyed the same way (per ad, per time bucket), and you compute CTR on read. That keeps the write path uniform — both are "count this event into this bucket" — and keeps the reported ratio always consistent with the counts it is built from.

5. API and Data Model

Keep the write API tiny and the read API shaped like the queries you agreed on.

# write path (from ad servers / SDKs; batched)
POST /v1/events
  [{ "event_id": "7f3c…", "type": "click", "ad_id": 91842,
     "campaign_id": 5521, "ts": "2026-09-25T14:03:07.412Z",
     "user_hash": "…", "country": "BR", "device": "android" }, …]
  → 202 Accepted          # accepted into Kafka, not yet counted

# read path (dashboards, reports)
GET /v1/ads/91842/counts?from=2026-09-25T00:00Z&to=2026-09-25T12:00Z&granularity=minute
  → { "status": "provisional", "points": [ { "t": "…14:03", "impressions": 8123, "clicks": 97 }, … ] }

GET /v1/billing/campaigns/5521?period=2026-09-24
  → { "status": "final", "billable_clicks": 180442, "invalid_filtered": 3127 }

Note the status field: the API tells the caller whether a number can still change.

6. The Hard Parts

It is worth naming the difficulties explicitly, because the architecture exists to answer each of them in turn. An interviewer is really probing whether you see past the word "count."

• Extreme write rate. Already covered — you cannot synchronously persist a final count per event, so you must decouple ingestion from aggregation.
• Deduplication. The same logical click can be reported more than once: clients retry on a flaky network, at-least-once queues redeliver, and bots or double-taps generate spurious repeats. Counting a click twice means over-billing an advertiser, which is both a correctness bug and a trust problem. Each event must be counted once.
• Late and out-of-order events. Events do not arrive in the order they happened. A mobile client that was offline may upload an impression from ten minutes ago; network paths reorder messages. A counter that closes a minute bucket the instant the clock ticks over will miss events that belong to that minute but show up afterward.
• Accuracy versus freshness. A dashboard wants numbers now; billing wants numbers that are exactly right. These goals conflict: the freshest number cannot have waited for every late event, and the exact number cannot be instantaneous. The design resolves this by serving both — a fast approximate path and a slow exact path — rather than pretending one number satisfies both.

7. Streaming Aggregation Pipeline

The backbone of the design is a streaming pipeline that separates the four concerns — capture, buffer, aggregate, serve. Each stage has one job and can be scaled independently of the others.

Walking the stages from left to right:

• Event ingest. Lightweight collectors receive impression and click events from clients and ad servers. Their only job is to validate and forward — they do no counting. This keeps the front edge cheap and horizontally scalable, which is what lets it absorb millions of events per second.
• Kafka queue. Events are written to a durable, partitioned log. This is the linchpin: it absorbs spikes, decouples the fast producers from the slower aggregators, and — crucially — gives you a replayable record of every raw event. Partitioning by ad id keeps all events for one ad on one partition, so a single consumer owns that ad's counts and never races another.
• Stream aggregator. A pool of consumers reads the queue and maintains running counts grouped by (ad id, minute bucket). Rather than touching a database per event, each consumer holds in-memory counters and flushes a small rollup row per ad per minute. Millions of raw events collapse into a few count rows.
• Rollup store. The flushed per-minute counts land in a store built for this shape of data — a time-series database or a columnar OLAP store — that handles append-heavy writes and fast range-and-group queries.
• Query and dashboards. Dashboards and reporting APIs read aggregated counts from the rollup store and compute CTR on the fly. They never touch the raw event stream.

function on_event(event):
  validate(event)                          # reject malformed
  kafka.produce(topic=event.type,          # "impression" or "click"
                key=event.ad_id,           # same ad -> same partition
                value=event)               # durable, replayable

function aggregator_loop():
  counts = {}                              # (ad_id, minute) -> n, in memory
  for event in kafka.consume():
    if seen(event.event_id): continue      # dedup, see next section
    bucket = floor_to_minute(event.ts)
    counts[(event.ad_id, bucket)] += 1
    if flush_due():
      # add these partial counts to the stored rows AND record the
      # Kafka offset in the same transaction (or use Flink checkpoints)
      rollup_store.add_and_commit(counts, kafka.position())
      counts.clear()

8. Deduplication by Event ID

Define a logical event ID at the source and preserve it across retries. An accidental double-tap may be two distinct events, not a network duplicate; fraud/invalid-event classification is a separate policy. Namespace IDs by source if different producers can reuse them.

For exact counted effects, the event claim, aggregate update, and relevant input position/state must be transactional or recoverable from one compatible checkpoint. An in-memory seen check alone loses state after a crash. A TTL bounds the guarantee: events replayed after expiry can count again. Dashboard dedup may use a bounded horizon; billing finalization needs retained exact identities or immutable contribution records covering all accepted corrections.

A Bloom filter can be a prefilter for an exact lookup. It cannot by itself discard billable events without undercounting new IDs. Whether an 83 GB provisional seen payload is affordable depends on partitioning and budget, not a universal 'too big' threshold.

Hot-key salting must route retries consistently (for example salt=hash(event_id)%s), or dedup must happen before the salted aggregates. Counts add only after each logical event has been assigned once. Rollups and invoice revisions need versioned corrections so reconciliation does not race streaming writes.

9. Rollups and Pre-Aggregation

Storing one row per raw event would be ruinous at tens of billions of events a day, and querying it for a dashboard would be hopeless. The answer is to pre-aggregate into rollups at increasing granularity, so each query reads the coarsest tier that can answer it.

The aggregator produces the finest tier — per ad, per minute. A rollup job then folds minutes into hours and hours into days. The arithmetic is trivial because counts are additive: an hour is just the sum of its sixty minutes. The payoff is in row count and query cost. A "last 30 days" report for an ad reads thirty day-rows instead of forty-three thousand minute-rows. You keep the fine-grained minute tier only for a short recent window (where people zoom in), and let the coarse tiers carry the long history.

10. Late and Out-of-Order Events

Because events arrive out of order, the aggregator cannot treat a minute bucket as final the instant the wall clock leaves that minute. The standard tool is a watermark with a grace period: a minute bucket stays open and accepting late arrivals for a while after it ends, and only then is its count flushed as provisional. An event stamped with a time inside an already-flushed bucket is not dropped; it is applied as a correction to that bucket.

The watermark is the pipeline's running estimate of "we have now seen everything up to time T". It is usually derived from the event times themselves (for example, the newest event time seen minus a few seconds), not from the wall clock, so a replay of old data behaves the same as live data.

This is why every event carries its own event timestamp from the source, and the aggregator buckets by that timestamp rather than by arrival time. Arrival-time bucketing would scatter an offline client's ten-minute-old events into the wrong minute. Event-time bucketing plus a grace window keeps them in the bucket they belong to, at the cost of a short delay before a bucket's count settles.

function assign_bucket(event):
  bucket = floor_to_minute(event.ts)       # event time, not arrival time
  if watermark() > bucket + BUCKET + GRACE: # watermark = "no older events expected"
    rollup_store.add_correction(event.ad_id, bucket, +1)  # late: fix the closed bucket
  else:
    open_buckets[(event.ad_id, bucket)] += 1               # still within grace

The grace period is a deliberate trade. A longer grace catches more stragglers before flushing, improving the freshly reported number, but delays when a bucket's count appears. A shorter grace makes counts appear sooner but relies more on corrections (and ultimately on reconciliation) to reach the true value. Either way, the streaming counts are explicitly provisional — which sets up the final piece.

11. Batch Reconciliation

The streaming path is fast but approximate: it can miss very late events, mishandle a rare dedup edge case across a window boundary, or carry a small error from a consumer that crashed and resumed. For numbers you will bill on, you need a path that is slow but exact. This is the classic lambda-style arrangement — a real-time layer for freshness alongside a batch layer for correctness.

Because every raw event was durably written to the queue (and archived to long-term storage), you have a complete, replayable record. A periodic batch job — say, hourly or daily — reads the full set of raw events for a closed period, deduplicates globally (no window limitation, since it sees everything at once), aggregates exactly, and writes the authoritative counts. Where the batch result disagrees with the streaming rollup, the batch value wins and the rollup is corrected.

function reconcile(period):
  raw   = event_archive.read(period)       # every raw event for the period
  uniq  = dedup_by_event_id(raw)           # global dedup, no window limit
  exact = group_and_count(uniq, by=[ad_id, minute])
  rollup_store.overwrite(period, exact)    # batch is authoritative
  emit_billing_counts(exact)               # numbers we invoice on

12. Billing-Grade Accuracy

Billing uses finalized, auditable logical events after exact dedup and the applicable validity rules, not a probabilistic provisional dashboard. 'Final' is a business cutoff under a documented accepted-lateness/adjustment policy; an unbounded network cannot prove that every possible old event has arrived.

Keep immutable invoice/count versions, rule versions, source ranges, and reconciliation IDs. Late accepted events or fraud reclassification produce explicit adjustments. Preserve enough raw evidence to explain each billed contribution. CPM impression billing needs exact impression treatment too, not only clicks.

13. Failure Modes

For each box, ask "what if this dies or slows down?" and have an answer ready.

14. Tradeoffs and Alternatives

Worked example: counting a retried click

Input deliveries are e1,e2,e1; all three are durably stored as deliveries, but only two logical events should contribute.

Run a small, testable model

Download concepts.py. It uses only the Python 3.10+ standard library. From the directory where you saved it, run:

python3 concepts.py dedup
python3 concepts.py test

The SQLite model atomically claims event IDs and updates a total, then verifies replay and rollback.

The program contains deterministic assertions and failure traces. It is a learning model, not a production service: it does not implement network consensus, distributed durability, authentication, or third-party delivery. Read its contracts and try changing an input before extending it.

15. A 45-Minute Interview Plan

16. Common Mistakes

• Counting in the database per event. One hot row per ad cannot take millions of writes a second.
• Bucketing by arrival time. Offline mobile events land in the wrong minute. Always bucket by event time.
• Claiming "exactly-once" from Kafka alone. Kafka's exactly-once covers Kafka-to-Kafka. The sink write and the offset commit must also be atomic, or the sink must be idempotent.
• Storing CTR. A stored ratio drifts from its inputs, and you cannot add ratios across minutes. Store the two counts; divide on read.
• Averaging CTRs. CTR for a day is total clicks ÷ total impressions, not the mean of 1,440 minute CTRs.
• Billing on streaming numbers. Only reconciled, fraud-filtered, closed-period counts go on an invoice.
• Forgetting hot keys. Partition by ad id, but plan for the one ad that is 1,000× bigger.

17. Summary

An ad click and impression counter is a stream-processing system whose every decision is shaped by two pressures that pull apart — enormous write volume and money-grade accuracy.

18. Check Yourself

1. An ad shows 900 impressions and 9 clicks in minute 1, and 100 impressions and 5 clicks in minute 2. What is the CTR for the two minutes?
   Answer

   14 ÷ 1,000 = 1.4%. Averaging the two minute CTRs (1% and 5%) would wrongly give 3%. This is why you store counts, not ratios.

2. A consumer adds its partial counts to the store, then crashes before committing its Kafka offset. What goes wrong, and how do you prevent it?
   Answer

   On restart it re-reads the same events and adds them again, so counts are too high. Commit the offset in the same transaction as the counts, or use checkpointed state with a transactional or idempotent sink.

3. Why do we bucket events by event time instead of arrival time?
   Answer

   Arrival time depends on the network and on when offline clients reconnect. Event time puts each event in the minute it actually happened, so replays and late uploads give the same answer.

4. Your impression dedup uses a Bloom filter with 1% false positives. Does it overcount or undercount, and by about how much?
   Answer

   It undercounts. About 1% of new events are wrongly judged "seen" and dropped (a bit less while the filter is still filling). That is why Bloom filters are not used for billable clicks.

5. One ad suddenly gets 20% of all traffic. What breaks, and what is the fix?
   Answer

   Its partition, and the single consumer that owns it, cannot keep up, so lag grows for that ad. Salt the key (for example ad_id#0..7), count each sub-key, and sum at read time.

6. What is the difference between the numbers on the dashboard and the numbers on the invoice?
   Answer

   Dashboard numbers are provisional: fast, windowed dedup, may still change. Invoice numbers come from the batch pass over the full raw log: global dedup, fraud filtered, for a closed period, and never change.

19. Key Terms

Impression

One showing of an ad to a user.

CTR

Click-through rate: clicks ÷ impressions for the same ad and window.

Event time

Event time is when the producer says an event happened; processing time is when a consumer handles it. A watermark is the processor's policy-driven estimate of event-time progress, used to emit and eventually clean up window state. Example and limits above.

Watermark

Event time is when the producer says an event happened; processing time is when a consumer handles it. A watermark is the processor's policy-driven estimate of event-time progress, used to emit and eventually clean up window state. Example and limits above.

Grace period

Extra time a window stays open to accept late events.

Idempotent

Safe to apply twice: the second application changes nothing.

Consumer lag

How far a consumer's position trails the newest message in a partition.

Key salting

Splitting one hot key into several sub-keys to spread its load.

Lambda architecture

A fast streaming path plus a slow exact batch path over the same data.

Kappa architecture

One streaming path; fix history by replaying the log through it.

Bloom filter

A compact set that can say "maybe seen" wrongly but never misses a real member.

Further reading

• Martin Kleppmann, Designing Data-Intensive Applications, chapter 11 (Stream Processing).
• Tyler Akidau et al., "The Dataflow Model," VLDB 2015 — event time, watermarks, and triggers.
• Tyler Akidau, Slava Chernyak, Reuven Lax, Streaming Systems (O'Reilly, 2018).
• Jay Kreps, "Questioning the Lambda Architecture" (2014) — the case for kappa.
• Apache Flink documentation: checkpointing and exactly-once end-to-end.