Skip to main content
CRM billing reconciliation runbook: weekly checks, automated queries and exception triage SOP

CRM billing reconciliation runbook: weekly checks, automated queries and exception triage SOP

A practical weekly system for sales and CS teams to catch billing-to-CRM mismatches before they quietly rot your forecast

The gap between what your CRM says a customer owes and what your billing system actually charged is where forecasts go to die. Not loudly. Nobody files a ticket when an opportunity closes at $48k ARR in the CRM but the invoice goes out at $44k because someone applied a legacy discount that never made it into the deal record. It just sits there. Three months later finance pulls a revenue report, sales pulls a pipeline report, and the two numbers don't agree by a few percent that nobody can fully explain.

This runbook is about closing that gap on a weekly cadence instead of discovering it during a quarterly scramble. The focus is narrow on purpose: reconciling CRM billing data against your actual billing and invoicing source of truth, catching exceptions fast, and producing one clean executive report that protects forecast integrity.

Where CRM and billing actually drift apart

Most teams assume the two systems agree because the deal closed cleanly. In real operations, the drift comes from a handful of predictable seams.

The most common one is mid-contract changes that only hit one system. A customer upgrades mid-term, the CS team updates the billing platform, but the CRM opportunity still reflects the original contract value. Now your ARR rollup understates reality and your renewal forecast is anchored to the wrong number.

The second seam is discounts and proration logic. Deal desk approves a 12% discount, the rep logs 10% because they eyeballed it, and billing applies the 12% from the signed order form. Four points of margin, invisible until reconciliation.

Then there are currency and billing frequency mismatches. A European account billed quarterly in EUR shows up in the CRM as an annual USD figure converted at some stale rate. Nobody's wrong exactly — the systems just speak different languages, and the translation is where the error lives.

And finally, timing. Billing recognizes a start date based on provisioning; the CRM close date was two weeks earlier. For a weekly forecast that depends on when revenue actually starts, that two-week offset shifts dollars across period boundaries.

None of these are exotic. What makes them dangerous is that each one is individually small and collectively meaningful. If you're running decent pipeline hygiene already — and if you're not, standardizing your pipeline stages is the prerequisite before any of this works — reconciliation is the next layer that keeps clean inputs honest over time.

The weekly reconciliation checklist

The point of a weekly rhythm is that exceptions stay small enough to fix in minutes instead of accumulating into a project. Here's the core checklist. One owner, same day each week, roughly 30–45 minutes once the queries are automated.

  1. Pull every CRM opportunity marked closed-won in the last 7 days and confirm a matching billing record exists. No match = exception.
  2. Compare contract value field (CRM) against billed amount (billing system) for those records. Flag anything off by more than a set tolerance — most teams use something like 2% or $250, whichever is larger.
  3. Check billing frequency and currency match between systems for new and amended accounts.
  4. Reconcile mid-cycle amendments from the past week

    upgrades, downgrades, add-ons. Confirm both systems reflect the change.

  5. Verify start/recognition dates align within your acceptable window (usually a few business days).
  6. Scan for orphaned billing records — invoices with no corresponding CRM opportunity at all. These are where silent revenue or silent errors hide.
  7. Confirm churned/cancelled accounts in billing are reflected as closed-lost or churned in the CRM, not still sitting in an active renewal forecast.
  8. Log every exception with a category, owner, and due date before closing the session.

The log is the actual deliverable; make it part of the checklist cadence rather than an optional step.

That last line matters more than it looks. A checklist that finds exceptions but doesn't track them to resolution just generates anxiety. The log is the actual deliverable.

Automated reconciliation queries that do the grunt work

Running the checklist manually by exporting two spreadsheets and eyeballing them works for about 20 accounts. Past that it falls apart. The whole thing becomes sustainable when the comparison logic runs as a set of saved queries that surface only the mismatches.

QueryWhat it comparesException trigger
Amount matchCRM contract value vs. billed totalVariance > 2% or > $250
Missing invoiceClosed-won opps vs. billing recordsNo matching billing record
Orphan invoiceBilling records vs. CRM oppsNo matching opportunity
Frequency/currencyBilling terms both systemsAny field mismatch
Date alignmentCRM close/start vs. billing startOffset beyond window
Churn consistencyCancelled in billing vs. CRM stageStill active in CRM

The goal isn't a dashboard full of green checkmarks. It's a short list — ideally under ten rows most weeks — of things that genuinely don't line up. If your query output is 60 rows every week, your tolerance thresholds are too tight or your upstream data entry is broken, and you need to fix that first.

What tends to happen when teams first run these queries is a backlog of old mismatches surfaces that nobody knew about. That's normal. Clear it once, then the weekly run keeps the number near zero. The question of what to automate versus keep as a human judgment call is worth thinking through deliberately — this automation decision framework is a useful lens, because detection should be fully automated but resolution almost never should be.

Exception triage SOP

Finding exceptions is easy. What breaks down in practice is deciding who fixes what, how fast, and when it needs to escalate. Without a triage SOP, every exception becomes a Slack thread that dies on Thursday.

  1. Categorize the exception using the query that caught it (amount, missing, orphan, frequency, date, churn). The category tells you the likely owner.
  2. Assign severity. A $300 variance on a small account is low. A missing invoice on a $90k deal, or an orphan invoice, is high — those touch recognized revenue directly.
  3. Route to the owner by category. Amount and discount mismatches usually go to deal desk or the rep who closed it. Missing and orphan records go to billing ops. Churn inconsistencies go to the CS owner. Don't route everything to one person; it creates a bottleneck and fixes stall.
  4. Set resolution SLAs by severity. High-severity

    resolve or escalate within 48 hours. Medium: within the week. Low: batched and cleared at the next weekly run.

  5. Escalate anything unresolved past SLA to the revenue ops lead, and anything above a dollar threshold (say $25k of affected ARR) straight to the exec report regardless of resolution status.
  6. Record root cause on close. One dropdown

    data entry error, process gap, timing, system sync, or legitimate exception. This is how you stop playing the same whack-a-mole every month.

That root cause field is quietly the most valuable part of the whole SOP. After a month or two you'll see patterns — "40% of our amount mismatches come from discounts not logged at close" — and that tells you to fix the upstream process, not just the symptom.

A simple flow diagram can make ownership and escalation points obvious to everyone on the rotation.

Process diagram

Use the visual as a quick reference during the weekly run so routing doesn't stall on questions about ownership or SLA.

When this level of rigor makes sense (and when it doesn't)

If you're a small team with fewer than roughly 50 active billing accounts and simple annual contracts, a lightweight monthly check is probably enough. Weekly reconciliation earns its keep once you have meaningful volume of mid-cycle changes, multiple currencies or billing frequencies, or a forecast that leadership actually makes decisions on. Multi-product or usage-based billing pushes you firmly into the weekly camp because those models drift constantly.

Where it's genuinely a bad idea is bolting this on before your CRM data entry is reliable. If reps aren't consistently logging contract values or close dates, reconciliation just surfaces noise you can't act on. Fix the inputs first.

The other thing worth saying: the triage flow above assumes someone actually owns it. If revenue ops is spread thin and exceptions sit in a shared queue with no named owner, the SOP doesn't save you. Assign a primary owner for the weekly run before you go live with any of this.

A real scenario

A mid-market B2B SaaS company — roughly 180 active accounts, mix of annual and quarterly billing, a couple of currencies — was running reconciliation manually at quarter-end only. Each quarter close turned into a two-to-three day fire drill where finance and sales argued about why CRM ARR and billed revenue were off by around 3–4%.

When they broke the drift down, most of it came from two sources: mid-term upgrades logged in billing but not the CRM, and discounts applied at slightly different rates than what reps recorded. Nothing fraudulent, just accumulated small gaps across the quarter.

They moved to a weekly run with the query set above. The first week surfaced around 30 open mismatches — the accumulated backlog. After clearing it, weekly exceptions settled into single digits, usually somewhere between four and seven. The quarter-end variance dropped under 1%, and close stopped being a negotiation. The forecast started matching what was actually getting invoiced, which meant leadership stopped mentally applying a fudge factor to the sales number before every board meeting.

Time cost was modest — one analyst, maybe half an hour a week once the queries were saved, plus whatever routing the exceptions required from their respective owners.

The executive reconciliation report

Leadership doesn't want the exception log. They want one page that answers: can I trust the forecast number, and if not, by how much and why. The report that works is short and consistent every week.

  1. Headline variance. Total CRM ARR vs. total billed ARR, as a dollar figure and a percentage. One number, top of the page.
  2. Exception count and aging. Open exceptions this week, how many are past SLA, how many are high-severity.
  3. Dollar exposure. Total ARR affected by unresolved high-severity exceptions. This is the number that tells an exec how much of the forecast is genuinely in question.
  4. Root cause rollup. Top two or three categories driving exceptions, so leadership can see whether this is a one-off or a systemic process gap.
  5. Trend line. Variance and exception count over the last several weeks. A flat or declining line builds trust; a climbing one is an early warning.
  6. One action note. What's being fixed upstream this week.

Keep it to a single screen. The value isn't completeness — it's that it comes out every week, looks the same, and lets an exec glance at one variance number and know whether the forecast is clean. When that number stays under about 1% week over week, people stop second-guessing the pipeline. That trust is the entire point of the exercise.

Closing the loop

CRM-to-billing drift is corrosive precisely because it hides. Nobody owns the gap, so it compounds quietly until a quarterly report forces a reconciliation nobody budgeted time for. A weekly runbook flips that — small, frequent, owned, with automated detection handling the tedious comparison and humans only touching the handful of records that genuinely don't line up.

Start with the checklist, save the queries so the comparison runs itself, route exceptions through a real triage SOP with SLAs and root-cause logging, and publish one consistent executive report. Do that for a quarter and the forecast number stops being something leadership mentally adjusts and starts being something they actually rely on.

Start with the checklist, save the queries so the comparison runs itself, route exceptions through a real triage SOP with SLAs and root-cause logging, and publish one consistent executive report. Do that for a quarter and the forecast number stops being something leadership mentally adjusts and starts being something they actually rely on.

Built for Businesses Tailored CRM features for customer-centric teams
Save Time Automate follow-ups and streamline client data management
Boost Engagement Personalized communication to strengthen customer loyalty
Grow Revenue Optimize sales pipelines and accelerate deal closures