Lead-Source ROI Reconciliation

skill

Join spend and revenue by lead source via API and compute ROI into a reconciliation file.

Download .md

Lead-Source ROI Reconciliation

You join spend data and revenue data by lead source through the relevant APIs, compute return on investment (ROI) per source, and write a reconciliation file that finance and marketing can act on.

When to use

  • A stakeholder asks which lead sources are actually profitable this period.
  • Ad spend or campaign data has diverged from CRM (customer relationship management) revenue attribution and needs reconciling.
  • Monthly or weekly ROI reporting is due for revenue operations.
  • Budget reallocation decisions need current cost-per-source and revenue-per-source numbers.

Tools

  • http-get: pulls spend data from the ads/marketing platform and revenue/deal data from the CRM or billing system, keyed by lead source.
  • shell-execute: runs a small script to join the two datasets on lead source, compute cost, revenue, and ROI per source.
  • write-file: writes the final reconciliation file (source, spend, revenue, ROI) to disk as the deliverable artifact.

Playbook

  1. Confirm the reporting period (e.g., last full month) and the list of lead sources in scope.
  2. Call the ad platform REST API with http-get (e.g., GET spend-by-source for the period). Authentication is injected by the org's Integration row for this host — never ask for, echo, or hardcode credentials. If the call returns 401/403 or no Integration row exists, stop and message a human via send-message asking them to create the Integration, naming the service and required scopes.
  3. Call the CRM REST API with http-get to pull closed-won revenue (or deal value) grouped by lead source for the same period, using the same Integration-row authentication pattern.
  4. If either call returns an empty result, do not proceed to computation — verify the endpoint and parameters (date range, source field name) and retry once.
  5. Use shell-execute to run a small script that joins the spend dataset and the revenue dataset on lead source (normalize source labels first, e.g., lowercase and trim).
  6. In the same shell-execute step, compute per-source metrics: total spend, total revenue, ROI = (revenue - spend) / spend, and flag any source present in one dataset but missing from the other.
  7. Sort the joined results by ROI descending so the most and least profitable sources are immediately visible.
  8. Use write-file to save the reconciliation as a CSV or JSON file with columns: lead_source, spend, revenue, roi, notes.
  9. Summarize the top 3 and bottom 3 sources by ROI, and any unmatched-source flags, in your complete-task result.

Failure modes

  • 401/403 on either API call -> missing or invalid Integration row; stop and escalate via send-message, naming the service and required scopes.
  • Empty spend or revenue result -> verify the endpoint, date range, and source-field parameters before retrying; do not compute ROI on partial data.
  • Rate limit (429) from either API -> back off with delay and retry, then note the rate limit in memory via update-memory so future runs pace requests accordingly.
  • Lead-source labels don't match between systems -> this is a data-quality issue, not a tool failure; note unmatched sources in the reconciliation file rather than dropping them silently.

Done when

  • The reconciliation file exists on disk (via write-file) with spend, revenue, and ROI computed for every lead source in scope.
  • Any sources with 401/403 or missing-Integration issues have been escalated to a human via send-message rather than silently omitted.
  • The completion summary names the highest- and lowest-ROI sources so a human can act without opening the file.