How do you check CRM against billing?

You check CRM against billing by matching every won deal in the CRM to the invoices that belong to it, and asking for each deal whether there is an invoice, whether the amount is right and whether it went out on time. The match works best through a deal number on the invoice. Without that number you match on customer, amount and date. Every deal with no invoice, or with a lower invoiced amount and no explanation, is a possible leak. Every invoice with no deal is usually a data error in the CRM, but that needs fixing too, or the check stays unreliable.

Why this check pays off so well

The CRM is where sales records what has been sold. Billing is where finance records what has been charged. In an ideal world the two are equal, apart from timing. In practice they differ, and every difference at billing's expense is revenue that never arrives.

Why the two drift apart is described in revenue leakage between CRM and billing. The short answer: the handover from deal to invoice almost always goes through a person, and whatever a person has to hand over can get stuck.

This check is often the first one a company carries out, because it can be done with two exports and a spreadsheet, and because the amounts per deal can be large. It is one of the core checks in how to find revenue leakage in a business.

Comparing CRM data with billing: what you need

From the CRM. All deals with the status won over a closed period. For each deal: deal number, customer name and customer ID, amount, close date, products or service lines if you track them, and whether it is a one-off or recurring deal. In HubSpot, Salesforce and Pipedrive this is a standard export.

From billing. All invoices over the same period plus a margin of two or three months afterwards, because invoices often come later than the close date. For each invoice: invoice number, customer and customer account number, date, amount excluding VAT and, if available, a reference to the deal or order.

Watch out for two pitfalls before you start:

• VAT. CRM amounts are usually excluding VAT, invoice totals often including. Always compare excluding VAT.
• Recurring deals. A deal worth EUR 36,000 a year may be invoiced as twelve monthly invoices of EUR 3,000. In that case, compare the sum of the invoices over the period with the part of the deal value that belongs to that period.

Why the CRM amount and the invoiced amount are conceptually not the same, even when everything goes right, is explained in why CRM data is not the same as financial data.

Step 1: matching

Matching is the hardest part. There are three levels.

Exact match on deal number. If the deal number or an order number is on the invoice, you match directly. This is the most reliable method. If the deal number is not yet on your invoices, adding it is the best investment you can make in this check.

Match on customer. If there is no deal number, you match on customer. That only works if the customer has the same ID in both systems, or if you build a mapping table between CRM company and customer account. Duplicate customers or differing names are where it goes wrong.

Match on customer, amount and date. Within a customer, you look for invoices whose amount and date fit a deal. This works reasonably well for simple deals, and poorly for deals invoiced in parts or invoices that combine several deals.

In practice you use a combination. First exact, then on customer, then the rest by hand or with software that suggests likely matches. How AI can help here, for example with differing names and split invoices, is covered in can AI see when CRM and billing do not match.

Step 2: classifying the differences

After matching, every deal and every invoice falls into one of these categories.

Focus on the two categories marked "possibly". That is where the money is.

Step 3: investigating

For every deal with no invoice and every deal with a lower invoice, you find the cause. The most common ones:

• The deal was invoiced but not matched. The invoice is under a different customer name or combined with another deal. Not a leak, but a reason to improve the matching.
• The deal was cancelled but is still marked as won. The customer pulled out and nobody set the deal to lost. Not a leak, but a polluted pipeline and an unreliable forecast.
• The deal was partly invoiced. A phase or a product has not yet been invoiced. Check whether that is according to plan or forgotten.
• A discount after the deal. The price was lowered after closing, and that was never reflected in the CRM. Check whether the discount was approved.
• The invoice was never raised. This is the real leak. Someone forgot, or assumed someone else was doing it.

Worked example

Worked example: suppose a software company with EUR 7 million in revenue compares 620 won deals from the past year with billing. After matching:

• 540 deals matched with the correct amount.
• 32 deals matched with a lower invoiced amount. After investigation, 20 are explained by approved discounts. For 12 the difference is unexplained, averaging EUR 1,900: EUR 22,800.
• 48 deals with no invoice. After investigation: 21 turned out to be invoiced under a different name, 13 had been cancelled but not set to lost, and 14 were genuinely never invoiced, averaging EUR 6,200: EUR 86,800.

Together EUR 109,600 in unexplained differences. The 34 deals that should have been matched or marked as lost are not leaks, but they are the reason this check takes so much manual work without a clean-up first.

From one-off to continuous

Running this check once brings in money straight away. But new deals are closed every week, and the handover to billing goes wrong again every week. To keep the leak closed:

1. Put the deal number on every invoice. A mandatory field in the billing system, or carried over automatically when the invoice is created from the CRM.
2. Agree a deadline. For example: every won deal has an invoice within 30 days, or a planned invoice date if it is phased.
3. Run the check weekly or monthly. Deals that pass the deadline become a task for an owner.
4. Keep the CRM clean. Cancelled deals are set to lost, customers have one ID. Otherwise the check keeps producing noise.

The technical and process side of reconciling CRM and billing on a structural basis is also called CRM-to-billing reconciliation. It is described in CRM-to-billing reconciliation explained. How this leak pattern looks alongside the seven others is on the use cases page.

Frequently asked questions

Which system leads: the CRM or billing?

For what has been sold, the CRM leads. For what has been invoiced, billing leads. The check is precisely about the difference between the two. For official revenue figures, the accounts lead.

What if my CRM is not reliable?

Then that is the first thing this check will show. Many deals with no invoice turn out, on closer inspection, to be cancelled or duplicated. Clean up the CRM for the period you are checking, and agree how to keep it clean.

How long after the close date should an invoice exist?

That depends on your process. For immediate delivery, within a few weeks. For phased projects, according to the invoicing schedule. Record the deadline, so the check knows when a missing invoice is a problem.

Can I do this check in Excel?

Yes, certainly the first time. With two exports and a match on deal number or customer you get a long way. For a recurring check Excel becomes laborious and error-prone, and automation is the logical next step.