# Low Level Design: Link Analytics Service

Source: https://www.techinterview.org/post/3233471533/lld-link-analytics/
Updated: 2026-06-30 · techinterview.org

## What Is a Link Analytics Service?

A Link Analytics Service tracks every interaction with a shortened URL or QR code and surfaces aggregated statistics: click counts, geographic distribution, referrer breakdown, device types, and time-series trends. It is the reporting backbone for URL shorteners and QR platforms. The core challenge is ingesting high-velocity click events without impacting redirect latency, then making aggregated data queryable in near-real-time.

## Data Model

Analytics data lives in two tiers: a raw event store for auditability and a pre-aggregated summary store for fast dashboard queries.


```
-- Raw click events (append-only, partitioned by date)
CREATE TABLE click_event (
    id            BIGINT UNSIGNED AUTO_INCREMENT,
    link_id       BIGINT UNSIGNED NOT NULL,
    clicked_at    DATETIME(3)  NOT NULL,
    country_code  CHAR(2),
    region        VARCHAR(64),
    city          VARCHAR(128),
    referrer      VARCHAR(2048),
    user_agent    VARCHAR(512),
    device_type   ENUM('desktop','mobile','tablet','bot'),
    browser       VARCHAR(64),
    os            VARCHAR(64),
    ip_hash       CHAR(64),     -- SHA-256 of IP for unique-visitor estimation
    PRIMARY KEY (id, clicked_at)
) PARTITION BY RANGE (YEAR(clicked_at) * 100 + MONTH(clicked_at));

-- Pre-aggregated hourly rollups
CREATE TABLE link_stats_hourly (
    link_id       BIGINT UNSIGNED NOT NULL,
    hour_bucket   DATETIME     NOT NULL,   -- truncated to the hour
    total_clicks  BIGINT       DEFAULT 0,
    unique_ips    BIGINT       DEFAULT 0,
    PRIMARY KEY (link_id, hour_bucket)
);

-- Dimension breakdown table
CREATE TABLE link_stats_dimension (
    link_id       BIGINT UNSIGNED NOT NULL,
    day_bucket    DATE         NOT NULL,
    dimension     VARCHAR(32)  NOT NULL,   -- e.g., 'country', 'device', 'referrer'
    value         VARCHAR(128) NOT NULL,
    click_count   BIGINT       DEFAULT 0,
    PRIMARY KEY (link_id, day_bucket, dimension, value)
);
```


## Click Tracking Workflow

The redirect hot path must stay fast. Click tracking is therefore fully asynchronous.

  - **Redirect service** issues the 302 and immediately emits a lightweight event to a Kafka topic `click-events`. The emit is fire-and-forget with a local buffer to survive brief Kafka unavailability.

  - **Stream processor** (Flink or Kafka Streams) consumes the topic, enriches each event with GeoIP lookup and User-Agent parsing, and writes enriched records to the `click_event` table.

  - **Aggregation job** runs on a micro-batch schedule (every 60 seconds) to upsert counts into `link_stats_hourly` and `link_stats_dimension`.

  - **Dashboard API** reads only from the aggregated tables; raw events are queried only for exports or deep-dive forensics.


```
// Pseudo-code: stream enrichment step
on_event(raw):
    geo   = geoip_lookup(raw.ip)
    ua    = parse_user_agent(raw.user_agent)
    event = merge(raw, geo, ua)
    event.ip_hash = sha256(raw.ip + daily_salt)
    write_to_click_event(event)
    emit_to_aggregation_queue(event)
```


## Unique Visitor Estimation

Exact unique-IP counting at scale is expensive. Use a HyperLogLog structure (native in Redis) per link per day. On each click, call `PFADD link:{id}:uv:{date} {ip_hash}`. Query with `PFCOUNT` for an estimate with <1% error and O(1) memory regardless of cardinality. Persist the HLL to the summary table during the nightly rollup.

## Failure Handling

  - **Kafka producer buffer full:** Drop the event and increment a `clicks_dropped` counter. Accept minor under-counting rather than slow the redirect. Alert when the drop rate exceeds 0.1%.

  - **GeoIP service down:** Enrich with empty geo fields and flag the record. A backfill job re-enriches flagged records once the service recovers.

  - **Aggregation job lag:** Dashboard queries fall back to the last successful rollup and display a staleness indicator. Use a Kafka consumer group lag metric to trigger auto-scaling of the processor.

  - **Partition pruning failures:** Archive partitions older than 90 days to cold object storage (Parquet on S3). Query via Athena or BigQuery for historical reports without keeping old partitions hot in MySQL.

## Scalability Considerations

  - **Event volume:** At 100K redirects/sec, Kafka partitions should be sized at ~10K events/sec each. Use at least 10 partitions per topic; increase as volume grows.

  - **Write amplification:** Batch inserts into `click_event` in micro-batches of 500-1000 rows to reduce per-row overhead.

  - **Query isolation:** Never run dashboard aggregation queries against the raw event table in production. All user-facing queries hit the pre-aggregated tables or a read replica.

  - **Time-zone handling:** Store all timestamps in UTC. Apply time-zone offsets at the API layer when returning bucketed data to clients.

  - **Multi-tenant isolation:** Shard `link_stats_hourly` and `link_stats_dimension` by `link_id` range or tenant ID to prevent noisy-neighbor effects on large accounts.

## Summary

A Link Analytics Service succeeds by decoupling the critical redirect path from all analytics work. Kafka absorbs the event burst; a stream processor enriches and stores raw data; micro-batch jobs maintain fast-query aggregation tables. HyperLogLog handles unique-visitor estimation at negligible memory cost. Partition pruning and cold-tier archival keep the hot database lean as data accumulates over months and years.

See also: [Netflix Interview Guide 2026: Streaming Architecture, Recommendation Systems, and Engineering Excellence](https://www.techinterview.org/post/3233460274/netflix-interview-guide-2026-streaming-architecture-recommendation-systems-and-engineering-excellence/)

See also: [Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering](https://www.techinterview.org/post/3233460275/scale-ai-interview-guide-2026-data-infrastructure-rlhf-pipelines-and-ml-engineering/)

See also: [Meta Interview Guide 2026: Facebook, Instagram, WhatsApp Engineering](https://www.techinterview.org/post/3233460272/meta-interview-guide-2026-facebook-instagram-whatsapp-engineering/)