Design an ads data model

System Design / Data Modeling: Ads Data Model

Design the data model (logical schema) for an ads platform that supports:

• Advertisers creating campaigns with line items and creatives
• Targeting (geo, device, content/genre, audience segments)
• Budgeting and pacing
• Serving and logging events: impression , click , and optionally conversion
• Reporting and analytics: spend, delivery, frequency, reach, and performance breakdowns

What to produce

• Key entities and relationships (ER-style description).
• Which parts belong to:
  • an OLTP store (serving/control plane)
  • an event log / OLAP store (analytics)
• Primary keys, important indexes, and partitioning/sharding considerations.
• How you would model:
  • creative rotation and experiments
  • deduplication/idempotency for events
  • slowly changing dimensions (e.g., targeting changes)

Assume large scale (global traffic) and that reporting must be available with low delay (minutes).