Design an Ad Click Aggregator: Counting Each Click Once

The same click often reaches the counter more than once, so a plain plus-one counts every copy. Advertisers then pay for clicks that never happened.

Fixing that is the job of an ad click aggregator: the part of an ad system that turns raw clicks into totals people can trust and bill from.

Picture a common setup. An ad network shows banner and video ads on news sites and music apps, called publishers. Advertisers pay the network for each click, and the network shares that money with the publishers. The setup is made up, but the problem is real.

The network handles about 25 million clicks a day, at an average of Rs 2.50 per click. A counting error of just 1 percent moves about Rs 6.25 lakh (625,000) a day.

Its first counter was the naive one, and it is worth seeing why it fails. Each click went into a queue, and a worker added one to a database row for that ad and that minute.

Then the billing team compared one advertiser's dashboard with a recount of the raw click log. The dashboard showed 1.8 lakh (180,000) clicks for the month. The recount said 1.5 lakh.

• Some phones on weak networks had sent the same click twice.
• A bot had clicked one of the advertiser's banners thousands of times in an hour.
• Worst of all, a counting worker had crashed, restarted from an old position in the queue and added 40 minutes of clicks a second time.

Each cause needs a different fix. The rest of this guide rebuilds the counter so each real click counts once, on time, in a way the billing team can check.

An ad click aggregator is a system that collects click events and turns them into totals, such as clicks per ad per minute, for dashboards and billing. To aggregate means to combine many small records into totals.

A few words come up again and again. An event is one record of something that happened, such as one click. A stream is a never-ending flow of events that you process as they arrive.

An impression is one showing of an ad to one person. Every click belongs to an impression, because nobody can click an ad they were never shown. That link turns out to be the key to removing duplicates.

When someone taps a banner, the tap does not go straight to the advertiser. It first reaches the network's click server, which records the click and then redirects the browser to the advertiser's page.

Everything after that record is the aggregator's job. It must carry the event safely, drop copies, count it in the right minute and store totals that can be summed fast.

It needs to count every valid click once, show near-live totals within a few minutes, and give billing a final number that matches the raw log. These goals pull against each other, so write them down first.

• Record each click and redirect the person quickly, so the advertiser's page opens without a visible delay.
• Show advertisers clicks per ad per minute, about three minutes behind real time.
• Let analysts sum clicks by ad, campaign, publisher and state, over any date range.
• Produce a final daily count per advertiser that billing can use and audit.

The non-functional needs, meaning how well the system must work rather than what it does, matter even more here. Counts must not lose or double clicks, because money depends on them. The system must survive a server crash and cope with sudden spikes.

Now the rough maths you would do on a whiteboard. The peak rate, the event size and the number of live ads are assumptions for this setup.

Most ads get no clicks in most minutes, so the real row count is far lower. Object storage, a cheap store for large files such as Amazon S3, can keep a year of raw events.

You can check the first and third rows with one line of Python. It prints clicks per second, then gigabytes per day.

python3 -c "print(25_000_000 / 86_400, 25_000_000 * 300 / 1e9)"
# prints: 289.35185185185185 7.5

It works as a pipeline with four main parts. A click server records each click, a log holds the events, a stream processor removes duplicates and counts, and an analytics store serves the totals.

The log here is Apache Kafka. Kafka keeps an append-only list of events that many readers can read at their own pace. It is split into partitions, separate logs that different readers can work through in parallel. Each event in a partition has a position number called its offset.

Kafka keeps events for a retention period, 168 hours (7 days) by default in Kafka 4.3, then deletes them. Inside that period, a fixed counter can read the log again from an earlier offset, which is called a replay.

The stream processor is Apache Flink, a program that reads events from Kafka continuously and keeps running totals. The analytics store is ClickHouse, a database built for fast sums over billions of rows.

This guide checks its Flink and Kafka claims against Flink 2.3.0 and Kafka 4.3, the latest releases as of 19 October 2026. Flink's Kafka connector is released separately, and its newest release, 5.0.0, lists only Flink 2.1 and 2.2. Our connector claims come from its docs rather than a run, so check compatibility before you pin Flink 2.3. Follow the main path in the diagram, then the side path into the raw archive.

Notice how little the click server does. It hands one event to Kafka and redirects. Slow work such as counting never sits between the person and the advertiser's page.

Whether it waits for Kafka to confirm the write before redirecting is a trade-off. Waiting adds delay to every redirect; not waiting can lose clicks if the server dies.

Notice also the cost of replay. Replay lets you fix a broken counter and run it again, but every replayed event is a chance to count something twice.

Duplicates come from three places: people and bots clicking the same ad again, phones and servers resending a click, and the pipeline reprocessing events after a failure. Each one needs a different fix.

The first kind is a business rule more than a bug. A person who double-taps a banner meant one visit. The network's rule is one billable click per impression, so a second tap on the same impression is not billed.

Bots are a closely related problem. Clicks from bots and click farms are called invalid traffic, and catching them is a field of its own. In this guide, a fraud filter marks such clicks before counting.

The second kind is a delivery problem. A phone sends a click, the network drops the reply, and the phone sends the click again. The click server may also retry its own write to Kafka.

The third kind is the sneakiest. A worker counts some events, crashes before saving its place, then restarts and counts them again. That is exactly what overbilled the advertiser in the opening.

Kafka helps with part of the second kind. A producer is any program that writes to Kafka. Idempotent means doing something twice has the same effect as doing it once. With the idempotent producer, the broker (a Kafka server) gives each producer an ID and drops resent messages by their sequence number.

In Kafka 4.3 this is on by default unless other producer settings conflict with it. It catches retries the Kafka client makes on its own, for example after a broker failure.

It cannot catch a resend that your own code makes by calling send again, and it cannot catch a phone's second request. Each of those is a new message, so the click itself must carry an ID that the counter checks.

You give every click a stable ID, keep a record of the IDs you have already counted, and count a click only when its ID is new. A time limit keeps that record small.

Picture Ganesh visarjan night in a busy city. Each neighbourhood's procession passes two tally points on the way to the lake, and volunteers at both keep tallies.

If the organisers simply add the two tallies, every procession counts twice. So each procession carries a numbered banner, and the desk at the lake ticks numbers off in one register. A number already ticked is never counted again.

Map it part by part. The banner number is the impression ID. The register is the counter's record of seen IDs. Clearing the register after the night is the time limit.

Where the picture breaks: processions do not resend themselves, and the register never crashes. In the real system both happen, which is why the record of seen IDs must survive a restart.

The ID comes from the ad server. When it shows an ad, it writes a fresh impression ID and the showing time into that ad's click link. It signs the link with a secret key, so the click server can tell if anyone changed those values. A second tap or a resent request carries the same impression ID, so the counter can catch it.

The counter keeps the seen IDs in Flink's keyed state. Keyed means the stream is split by impression ID, so every click for one impression reaches the same worker and the same small slice of saved data.

Here is the rule in plain Python, a model of what the Flink job does rather than Flink's own API. The state argument stands for that slice of keyed state, which Flink saves in every checkpoint.

from collections import defaultdict

DAY = 24 * 60 * 60                  # seconds

def first_billable_click(state, click):
    """state is this impression's keyed state, saved in every checkpoint."""
    if click["ts"] - click["shown_at"] > DAY:
        return False                # clicked over a day after the ad was shown
    if state.get("seen"):
        return False                # this impression was already counted
    state["seen"] = True
    return True

states = defaultdict(dict)          # Flink keeps one of these per impression ID
tap = {"imp": "i-901", "ad_id": 77, "shown_at": 1000, "ts": 1030}
retry = dict(tap, ts=1032)          # same impression, sent again
other = {"imp": "i-902", "ad_id": 77, "shown_at": 1000, "ts": 1040}
for c in (tap, retry, other):
    print(c["imp"], first_billable_click(states[c["imp"]], c))

Run it with python3 and it prints three lines: i-901 True, then i-901 False, then i-902 True.

What just happened? The first click on impression i-901 was counted and set the seen flag. The resend found the flag and stopped. A different impression on the same ad counted normally.

Invalid clicks are dropped before this check, so a bot's click never marks an impression as seen. Clicks that arrive more than a day after the showing are not billable under the network's rule, so they stop at the first check.

Forgetting old IDs on click time, not wall-clock time

The seen flag only needs to live for one day after the showing, because older clicks are not billable anyway. Our rough guess is 100 bytes per ID, clean-up timer included, so 25 million IDs come to about 2.5 GB across all workers.

The obvious tool is Flink's state time-to-live (TTL), which deletes a stored value after a set time. The Flink 2.3 docs say only TTLs in processing time are supported, meaning wall-clock time on the machine.

So a one-day TTL means one day of the job's running time, not one day of click time. We checked this on Flink 2.3.0 with a two-second TTL.

Two clicks with the same click time arrived three seconds apart by the wall clock, and the second was counted again. Sent with no gap, the second was dropped.

Pause and predict: a bad deploy stops the job for two days, and it is then restored from its last checkpoint. What happens to a resent click that arrives right after the restart?

It is counted again. Flink stores each value's last-write time with the value, so after two days every remembered ID has expired. A resend of a click counted before the outage now looks new.

The fix is to forget on click time instead. Register an event-time timer for the showing time plus one day, and clear the flag when it fires. An event-time timer fires when the watermark, explained in the next section, passes its time.

Timers are saved in checkpoints along with the state, so the clean-up survives a restart. In our Flink 2.3.0 test, a three-second wall-clock gap no longer re-counted the resend, and each flag cleared when the watermark passed showing time plus one day.

One more trap if you write the job in Python. In our PyFlink 2.3.0 test, a value with a two-second TTL was still returned six seconds later, until we set python.state.cache-size to 0.

The PyFlink docs say this cache is turned off for such state. As of 19 October 2026 that did not match our run, so pin your version and test expiry before you rely on it.

Keeping both in Flink's state means they are saved in the same checkpoint and restored together, so they cannot disagree.

You count each click in the minute it happened, called event time, and use a watermark to decide when a minute is complete enough to publish. Clicks that come even later go to a separate late stream that the nightly recount covers.

Every stream has two clocks. Event time is when the click happened. Processing time is when your server got around to handling it.

For billing, take event time from the click server's clock when the click arrives, not from the phone. A user can change a phone's clock, and the click server sees every click first anyway.

After that point the two clocks drift apart. A click server may buffer events for a few seconds, one Kafka partition may be read behind the others, and a replay reads clicks that are hours old.

Count by processing time, and a replay at noon puts three hours of old clicks into the noon minutes. Flink's docs warn that processing time does not give deterministic results, because it depends on how fast records arrive.

A window is a time bucket for counting. The counter uses tumbling windows: fixed one-minute buckets that do not overlap, so every click belongs to exactly one window.

The hard part is knowing when to close a window. A watermark is a marker that flows with the stream. Flink's docs define a watermark for time t as saying event time has reached t, so no older events should follow.

Back on the visarjan route, the organisers post the 10 pm tally at 10:15, not at 10 sharp, because some processions always run behind. Those fifteen minutes of waiting are their watermark.

The counter's watermark is the newest click time seen, minus two minutes. When the watermark passes the end of a window, that window's count is published. Here is that rule as a plain Python model.

from collections import defaultdict

WINDOW = 60                         # one-minute tumbling windows, in seconds
WAIT = 120                          # watermark trails the newest click by two minutes
counts = defaultdict(int)           # (ad_id, window_start) -> clicks
newest = 0

def t(hms):                         # "19:59:42" -> seconds since midnight
    h, m, s = map(int, hms.split(":"))
    return h * 3600 + m * 60 + s

def hm(sec):
    return f"{sec // 3600:02d}:{sec % 3600 // 60:02d}"

def add(click):
    global newest
    start = click["ts"] - click["ts"] % WINDOW
    if start + WINDOW <= newest - WAIT:          # window already closed
        print("late, sent to the late-clicks topic:", hm(start))
        return
    counts[(click["ad_id"], start)] += 1
    newest = max(newest, click["ts"])
    publish_closed()

def publish_closed():
    watermark = newest - WAIT
    for key in [k for k in counts if k[1] + WINDOW <= watermark]:
        print("publish ad", key[0], hm(key[1]), "clicks", counts.pop(key))

for ts in ["19:59:42", "19:59:50", "20:00:05", "20:02:10", "19:59:58", "20:03:30"]:
    add({"ad_id": 77, "ts": t(ts)})

Pause and predict before you run it: the 19:59:58 click arrives after the 20:02:10 click. Which window gets it?

Running it prints three lines: publish ad 77 19:59 clicks 2, then late, sent to the late-clicks topic: 19:59, then publish ad 77 20:00 clicks 1.

What just happened? The 20:02:10 click moved the watermark to 20:00:10, past the end of the 19:59 window, so that window was published with 2 clicks. The 19:59:58 click came after that, so it went to the late stream.

We sent the same clicks, plus one resend and one bot click, through a real Flink 2.3.0 job. It published the same 19:59 count of 2, put the 19:59:58 click in its late output and dropped the other two.

Flink's built-in watermark for a fixed delay is the newest timestamp minus the delay minus one millisecond. A window fires when the watermark reaches the window's last millisecond, so the model above closes windows at the same watermark value.

A real job has one more twist. With the watermark strategy set on the Kafka source, each partition gets its own watermark. Flink uses the minimum of them, so one slow or idle partition holds every window back. Setting idleness on the watermark strategy, withIdleness in the Java API, lets Flink skip a partition that has gone quiet.

Flink calls extra waiting after the watermark allowed lateness. Its default is zero, so by default late elements are dropped. You can allow some lateness so a window fires again with a new total, or send late events to a side output, a separate stream for them.

The trade-off is plain. With the two-minute wait, each minute's count appears about three minutes after that minute starts. A longer wait means fewer late clicks but slower dashboards. Pick the wait by measuring how late your real clicks arrive, then alert when the late stream grows.

A 2015 paper on the Dataflow Model makes the bigger point. It argues that with endless data you never know whether you have seen all of it, only that new data will keep arriving. So design for late data from the start.

Flink's docs add a limit of their own. A job can only wait a finite time, and that limits how deterministic event-time results can be. On a replay, slightly different clicks may count as late, so live totals can shift a little.

You save the counter's state and its place in the log together, in one checkpoint, and you write totals as rows that a replay overwrites. A restart then repeats work, but once totals are read the right way, shown below, no click counts twice.

A checkpoint is a saved snapshot. Flink regularly saves every worker's state, here the seen IDs and the open window counts, together with the Kafka offsets it has read up to.

Checkpointing is off by default in Flink 2.3, so you must turn it on with an interval. Once on, its default mode is exactly-once, the mode this section relies on.

After a crash, the job reloads the last checkpoint and rewinds Kafka to the saved offsets. Events after the checkpoint are read again, but the state is back where it was, so nothing is counted twice inside the job.

This is also why moving a consumer group's offsets by hand does not steer a job restored from a checkpoint. A consumer group is Kafka's name for a set of readers that share saved positions. Flink's Kafka source keeps its offsets in the checkpoint, and its docs say the offsets it commits to Kafka are only for monitoring.

Pause and predict: the job published the 19:59 window's total of 412 clicks, then crashed before the next checkpoint. What happens to that row on restart?

The job replays from the checkpoint and publishes the 19:59 window again. If each publish adds to the stored count, the row becomes 824. That is the opening's 40-minute bug in one line.

So never send a plus one. Send the full total as a row keyed by ad and minute, and let a repeat replace it. That makes the write idempotent, like Kafka's producer: repeating it changes nothing.

In ClickHouse, the usual way to replace rows by key is the ReplacingMergeTree table engine. ClickHouse stores each insert as a separate part on disk and merges parts later. The engine keeps one row per sorting key, here ad and minute, but only during those background merges.

You do not control when a merge runs. Until it does, both copies are stored, and adding FINAL to a query gives the merged answer now. You can check this on any ClickHouse. We ran it on ClickHouse 26.9 through chdb, an embedded build for Python.

CREATE TABLE minute_clicks (
    ad_id UInt32,
    window_start DateTime,
    clicks UInt64
) ENGINE = ReplacingMergeTree
ORDER BY (ad_id, window_start);

INSERT INTO minute_clicks VALUES (77, '2026-10-17 19:59:00', 412);
INSERT INTO minute_clicks VALUES (77, '2026-10-17 19:59:00', 412);

SELECT sum(clicks) FROM minute_clicks FINAL;

The last query prints 412. Without FINAL, the same sum printed 824 in our run, because the two inserts were still separate parts. A plain MergeTree table keeps both rows for good.

If a late click makes Flink fire the window again with 413, insert that row too. Without a version column, ReplacingMergeTree keeps the last inserted row, so FINAL then returns 413.

Kafka's own docs give the general rule. A consumer is any program that reads from Kafka. The docs suggest storing a consumer's offset in the same place as its output.

Their reason is that many output systems cannot join a two-phase commit, a protocol where several systems agree to commit together. Overwriting rows by key gets the same effect without one.

Flink's Kafka sink also has an exactly-once mode. It writes results inside a Kafka transaction, a group of writes that become visible together or not at all. The transaction commits on each checkpoint. Readers set to read only committed data see no duplicates, but they see nothing until the checkpoint completes.

The connector docs also warn that Kafka can expire an open transaction and lose data. Set transaction.timeout.ms well above your longest checkpoint plus restart time. For a store that replaces rows by key, the overwrite approach is simpler.

Our guide on delivery guarantees covers why exactly-once usually means effectively-once. This design shows it: events may be read twice, yet the final totals look as if each was read once.

The team now follows four habits:

• Checkpoint every 30 seconds, so a restart repeats about half a minute of work, plus the time a checkpoint takes.
• Key every output row by ad and window start, never by an auto-generated row number, and read totals with FINAL.
• Alert when consumer lag, how far the counter is behind the newest events in Kafka, grows past two minutes.
• Restart from a checkpoint or a savepoint, a snapshot you take on purpose, never from offsets moved by hand.

They are checked against a slower batch recount every night, and billing uses the batch number. The live stream serves dashboards and alerts, and the nightly job is the record of truth.

Turning streamed ad clicks into billing numbers is not a new problem. A 2013 paper describes Photon, a system that joins web search queries with ad clicks and survives the loss of a whole datacentre.

Its joined logs are used for business metrics, including billing for advertisers. The paper promises no duplicates in the output at any point in time, most joinable events in near real time, and exactly-once results eventually. Photon reaches exact results inside one streaming system. This design takes a different route to exact numbers: a separate nightly recount.

The nightly job reads yesterday's raw clicks from the archive. It applies the same rules as the stream: drop invalid clicks, one click per impression, count by event time. Then it compares totals per advertiser.

Follow the two arrows into the question box. The comparison is the only place where the fast numbers and the exact numbers meet.

Most nights the gap is tiny, made of late clicks. A big gap means a bug, a bad deploy or a partition nobody was reading. That is exactly when the billing team wants to know, before any invoice goes out.

Batch alone is not enough. Advertisers want to see a campaign working within minutes, and the fraud team needs to spot a click storm while it is happening.

Lead with the money: every click is billed, so the design is about counting each valid click once. Then walk the pipeline and spend most of your time on duplicates and late data.

1. 1

   Pin down the requirements

   Ask about click volume, how fresh dashboards must be, and whether counts are used for billing. Billing changes how strict every later choice must be.

2. 2

   Estimate the load

   Turn daily clicks into average and peak per second. Then work out raw bytes per day and total rows per day.

3. 3

   Draw the pipeline

   Click server, Kafka, stream processor and analytics store, plus a raw archive. Keep the click server's job tiny so redirects stay fast.

4. 4

   Name the duplicate sources

   People and bots, resent clicks, and replays after a crash. Give the fix for each: impression IDs, the idempotent producer, and checkpoints with writes that replace totals.

5. 5

   Explain time

   Count by event time in one-minute tumbling windows. Close windows with a watermark, send late clicks to a late stream, and forget seen IDs on click time.

6. 6

   Close the loop

   A nightly recount from the raw archive is the billing number. The live path exists for speed, and a gap between them raises an alert.