One view out of three systems

Every business has a number that lives in three places and agrees in none of them. Revenue in the accounting system, deals in the CRM, and usage in the product, each correct by its own definition and unreconcilable at a glance.

The instinct is to build a dashboard. The cheaper move is to have someone read all three and write down what is true.

Say what the number actually means, first

This is the whole exercise. Most reconciliation arguments are definitional rather than numerical.

Does revenue mean invoiced or collected? Does a customer count from the day they signed or the day they went live? Is a deal closed when it is verbally agreed or when the contract is back?

Write your answers down. Anything left ambiguous will be resolved differently each run, and the report will appear to fluctuate for no reason.

Connect the sources and pull one period by hand

For last month, pull:
  from QuickBooks, invoiced revenue by customer
  from the CRM, deals marked closed-won with their value and close date
  from the sheet, live customer counts

Put them side by side per customer. Flag any customer that appears in one
source and not another, and any where the values disagree by more than five
percent.

Use these definitions: revenue means invoiced, not collected. A deal counts
in the month its contract came back, not when it was verbally agreed.

Do not reconcile the differences. Show them to me.

"Do not reconcile them" is the instruction that makes the first run useful. A single tidy number hides the fact that your three systems disagree, which is the thing you most need to see. Once you know where and why they differ, you can decide which source wins. Before that, a reconciled figure is a guess wearing a suit.

Decide which source is authoritative, per field

Not per system. Revenue is authoritative in the accounting system; close dates are authoritative in the CRM; usage only exists in the product. Writing that down is the actual deliverable of this playbook, and it usually survives longer than the report does.

Then schedule it

On the third of each month, produce the reconciled view for last month
using our agreed definitions and source precedence. Write it to the sheet.

List any customer where sources still disagree, with both figures. If none,
say so.

The disagreement list should shrink over the first few months as you fix the records that caused it, then stabilise. If it stays long, the definitions are still ambiguous rather than the data being bad.

What good looks like

One view, monthly, that everybody trusts because they know which source wins for each field and can see where the sources still differ.

What this does not replace is a data warehouse. It replaces the afternoon somebody spends in three tabs, which is a different and more common problem.