Why your ERP, CRM and spreadsheet never agree

A familiar meeting: finance says September revenue was one number, sales says another, and the spreadsheet someone built last year says a third. Twenty minutes later nobody has decided anything, because the meeting became an argument about whose number is right.

The usual instinct is to pick a winner. The ERP is the system of record, so the ERP is right. That settles the argument and misses the point. The numbers differ for reasons, and the reasons are findable.

The five reasons systems disagree

1. Different grain. The ERP stores invoice lines. The CRM stores deals. The spreadsheet stores one row per customer per month. They are all "revenue", at three different levels of detail, and adding them up gives three different totals.

2. Different time. A deal is closed in the CRM in October, shipped in September, invoiced in September and paid in November. Each system stamps a different one of those dates on the same sale. A month-end report built on different dates will never match.

3. Different keys. The ERP calls a customer C-100. The CRM calls the same customer A-1. Somewhere there is a spreadsheet, maintained by one person, that says they are the same. If that mapping is missing, wrong or out of date, revenue lands on the wrong account or on no account.

4. Manual edits. The spreadsheet started as an export. Then someone fixed a typo, then added a column for a special case, then pasted last quarter's figures over this quarter's. The export and the edited copy are no longer the same data.

5. Different definitions. Does "revenue" include credit notes? Taxes? Freight? Does an "active customer" mean bought in the last 90 days, or has an open contract? Each team answered these questions once, informally, and differently.

What to do instead of picking a winner

1. Write one definition per metric. One sentence, one owner, one place where it is calculated. If finance and sales really need different numbers, give them different names: "invoiced revenue" and "booked revenue".
2. Build the mapping explicitly. A small table that says which ERP customer is which CRM account. It needs an owner and a check for customers that are missing from it.
3. Reconcile on every refresh, and show the gap. Do not hide differences by forcing one system to match. List them.

A reconciliation you can run today

Assume three tables, however you get them out of your systems: ERP invoices, CRM deals and the mapping.

-- erp_invoices(invoice_id, customer_id, invoice_month, amount)
-- crm_deals(deal_id, account_id, close_month, stage, amount)
-- customer_map(erp_customer_id, crm_account_id)

WITH erp AS (
  SELECT m.crm_account_id AS account_id,
         i.invoice_month  AS month,
         SUM(i.amount)    AS erp_amount
  FROM erp_invoices i
  JOIN customer_map m ON m.erp_customer_id = i.customer_id
  GROUP BY m.crm_account_id, i.invoice_month
),
crm AS (
  SELECT account_id,
         close_month   AS month,
         SUM(amount)   AS crm_amount
  FROM crm_deals
  WHERE stage = 'closed_won'
  GROUP BY account_id, close_month
)
SELECT COALESCE(erp.account_id, crm.account_id) AS account_id,
       COALESCE(erp.month, crm.month)           AS month,
       COALESCE(erp.erp_amount, 0)              AS erp_amount,
       COALESCE(crm.crm_amount, 0)              AS crm_amount,
       COALESCE(erp.erp_amount, 0) - COALESCE(crm.crm_amount, 0) AS gap
FROM erp
FULL OUTER JOIN crm
  ON crm.account_id = erp.account_id
 AND crm.month = erp.month
WHERE COALESCE(erp.erp_amount, 0) <> COALESCE(crm.crm_amount, 0)
ORDER BY ABS(COALESCE(erp.erp_amount, 0) - COALESCE(crm.crm_amount, 0)) DESC;

The FULL OUTER JOIN matters. A normal join drops anything that exists on only one side, which is exactly what you are looking for. This query keeps both.

On a small sample with three mapped accounts, it returns three rows:

Read it like a list of questions, not a verdict. Account A-2 shows up twice with opposite signs: the deal was closed in October but invoiced in September. That is a timing difference (reason 2), not an error. Account A-3 differs by 50 in the same month, which is a real discrepancy someone should look at (reason 1, 4 or 5). Account A-1 is absent because it matched exactly.

Catch the missing keys

Reason 3 deserves its own check, because an unmapped customer silently drops out of the join above:

SELECT i.customer_id,
       SUM(i.amount) AS amount_without_a_crm_account
FROM erp_invoices i
LEFT JOIN customer_map m ON m.erp_customer_id = i.customer_id
WHERE m.erp_customer_id IS NULL
GROUP BY i.customer_id;

On the same sample this returns one customer, C-999, with 250 of invoiced revenue that belongs to no CRM account. Run this first. It is often the biggest gap, and it is the cheapest to fix.

Make it routine

A reconciliation you run once is an audit. A reconciliation that runs on every refresh is a control. Put both queries in the pipeline, store the results, and show the open gaps next to the report they affect. Over a few weeks the list gets shorter, and "whose number is right" stops being a meeting topic.

That is level two of the ladder: one version of the truth. Everything above it, from forecasts to AI agents, reads these numbers. If they disagree, so will the results.

The queries use standard SQL and were run on sample data in SQLite. They work with minor changes in Postgres, SQL Server, Snowflake, Databricks and other warehouses. Where a database lacks FULL OUTER JOIN, a UNION of two LEFT JOINs does the same job.