Everyone thinks Google Analytics is a counter with a chart on top. “Drop a snippet on every page, count the pageviews, draw a line graph, done.” Then the interviewer piles on the constraints that make it a real system: it is not one website, it is billions of them, each a separate tenant that must only ever see its own data; it is not a thousand hits, it is 100 billion events a day landing from every corner of the internet; the product does not show one number, it shows a live “users on site right now” ticker and “sessions per day for the last 18 months” and “of everyone who saw the product page, what fraction added to cart then checked out” and “of the users who first showed up in March, how many came back in April.” Those are four completely different query shapes over the same firehose, and one of them (unique users) cannot even be answered exactly at this scale without melting.

Google Analytics is deceptive because the naive INSERT per hit demos fine on one site and dies the instant you have a multi-tenant firehose, a dashboard that wants both live and 18-months-back, and analytical questions (funnels, cohorts, unique users) that are not counts at all but sequences and set operations over billions of rows. The whole design comes down to five decisions: how you collect an un-droppable, multi-tenant event stream at the edge (a beacon into a durable log, not a database write); how you group raw hits into sessions when events arrive late, out of order, and span a 30-minute idle gap (stateful stream sessionization); how you serve a real-time “right now” view and an historical “last 18 months” view from paths with opposite performance profiles (two engines, not one); how you answer funnels and cohorts which are ordered-sequence and retention questions, not GROUP BY COUNT; and how you count unique users over billions of events without storing every user ID (probabilistic sketches). All of it multi-tenant, so tenant A’s viral spike never starves tenant B.

Let me do it properly.

Functional Requirements (FR)

In scope:

  • Collect hits and events. A snippet (gtag.js style) on a customer’s page fires a beacon on pageview, click, scroll, purchase, or any custom event, carrying the property ID, an anonymous client/user ID, the URL, referrer, device, geo, and a bag of custom dimensions. This is the firehose.
  • Sessionize. Group a user’s hits into sessions - a session is a run of activity that ends after 30 minutes of inactivity (or at midnight, or on a new campaign source). Sessions are the unit almost every GA metric is built on.
  • Real-time reports. “Active users in the last 5 minutes,” “top pages right now,” “events per second,” updating every few seconds. A live view of the last ~30 minutes.
  • Historical reports. “Sessions, users, bounce rate, and pageviews per day for this property over any range up to 18 months,” sliced by any dimension (source, medium, country, device, landing page), sub-second.
  • Funnels. “Of users who did step A (viewed product), how many went on to do B (add to cart) then C (purchase), in order, within the window?” With drop-off at each step.
  • Cohorts / retention. “Of users who first appeared in week W, what fraction returned in W+1, W+2, …?” The retention grid.
  • Unique users / uniques. Distinct users and distinct sessions per dimension - which at this scale is the hard-to-count metric.

Explicitly out of scope (say it so you own the scope):

  • Cross-device identity resolution. Stitching the same human across phone, laptop, and logged-in account is an identity graph problem (deterministic + probabilistic matching). We count on a client/user ID as given and note where the identity service would sit.
  • Ad attribution / bid. Deciding which ad or campaign gets credit for a conversion days later is a separate attribution system. We record source/medium on the session; we do not run the attribution model.
  • Bot and spam filtering. Distinguishing a real visitor from a crawler or a spam referral is a large filtering system; we note the filter gate on the ingest path and assume hits reaching aggregation are the ones worth counting.
  • BI / SQL export (BigQuery-style raw access). Letting a customer run arbitrary SQL over their raw hit stream is a real GA feature but is just “expose the raw log per tenant to a query engine.” We design the pipeline that produces it and mention it, but the reporting product is the focus.
  • PII governance / consent. Consent modes, IP anonymization, and data-retention deletion are legally essential and we note where they gate the pipeline, but the policy engine is its own thing.

The one decision that drives everything: Google Analytics is a multi-tenant, write-heavy event pipeline whose reads are not counts but four different analytical shapes - live, historical, sequential (funnels), and retention (cohorts) - over a firehose too big to store per-user-ID exactly. So the design is dominated by collecting an un-droppable multi-tenant stream into a durable log, sessionizing it statefully, pre-aggregating it into rollups a columnar store can serve sub-second, and answering the sequence/set-heavy questions with purpose-built structures (sequence scans, retention grids, HyperLogLog) rather than pretending they are GROUP BY COUNT.

Non-Functional Requirements (NFR)

  • Scale: 100B events/day. That averages ~1.15M events/sec, but web traffic follows the sun and spikes on events (a product launch, a Black Friday), so peak is ~5M events/sec. Reads are far lighter than writes - customers open dashboards, low hundreds of thousands of report queries per second globally at most, heavily cacheable and served from pre-aggregated rollups.
  • Multi-tenancy: on the order of billions of properties (websites/apps), with an extreme long tail - a few huge tenants (a global retailer) generate more traffic than millions of tiny blogs combined. Isolation is a hard requirement: tenant A must never see tenant B’s data, and A’s traffic spike must not degrade B’s ingestion or queries.
  • Latency: real-time report freshness under ~10 seconds (hit happening to it showing in “active users”). Historical report query p99 under ~500ms (served from rollups). Funnel/cohort queries are heavier and may take a few seconds for a fresh compute, but common ones are pre-materialized.
  • Availability: 99.9%+ on collection. The beacon endpoint must stay up globally; a customer whose page is loading cannot wait on us, so collection is fire-and-forget from the browser’s side and durable on ours. Report reads can degrade to slightly stale rather than error.
  • Consistency: the whole product is eventually consistent. A pageview showing up in reports a few seconds late is fine; a daily total that is exact by the next day is fine. Nothing here is money-moving per-event, so we can trade exactness for scale where it pays - notably unique-user counts, which are served approximately (within ~1-2%) rather than exactly.
  • Durability: high for accepted hits. Once the beacon is durably logged we should not lose it, because reports are recomputed from it. Raw hits are retained (weeks hot, months-to-years cold) so any rollup, funnel, or cohort can be recomputed - the reports are a derived, rebuildable view of an immutable hit log.
  • Correctness: approximately-once is acceptable. Unlike an ad-click biller, a double-counted pageview is a rounding error, not an overcharge. We still deduplicate obvious retries, but we spend our correctness budget on sessionization and dimensional consistency, not on exactly-once billing guarantees.

Back-of-the-Envelope Estimation (BoE)

Let me ground the design in numbers.

Traffic:

Events/day:                100,000,000,000   (100B)
Average QPS = 100B / 86,400 sec ≈ 1,150,000 QPS   (misleading average)
Web traffic is diurnal + spiky (launches, sales, viral):
Peak QPS (say ~4-5x avg)  ≈ 5,000,000 QPS   (the number we size for)

The daily average of ~1.15M QPS is a trap. Traffic concentrates in business hours across overlapping time zones and spikes hard on marquee events, so we size collection for ~5M events/sec, not 1.15M.

Read vs write:

Writes (hits):    ~5,000,000/sec peak. The firehose. Multi-tenant, un-droppable.
Reads (reports):  customers opening dashboards + scheduled/API pulls.
   Millions of active properties, dashboards auto-refresh, plus the API.
   Order of hundreds of thousands of QPS globally, heavily cacheable and
   served from pre-aggregated rollups, not raw hits.

The asymmetry is large: writes outnumber reads by ~10-50:1, the writes are a multi-tenant firehose, and the reads are four different analytical shapes. That shape - a huge un-droppable write stream feeding a small set of pre-aggregated, analytically-diverse read surfaces - is why this is a stream + OLAP pipeline, not a request/response database.

Storage:

Raw hit size:  property_id, client_id, event_name, url, referrer, ts, geo,
               device, ~10 custom dims ≈ ~400 bytes on the wire,
               ~150 bytes packed columnar.

Raw hits/day:  100B * 150 bytes ≈ 15 TB/day of raw events (packed).
Retain raw ~30 days hot for recompute:  ~450 TB hot.
Tier older raw to cold object storage for months/years of audit + reprocess.

Session records/day:  ~100B hits at, say, ~8 hits/session -> ~12.5B sessions/day.
               ~300 bytes/session (dims + first/last ts + hit summary)
               ≈ ~3.75 TB/day of session records.

Rollups are SMALL relative to raw:
  pre-aggregated (property, day, source, country, device) cubes are counts,
  not events -> tens to low hundreds of GB/day depending on cardinality,
  and coarser rollups (per-week, per-month) are orders of magnitude smaller.

The raw hit log is fat (~15 TB/day) but cheap and sequential; the rollups the dashboard reads are small because they are counts, not hits. This split - fat immutable raw log, thin queryable rollups - is the storage shape of every good analytics pipeline. Uniques are the exception: to count them exactly you would need every user ID, which is why we use sketches (below) that are kilobytes, not gigabytes.

Bandwidth:

Ingest: 5M events/sec * 400 bytes ≈ 2 GB/sec ≈ 16 Gbps inbound at peak,
        spread across dozens of edge PoPs worldwide.
Query:  report responses are small aggregates, a few KB each,
        hundreds of thousands/sec -> low single-digit GB/sec. Cacheable.

Ingest bandwidth is real (~16 Gbps) but spread across a global edge; the scarce resources are write throughput into a durable log (5M/sec, multi-tenant), stateful sessionization (grouping hits with a 30-min idle gap under late arrival), and query engines for four different report shapes. Size for those.

High-Level Design (HLD)

The system is a multi-tenant stream + OLAP pipeline, not a CRUD app. Beacons hit a global edge, get validated and written to a durable partitioned log (Kafka). A stream processor (Flink) sessionizes the hits and folds them into rollups. Real-time reports read from a hot in-memory store fed directly by the stream; historical reports read from a columnar OLAP store; funnels and cohorts run over the raw sessionized log (pre-materialized when common). A batch path recomputes exact daily rollups from the immutable raw log.

              COLLECTION PATH (beacons, ~5M/sec peak, MULTI-TENANT, un-droppable)
   ┌────────────────────────────────────────────────────────────────────────────┐
   │  gtag.js / SDK  --collect beacon-->  Global CDN Edge / Collector (many PoPs) │
   │                                      - validate property_id + payload        │
   │                                      - bot/consent gate, geo from IP         │
   │                                      - 204 No Content (fire-and-forget)       │
   └───────────────────────────────────────┬──────────────────────────────────────┘
                                            │  produce (partitioned by property_id + client_id)
                              ┌─────────────▼──────────────┐
                              │   KAFKA  (durable hit log)  │  source of truth
                              │   topic: hits, N partitions │  retained ~30 days
                              │   tenant-fair quotas        │
                              └───┬───────────────────┬─────┘
                real-time         │                   │            batch / exact
                (speed layer)     │                   │            (accuracy layer)
                   ┌──────────────▼───────┐   ┌────────▼───────────────────────┐
                   │  Flink: SESSIONIZE   │   │  Raw Archiver -> Object store   │
                   │  - keyBy(property,   │   │  (Parquet, dt/hr partitioned)   │
                   │    client_id)        │   └────────┬───────────────────────┘
                   │  - session window    │            │ hourly / nightly
                   │    (30-min gap)      │   ┌────────▼───────────────────────┐
                   │  - roll to counts    │   │  Batch Recompute (Spark)        │
                   │  - HLL for uniques   │   │  - exact daily rollups,         │
                   └───┬──────────┬───────┘   │    cohorts, funnels from raw    │
          live counts  │          │ rollups   │  - overwrites approx periods    │
              ┌────────▼───┐  ┌───▼───────────▼─┐
              │ REAL-TIME  │  │  OLAP STORE      │◄── serves historical
              │ store      │  │ (Druid/ClickHouse│    reports + slices
              │ (Redis/    │  │  /BigQuery)      │
              │  Pinot RT) │  │  rollups + HLL   │    ┌──────────────────────┐
              │ last ~30m  │  │  cubes per tenant│    │ SESSION LOG (columnar)│
              └────┬───────┘  └───┬──────────────┘    │ funnel/cohort scans   │
                   │              │                   └───┬───────────────────┘
                   │  READ PATH (reports, tenant-scoped) │
              ┌────▼──────────────▼───────────────────────▼──┐
              │  Query / Reporting Service (per-tenant authz) │
              │  + result cache; routes to RT vs OLAP vs scan │
              └────────────────────┬──────────────────────────┘
                          ┌────────▼────────┐
                          │  GA Dashboard / │
                          │  Reporting API  │
                          └─────────────────┘

Request flow - a hit (the write hot path):

  1. The page’s snippet fires a beacon (POST /collect or an image/sendBeacon) carrying property_id, an anonymous client_id (a first-party cookie / storage value), the event, URL, referrer, and custom dimensions. The browser uses navigator.sendBeacon so the hit survives page unload and does not block navigation.
  2. The nearest edge collector (one of many global PoPs) validates the property_id, applies the bot/consent gate, resolves geo from the IP, and does nothing else expensive. It appends the hit to Kafka, partitioned by (property_id, client_id), and returns 204 No Content. Collection is fire-and-forget from the browser’s view; durability is our problem, solved by the log.
  3. Kafka is the durable buffer and source of truth. It absorbs the 5M/sec spike, enforces per-tenant quotas so one property’s flood cannot starve the topic, and retains hits ~30 days so any rollup, funnel, or cohort can be recomputed.
  4. The Flink stream processor consumes hits, keyed by (property_id, client_id), and sessionizes with a 30-minute inactivity gap. As sessions form and events land, it rolls counts (pageviews, sessions, events per dimension), updates HyperLogLog sketches for uniques, and writes the sessionized records to the session log.
  5. Flink feeds two sinks: the real-time store (last ~30 minutes, for the live view) directly, and the OLAP rollup cubes for historical reports. Within seconds the live view reflects the hit.

Request flow - the read paths (all tenant-scoped):

  1. Real-time report: the Reporting Service authorizes the caller for that property_id, then reads “active users / top pages, last 5-30 min” from the real-time store. Sub-second, approximate, always the freshest.
  2. Historical report: reads pre-aggregated rollup cubes from the OLAP store for the requested range/dimensions - never raw hits. “Sessions per day, last 90 days, by country” is a few hundred tiny rows. Sub-500ms and cacheable.
  3. Funnel / cohort: runs a sequence or retention scan over the session log (or reads a pre-materialized result for common definitions). Heavier; cached and often precomputed nightly.
  4. Uniques: distinct users/sessions come from HLL sketches stored alongside the rollups - mergeable across dimensions and time buckets, approximate within ~1-2%.

The key architectural insight: the raw hit log is the source of truth, and every report - live counts, historical cubes, funnels, cohorts, uniques - is a derived, recomputable view of it. Collection’s only job is to get the hit durably into the log fast and fairly across tenants; all the hard aggregation happens asynchronously downstream and can be re-run. That decoupling lets us absorb a 5M/sec multi-tenant spike (Kafka is the shock absorber), serve a live-ish view immediately, serve historical cubes sub-second, and still recompute exact daily numbers from the same immutable log.

Component Deep Dive

Five hard parts: (1) collecting an un-droppable multi-tenant firehose fairly, (2) sessionizing a hit stream statefully under late/out-of-order arrival, (3) serving real-time and historical reports from opposite-profile paths, (4) answering funnels and cohorts which are sequence and retention questions, and (5) counting unique users at scale without storing every ID. I will walk each from naive to scalable.

1. Collecting the Firehose: Multi-Tenant, Un-Droppable

The write peak is ~5M hits/sec from billions of properties, and one tenant must not starve another.

Approach A: Beacon writes straight to a database (the naive instinct)

The collector inserts a row per hit, or increments a per-property counter, synchronously in a database.

INSERT INTO hits (property_id, client_id, event, url, ts, ...) VALUES (...);
-- or the "clever" version:
UPDATE counters SET pageviews = pageviews + 1 WHERE property_id = ? AND day = ?;

Where it breaks: at 5M writes/sec no relational database keeps up - a million-plus durable inserts a second with index maintenance is hopeless. The counter UPDATE is worse: a huge tenant’s counter row becomes a single contended lock every hit serializes on, so a Black Friday retailer turns into a lock convoy that also blocks the shared database for every other tenant. There is no fairness: one flood degrades everyone. And a spike above the write ceiling has nowhere to go - you block the beacon (the customer’s page stalls, unacceptable) or drop hits. The database also couples collection speed to aggregation logic. Wrong tool.

Approach B: Edge collectors appending to a durable, tenant-fair log

Split “accept the hit durably” from “aggregate the hit.” The edge collector does only the first, as fast as possible, close to the user:

Edge Collector (stateless, global PoPs, horizontally scaled):
   validate(property_id, payload)   # known property, schema, size limits
   gate(bot?, consent?)             # drop/tag before it enters the pipeline
   enrich(geo from IP, device from UA, server ts)
   produce("hits", key=(property_id, client_id), value=hit)  # append to Kafka
   return 204                       # fire-and-forget; durability is ours

Kafka is built for exactly this: a partitioned, replicated, append-only log that sustains millions of writes/sec because appends are sequential with no per-hit indexes or random updates. Producers never block on consumers - a 5M/sec spike is just written and drained downstream at its own pace. This is the shock absorber.

  • Partitioning: key by (property_id, client_id) so all of one user’s hits land on one partition in order - which sessionization needs (a session is per user) - while still spreading a big tenant’s users across many partitions. Keying by property_id alone would hotspot one partition for a huge tenant; adding client_id spreads the load while keeping each user’s stream together.
  • Tenant fairness / quotas: enforce per-property rate quotas and Kafka client quotas so one property’s flood is throttled (or shunted to an overflow topic) rather than consuming the whole cluster. A viral tenant gets its own dedicated partitions/topic; the long tail of tiny sites shares pooled partitions. This is the isolation NFR made real at the log.
  • Replication + durability: replication factor 3, acks=all; the collector’s 204 is honest because the hit is un-loseable before the browser moves on.
  • Edge, not central: collectors run at many PoPs so the beacon terminates near the user (low latency, high availability), then forwards into regional Kafka. Losing a PoP just reroutes beacons; nothing stateful lives there.
  • Retention: keep raw hits ~30 days hot; this is the log the batch path replays and the safety net for rebuilding any report.

Collectors are stateless, scaled horizontally worldwide, and lose nothing when one dies. All the expensive, stateful work moved downstream where it can be batched, parallelized, and re-run - and, crucially, where per-tenant fairness can be enforced without a shared lock.

2. Sessionization: Hits -> Sessions Under Late and Out-of-Order Arrival

Almost every GA metric (sessions, bounce rate, session duration, source attribution, funnels) is built on the session: a run of one user’s hits that ends after 30 minutes of inactivity. Turning an unordered hit stream into sessions is the stateful heart of the pipeline.

Approach A: Batch job groups hits by user each night (naive)

Nightly, scan the day’s hits, sort each user’s hits by time, and cut a new session wherever the gap exceeds 30 minutes.

Where it breaks: latency and correctness. Nightly means “sessions today” is a day stale - no real-time view at all. Sorting every user’s hits over the full day is expensive and re-does work every run. Worse, sessions cross the batch boundary: a session starting at 23:55 and continuing to 00:10 gets split by a midnight-bounded batch, or double-counted. And late hits (a mobile client that was offline) that arrive after the batch ran are simply missed. Batch is fine as the exact nightly reconciliation, but it cannot be the live path.

Approach B: Stream sessionization with session windows and watermarks

Run Flink keyed by (property_id, client_id) with a session window - a window that stays open while hits keep arriving and closes after a 30-minute inactivity gap:

keyBy(property_id, client_id)
  .window(EventTimeSessionWindows.withGap(30 minutes))
  .aggregate(sessionAggregator)     # first/last ts, hit list, entry/exit, source
  -> emit session record; roll up counts + HLL

Three ideas make this correct and fast:

  • Event time, not processing time. A hit belongs to the session of when it happened, using the hit’s own timestamp, not when we processed it. A processing hiccup must not fabricate a session boundary. Flink windows by event time.
  • Watermarks + allowed lateness for out-of-order hits. Hits arrive late and out of order (offline mobile buffers, slow networks). A watermark asserts “I have seen all hits up to time T.” The session window closes when the watermark passes last-hit + 30 min + a grace period. A hit that lands within the grace but for an already-idle user re-opens or merges into the session correctly; a hit later than the grace goes to a side output for the batch path to fold in. This grace is the explicit fresh-vs-settled knob.
  • Incremental state, dynamic merge. Flink keeps only the running session aggregate per key (first/last timestamp, hit summary, entry page, source/medium), not raw hits, so state is bounded. Session windows merge: if a late hit falls between two windows that are now within 30 minutes of each other, Flink merges them into one session - exactly the semantics GA needs. State is checkpointed with Kafka offsets so a restart replays without losing or duplicating sessions.
user's hits (event time):  10:00  10:07  10:20  ...gap>30m...  11:15  11:20
sessionization:            [ session 1: 10:00-10:20 ]          [ session 2: 11:15-11:20 ]
a late hit at 10:25 arriving at 10:40  -> extends session 1 (still within gap)
a late hit at 10:25 arriving at 12:00  -> past grace -> side output -> batch reconciles

Each emitted session record (property, client, start/end, entry/exit page, source/medium, device, geo, ordered hit list, event counts) is written to the session log (columnar) - the substrate funnels and cohorts scan - and simultaneously rolled into the count cubes and HLL sketches. Sessionization is where “a pile of hits” becomes “a story,” and doing it in the stream is what makes the live view possible while the batch path guarantees the boundary-crossing and very-late cases are eventually exact.

3. Real-Time vs Historical: Two Engines, Not One

The product shows a live “users in the last 5 minutes” ticker and “sessions per day for the last 18 months.” These have opposite profiles: the live view is tiny (last ~30 min) but must be seconds-fresh and update constantly; the historical view is enormous (18 months x every dimension) but only needs to be minutes-fresh and is read as pre-aggregated slices.

Approach A: One store for both (naive)

Serve every report from a single store - either the OLAP rollup store or a big time-series database.

Where it breaks: whichever you pick is wrong for the other. Serve the live ticker from the historical OLAP store and you are paying its write-batching / segment-commit latency (tens of seconds to minutes), so “real-time” is stale. Serve the 18-month report from the real-time store and you are trying to keep 18 months of every-dimension data in a hot, high-write store sized for the last 30 minutes - it will not fit and every long-range scan thrashes it. One engine cannot be both a low-latency ring buffer and a deep columnar warehouse.

Approach B: A speed layer and an accuracy layer, from the same stream

Split by profile and feed both from the sessionization stream:

  • Real-time store (speed layer): a small, hot store (Redis, or a real-time OLAP like Apache Pinot’s real-time tier / Druid’s real-time nodes) holding only the last ~30 minutes, updated directly by Flink every few seconds. It answers “active users now,” “top pages now,” “events/sec,” “conversions in the last N minutes.” Data ages out of it continuously - it is a ring buffer, not an archive. Approximate and disposable.
  • Historical store (accuracy layer): a columnar OLAP store (Druid / ClickHouse / BigQuery-style) holding pre-aggregated rollup cubes per tenant: (property_id, date, source, medium, country, device, landing_page) -> {sessions, users(HLL), pageviews, events, bounces, duration_sum}. Rolled up minute -> hour -> day -> week -> month. A historical query reads the coarsest granularity covering its range, sliced by the requested dimensions - tens to hundreds of rows, sub-500ms, cacheable.
  • Reconciliation: the streaming path writes approximate rollups for the current, open period; the batch path recomputes exact daily rollups, cohorts, and funnels from the immutable raw log after each period closes and overwrites the approximate values. Historical reports for closed days read the exact batch numbers; the live tail reads the streamed approximate numbers.
    time --------------------------------------------------->
    [ closed days: EXACT batch rollups in OLAP ] [ today: APPROX stream rollups ] [ last 30m: RT store ]
                     historical reports read here         historical tail here        live ticker here
    batch recomputes each closed day from the raw log and overwrites the approx cube.
Concern Real-time (speed) Historical (accuracy)
Window last ~30 minutes up to 18 months
Freshness seconds minutes (stream), exact next day (batch)
Store hot ring buffer (Redis / RT-OLAP) columnar OLAP rollup cubes
Query shape “now” counts, tiny scan grouped range over cubes
Correctness approximate exact once batch-verified
Powers live ticker, “right now” dashboards, trends, exports

Both layers can coexist cleanly for the same reason as always: reports are a derived view of the immutable raw log. The batch path can always recompute the truth; the stream path is a fast, disposable approximation the truth eventually replaces.

4. Funnels and Cohorts: Sequences and Retention, Not GROUP BY COUNT

Funnels and cohorts are the questions that expose why a plain rollup cube is not enough. A funnel asks about an ordered sequence per user; a cohort asks about set membership over time per user. Neither is a GROUP BY COUNT over pre-aggregated counts - both need per-user event sequences.

Approach A: Answer them from the rollup cubes (naive)

Try to compute “viewed product -> added to cart -> purchased” by reading the count of each event from the cube and dividing.

Where it breaks: the cube has counts, not sequences. “1M product views, 200K add-to-carts, 50K purchases” tells you nothing about order or the same user: maybe the 200K who added to cart never viewed the product page (deep-linked), and the 50K purchasers are a different set again. A funnel requires that the same user did A then B then C, in order, within a window - information the count cube has thrown away. Cohorts are the same problem: “users first seen in March who returned in April” needs each user’s first-seen date and return dates, which counts do not carry. You cannot reconstruct per-user sequences from aggregates.

Approach B: Sequence and retention scans over the session log, pre-materialized

The session log already holds per-user, ordered event sequences (that is what sessionization produced), so run the right kind of scan over it:

  • Funnels = ordered sequence match. For a funnel A -> B -> C over window W, scan each user’s events in the session log and check whether A occurs, then B after A, then C after B, all within W. Report the count surviving each step; the drop-off is the difference. In an OLAP engine that supports it, this is a native funnel/windowFunnel / MATCH_RECOGNIZE operator (ClickHouse windowFunnel, Druid, or a Flink CEP job over the stream). Because it is per-user sequence matching, it must run over the raw per-user event stream, not the cube.
windowFunnel(window=1h, events per user ordered by ts):
   step1: event = 'view_product'
   step2: event = 'add_to_cart'   (after step1)
   step3: event = 'purchase'      (after step2)
-> returns the furthest step each user reached; count users per step = funnel.
  • Cohorts = retention grid. Assign each user a cohort key = their first-seen bucket (week/month), computed once and stored on the user’s profile/state. Then for each later bucket, count distinct users of cohort C who were active - a set intersection: active_in(week N) ∩ first_seen(week C). Materialize this as a retention grid (cohort_week, offset_week) -> retained_users. At scale the distinct-user counts are again HLL sketches so intersections/unions are cheap and mergeable.
              W+0    W+1    W+2    W+3
cohort Mar-W1  100%   38%    24%    19%
cohort Mar-W2  100%   41%    26%     -
cohort Mar-W3  100%   35%     -      -
each cell = |active_in(W+k) ∩ cohort| , stored as HLL for cheap merges.
  • Pre-materialize the common ones, compute the rest on demand. Standard funnels and the retention grid a customer looks at daily are computed by the nightly batch job and stored, so opening the report is a cache read. Ad-hoc funnel definitions run a bounded on-demand scan over the (columnar, tenant-partitioned) session log, cached after first compute. This keeps the interactive p99 sane while still allowing arbitrary questions.
  • Tenant partitioning of the session log. The session log is partitioned by property_id (then by date), so a funnel/cohort scan for one tenant touches only that tenant’s data - both an isolation and a performance requirement. A huge tenant gets its own segments; small tenants share.

The lesson: match the storage to the question. Counts live in cubes; sequences and set-membership-over-time live in the session log where per-user order is preserved, and the expensive ones are precomputed.

5. Counting Unique Users at Scale: Sketches, Not Sets

“Unique users” and “unique sessions” per dimension are the metric that cannot be a simple COUNT. Distinct-counting requires remembering which IDs you have seen; at 100B events/day across billions of dimension combinations, storing the ID sets is impossible.

Approach A: Store the set of user IDs per bucket (naive)

For each (property, day, dimension) keep a set of client_ids and take its size.

Where it breaks: memory. A single big tenant can have hundreds of millions of daily users; multiply by every dimension slice (source x country x device x landing page x …) and by every property and every day, and you are storing astronomically many ID sets. You also cannot combine them cheaply - “unique users across these three countries” needs a set union, so you must keep the raw sets, not just the counts, forever. It does not fit, and merges are expensive.

Approach B: HyperLogLog sketches - approximate, tiny, mergeable

Replace each ID set with a HyperLogLog (HLL) sketch: a fixed ~12-16 KB structure that estimates cardinality within ~1-2% error by hashing IDs and tracking the maximum run of leading zeros seen (rare hash patterns imply many distinct inputs).

  • Constant size regardless of cardinality. An HLL for 10 users and one for 500M users are both ~12 KB. So a (property, day, dimension) -> HLL cube is small even with huge cardinality, and we store one sketch per bucket instead of a set.
  • Mergeable = combinable across time and dimensions. The killer property: HLL(A) ∪ HLL(B) is a cheap element-wise max, and its cardinality estimates |A ∪ B|. So “unique users this week” = merge the 7 daily sketches; “uniques across these countries” = merge those countries’ sketches - all without touching raw IDs. This is what makes uniques answerable for arbitrary ad-hoc ranges and slices at report time. Retention-grid cells (set intersections) use HLL too, via inclusion-exclusion or MinHash where exact intersection is needed.
  • Flink maintains sketches inline. As sessionization processes hits, it updates the relevant HLL sketches per dimension bucket alongside the plain counts, and the batch path recomputes exact-as-HLL sketches nightly. Sketches live next to the rollup cubes in the OLAP store.
  • When exact is required. For the rare case a customer needs an exact unique count (small property, compliance export), fall back to a COUNT DISTINCT over that tenant’s session log for the range - affordable precisely because that tenant is small. Big tenants get the approximate-but-instant HLL answer, which is the right trade for a dashboard.

The trade is explicit and correct for analytics: ~1-2% error on unique counts in exchange for constant-size, mergeable, instantly-queryable uniques over any range and slice. Nobody makes a decision differently because a 40.0M unique-user count is really 39.6M, and no other structure gives you cheap arbitrary-range distinct counts at this scale.

API Design & Data Schema

Collection API (the write plane)

POST /collect            (or GET beacon / navigator.sendBeacon)
  {
    "property_id":  "G-8F2A91C4",     # tenant; validated at the edge
    "client_id":    "cid_2b9f...",    # anonymous first-party id (cookie/storage)
    "user_id":      "u_88",           # optional, if the site sets a logged-in id
    "event":        "page_view",      # or add_to_cart, purchase, custom...
    "url":          "https://shop.example/p/42",
    "referrer":     "https://google.com/",
    "ts":           1752537600123,    # client event time (ms); server ts also stamped
    "device":       "mobile",
    "params":       { "value": 499, "currency": "INR", "item_id": "42" }
  }
  204 No Content   -> hit durably appended to Kafka. Aggregation is async.

Notes:
  - 204, no body: fire-and-forget so the customer's page never waits on us.
  - Beacon sits behind the bot/consent gate; filtered hits never enter the log.
  - Batched transport: SDK may send multiple events per beacon to cut overhead.

Reporting API (the read plane, all tenant-scoped + authorized)

GET /v1/realtime?property_id=G-...&metrics=activeUsers,eventsPerSec&dimensions=page
  200 OK   (served from the real-time store; last ~30 min; approximate)
  { "activeUsers": 12840, "topPages": [ {"page":"/p/42","users":903}, ... ] }

GET /v1/report?property_id=G-...&from=2026-04-01&to=2026-06-30
              &metrics=sessions,users,pageviews,bounceRate
              &dimensions=country,deviceCategory&granularity=day
  200 OK   (served from OLAP rollup cubes + HLL for users; cached)
  { "rows": [ {"date":"2026-04-01","country":"IN","device":"mobile",
               "sessions":51200,"users":40880,"pageviews":133400,"bounceRate":0.42}, ... ],
    "settled": true }   # exact (batch-verified) vs streaming/approx

GET /v1/funnel?property_id=G-...&steps=view_product,add_to_cart,purchase
              &window=1h&from=...&to=...
  200 OK   -> [ {"step":"view_product","users":1000000},
                {"step":"add_to_cart","users":210000},
                {"step":"purchase","users":48000} ]   # per-step drop-off

GET /v1/cohorts?property_id=G-...&cohortBy=firstSeenWeek&metric=retention&span=8w
  200 OK   -> retention grid: cohort_week x offset_week -> retained %

The settled flag surfaces the streaming-approx vs batch-exact distinction to the UI, exactly as the two-layer design intends. Every read carries property_id and is authorized against the caller’s tenant grant before any store is touched.

Data model: immutable hit log + session log + derived rollups

Four stores for four jobs - a durable hit log (source of truth), a session log (per-user sequences for funnels/cohorts), an OLAP store (queryable rollup cubes + HLL), and cold archival.

Raw hit log - Kafka (source of truth, replayable, tenant-fair):

topic:        hits
partitioning: by (property_id, client_id)   # user's hits together; big tenants spread
replication:  factor 3, acks=all on produce
quotas:       per-property rate limits; hot tenants -> dedicated partitions/topic
retention:    ~30 days hot (recompute window + safety net)
message:      { property_id, client_id, user_id?, event, url, referrer, ts,
                geo, device, params{...} }

Session log - columnar store (per-user sequences, funnel/cohort substrate):

sessions   (partitioned by property_id, then date)
  property_id     STRING     partition / tenant key
  session_id      STRING     synthetic id
  client_id       STRING     the user
  cohort_key      STRING     first-seen week/month (for cohorts)
  started_at      TIMESTAMP
  ended_at        TIMESTAMP
  source          STRING     medium STRING   campaign STRING   (attribution dims)
  country         STRING     device STRING   landing_page STRING   exit_page STRING
  events          ARRAY< {event, ts, url, params} >   # ORDERED - funnels need this
  pageviews       INT        event_count INT   duration_sec INT   bounced BOOL
  Sort:           (property_id, client_id, started_at)

Rollup cubes + sketches - OLAP store (Druid/ClickHouse/BigQuery, what dashboards read):

rollup_daily   (and _hourly, _weekly, _monthly)
  property_id     STRING     part of key / partition
  bucket          DATE/TS    day (or hour/week/month) - part of key
  source          STRING     medium STRING   country STRING   device STRING
  landing_page    STRING     dimensions
  sessions        BIGINT     pageviews BIGINT   events BIGINT   bounces BIGINT
  duration_sum    BIGINT     (for avg session duration)
  users_hll       BLOB       HyperLogLog sketch (~12 KB) for distinct users
  sessions_hll    BLOB       HLL for distinct sessions
  settled         BOOL       true once batch-verified, else streaming/approx
  Sort/index:     (property_id, bucket) primary; per-dimension secondary
  Rollup:         minute -> hour -> day -> week -> month

Real-time store - Redis / RT-OLAP (last ~30 min ring buffer):

key:   rt:{property_id}:{metric}:{minute}    value: count / HLL, TTL ~35 min
purpose: "active users now", "top pages now", events/sec. Ages out continuously.

Why an OLAP columnar store, not a relational DB, for historical serving: report queries are analytical - “sum sessions for this property over this range, grouped by day, sliced by country and device.” Columnar stores scan only the needed columns, exploit time-bucketed and tenant partitioning, and pre-aggregate rollups so a range query touches tens of rows, not billions. A row-store would choke; the raw log cannot answer a grouped range query at all. And every derived store is rebuildable from the hit log. Why a separate session log: funnels and cohorts need per-user ordered sequences, which the count cube has discarded - so we keep the sequences in a store partitioned for tenant-scoped sequence scans. Each store does the one thing it is best at.

Bottlenecks & Scaling

Where it breaks first, in order, and the fix for each:

1. Collection write throughput (~5M/sec, multi-tenant, un-droppable). No single database sustains millions of durable writes/sec, and a per-property counter row serializes on a lock that blocks every tenant. Fix: stateless global edge collectors appending to a partitioned, replicated Kafka log with acks=all, keyed by (property_id, client_id). Sequential appends scale to millions/sec; aggregation moves off the ingest path.

2. Noisy-neighbor tenants. One viral property could flood the pipeline and starve millions of small tenants - the multi-tenancy failure mode. Fix: per-tenant Kafka quotas and rate limits; the hottest properties get dedicated partitions/topics while the long tail shares pooled ones. Isolation at the log, the OLAP partitions, and the session-log segments so one tenant’s spike or heavy funnel never degrades another’s ingest or queries.

3. Hot user / hot partition. Keying by (property, client_id) mostly spreads load, but a huge tenant still concentrates on its partitions. Fix: dedicated partition sets per big tenant scaled to its volume, detected dynamically; sessionization parallelism scaled per key group. Small tenants stay simple on shared partitions.

4. Sessionization state and late events. Session windows keep per-user state; late/out-of-order hits split or miss sessions; sessions cross day boundaries. Fix: event-time session windows (30-min gap) with watermarks, allowed-lateness, and window merging; hits past the grace go to a side output the batch path folds in. State is bounded (aggregate, not raw hits) and checkpointed with offsets. The nightly batch recompute fixes boundary-crossing and very-late sessions exactly.

5. Real-time freshness vs deep historical range. One store cannot be both a seconds-fresh ring buffer and an 18-month warehouse. Fix: two layers - a hot real-time store for the last ~30 min, and a columnar OLAP rollup store for history; the batch path recomputes exact closed periods and overwrites the streamed approximations.

6. Funnel/cohort scan cost. These need per-user sequences and cannot use the count cubes; naive on-demand scans over billions of sessions are slow. Fix: pre-materialize common funnels and the retention grid nightly; run ad-hoc definitions as bounded, tenant-partitioned sequence scans over the columnar session log (native windowFunnel / CEP), cached after first compute.

7. Unique-user cardinality. Exact distinct counts require storing every ID per bucket - impossible at this cardinality and un-mergeable across slices. Fix: HyperLogLog sketches (~12 KB, ~1-2% error) per (property, bucket, dimension), mergeable across time and dimensions for arbitrary-range/slice uniques; exact COUNT DISTINCT fallback only for small tenants that need it.

8. Query-side scan cost on history. Long-range dashboards would scan billions of rows if they hit raw or per-minute data. Fix: pre-aggregated rollup hierarchy (minute -> hour -> day -> week -> month) in the columnar store; the query service picks the coarsest granularity covering the range, plus a result cache for repeated dashboard polls. “Last 18 months by month” reads ~18 rows.

9. Stream processor failure. Flink node loss could lose in-flight session/window state or re-emit rollups. Fix: checkpointing of window state and Kafka offsets together; on restart, replay from the last checkpoint. Combined with idempotent absolute-count upserts (write the window’s absolute count, not +1), no committed rollup is lost or double-applied. Worst case, recompute the affected period from the raw log.

10. Hit-log and raw-storage growth (~15 TB/day). The hot log cannot grow forever. Fix: short Kafka retention (~30 days) for the recompute window, tier raw hits to cheap object storage (Parquet, dt/hr partitioned) for months-to-years of audit/reprocess, and age out rollups (per-minute for days, per-day for months, per-month for years). Counts and sketches are tiny; only raw hits are fat, and they move to cold storage.

Wrap-Up

The trade-offs that define this design:

  • Durable multi-tenant log over database ingestion. We accept hits by appending to a replicated, tenant-fair Kafka log at the edge, not writing a database, trading a familiar CRUD model for the ability to absorb 5M un-droppable events/sec across billions of tenants and to isolate noisy neighbors. The log is the shock absorber and the source of truth.
  • Stream sessionization over nightly batch. We turn hits into sessions with event-time session windows and watermarks so the live view works, trading some settling delay for freshness, and let the batch path fix boundary-crossing and very-late sessions exactly.
  • Two layers for two profiles. A hot ring-buffer serves the seconds-fresh “right now” view; a columnar OLAP rollup store serves 18 months of history sub-second; the batch path reconciles them. No single store pretends to be both.
  • Storage matched to the question. Counts live in rollup cubes; funnels and cohorts live in a per-user session log where order and membership are preserved and the common ones are pre-materialized; uniques live in mergeable HyperLogLog sketches. We do not force sequences and set operations through a GROUP BY COUNT.
  • Approximate where it is free. Unique counts are HLL (~1-2% error) for constant-size, mergeable, instant answers, because a dashboard does not need the 40.0M-vs-39.6M distinction. We spend the correctness budget on sessionization and tenant isolation, not on per-event exactness.
  • Derived reports over precious reports. Every report - live counts, historical cubes, funnels, cohorts, uniques - is a rebuildable view of the immutable hit log, so any store below the log is disposable and recomputable. Only the log is precious.

One-line summary: stateless global edge collectors appending an un-droppable, tenant-fair hit stream to a replicated Kafka log, a Flink processor sessionizing it into per-user sessions and folding them into rollup cubes and HyperLogLog sketches, served as a seconds-fresh real-time layer plus a sub-second columnar historical layer, with funnels and cohorts run over a per-user session log and a nightly batch path recomputing exact numbers - trading per-event exactness and unique-count precision for the scale to answer four different report shapes over 100B events a day across billions of properties.