How to reconcile CRM and accounting data before automating a sync
Resolve identity, field ownership and record-level differences before enabling writes. Run the synthetic CSV example and inspect the mismatches hidden by totals.
Published by DWKD. Practical integration guide with synthetic CSV files and an offline checker. All identifiers and amounts are fictional.
Before automating a CRM/accounting sync, reconcile identity, field ownership and comparable records. The important question is not whether a connector can move data. It is whether the next write will update the right record, preserve the right value and leave a difference someone can explain.
The downloadable reconciliation example compares seven CRM invoice-mirror rows with seven ledger rows. Four invoice IDs match, four need attention, and two missing records cancel each other in the overall amount. The checker makes no network requests and writes to neither system.
Choose one comparable business question
A CRM deal amount and an accounting invoice balance are not interchangeable. A deal can be won before anything is invoiced, an invoice can be partially paid, and credits can change a balance. Starting with “make the two dashboards equal” can produce a connector that overwrites useful information to hide a difference in meaning.
For this example, the question is narrower: Does the CRM’s invoice mirror contain the same customer, currency and open amount as the ledger extract for each invoice at the agreed snapshot? Both files represent one invoice per row, with nonnegative open amounts. The synthetic scope is USD and EUR; it excludes credit notes, payments, tax calculation and exchange-rate conversion. Its totals are not recognized revenue or a financial close.
In your own scope, record the cutoff, timezone, export filters and when each extraction finished. If systems cannot provide the same point-in-time snapshot, record the extraction window and classify possible timing differences. A comparison cannot establish completeness beyond the files it received.
Separate identity from resemblance
Names, email addresses and domains help a human investigate a match. They are fragile defaults for an automatic merge. A company can change its name; two accounts can share a billing address; a group can have several legal entities. Keep the source system’s stable ID and an explicit mapping to the destination ID.
The sample crosswalk maps C-01 to A-01. The invoice key is a shared external invoice ID. This is a deliberately small one-to-one example. If your business needs many CRM accounts mapped to one ledger customer, document that relationship and its consequences instead of bypassing a uniqueness check.
External IDs are supported by real integration APIs. For example, Salesforce’s external-ID upsert documentation describes different behavior for no match, one match and multiple matches. A connector’s ability to upsert does not establish that your chosen ID is unique or correctly mapped.
Assign authority by field, not by application
“The CRM is the source of truth” leaves too much unspecified. Record the direction and conflict rule for every field you intend to move. Here is a starting discussion, not a rule imposed on every business.
| Field | Possible authority | Conflict decision |
|---|---|---|
| Sales owner and prospect stage | CRM | Do not let an accounting refresh overwrite the sales workflow. |
| Posted invoice open amount | Ledger | CRM mirror is read-only; investigate disagreement before refreshing it. |
| Billing identity | Explicitly named business owner | Resolve different entities before linking; no automatic name merge. |
| Customer crosswalk | Reviewed integration mapping | Unmapped or conflicting IDs enter an exception queue. |
| Removal or merge | Explicit lifecycle policy | A missing export row is not permission to delete a customer. |
“Latest update wins” is also a business rule, not a neutral default. A later synchronization timestamp may describe a copied stale value. Decide whether a blank means unknown, intentionally cleared or not included; do not let those meanings collapse into one update.
Inspect the synthetic result
The checker rejects duplicate invoice IDs and ambiguous customer mappings instead of silently keeping the last row. It then compares the union of IDs, so a record missing on either side cannot disappear inside an inner join. Amounts are parsed from strings using Python Decimal, avoiding binary floating-point approximation for this two-decimal fixture.
| Invoice | Result | Evidence and next question |
|---|---|---|
| 100, 102, 103 and 105 | Matched | Customer mapping, currency and amount agree. Invoice 103 is zero; zero is not missing. |
| 101 | Amount mismatch | CRM: USD 500.00; ledger: USD 450.00. Check snapshot timing and source history before proposing an update. |
| 104 | Identity unresolved | Both show USD 80.00, but CRM customer C-04 has no approved crosswalk. |
| 106 | CRM only | USD 100.00 exists only in the mirror. Missing ledger data is not proof that the invoice should be deleted. |
| 107 | Ledger only | USD 100.00 exists only in the ledger extract. Investigate filtering, timing and mapping before creating a mirror record. |
The USD control totals are 2,180.00 in CRM and 2,130.00 in the ledger, a difference of 50.00. EUR is 200.00 on each side and remains separate. There is no meaningful combined “2,380.00” money total without an explicit currency-conversion policy.
Now change invoice 101 in a copy of the CRM CSV from 500.00 to 450.00. The USD difference becomes zero, but invoices 106 and 107 still describe different populations, and invoice 104 still lacks a mapping. That is why agreement of totals is a control, not proof of record-level agreement.
Turn differences into decisions
Give each exception an owner, evidence and disposition: correct an export, verify an identity, accept a documented timing difference, or propose a specific field update. The download’s decision log separates the observation from the approved action. The checker reports the first blocking comparison for each invoice; after resolving identity, rerun it to reveal any subsequent currency or amount disagreement.
Keep the original snapshots. Produce a proposed-change file before enabling writes, showing source value, current destination value, intended value and approval basis. Recheck the destination before applying a change; a reconciliation completed yesterday should not overwrite someone’s correction today.
Run it, then test the next boundary
Extract the ZIP and run the following with Python 3.10 or newer. No additional packages or account are needed. A successful command means the comparison ran; it does not mean the records agree. Read the JSON status and exception list.
python3 reconcile.py
python3 -m unittest test_reconcile.py -v
python3 reconcile.py --crm my-crm-copy.csvThe included results provide a baseline. The tests cover offsetting missing records, incorrect customers, currencies, duplicate IDs, ambiguous crosswalks, malformed amounts and exact small decimal sums. Invalid input is rejected; records are never repaired in place.
This is a preparation exercise, not a connector. A production design still needs authenticated access, pagination, bounded retries, replay protection, merge/deletion handling, monitoring and recovery tested against the actual products. Use the retry and recovery demonstration for the next failure-handling question, and the messy-data investigation when the exports themselves are unreliable.
For a broader investigation scope, use the data quality audit evidence pack. If the mappings, definitions or exceptions need a business decision, explore data and integration consulting or Work with Kenny. Bring the disagreement before choosing a synchronization tool.