Aggregation and Reporting
You'll learn: How to design time-series aggregations for tracking notification performance metrics across channels and campaigns.
The Core Challenge
Once you've captured raw analytics events (sent, delivered, opened, clicked), you need to roll them up into meaningful reports. Business teams want answers like: "What's our email open rate this week?" or "How does SMS perform vs push notifications?" Computing these from billions of raw events in real-time is impractical—you need pre-aggregated data organized by time windows and dimensions.
Time-Series Aggregation Design
Think of this like summarizing a book chapter-by-chapter instead of re-reading every word each time someone asks about the plot.
Key dimensions to aggregate by:
- Time buckets: Minute, hour, day, week, month
- Channel: Email, SMS, push, in-app
- Campaign ID: Which marketing campaign or notification type
- Status: Sent, delivered, bounced, opened, clicked
Example aggregation table schema:
aggregated_metrics {
bucket_timestamp: timestamp,
granularity: enum(minute, hour, day),
channel: string,
campaign_id: string,
sent_count: int,
delivered_count: int,
opened_count: int,
clicked_count: int
}
Processing Strategy
Use stream processing (Spark Streaming, Flink, or Kafka Streams) to consume analytics events and maintain running aggregates in real-time. For larger time windows, batch jobs recompute hourly/daily rollups to ensure accuracy.
Rate calculations happen at query time: open_rate = opened_count / delivered_count. Store counts, not percentages—they're easier to re-aggregate across different dimensions.
Store aggregates in time-series databases (InfluxDB, TimescaleDB) optimized for timestamp-range queries and downsampling.
Key Takeaway: Pre-aggregate notification metrics into time-bucketed tables by channel and campaign, using stream processing for real-time updates and storing raw counts for flexible reporting.