Design a Data Model for an Ads Platform
Design a data model for an online advertising system that supports creating ads and analyzing delivery and performance. The system should support advertisers with multiple campaigns, campaigns containing ad groups and creatives/ads, targeting (e.g., geo, device, interests, custom audiences), budgeting and pacing (daily/lifetime budgets), and reporting/analytics on delivery events like impressions, clicks, and conversions.
Tasks:
- Propose a relational schema (tables + key columns) for the "source of truth" entities.
- Decide how to represent targeting in the model (normalized tables vs. JSON/KV vs. hybrid) and justify your choice.
- Design how event logs (impression/click/conversion) should be stored for high volume and how they join back to ads/campaigns for reporting.
- List the most important indexes/partition keys.
- Provide 2-3 example queries the business will need (e.g., campaign spend yesterday, CTR by creative, conversions by audience).
Assumptions:
- A typical ads hierarchy: Advertiser → Campaign → AdGroup → Ad/Creative.
- Event volume is large (analytics-scale). State any additional assumptions you need.
AI interview prep
Core dilemma
Balancing the need for a normalized, queryable relational model for campaign management and budgeting against the extreme volume and analytical requirements of event data (impressions, clicks, conversions).
3 tradeoffs and 2 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Design a Data Model for an Ads Platform
Design a data model for an online advertising system that supports creating ads and analyzing delivery and performance. The system should support advertisers with multiple campaigns, campaigns containing ad groups and creatives/ads, targeting (e.g., geo, device, interests, custom audiences), budgeting and pacing (daily/lifetime budgets), and reporting/analytics on delivery events like impressions, clicks, and conversions.
Tasks:
- Propose a relational schema (tables + key columns) for the "source of truth" entities.
- Decide how to represent targeting in the model (normalized tables vs. JSON/KV vs. hybrid) and justify your choice.
- Design how event logs (impression/click/conversion) should be stored for high volume and how they join back to ads/campaigns for reporting.
- List the most important indexes/partition keys.
- Provide 2-3 example queries the business will need (e.g., campaign spend yesterday, CTR by creative, conversions by audience).
Assumptions:
- A typical ads hierarchy: Advertiser → Campaign → AdGroup → Ad/Creative.
- Event volume is large (analytics-scale). State any additional assumptions you need.