Synchronize Customer Records Across Systems
Which phone number is right? The CRM says one thing, the billing system says another, and the support desk has a third that a customer typed in last Tuesday. You can ask the customer, and sometimes you should. But if you have 4,000 customers and three systems, you need a rule, and the rule needs to run without you.
That rule is what this article is about. If you administer a CRM, you already know the symptoms. Duplicate contacts, a company name spelled four ways, an address that changed in one place and not the others, and a sync that was supposed to fix all of this and sometimes makes it worse. Synchronization is not a connector you switch on. It is a set of decisions about identity, authority, and conflict, and the connector only carries them out.
Decide what a customer is before you move a single field
Systems disagree about what a record means. Your CRM may have a person and a company. Your billing system may have only an account, which could be a person or a company. Your support tool may have a requester, who is a person and may not be a buyer at all.
Write down, in plain words, the entities you are syncing and how they relate.
- Person. An individual with an email address.
- Organization. A company or household that pays or is served.
- Account. The billing relationship, which may link to one organization and several people.
If you skip this step, you will sync a billing account into a CRM contact and wonder why a company with six staff looks like six customers or one, depending on the day. Draw the three shapes and mark which system creates each. Then everything else gets easier.
Identity resolution, or how two records become one customer
Identity resolution is the work of deciding that a record in System A and a record in System B refer to the same real world customer. There are two broad approaches.
Deterministic matching uses a key that should be unique and stable. An email address is the common one. A customer number issued by one system and stored in the other is better, because people change emails and share them. An external ID, written into each system and pointing at the other, is the cleanest key you can have.
Probabilistic matching scores similarity across several fields, such as name, phone, postal code, and company, and treats a high enough score as a match. It catches more, and it also gets more wrong.
My advice for a small team is to use deterministic keys wherever they exist and keep fuzzy matching for a review queue, not for automatic merges. A wrong automatic merge is expensive to undo, because it combines two real people's history into one record.
Normalize before you compare
Most failed matches are formatting, not identity. Before you compare, normalize.
- Lowercase and trim email addresses. Treat a plus tag or a dot variation with care, because they are not equal in every mail system.
- Strip phone numbers to digits and store them with a country code. Puerto Rico numbers use the 787 and 939 area codes and sit under country code 1, so a number saved with and without the leading 1 should still match.
- Standardize company suffixes, such as Inc, Corp, LLC, and their Spanish equivalents.
- Fold accents for comparison only. José and Jose should match, but keep the original for display.
That last point matters in a bilingual market. Names with accents, double surnames, and Hispanic naming patterns break matching rules written for a single given name and a single family name. Test your rules on your own data before you trust them.
Survivorship, or who wins when the values differ
Once you know two records are the same customer, you still have two values for most fields. Survivorship rules decide which value becomes the golden one. There are four common policies.
| Policy | How it decides | Best for |
|---|---|---|
| Source priority | One system always wins for a field | Fields with a clear authority, such as billing address from the billing system |
| Most recent wins | The latest edit survives | Contact details people update themselves |
| Most complete wins | The non-empty or longer value survives | Names and descriptions |
| Manual review | A person picks | High value accounts and ambiguous cases |
Do not pick one policy for the whole record. Pick it per field. The billing system should probably own legal name, tax identifiers, and billing address, because those are tied to invoices. The CRM should probably own owner, lifecycle stage, and sales notes. The support tool may own the preferred contact channel. Build a small table with every synced field and the system that owns it, and treat that table as a design document you keep up to date.
A worked example, with hypothetical records
Suppose Marisol Vega appears in three systems.
- CRM, email m.vega@example.com, phone 787 555 0142, company Vega Dental, edited in February.
- Billing, email marisol@vegadental.example, phone 787 555 0199, billing address on Calle Luna, edited in May.
- Support, email m.vega@example.com, phone blank, edited in October.
The email in the CRM and support records matches, so those two link automatically. The billing record has a different email, so deterministic matching misses it. Suppose the billing record also carries a customer number that was written into the CRM last year. Now the match is certain, and the three records link under one customer ID.
For the phone field, the rule is most recent wins among non-empty values, unless billing owns the field. Because billing is an authority for the billing phone, the billing number is kept in a billing phone field and the CRM keeps its own. The support record has a blank, and blanks should never overwrite a real value. That is a rule worth stating out loud, because many sync tools will happily replace a number with an empty string.
This is invented data, but the pattern is real. Most of the interesting decisions are about blanks, aliases, and fields with more than one valid answer.
Merge handling, the part that goes wrong quietly
When you find duplicates inside one system, you merge them. Merging is a destructive act, and sync makes it worse because other systems may still hold references to the record that disappeared.
Before you enable any merge, answer these questions.
- Which record survives, and what happens to the ID of the loser? A good system keeps a redirect or an alias so old references still resolve.
- Do downstream systems hear about the merge? If System A merges two contacts and System B still has both, your next sync may recreate the duplicate.
- Can you reverse a merge? If not, require a human to approve it.
- Where does the merged history go, such as notes, tasks, tickets, and invoices? Check that none of it is dropped.
A simple, safe policy is to never merge automatically across systems, and to automatically merge within one system only when a deterministic key matches exactly. Everything else goes to a review queue with both records side by side.
Bidirectional sync and the loop that eats your weekend
One way sync is easy to reason about. Data flows from the source to the target. Two way sync is where administrators lose weekends, because a change in A updates B, B reports the update as a change, and A updates again. That is a sync loop. At best it burns your API quota. At worst it overwrites real edits with stale values, or fills your audit log with noise.
There are a few standard defenses.
Origin tagging. Each write carries a marker saying which system made it. When System B sees a change it made itself, it ignores the echo.
Field ownership. If each field has one owner, most conflicts vanish, because the other system only reads it. You can still call this two way sync at the record level, but at the field level the flow is one way.
Change detection by comparison. Before writing, compare the incoming value to the current value. If they are equal, do nothing. This simple check stops many loops, because a no-op write produces no change event.
Version or timestamp checks. Include a version number or last modified time. Reject a write based on older data than what the target already holds. This is called optimistic concurrency, and it prevents a slow job from stomping a newer edit.
Also watch the clock. If systems stamp edits in different time zones or with clock skew, "most recent wins" can pick the wrong value. Store and compare in UTC, and be careful with systems that only report dates and not times.
Conflict policy, written down
A conflict is when two systems changed the same field between syncs. You need a policy before it happens. Mine looks like this.
- If the field has an owner system, the owner wins and the other value is logged.
- If there is no owner, the most recent non-empty change wins.
- If the field is on a protected list, such as legal name, tax ID, or email used for login, a person reviews it.
- Every overwritten value is stored in a change log with the old value, the new value, the system, and the time.
The log is not optional. When a salesperson says the phone number was right yesterday, you need to show what changed and why.
Consent and privacy do not sync by accident
A customer's consent status deserves special care. If someone unsubscribed in your email system, that status must reach the CRM and any other tool that sends messages, and it should win over a more recent edit that says otherwise. Treat opt out as a field where the most restrictive value wins. This is a general design principle and not legal advice, since the rules depend on where your customers are and what you send them.
Also sync only what you need. Copying every field from every system into every other system multiplies your exposure if one of them is breached, and it makes your deletion work harder when a customer asks you to remove their data.
Questions to ask a vendor or an integrator
- Does the connector match on an external ID, and can I set my own matching key?
- Can I set ownership per field?
- How does it handle blanks, nulls, and deleted values?
- Does it detect and suppress its own echo writes?
- What happens when the target API rate limits or fails midway? Does it retry safely without creating duplicates?
- Can I see a log of every change, and export it?
- How are merges in one system reflected in the other?
If the answers are vague, test with a sandbox copy of 50 records and watch what it does.
What to do on Monday morning
- List every system that holds customer records and the entity each one thinks it is storing.
- Build the field ownership table for your ten most important fields.
- Find a stable key, and if you have none, add an external ID field to each system.
- Export a sample of 200 records from two systems and measure how many match on your key. The unmatched ones will teach you the most.
- Write the conflict policy in half a page and get the sales and finance leads to agree.
Do these before you buy or configure anything. A sync tool will faithfully carry out the wrong policy at high speed.
If you would like help designing the ownership table or testing a sync on a copy of your data, that is the kind of work we do at Ozcorp InfraOps. Even if you never call us, I hope the half page of policy saves you a few late nights.
Discuss your infrastructure requirements
Review the current service offer or schedule a conversation about the scope you need.
Stay in touch
Follow Ozcorp
Leave your details for occasional notes about new articles and Ozcorp updates.