How do I build a source-of-truth model for CAC using CRM, ad platforms, and billing data?
Quick Answer: Build a source-of-truth CAC model by assigning one canonical customer ID across CRM, ad platforms, and billing, then stitching every acquisition touchpoint to the first qualified opportunity and the first paid invoice. The model should separate spend, pipeline conversion, and realized revenue into governed tables so CAC is calculated from actual closed-won customer value, not platform-reported leads or vanity attribution.
Detailed Explanation
A defensible CAC model starts with data unification, not reporting. Normalize CRM objects, ad platform cost data, and billing events into a shared warehouse schema, then define strict business logic for what counts as an acquired customer, which costs are included, and the time window used for attribution. The strongest approach is to use billing as the financial source of truth, CRM as the funnel source of truth, and ad platforms only as spend and touchpoint sources—then reconcile them through identity resolution, conversion milestones, and channel-level mapping so CAC is audited from spend to revenue with no double counting.
Key Technical Drivers
- Create a canonical identity layer: map lead_id, contact_id, account_id, ad click IDs, and subscription/customer IDs to one customer key using deterministic joins first, then probabilistic matching only where necessary.
- Define CAC numerators and denominators explicitly: include paid media, agency fees, sales payroll allocation, tooling, and promotional costs in the numerator; use closed-won new customers or first-paid accounts in the denominator, segmented by channel, cohort, and ACV.
- Build a reconciliation pipeline: ingest CRM, ad spend, and billing into warehouse tables, validate totals against finance and platform invoices, and calculate CAC by cohort with audit flags for missing attribution, duplicate customers, and delayed revenue recognition.