System Design Case Study

Problem Statement

How do you track notification delivery metrics at 5B notifications/day — measuring sent, delivered, opened, clicked, bounced, and unsubscribed — using tracking pixels for email opens, redirect URLs for clicks, and ISP feedback loop processing for spam complaints?

Functional Requirements

What the analytics system must do · core tracking behaviours

Non-Functional Requirements

Quality constraints shaping the analytics architecture

Scale Estimation

Derive infrastructure sizing from given constraints. Show this reasoning in an interview.

APIs

Tracking endpoints (pixel/click) and analytics query APIs

GET /t/o/n_abc123_v2.gif

Response: 200 OK
Headers:
  Content-Type: image/gif
  Content-Length: 43
  Cache-Control: no-cache, no-store, must-revalidate
Body: [1x1 transparent GIF bytes]

Side effect: Emit event to Kafka
  { "type": "open", "notification_id": "n_abc123", "variant": "v2",
    "timestamp": "2024-03-15T10:30:00Z", "user_agent": "...", "ip": "..." }

GET /t/c/n_abc123/link_cta_1

Response: 302 Found
Headers:
  Location: https://example.com/product/123?utm_source=notification
  Cache-Control: no-cache

Side effect: Emit event to Kafka
  { "type": "click", "notification_id": "n_abc123", "link_id": "link_cta_1",
    "destination": "https://example.com/product/123",
    "timestamp": "2024-03-15T10:31:00Z" }

{
  "event_type": "bounce",              // bounce | complaint | unsubscribe
  "bounce_type": "hard",               // hard | soft
  "email": "user@example.com",
  "notification_id": "n_abc123",
  "provider": "sendgrid",
  "reason": "550 User not found",
  "timestamp": "2024-03-15T10:32:00Z"
}

Response: 200 OK { "processed": true, "suppression_added": true }

GET /v1/analytics/metrics/order_shipped?period=24h&channel=email

Response 200:
{
  "template_id": "order_shipped",
  "channel": "email",
  "period": "24h",
  "metrics": {
    "sent": 2450000,
    "delivered": 2401000,    "delivery_rate": 0.98,
    "opened": 612000,        "open_rate": 0.255,
    "clicked": 122000,       "ctr": 0.05,
    "bounced": 49000,        "bounce_rate": 0.02,
    "complained": 1200,      "complaint_rate": 0.0005,
    "unsubscribed": 4800,    "unsub_rate": 0.002
  },
  "variants": {
    "v1": { "open_rate": 0.24, "ctr": 0.045 },
    "v2": { "open_rate": 0.27, "ctr": 0.055 }
  }
}

Data Model

Event store, aggregation tables, and suppression lists

tracking_events (Kafka → ClickHouse)

CREATE TABLE tracking_events (
  event_id          String,
  notification_id   String,
  user_id           String,
  template_id       String,
  channel           Enum8('push'=1, 'email'=2, 'sms'=3, 'inapp'=4),
  event_type        Enum8('sent'=1, 'delivered'=2, 'opened'=3, 'clicked'=4, 'bounced'=5, 'complained'=6, 'unsubscribed'=7),
  variant           Nullable(String),   -- A/B test variant
  metadata          String,             -- JSON: user_agent, ip, link_id, bounce_reason
  event_time        DateTime64(3),
  ingestion_time    DateTime64(3)
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(event_time)
ORDER BY (template_id, channel, event_type, event_time)
TTL event_time + INTERVAL 90 DAY;

template_metrics_realtime (Redis — pre-aggregated counters)

// Per-template, per-channel, per-hour counters
// Key pattern: metrics:{template_id}:{channel}:{hour_bucket}:{metric}
INCR metrics:order_shipped:email:2024031510:sent
INCR metrics:order_shipped:email:2024031510:delivered
INCR metrics:order_shipped:email:2024031510:opened
INCR metrics:order_shipped:email:2024031510:clicked

// Per-variant counters (for A/B testing)
INCR metrics:order_shipped:email:2024031510:v1:opened
INCR metrics:order_shipped:email:2024031510:v2:opened

EXPIRE metrics:order_shipped:email:2024031510:sent 604800  // 7-day TTL (hot window)

suppression_list (Redis + DynamoDB)

// Hard bounced emails — never send again
SADD suppression:hard_bounce "user@example.com" "invalid@gone.com"

// Complaint suppressions — auto-unsubscribe
SADD suppression:complaint "annoyed@user.com"

// Persistent store (DynamoDB)
{
  "email":          "user@example.com",     // partition key
  "reason":         "hard_bounce",          // hard_bounce | complaint | unsubscribe
  "source":         "sendgrid",
  "notification_id": "n_abc123",
  "suppressed_at":  "2024-03-15T10:32:00Z",
  "ttl":            null                    // permanent for hard bounces
}

delivery_funnel (ClickHouse — materialized view)

CREATE MATERIALIZED VIEW delivery_funnel
ENGINE = SummingMergeTree()
ORDER BY (template_id, channel, event_type, hour_bucket)
AS SELECT
  template_id,
  channel,
  event_type,
  toStartOfHour(event_time) AS hour_bucket,
  count() AS event_count,
  uniqHLL12(user_id) AS unique_users
FROM tracking_events
GROUP BY template_id, channel, event_type, hour_bucket;

Architecture Overview

Tracking Endpoints (Edge) → Kafka → Stream Processor → ClickHouse + Redis Counters → Dashboard

Key Design Decisions

Tradeoffs made in this architecture and their rationale

Resilience & Edge Cases

How the analytics system handles failures and edge cases

Interview Cheat Sheet

5 key points to nail a notification analytics interview