Designing a Product Analytics System: Event Collection, Funnel Computation, and Retention Analysis at Scale

Most product analytics problems look simple until you try to build them. You need to answer questions like “what percentage of users who signed up last week reached checkout within 7 days?” and “where do users drop out of the onboarding funnel?” Those answers require matching event sequences across millions of users, computing cohorts retroactively, and doing it fast enough that a PM can iterate on product decisions without waiting hours for a query to finish.

This article walks through how to design a product analytics system from scratch: event schema, collection pipeline, dual-layer ingestion, funnel computation, and retention cohort analysis. The goal is a system that handles 100M+ events per day, serves funnel queries in under 5 seconds, and lets you add new metrics without re-ingesting historical data.

Event Schema Design

The event is the unit of currency in your entire system. Get the schema wrong and you will be migrating forever.

A sound event schema has two layers: a fixed envelope and a flexible payload.

interface AnalyticsEvent {
  // Fixed envelope — always present, always the same shape
  eventId: string;          // UUID v7 (lexicographically sortable by time)
  eventName: string;        // snake_case: "page_viewed", "checkout_started"
  userId: string | null;    // null for anonymous events
  anonymousId: string;      // always set; generated client-side on first visit
  sessionId: string;
  timestamp: string;        // ISO 8601, client-side clock
  receivedAt: string;       // ISO 8601, server clock on ingestion
  context: EventContext;
  // Flexible payload
  properties: Record<string, unknown>;
}

interface EventContext {
  platform: "web" | "ios" | "android" | "server";
  userAgent?: string;
  url?: string;
  referrer?: string;
  appVersion?: string;
  locale?: string;
  timezone?: string;
  ip?: string;            // stripped before storage in compliant systems
}

A few decisions worth explaining here:

UUID v7 for eventId. Unlike UUID v4, UUID v7 embeds a millisecond-precision timestamp in the most significant bits. This means events sort correctly by time at the byte level, which matters enormously for columnar storage engines that use min/max statistics to skip row groups.

timestamp vs receivedAt. The client sends timestamp (its local clock). The server stamps receivedAt on arrival. You use timestamp for funnel and retention logic because that reflects user behavior. You use receivedAt for ingestion monitoring and late-arrival handling. Never conflate them.

anonymousId always present. Anonymous users do things. Before a visitor signs up, they may view 10 pages, start an onboarding flow, and abandon it. Without anonymousId, you lose that pre-signup behavior. On signup, you emit an identify event linking anonymousId to userId, and your identity graph resolves the full session history.

Properties are a free-form bag. This is where teams get themselves into trouble. Enforce a schema registry for high-volume event types. Every event with more than 10 daily consumers should have a defined schema, a TypeScript type, and a CI check that validates events conform to that type before they reach production.

Client-Side Collection and Batching

Raw event volume from browser and mobile clients is bursty. A user scrolling through a feed might generate 20 events in 3 seconds, then nothing for 30. Sending each event as a separate HTTP request is wasteful and unreliable.

The standard approach is a client-side queue with batch flush:

class AnalyticsClient {
  private queue: AnalyticsEvent[] = [];
  private flushTimer: ReturnType<typeof setTimeout> | null = null;
  private readonly BATCH_SIZE = 50;
  private readonly FLUSH_INTERVAL_MS = 3000;

  track(eventName: string, properties: Record<string, unknown>): void {
    const event: AnalyticsEvent = {
      eventId: generateUUIDv7(),
      eventName,
      userId: this.userId,
      anonymousId: this.getOrCreateAnonymousId(),
      sessionId: this.sessionId,
      timestamp: new Date().toISOString(),
      receivedAt: "", // filled by server
      context: this.getContext(),
      properties,
    };

    this.queue.push(event);

    if (this.queue.length >= this.BATCH_SIZE) {
      this.flush();
    } else if (!this.flushTimer) {
      this.flushTimer = setTimeout(() => this.flush(), this.FLUSH_INTERVAL_MS);
    }
  }

  private async flush(): Promise<void> {
    if (this.flushTimer) {
      clearTimeout(this.flushTimer);
      this.flushTimer = null;
    }

    const batch = this.queue.splice(0, this.BATCH_SIZE);
    if (batch.length === 0) return;

    try {
      await fetch("/api/events/batch", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ events: batch }),
        // keepalive lets the request complete on page unload
        keepalive: true,
      });
    } catch {
      // Re-queue on failure with backoff (simplified here)
      this.queue.unshift(...batch);
    }
  }
}

On page unload, you need keepalive: true on the fetch call to let the final batch complete even as the page tears down. For mobile clients, the equivalent is sending the queue when the app moves to the background.

The keepalive flag has a 64KB body limit in most browsers. If your batch could exceed that, split it into two smaller requests before flushing on unload.

Server-Side Ingestion Pipeline

The ingest endpoint receives batches and fans them out to two destinations: a real-time stream and a durable batch store. This dual-path design is the core architectural decision in a product analytics system.

// POST /api/events/batch
async function handleEventBatch(req: Request): Promise<Response> {
  const { events } = await req.json() as { events: AnalyticsEvent[] };

  const stamped = events.map((e) => ({
    ...e,
    receivedAt: new Date().toISOString(),
  }));

  // Validate and enrich
  const valid = stamped.filter(validateEvent);

  // Fan out — fire and forget for the stream; await for durability
  await Promise.all([
    publishToStream(valid),      // Kafka / Kinesis topic
    writeToRawStore(valid),      // object storage (S3 / GCS / R2)
  ]);

  return new Response(JSON.stringify({ accepted: valid.length }), {
    status: 200,
  });
}

Raw store. Write every valid event to partitioned object storage immediately: s3://analytics-raw/dt=2026-05-05/hour=14/. Parquet format, partitioned by date and hour. This is your source of truth. You can replay any downstream computation from it. The overhead per event is negligible; object storage is cheap.

Stream. Publish to a Kafka topic (or Kinesis if you are already on AWS). Consumers on this topic power real-time dashboards, session tracking, and alerting. Latency target here is under 5 seconds from client to consumer.

Validation at ingest should be permissive but not absent. Reject events with no anonymousId, malformed timestamps, or bodies over a size threshold. Accept everything else, including events with unknown names or unusual properties. You can tighten validation downstream; you cannot recover events you dropped at ingest.

Computing Funnels

A funnel asks: of users who did step A, how many also did step B within a time window, and how many then did step C?

The naive implementation joins users across steps, which is expensive. The efficient implementation works in two phases: build per-user event sequences, then count how many sequences satisfy each step condition.

interface FunnelStep {
  eventName: string;
  filters?: Record<string, unknown>; // e.g. { plan: "pro" }
}

interface FunnelQuery {
  steps: FunnelStep[];
  windowDays: number;   // user must complete all steps within this window
  startDate: string;
  endDate: string;
}

interface FunnelResult {
  steps: Array<{
    name: string;
    count: number;
    conversionRate: number; // relative to previous step
  }>;
}

In SQL over a columnar store (BigQuery, ClickHouse, Redshift), the standard approach uses window functions to track the earliest timestamp for each subsequent step after the prior step’s timestamp:

WITH step1 AS (
  SELECT
    user_id,
    MIN(timestamp) AS step1_at
  FROM events
  WHERE event_name = 'signup_completed'
    AND timestamp BETWEEN '2026-04-01' AND '2026-05-01'
  GROUP BY user_id
),
step2 AS (
  SELECT
    s1.user_id,
    s1.step1_at,
    MIN(e.timestamp) AS step2_at
  FROM step1 s1
  JOIN events e
    ON e.user_id = s1.user_id
    AND e.event_name = 'onboarding_started'
    AND e.timestamp > s1.step1_at
    AND e.timestamp <= s1.step1_at + INTERVAL '7 days'
  GROUP BY s1.user_id, s1.step1_at
),
step3 AS (
  SELECT
    s2.user_id,
    s2.step1_at,
    s2.step2_at,
    MIN(e.timestamp) AS step3_at
  FROM step2 s2
  JOIN events e
    ON e.user_id = s2.user_id
    AND e.event_name = 'first_feature_used'
    AND e.timestamp > s2.step2_at
    AND e.timestamp <= s2.step1_at + INTERVAL '7 days'
  GROUP BY s2.user_id, s2.step1_at, s2.step2_at
)
SELECT
  COUNT(DISTINCT step1.user_id)  AS step1_count,
  COUNT(DISTINCT step2.user_id)  AS step2_count,
  COUNT(DISTINCT step3.user_id)  AS step3_count
FROM step1
LEFT JOIN step2 USING (user_id)
LEFT JOIN step3 USING (user_id);

This pattern scales reasonably well in ClickHouse or BigQuery at 100M events per day. The key is that columnar storage lets the engine scan only the event_name, user_id, and timestamp columns for most funnel queries.

For sub-second funnel queries, you need pre-aggregation. Maintain a materialized table of user-event sequences at hourly or daily granularity, updated by a streaming job. The tradeoff is that pre-aggregated funnels are frozen at query definition time: you can only query the funnels you anticipated. Ad-hoc funnels require scanning raw events.

Cohort-Based Retention Analysis

Retention analysis groups users by the date of their first qualifying event (their cohort date), then measures how many returned on each subsequent period. The standard output is a triangle table where row N is a cohort and column M is how many users from that cohort were active M periods later.

interface RetentionQuery {
  cohortEvent: string;       // what qualifies a user for a cohort: "signup_completed"
  retentionEvent: string;    // what counts as "returning": "session_started"
  granularity: "day" | "week" | "month";
  startDate: string;
  endDate: string;
  maxPeriods: number;        // how many periods to compute: 12 for 12-week retention
}

interface RetentionRow {
  cohortDate: string;
  cohortSize: number;
  periods: number[];  // periods[0] is day 0 (100%), periods[1] is day 1 rate, etc.
}

The SQL for retention follows the same structure as funnel computation: assign each user a cohort date, then count how many were active in each subsequent period.

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('week', MIN(timestamp)) AS cohort_week
  FROM events
  WHERE event_name = 'signup_completed'
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('week', timestamp) AS activity_week
  FROM events
  WHERE event_name = 'session_started'
),
retention AS (
  SELECT
    c.cohort_week,
    DATE_DIFF('week', c.cohort_week, a.activity_week) AS period,
    COUNT(DISTINCT c.user_id) AS retained_users
  FROM cohorts c
  JOIN activity a ON a.user_id = c.user_id
    AND a.activity_week >= c.cohort_week
  GROUP BY c.cohort_week, period
)
SELECT
  r.cohort_week,
  r.period,
  r.retained_users,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  r.retained_users::float / COUNT(DISTINCT c.user_id) AS retention_rate
FROM retention r
JOIN cohorts c USING (cohort_week)
GROUP BY r.cohort_week, r.period, r.retained_users
ORDER BY r.cohort_week, r.period;

Retention queries are expensive because they touch every event for every user in every cohort. At 100M events per day over 90 days, that is 9B rows. Pre-aggregation here is not optional for interactive query times: maintain a user_activity_by_period summary table that records, for each user, the set of weeks they were active. This compresses retention queries from scanning raw events to joining two small tables.

Pre-Aggregation vs Query-Time Computation

This is the central architectural tradeoff. Here is how it plays out in practice:

Dimension	Pre-aggregation	Query-time computation
Query latency	Under 100ms	2-30s depending on data volume
Flexibility	Only predefined metrics	Arbitrary slices and filters
Backfill cost	High (recompute all aggregates on schema change)	Low (raw data is the source of truth)
Storage cost	Low (aggregates are small)	High (raw events retained longer)
Complexity	Two systems to maintain (stream processor + query engine)	One system (query engine only)
Freshness	Near-real-time if streaming, hourly if batch	Real-time for small windows, hours for full scans

The practical answer for most teams: use query-time computation as the default, and add pre-aggregation selectively for the metrics that are queried most frequently or that need to return in under a second.

Start with a columnar store (ClickHouse is the common choice for self-hosted; BigQuery or Redshift for managed). Run raw event queries against it. Measure which queries are slow. Build materialized views or summary tables for those specific queries. Do not pre-aggregate everything upfront; you will build the wrong aggregates and then face painful migrations when the product evolves.

Production Considerations

Late events. Mobile clients send events when the app is offline and the user reconnects later. Events with timestamp more than 24 hours before receivedAt are late arrivals. Your batch pipeline needs to handle them: Spark or Hive queries that partition by receivedAt will miss late events that belong in yesterday’s partition. Partition by timestamp and reprocess recent partitions, or use incremental compaction over a rolling window.

Identity resolution. Users visit your site anonymously, sign up, and then log in on a different device. Your anonymousId-to-userId mapping is a graph problem. A simple approach: on identify events, write the mapping to a lookup table and apply it at query time. A complete approach: run a periodic identity graph computation that merges all anonymousId values that transitively link to the same userId.

High-cardinality properties. If properties.search_query has 10M distinct values, grouping by it in a funnel query will kill your query engine. Enforce a high-cardinality allowlist: only properties explicitly marked as low-cardinality should be available for segmentation in the UI.

Sampling. At very high event volumes, sampling is a legitimate tool. Log 100% of conversion events and 10% of page view events. Weight sampled counts by the sampling rate in your query engine. This is exactly what Google Analytics does. The tradeoff is that sampled data can misrepresent the tail of your funnel where event counts are already small.

Tenant isolation in multi-product systems. If you are building this for multiple products or teams, prefix every event with a source field and partition raw storage and stream topics by source. Funnel and retention queries should never cross source boundaries unless explicitly designed to do so.

Closing

A product analytics system is a pipeline with clear layers: a well-typed event envelope, a client that batches reliably, a server that fans out to raw storage and a stream, and a query engine that can answer funnel and retention questions without requiring you to predict every query in advance.

The hard part is not any single component. It is the discipline to keep the event schema clean, to preserve raw events even when you have aggregates, and to resist building pre-aggregation pipelines until you have measured which queries actually need them. Query-time computation over a columnar store gets you further than most teams expect before pre-aggregation becomes necessary.

Start with raw events in ClickHouse or BigQuery, a simple client SDK with batch flushing, and a direct ingest endpoint. Add the Kafka fan-out when you have real-time consumers that need it. Add materialized views when you have measured that specific queries are too slow. That sequence avoids the trap of building a distributed streaming system before you have enough data to justify it.