Reconcile Lead-Source ROI

command

Join spend and revenue by lead source and report ROI.

Download .md

Reconcile Lead-Source ROI

Join spend and revenue by source to calculate return on ad spend and identify your most efficient channels.

Inputs

  • Spend data source — where marketing spend is tracked (ad platform export, CRM field, accounting system)
  • Revenue data source — system of record for attributed revenue (CRM deals, invoices, accounting)
  • Attribution window — days between spend and revenue recognition (default: 30)
  • Reporting period — date range to analyze

Steps

  1. Export spend by lead source from {spend_data_source} with consistent date formatting.
  2. Export attributed revenue by lead source from {revenue_data_source} within {attribution_window}.
  3. Join datasets by source and calculate aggregate spend and revenue per source.
  4. Compute metrics: total spend, attributed revenue, ROAS (return on ad spend), margin percentage.
  5. Sort by ROAS to identify top and bottom performers.
  6. Flag sources with fewer than 10 conversions as low-confidence.
  7. Document attribution assumptions and share findings with finance and marketing.

Constraints

  • Do not mix revenue from different attribution models; use one source of truth.
  • Exclude records with missing or ambiguous lead-source attribution.
  • Pause spend on sources with ROAS below 1.0 until root cause is investigated.

Done when: You have a reconciled report showing spend, attributed revenue, ROAS, and margin by source for {reporting_period}, with low-confidence sources flagged.