What is the best database design for tracking lead source performance over time?
Quick Answer: The best database design for tracking lead source performance over time is an event-based schema with normalized source, campaign, and lead tables, plus append-only attribution events tied to timestamps. This structure preserves historical accuracy, supports multi-touch attribution, and makes it easy to query conversion rate, pipeline value, and revenue by source over any date range without overwriting prior data.
Detailed Explanation
For reliable lead source performance analysis, avoid storing only a single mutable "lead source" field on the lead record, because it destroys historical context the moment a lead is reassigned or re-attributed. Instead, use a normalized relational model where leads, sources, campaigns, touchpoints, and outcomes are separated into distinct tables, and every source interaction is recorded as an immutable event with a timestamp, channel, and attribution metadata. This enables first-touch, last-touch, and multi-touch reporting, while also allowing time-series analysis of source efficiency by week, month, region, or sales stage. If you need long-term performance tracking at scale, pair the transactional schema with indexed fact tables or materialized views so dashboards can aggregate conversions, CAC, SQL rate, and revenue contribution quickly without compromising data integrity.
Key Technical Drivers
- Create an append-only `lead_attribution_events` table with `lead_id`, `source_id`, `campaign_id`, `touch_type`, `occurred_at`, and `weight` so historical attribution is never overwritten.
- Normalize core entities into `leads`, `sources`, `campaigns`, and `conversions`, then join them through indexed foreign keys to support fast cohort and funnel queries.
- Add pre-aggregated reporting tables or materialized views by day/source to track metrics such as lead volume, MQL rate, SQL rate, pipeline value, and closed-won revenue over time.