Table of Contents

1. Introduction
2. Functional and Non-Functional Requirements
3. Capacity Estimation
4. Data Model
5. High-Level Architecture
6. Event Collection Pipeline
7. Session Tracking
8. Data Processing and Aggregation
9. Storage Layer Design
10. Query Engine and Reporting
11. Real-Time Analytics
12. Funnel and Cohort Analysis
13. Sampling and Approximation
14. Privacy and Compliance
15. Interview Questions and Answers

1. Introduction

Web analytics platforms like Google Analytics process billions of events per day from millions of websites, providing real-time dashboards, conversion funnels, audience segmentation, and custom reports. The scale is staggering: Google Analytics processes over 50 billion hits per day, stores petabytes of data, and must serve queries with sub-second latency across complex analytical dimensions.

The fundamental challenge is designing a system that can simultaneously handle high-volume event ingestion (write-heavy), provide real-time dashboards (streaming aggregation), support ad-hoc analytical queries (read-heavy), and maintain data accuracy across distributed processing nodes. This is a classic Lambda or Kappa architecture problem with unique constraints around sessionization, attribution modeling, and privacy compliance.

Scale Overview

2. Functional and Non-Functional Requirements

Functional Requirements

1. Event collection: Track pageviews, custom events, e-commerce transactions, and user interactions via JavaScript SDK and server-side APIs.
2. Session tracking: Group events into sessions with configurable timeout rules, track user journeys across pages.
3. Real-time dashboard: Live visitor count, active pages, and event stream with <5 second delay.
4. Standard reports: Audience overview, acquisition channels, behavior flow, conversions — pre-aggregated for fast loading.
5. Custom reports: User-defined dimensions and metrics with ad-hoc querying capability.
6. Funnel analysis: Multi-step conversion funnels with drop-off visualization.
7. Audience segmentation: Filter and segment users by any combination of dimensions.
8. Cohort analysis: Group users by acquisition date and track behavior over time.
9. E-commerce tracking: Revenue, transactions, product performance, and attribution.

Non-Functional Requirements

3. Capacity Estimation

public class AnalyticsCapacityEstimator
{
    public static void Estimate()
    {
        long eventsPerDay = 50_000_000_000L;
        long avgEventSizeBytes = 500;
        int uniqueDimensions = 20;

        // Ingestion
        double eventsPerSecond = eventsPerDay / 86400.0;
        double ingestionBandwidthGB = (eventsPerDay * avgEventSizeBytes) / (1024.0 * 1024 * 1024);
        Console.WriteLine($"Events/sec: {eventsPerSecond:N0}");
        Console.WriteLine($"Daily ingestion: {ingestionBandwidthGB:N0} GB");

        // Raw storage (compressed)
        double compressionRatio = 10.0; // Columnar compression
        double rawStorageGB = ingestionBandwidthGB / compressionRatio;
        double yearlyStoragePB = (rawStorageGB * 365) / 1024 / 1024;
        Console.WriteLine($"Yearly raw storage: {yearlyStoragePB:N1} PB");

        // Pre-aggregated storage
        int dimensionCombinations = 500; // Common dimension combos
        double aggregatedGBPerDay = dimensionCombinations * 100; // 100 GB per combo per day
        Console.WriteLine($"Aggregated storage/day: {aggregatedGBPerDay:N0} GB");

        // Processing
        long processingNodes = (long)Math.Ceiling(eventsPerSecond / 10000); // 10K events/sec per node
        Console.WriteLine($"Processing nodes needed: {processingNodes}");
    }
}

4. Data Model

Event Schema

public class AnalyticsEvent
{
    // Identity
    public string EventId { get; set; }
    public string PropertyId { get; set; }        // Website/app property
    public string VisitorId { get; set; }          // Anonymous visitor ID
    public string UserId { get; set; }             // Authenticated user (nullable)
    public string SessionId { get; set; }

    // Event details
    public EventType EventType { get; set; }       // Pageview, Click, Custom
    public string EventName { get; set; }
    public string EventCategory { get; set; }
    public string EventAction { get; set; }
    public string EventLabel { get; set; }
    public long EventValue { get; set; }

    // Page/Screen
    public string PageUrl { get; set; }
    public string PageTitle { get; set; }
    public string ReferrerUrl { get; set; }
    public string ScreenName { get; set; }

    // User context
    public string Browser { get; set; }
    public string BrowserVersion { get; set; }
    public string OS { get; set; }
    public string Device { get; set; }
    public string Country { get; set; }
    public string City { get; set; }
    public string Language { get; set; }
    public string ScreenResolution { get; set; }
    public bool IsNewVisitor { get; set; }

    // E-commerce
    public TransactionData Transaction { get; set; }
    public List<ProductData> Products { get; set; }

    // Custom dimensions
    public Dictionary<int, string> CustomDimensions { get; set; }
    public Dictionary<int, long> CustomMetrics { get; set; }

    // Timestamps
    public DateTimeOffset EventTime { get; set; }
    public DateTimeOffset IngestionTime { get; set; }
}

public enum EventType
{
    PageView,
    ScreenView,
    Event,
    Transaction,
    Item,
    Social,
    Exception,
    Timing
}

public class TransactionData
{
    public string TransactionId { get; set; }
    public decimal Revenue { get; set; }
    public decimal Tax { get; set; }
    public decimal Shipping { get; set; }
    public string Affiliation { get; set; }
}

public class ProductData
{
    public string ProductId { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

5. High-Level Architecture

Component Responsibilities

6. Event Collection Pipeline

JavaScript SDK Design

public class AnalyticsSDK
{
    private readonly string _propertyId;
    private readonly string _endpoint;
    private readonly Queue<AnalyticsEvent> _eventQueue;
    private readonly Timer _flushTimer;
    private readonly int _batchSize;

    public AnalyticsSDK(string propertyId, string endpoint = "https://collect.analytics.com")
    {
        _propertyId = propertyId;
        _endpoint = endpoint;
        _eventQueue = new Queue<AnalyticsEvent>();
        _batchSize = 20;
        _flushTimer = new Timer(FlushAsync, null, TimeSpan.FromSeconds(30), TimeSpan.FromSeconds(30));
    }

    public void TrackPageView(string url, string title = null)
    {
        var evt = CreateEvent(EventType.PageView);
        evt.PageUrl = url;
        evt.PageTitle = title;
        evt.ReferrerUrl = document.referrer;
        Enqueue(evt);
    }

    public void TrackEvent(string name, string category, string action,
        string label = null, long value = 0)
    {
        var evt = CreateEvent(EventType.Event);
        evt.EventName = name;
        evt.EventCategory = category;
        evt.EventAction = action;
        evt.EventLabel = label;
        evt.EventValue = value;
        Enqueue(evt);
    }

    private AnalyticsEvent CreateEvent(EventType type)
    {
        return new AnalyticsEvent
        {
            EventId = Guid.NewGuid().ToString("N"),
            PropertyId = _propertyId,
            VisitorId = GetOrCreateVisitorId(),
            SessionId = GetOrCreateSessionId(),
            EventType = type,
            Browser = DetectBrowser(),
            OS = DetectOS(),
            Device = DetectDevice(),
            ScreenResolution = $"{screen.width}x{screen.height}",
            Language = navigator.language,
            EventTime = DateTimeOffset.UtcNow
        };
    }

    private void Enqueue(AnalyticsEvent evt)
    {
        lock (_eventQueue)
        {
            _eventQueue.Enqueue(evt);
            if (_eventQueue.Count >= _batchSize)
                _ = FlushAsync(null);
        }
    }

    private async Task FlushAsync(object state)
    {
        List<AnalyticsEvent> batch;
        lock (_eventQueue)
        {
            batch = _eventQueue.ToList();
            _eventQueue.Clear();
        }

        if (!batch.Any()) return;

        try
        {
            var payload = JsonSerializer.SerializeToUtf8Bytes(batch);
            await SendBeacon($"{_endpoint}/collect", payload);
        }
        catch
        {
            // Retry with exponential backoff
            await Task.Delay(1000);
            lock (_eventQueue)
            {
                foreach (var evt in batch)
                    _eventQueue.Enqueue(evt);
            }
        }
    }

    private string GetOrCreateVisitorId()
    {
        var id = localStorage.getItem("_ga_vid");
        if (id == null)
        {
            id = Guid.NewGuid().ToString("N");
            localStorage.setItem("_ga_vid", id);
        }
        return id;
    }
}

Collection Gateway

public class CollectionGateway
{
    private readonly IKafkaProducer _kafka;
    private readonly IRateLimiter _rateLimiter;
    private readonly IValidator _validator;

    public async Task<CollectionResponse> CollectAsync(HttpRequest request)
    {
        var batch = await DeserializeRequestAsync(request);

        // Rate limit per property
        if (!await _rateLimiter.AllowAsync($"prop:{batch.PropertyId}", 100000))
            return new CollectionResponse { Status = 429, Message = "Rate limit exceeded" };

        // Validate and enrich events
        var validEvents = new List<AnalyticsEvent>();
        foreach (var evt in batch.Events)
        {
            if (_validator.Validate(evt))
            {
                evt.IngestionTime = DateTimeOffset.UtcNow;
                evt.Country = await GeoLookupAsync(request.RemoteIP);
                validEvents.Add(evt);
            }
        }

        // Publish to Kafka
        if (validEvents.Any())
        {
            var messages = validEvents.Select(e => new Message(
                key: e.PropertyId,
                value: Serialize(e)
            )).ToList();

            await _kafka.SendBatchAsync("raw-events", messages);
        }

        return new CollectionResponse { Status = 200, EventsReceived = validEvents.Count };
    }
}

7. Session Tracking

Sessionization Logic

Session tracking groups individual events into user sessions. A session ends after 30 minutes of inactivity or at midnight. The session builder must handle late-arriving events and session timeouts correctly.

public class SessionBuilder
{
    private readonly TimeSpan _sessionTimeout = TimeSpan.FromMinutes(30);

    public Session BuildSession(List<AnalyticsEvent> visitorEvents)
    {
        var sorted = visitorEvents.OrderBy(e => e.EventTime).ToList();
        var sessions = new List<Session>();
        var currentSession = new Session
        {
            SessionId = Guid.NewGuid().ToString("N"),
            VisitorId = sorted.First().VisitorId,
            StartTime = sorted.First().EventTime,
            Events = new List<AnalyticsEvent>()
        };

        foreach (var evt in sorted)
        {
            var timeSinceLastEvent = evt.EventTime - (currentSession.Events.LastOrDefault()?.EventTime
                ?? currentSession.StartTime);

            if (timeSinceLastEvent > _sessionTimeout)
            {
                // Session timeout — end current session, start new one
                FinalizeSession(currentSession);
                sessions.Add(currentSession);

                currentSession = new Session
                {
                    SessionId = Guid.NewGuid().ToString("N"),
                    VisitorId = evt.VisitorId,
                    StartTime = evt.EventTime,
                    Events = new List<AnalyticsEvent>()
                };
            }

            currentSession.Events.Add(evt);
        }

        FinalizeSession(currentSession);
        sessions.Add(currentSession);

        return sessions;
    }

    private void FinalizeSession(Session session)
    {
        session.EndTime = session.Events.LastOrDefault()?.EventTime ?? session.StartTime;
        session.Duration = session.EndTime - session.StartTime;
        session.PageviewCount = session.Events.Count(e => e.EventType == EventType.PageView);
        session.EventCount = session.Events.Count;
        session.EntryPage = session.Events.FirstOrDefault(e => e.EventType == EventType.PageView)?.PageUrl;
        session.ExitPage = session.Events.LastOrDefault(e => e.EventType == EventType.PageView)?.PageUrl;
        session.Referrer = session.Events.FirstOrDefault()?.ReferrerUrl;
        session.TransactionRevenue = session.Events
            .Where(e => e.Transaction != null)
            .Sum(e => e.Transaction?.Revenue ?? 0);
    }
}

8. Data Processing and Aggregation

Pre-Aggregation Pipeline

public class PreAggregator
{
    // Pre-compute common report dimensions
    public async Task AggregateDailyAsync(string propertyId, DateTimeOffset date)
    {
        var dimensions = new[]
        {
            new[] { "browser" },
            new[] { "country" },
            new[] { "device" },
            new[] { "page_url" },
            new[] { "referrer_url" },
            new[] { "browser", "country" },
            new[] { "browser", "device" },
            new[] { "country", "device" }
        };

        foreach (var dims in dimensions)
        {
            var aggregationKey = string.Join(":", dims);
            var metrics = await ComputeMetricsAsync(propertyId, date, dims);

            await SaveAggregationAsync(new AggregationRecord
            {
                PropertyId = propertyId,
                Date = date,
                Dimensions = aggregationKey,
                Metrics = metrics
            });
        }
    }

    private async Task<AggregatedMetrics> ComputeMetricsAsync(
        string propertyId, DateTimeOffset date, string[] dimensions)
    {
        var events = await GetEventsForDateAsync(propertyId, date);

        return new AggregatedMetrics
        {
            // Standard metrics
            TotalVisitors = await ComputeUniqueAsync(events, "visitor_id"),
            NewVisitors = events.Count(e => e.IsNewVisitor),
            Sessions = await ComputeUniqueAsync(events, "session_id"),
            Pageviews = events.Count(e => e.EventType == EventType.PageView),
            BounceRate = ComputeBounceRate(events),
            AvgSessionDuration = ComputeAvgSessionDuration(events),

            // Engagement metrics
            EventsPerSession = (double)events.Count / Math.Max(1, await ComputeUniqueAsync(events, "session_id")),
            PagesPerSession = (double)events.Count(e => e.EventType == EventType.PageView)
                / Math.Max(1, await ComputeUniqueAsync(events, "session_id")),

            // E-commerce
            Transactions = events.Count(e => e.Transaction != null),
            Revenue = events.Where(e => e.Transaction != null).Sum(e => e.Transaction.Revenue),
            AvgOrderValue = events.Where(e => e.Transaction != null)
                .Select(e => e.Transaction.Revenue)
                .DefaultIfEmpty(0).Average()
        };
    }
}

HyperLogLog for Unique Counts

public class UniqueCounter
{
    // HyperLogLog for approximate unique visitor counting
    // ~1.04% standard error with 2^14 = 16384 registers
    private readonly int _precision = 14;
    private readonly int _registerCount;
    private readonly byte[] _registers;

    public UniqueCounter(int precision = 14)
    {
        _precision = precision;
        _registerCount = 1 << precision;
        _registers = new byte[_registerCount];
    }

    public void Add(string element)
    {
        var hash = MurmurHash3.Hash(Encoding.UTF8.GetBytes(element));
        var registerIndex = hash & (_registerCount - 1);
        var remainingHash = (uint)(hash >> _precision);
        var leadingZeros = CountLeadingZeros(remainingHash) + 1;

        if (leadingZeros > _registers[registerIndex])
            _registers[registerIndex] = (byte)leadingZeros;
    }

    public long Estimate()
    {
        var alpha = 0.7213 / (1 + 1.079 / _registerCount);
        var sum = _registers.Sum(r => Math.Pow(2, -r));
        var estimate = alpha * _registerCount * _registerCount / sum;

        // Small range correction
        var zeros = _registers.Count(r => r == 0);
        if (estimate <= 2.5 * _registerCount && zeros > 0)
            return (long)(_registerCount * Math.Log((double)_registerCount / zeros));

        return (long)estimate;
    }

    public void Merge(UniqueCounter other)
    {
        for (int i = 0; i < _registerCount; i++)
        {
            if (other._registers[i] > _registers[i])
                _registers[i] = other._registers[i];
        }
    }
}

9. Storage Layer Design

Multi-Tier Storage Architecture

ClickHouse Schema

public class ClickHouseSchema
{
    // ClickHouse DDL for analytics events
    public const string EventsTable = @"
CREATE TABLE analytics_events (
    event_id String,
    property_id String,
    visitor_id String,
    user_id Nullable(String),
    session_id String,
    event_type Enum8('PageView' = 1, 'Event' = 2, 'Transaction' = 3),
    event_name String DEFAULT '',
    page_url String,
    referrer_url String DEFAULT '',
    browser String,
    os String,
    device String,
    country String,
    city String DEFAULT '',
    language String DEFAULT '',
    is_new_visitor UInt8,
    event_value Int64 DEFAULT 0,
    transaction_revenue Decimal(18,2) DEFAULT 0,
    event_time DateTime64(3),
    ingestion_time DateTime64(3),
    -- Partition by date for efficient range queries
    event_date Date DEFAULT toDate(event_time)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (property_id, event_date, event_type, visitor_id)
TTL event_date + INTERVAL 2 YEAR;

-- Pre-aggregated table for fast dashboard queries
CREATE TABLE daily_aggregates (
    property_id String,
    event_date Date,
    dimension String,
    dimension_value String,
    visitors AggregateFunction(uniq, String),
    pageviews AggregateFunction(sum, UInt64),
    sessions AggregateFunction(uniq, String),
    bounce_rate AggregateFunction(avg, Float64)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (property_id, event_date, dimension, dimension_value);
";

    public const string RealtimeTable = @"
CREATE TABLE realtime_metrics (
    property_id String,
    minute DateTime,
    active_visitors AggregateFunction(uniq, String),
    pageviews AggregateFunction(sum, UInt64),
    top_pages AggregateFunction(topK(10), String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(minute)
ORDER BY (property_id, minute);
";
}

10. Query Engine and Reporting

Report API Design

public class ReportService
{
    private readonly IClickHouseClient _clickhouse;
    private readonly IPreAggregationStore _preAggStore;
    private readonly ICacheService _cache;

    public async Task<ReportResponse> GetReportAsync(ReportRequest request)
    {
        // Check cache first
        var cacheKey = BuildCacheKey(request);
        var cached = await _cache.GetAsync<ReportResponse>(cacheKey);
        if (cached != null) return cached;

        // Check pre-aggregated data
        if (IsPreAggregatable(request))
        {
            var result = await _preAggStore.QueryAsync(request);
            if (result != null)
            {
                await _cache.SetAsync(cacheKey, result, TimeSpan.FromMinutes(5));
                return result;
            }
        }

        // Fall back to raw query
        var report = await ExecuteRawQueryAsync(request);
        await _cache.SetAsync(cacheKey, report, TimeSpan.FromMinutes(5));
        return report;
    }

    private async Task<ReportResponse> ExecuteRawQueryAsync(ReportRequest request)
    {
        var sql = BuildSQL(request);
        var results = await _clickhouse.QueryAsync(sql);

        return new ReportResponse
        {
            Dimensions = request.Dimensions,
            Metrics = ComputeMetrics(results, request.Metrics),
            Rows = results,
            SampleSize = request.SamplingRate == 1.0
                ? (long?)null
                : (long)(results.TotalRows / request.SamplingRate),
            DataRange = new DateRange { Start = request.StartDate, End = request.EndDate }
        };
    }

    private string BuildSQL(ReportRequest request)
    {
        var sb = new StringBuilder();
        sb.AppendLine("SELECT");

        // Dimensions
        sb.AppendLine(string.Join(",\n  ", request.Dimensions.Select(d => $"dimension_{d} AS {d}")));

        // Metrics
        foreach (var metric in request.Metrics)
        {
            sb.AppendLine($",{GetMetricExpression(metric)} AS {metric}");
        }

        sb.AppendLine("FROM analytics_events");
        sb.AppendLine($"WHERE property_id = '{request.PropertyId}'");
        sb.AppendLine($"AND event_date BETWEEN '{request.StartDate:yyyy-MM-dd}' AND '{request.EndDate:yyyy-MM-dd}'");

        // Filters
        foreach (var filter in request.Filters)
        {
            sb.AppendLine($"AND {BuildFilterClause(filter)}");
        }

        // Group by
        if (request.Dimensions.Any())
        {
            sb.AppendLine("GROUP BY " + string.Join(", ", request.Dimensions));
            sb.AppendLine($"ORDER BY {request.Metrics.First()} DESC");
        }

        sb.AppendLine($"LIMIT {request.Limit ?? 1000}");

        return sb.ToString();
    }
}

11. Real-Time Analytics

public class RealtimeAggregator
{
    private readonly IRedisCluster _redis;
    private readonly IWebSocketHub _wsHub;

    public async Task ProcessEventAsync(AnalyticsEvent evt)
    {
        var propertyId = evt.PropertyId;
        var minute = evt.EventTime.FloorToMinute();

        // Update active visitor count (HyperLogLog in Redis)
        await _redis.HyperLogLogAddAsync($"rt:active:{propertyId}:{minute}", evt.VisitorId);

        // Update pageview counter
        await _redis.IncrementAsync($"rt:pageviews:{propertyId}:{minute}");

        // Update top pages (sorted set)
        await _redis.SortedSetIncrementAsync($"rt:top_pages:{propertyId}:{minute}",
            evt.PageUrl, 1);

        // Update country distribution
        await _redis.IncrementAsync($"rt:country:{propertyId}:{minute}:{evt.Country}");

        // Broadcast to connected dashboards
        await _wsHub.BroadcastAsync(propertyId, new RealtimeUpdate
        {
            Type = "event",
            Data = new
            {
                pageUrl = evt.PageUrl,
                country = evt.Country,
                browser = evt.Browser,
                device = evt.Device,
                timestamp = evt.EventTime
            }
        });
    }

    public async Task<RealtimeMetrics> GetCurrentMetricsAsync(string propertyId)
    {
        var minute = DateTimeOffset.UtcNow.FloorToMinute();

        return new RealtimeMetrics
        {
            ActiveVisitors = await _redis.HyperLogLogCountAsync(
                $"rt:active:{propertyId}:{minute}"),
            Pageviews = await _redis.GetAsync<long>(
                $"rt:pageviews:{propertyId}:{minute}"),
            TopPages = await _redis.SortedSetTopNAsync(
                $"rt:top_pages:{propertyId}:{minute}", 10),
            Timestamp = minute
        };
    }
}

12. Funnel and Cohort Analysis

Funnel Analysis

public class FunnelAnalyzer
{
    private readonly IClickHouseClient _clickhouse;

    public async Task<FunnelResult> AnalyzeFunnelAsync(FunnelRequest request)
    {
        // Build funnel query: track users through sequential steps
        var steps = request.Steps;
        var results = new List<FunnelStep>();

        for (int i = 0; i < steps.Count; i++)
        {
            var step = steps[i];
            var sql = $@"
SELECT
    uniqExact(visitor_id) as visitors,
    countIf(event_name = '{step.EventName}') as events
FROM analytics_events
WHERE property_id = '{request.PropertyId}'
  AND event_date BETWEEN '{request.StartDate:yyyy-MM-dd}' AND '{request.EndDate:yyyy-MM-dd}'
  AND visitor_id IN (
      SELECT visitor_id
      FROM analytics_events
      WHERE event_name = '{step.EventName}'
        AND event_time >= '{request.StartDate:O}'
        AND event_time <= '{request.EndDate:O}'
  )";

            if (i > 0)
            {
                // Filter to users who completed previous step
                sql += $"  AND visitor_id IN (SELECT visitor_id FROM step_{i - 1})";
            }

            var result = await _clickhouse.QuerySingleAsync(sql);
            results.Add(new FunnelStep
            {
                StepNumber = i + 1,
                StepName = step.EventName,
                Visitors = result.visitors,
                Events = result.events,
                ConversionRate = i == 0 ? 1.0 : (double)result.visitors / results[0].Visitors,
                DropOffRate = i == 0 ? 0 : 1.0 - (double)result.visitors / results[i - 1].Visitors
            });
        }

        return new FunnelResult
        {
            Steps = results,
            OverallConversion = results.Any()
                ? (double)results.Last().Visitors / results.First().Visitors
                : 0,
            TotalUsers = results.FirstOrDefault()?.Visitors ?? 0
        };
    }
}

Cohort Analysis

public class CohortAnalyzer
{
    private readonly IClickHouseClient _clickhouse;

    public async Task<CohortResult> AnalyzeCohortAsync(CohortRequest request)
    {
        var sql = $@"
SELECT
    date_trunc('{request.CohortPeriod}', first_seen_date) as cohort_date,
    date_diff('{request.MetricPeriod}', cohort_date, date_trunc('{request.MetricPeriod}', event_date)) as period,
    uniqExact(visitor_id) as active_users,
    count(page_url) as pageviews
FROM analytics_events
WHERE property_id = '{request.PropertyId}'
  AND event_date BETWEEN '{request.StartDate:yyyy-MM-dd}' AND '{request.EndDate:yyyy-MM-dd}'
GROUP BY cohort_date, period
ORDER BY cohort_date, period";

        var results = await _clickhouse.QueryAsync(sql);
        return BuildCohortTable(results);
    }
}

13. Sampling and Approximation

At scale, querying 100% of data is too expensive. Sampling provides fast approximate results with known accuracy bounds.

public class SamplingStrategy
{
    // Deterministic sampling: same visitor always sampled consistently
    public bool ShouldSample(string visitorId, double samplingRate)
    {
        var hash = MurmurHash3.Hash(Encoding.UTF8.GetBytes(visitorId));
        return (hash % 10000) < (int)(samplingRate * 10000);
    }

    // Reservoir sampling for unknown-size streams
    public List<AnalyticsEvent> ReservoirSample(List<AnalyticsEvent> events, int sampleSize)
    {
        var reservoir = new List<AnalyticsEvent>(sampleSize);
        int count = 0;

        foreach (var evt in events)
        {
            if (count < sampleSize)
            {
                reservoir.Add(evt);
            }
            else
            {
                int j = Random.Shared.Next(count + 1);
                if (j < sampleSize)
                    reservoir[j] = evt;
            }
            count++;
        }

        return reservoir;
    }

    // Estimate metrics with confidence intervals
    public SamplingResult EstimateWithCI(double sampledMean, double sampledVariance,
        int sampleSize, double confidenceLevel = 0.95)
    {
        var z = confidenceLevel == 0.95 ? 1.96 : 2.576; // Z-score for 95% or 99%
        var marginOfError = z * Math.Sqrt(sampledVariance / sampleSize);

        return new SamplingResult
        {
            Estimate = sampledMean,
            LowerBound = sampledMean - marginOfError,
            UpperBound = sampledMean + marginOfError,
            ConfidenceLevel = confidenceLevel,
            SampleSize = sampleSize,
            RelativeError = marginOfError / Math.Max(Math.Abs(sampledMean), 0.001)
        };
    }
}

14. Privacy and Compliance

• IP anonymization: Truncate last octet of IP addresses before storage. Never store full IPs.
• Data retention policies: Configurable per-property. Auto-delete raw events after retention period. Aggregate data preserved longer.
• GDPR compliance: User data export (right to portability), deletion (right to erasure), and consent management. Implement user ID pseudonymization.
• Cookie consent: SDK respects cookie consent settings. No tracking without consent in EU. Use cookieless tracking where possible.
• Data processing agreements: Legal framework for data processing across jurisdictions. Support data residency requirements (EU data stays in EU).

public class PrivacyManager
{
    public AnalyticsEvent ApplyPrivacyRules(AnalyticsEvent evt, PrivacySettings settings)
    {
        if (settings.IpAnonymization)
        {
            // Truncate IP to /24 network
            evt.IpAddress = AnonymizeIP(evt.IpAddress);
        }

        if (settings.DoNotTrack)
        {
            // Respect DNT header — don't store personal identifiers
            evt.VisitorId = HashForDNT(evt.VisitorId);
            evt.UserId = null;
        }

        if (settings.CookieConsent == false)
        {
            // No consent — use fingerprint-resistant tracking
            evt.VisitorId = GenerateEphemeralId();
        }

        // Apply data retention
        if (settings.DataRetentionDays > 0)
        {
            evt.RetentionExpiry = DateTimeOffset.UtcNow.AddDays(settings.DataRetentionDays);
        }

        return evt;
    }

    public async Task DeleteUserDataAsync(string userId)
    {
        // Delete from all storage tiers
        await _clickhouse.ExecuteAsync(
            $"DELETE FROM analytics_events WHERE user_id = '{userId}'");
        await _redis.DeleteAsync($"user:*:{userId}");
        await _s3.DeleteByPrefixAsync($"user-data/{userId}/");

        // Log deletion for audit
        await _auditLog.LogAsync(new AuditEntry
        {
            Action = "UserDataDeletion",
            UserId = userId,
            Timestamp = DateTimeOffset.UtcNow
        });
    }
}

15. Interview Questions and Answers

Q1: How do you handle duplicate events in the collection pipeline?

Use event-level deduplication with a Bloom filter backed by Redis. Each event gets a unique ID computed from (visitor_id, event_type, event_time, page_url). Before processing, check the Bloom filter. If the event is "probably seen," do an exact check in a short-lived Redis set (TTL = 5 minutes). This handles both SDK retries and network duplicates with minimal storage overhead.

Q2: How would you handle counting unique visitors across multiple websites?

Use a hierarchical HyperLogLog approach. Each website maintains a local HyperLogLog. For cross-site analytics, merge HyperLogLogs from all sites. The merge operation is O(n) where n is the number of registers. For exact counts, use a daily batch job that deduplicates visitor IDs across sites. The HyperLogLog merge gives approximate counts in real-time, while the batch job provides exact counts for billing and reporting.

Q3: How do you handle late-arriving events that affect session boundaries?

Implement a session correction window. When a late event arrives (up to 4 hours late), check if it falls within an existing session or extends/creates a new session. Use a watermarked stream processing approach in Flink that allows a 4-hour allowed lateness. After the watermark passes, late events are dropped or attributed to the nearest session. The dashboard shows a "processing" indicator until the watermark passes.

Q4: How would you design custom report building without impacting system performance?

Use a separate processing tier for custom reports. Raw events are stored in cold storage (S3/Parquet). Custom reports run as Spark jobs on a separate cluster. For interactive custom reports, pre-compute the top 100 dimension combinations and cache results. For truly custom queries, execute against the cold storage using a serverless query engine (BigQuery, Athena) with a timeout of 60 seconds. Users are notified when their custom report is ready.

Q5: How do you handle multi-device user identification?

Implement a user ID stitching system. When a user authenticates, associate their authenticated user ID with all anonymous visitor IDs seen on that device. Build a mapping table (visitor_id → user_id) updated in real-time. For cross-device tracking, use the authenticated user ID as the join key. The session builder joins anonymous sessions with authenticated sessions to create a unified user journey. Privacy controls allow users to opt out of cross-device tracking.

Q6: How would you handle a sudden traffic spike (e.g., Black Friday)?

The system auto-scales at multiple layers: (1) Collection gateway scales horizontally via Kubernetes HPA. (2) Kafka partitions scale via pre-provisioned partitions (1000+). (3) Flink scales by adding task managers. (4) ClickHouse scales via shard addition. (5) During extreme spikes, implement adaptive sampling (reduce from 100% to 10% sampling) to maintain system stability while preserving data quality. Real-time dashboards show a sampling indicator when active.

Q7: How do you ensure data accuracy when events are processed out of order?

Use event-time processing (not processing-time) in Flink. Each event carries its own timestamp. Flink's watermark mechanism handles out-of-order events by allowing a configurable lateness window. Late events within the window are processed correctly. Events outside the window are logged for monitoring but not processed. The system tracks a "processing lag" metric to alert when out-of-order events exceed acceptable thresholds.

Q8: How would you handle cookie restrictions and GDPR?

Implement a multi-strategy approach: (1) First-party cookies for basic tracking with user consent. (2) Server-side tracking via Measurement Protocol for data accuracy. (3) Cookieless identification using privacy-preserving techniques (contextual signals, first-party ID). (4) Consent management platform integration — SDK checks consent status before setting cookies or sending personal data. (5) IP anonymization always on. (6) Automatic data deletion after configured retention period.