How do you model lead source, source detail, and campaign attribution in CRM data architecture?
Quick Answer: Model lead source, source detail, and campaign attribution as separate but linked dimensions: a canonical acquisition source on the lead/contact record, a granular source-detail field for the specific touchpoint, and a normalized campaign attribution layer that can store first-touch, last-touch, and multi-touch credits. In practice, this means every inbound record should resolve to a controlled source taxonomy, preserve the original acquisition event, and connect to campaign objects through immutable event logs so reporting stays consistent across marketing, sales, and revops.
Detailed Explanation
A robust CRM data architecture should distinguish between where a lead came from, how that lead was acquired, and which campaigns influenced conversion. The lead source is the highest-level channel classification, such as Organic Search, Paid Search, Referral, Partner, Event, or Outbound; source detail captures the specific mechanism or subchannel, such as a Google Ads campaign, a LinkedIn Lead Gen Form, a webinar registration, or a referral partner name; campaign attribution then links those acquisition events to one or more marketing campaigns using a governed model that can support first-touch, last-touch, and weighted multi-touch logic. The best practice is to store these values in normalized fields and event tables, preserve the original values at creation time for auditability, and prevent downstream overwrites so reporting remains accurate across lifecycle stages and attribution windows.
Key Technical Drivers
- Use a controlled source taxonomy for the primary `lead_source` field and keep it limited to enterprise-reportable categories; map all inbound channels into this canonical set through validation rules or middleware.
- Store `source_detail` as the exact subchannel or touchpoint descriptor, and write the original value to an immutable acquisition event so the CRM can preserve campaign metadata even if the lead is later re-engaged.
- Model campaign attribution in a separate association table or event stream with fields for `campaign_id`, `touch_type` (first-touch, last-touch, assisted), `timestamp`, and `credit_weight` to support both operational dashboards and multi-touch revenue reporting.