Consolidating Customer Data Scattered Across Departments and Spreadsheets

When five departments each keep their own customer list, finance will not give up its spreadsheet, and customers are getting duplicate emails and missed orders, the fix is a consolidation, not a big-bang switch. Publish a single authoritative record for customer identity and name who owns each attribute. Match the copies on deterministic keys such as registration number, VAT number, normalised email and the finance account number, never on fuzzy company names. Build a reconciliation view that shows where the copies disagree. Then move departments onto the master one at a time, finance last, with the accounting system integrated rather than replaced. In Dataverse, duplicate detection rules warn, alternate keys enforce, and merge moves related records permanently.

Why does every department end up with its own customer list?

Because each list was the fastest way for that team to do its job. Sales needed a pipeline, marketing needed a mailing list, customer service needed contact details for support, operations needed delivery addresses and order history, and finance needed billing details and credit terms. Nobody set out to create five versions of the truth; each copy started as a reasonable export and then drifted as its owners corrected it locally.

The symptoms are predictable once the copies drift:

• Duplicate emails, because marketing and sales both mail the same person from lists that do not know about each other, and an unsubscribe recorded in one list does not reach the other.
• Missed orders, because an order is logged against a customer record that the team fulfilling it cannot see, or against a second record for the same customer that nobody is watching.
• Arguments about numbers, because revenue per customer, open cases and credit exposure are calculated from different lists that do not agree on who the customers are.
• Personal data that nobody can find all copies of, which becomes a real problem the first time a customer asks what you hold about them or asks to be forgotten.

Understanding why each list exists matters, because the consolidation only works if the master does the job each spreadsheet was doing. A department that loses something it relied on will quietly start a new spreadsheet.

What should the single authoritative customer record contain?

Start with identity, not everything. The authoritative record answers one question reliably: who is this customer? That means a master customer ID issued by the new record, the legal name, the identifiers that prove identity, the parent and child relationships between companies, and for individuals the person and the organisation they belong to. Departmental detail such as sales stage, service entitlements or delivery preferences can join later, once identity is trusted.

Then write down, for every attribute, which system or team owns it. This ownership table is the document that ends most of the arguments:

• Legal name, registration number and company hierarchy: owned by the master, maintained by whoever is accountable for customer data.
• Contacts, roles and marketing consent: owned by the master, with consent captured once and read by every team that sends email.
• Billing address, payment terms, credit limit and balance: owned by the accounting system, copied into the master read only.
• Sales pipeline, cases and delivery history: owned by the application that runs the process, linked to the master customer ID.

Publish the record and the ownership table where every department can see them. A master that only the project team knows about is just a sixth spreadsheet.

Why should matching use deterministic keys rather than fuzzy name matching?

Because a name is not an identity. The same trading name with and without "Ltd", or with a branch city in brackets, might be one customer, two branches of one customer, or two unrelated companies. Fuzzy name matching produces a similarity score, and any threshold you choose will merge some genuinely different customers and miss some genuine duplicates. Once a false match has merged two customers, their orders, invoices and service history are mixed together, and separating them again is slow manual work.

Deterministic keys give a yes or no answer that can be checked and repeated:

• Company registration numbers, such as Companies House numbers in the UK, KvK numbers in the Netherlands or the commercial register number in Germany.
• VAT or tax identification numbers, normalised to one format.
• The customer account number in the accounting system, which finance already treats as authoritative.
• For individuals, a normalised email address: trimmed, lowercased and checked for shared inboxes such as info@ or accounts@, which should never be used as a personal key.
• Website domain, useful as supporting evidence for companies but not on its own, because groups share domains.

Normalise each key into its own column before matching, so spacing, punctuation and letter case do not create false differences. Every source row then gets a cross-reference entry that maps its original ID in the department spreadsheet to the master customer ID. Fuzzy matching still has a place: it produces a candidate list for people who know the customers to review, never an automatic merge. The rows that have no deterministic key at all are the ones that need that human review, so check early how many rows in each list actually carry a usable key.

How do you build a reconciliation view that shows where the copies disagree?

Load every departmental copy into a staging area as it is, untouched, with its source name and original row ID. Join each row to the master through the cross-reference, then compare attribute by attribute. The reconciliation view has one row per master customer, per attribute, per source, and sorts every comparison into one of four states:

• Matched and equal: the copy agrees with the master, so there is nothing to do.
• Matched with a difference: same customer, different value, such as two billing addresses or two phone numbers. The attribute owner decides which is right.
• In the source only: a customer one department knows about that the master does not. It is either a new customer to add or a duplicate that did not match on a key.
• In the master only: a customer the department does not have, which is often the cause of a missed order or a missed renewal.

Give every open difference an owner and an age, and review the oldest ones weekly. The view can be a set of tables in a staging database with a Power BI report on top, or tables and views inside Dataverse itself; what matters is that it is rerun on every load and that the numbers go down over time. The same reconciliation pattern, with sign-off and evidence kept, is what a data migration uses to prove nothing was lost, and our Salesforce to Dynamics 365 data migration guide sets out the validation checks and the source key approach in detail rather than repeating them here.

How do you move departments onto the master without a big-bang switch?

One department at a time, in an order that removes the most pain for the least risk, with the spreadsheet made read only only after the department has worked from the master for a full cycle. A big-bang switch asks every team to change how it works on the same day, which is also the day any problem in the data is most visible and least understood.

A workable sequence for most organisations:

1. Marketing first, because duplicate emails and consent errors are the most visible symptom, and a mailing list reads identity and consent from the master without changing any financial process.
2. Sales next, moving pipeline and account ownership onto records linked to the master customer ID.
3. Customer service, so cases attach to the same customer that sales and marketing see.
4. Operations and order handling, which closes the gap behind missed orders because orders are placed against the master customer.
5. Finance last, and only for identity, because its spreadsheet usually feeds or mirrors the accounting system and supports credit control and audit.

For each department the steps repeat: load its copy with its source keys, clear the reconciliation differences that matter to it, switch its day-to-day work to read and write the master, keep the old spreadsheet available but read only, then archive it after an agreed period. During the overlap, each attribute is written in one place and propagated one way; two-way sync of the same field between a spreadsheet and the master is how drift starts again. Where the consolidation is part of retiring many systems rather than spreadsheets, the ownership and one-way propagation rules for a longer programme are covered in our guide to consolidating legacy systems onto Dataverse.

Why does finance go last, and how do you integrate with the accounting system instead of replacing it?

Finance goes last because its customer data is tied to invoices, balances, credit decisions and the audit trail, and changing that while the master is still settling puts the close at risk. It also rarely needs to give up its system. The accounting system stays the owner of billing details, payment terms, credit limits and balances. What finance gives up is maintaining customer identity in a separate spreadsheet.

The integration that makes that work is usually simple in shape:

• Store the accounting customer account number on the master record as a key, so each side can find the other without matching on names.
• Decide where a new customer is created. A common pattern is creation in the CRM, a finance approval step, then creation in the accounting system and the account number written back to the master.
• Propagate each attribute one way, in the direction of its owner: identity from the master to accounting, balances and credit status from accounting to the master, read only.
• Make every write idempotent on the key, so a resend updates rather than duplicates, and log every failure somewhere a named person reads.

If there is already an undocumented integration between the CRM side and finance, understand it before you add to it; our guide to untangling a black-box integration to finance covers reconstructing one safely. Solzet integrates with accounting and finance systems but does not implement them: we do not implement Dynamics 365 Finance, Business Central, Dynamics NAV, Finance and Operations or any other ERP. Where the accounting side needs a change, such as a new field for the master customer ID, we specify it and work with your finance team or their vendor.

What do duplicate detection rules, alternate keys and merge actually do in Dataverse?

If the master lives in Dataverse, three features do three different jobs, and confusing them is why many cleanups do not last.

• Duplicate detection rules find likely duplicates and warn. They run when records are created or updated in the app, during imports where detection is enabled, and in duplicate detection jobs. They do not run on API writes unless the request asks for them, so integrations bypass them by default. They never stop a user who chooses to save anyway.
• Alternate keys enforce uniqueness. Defining an alternate key on a normalised registration number or email column makes Dataverse reject a second record with the same value, whatever route it arrives through, and lets integrations upsert on that key instead of creating new records. A key cannot be activated while duplicate values still exist in that column, so it comes after the cleanup, and key matching is not case sensitive, which is one more reason to normalise the value first.
• Merge combines two accounts, contacts, leads or cases. It moves related child records, such as activities, notes, cases, opportunities, orders and invoices, onto the surviving record and deactivates the other one. There is no unmerge, and merging accounts changes per-customer history in past reporting periods too.

That last point is why the deterministic matching and the reconciliation view come before any merge. The detailed method, including how to tune matching rules to real data, snapshot reports, merge in logged batches that can be reversed by hand, and put intake controls on every route, is in our guide to fixing duplicate customer data in Dynamics 365. Where the duplicates come from two CRMs after an acquisition rather than from departmental spreadsheets, match tiers and survivorship rules are covered in our post-merger CRM consolidation guide.

What will an auditor ask when every team has its own spreadsheet?

Usually four questions, and the consolidation should be designed to answer them:

• Which record is the authoritative one for a customer, and is that written down? The published ownership table answers this.
• Who can change customer data, and is that access controlled? Spreadsheets on shared drives usually fail here; security roles on the master and read only access to retired sheets fix it.
• What changed, when and by whom? In Dataverse, auditing can be switched on for the customer tables and the columns that matter, such as legal name, registration number, bank or billing details and consent.
• How do you know the copies agree? The reconciliation view, rerun on every load and with differences owned and dated, is the evidence.

While the consolidation is half finished, keep a register of every customer spreadsheet that still exists, its owner, whether it is still written to, and its retirement date. Auditors generally accept a transition that is controlled and evidenced; what they question is a spreadsheet nobody admits is still in use. For data protection, the register also tells you where personal data lives until the last copy is archived.

Should the master customer record live in Dataverse or in a custom CRM?

Decide this after the ownership table and the matching keys exist, because they are the requirements. Dataverse is a strong home for the master when your teams already work in Microsoft 365, when sales, service and field operations should share one customer record, or when you are adopting Dynamics 365 anyway. For a sales team moving off spreadsheets, our Dynamics 365 Sales implementation service covers the pipeline, forecasting and account model that sits on top of that master record.

Where per-user licensing does not fit, or where you need full control of hosting and the data model, the master can be a custom-built CRM on React, Node.js, PostgreSQL or .NET. In that case uniqueness is enforced with database constraints on the normalised keys from the first day, and the cross-reference and reconciliation tables are part of the schema rather than a staging add-on. The consolidation method in this guide is the same either way, which is why it is the right place to start before any platform decision.

Solzet runs this work as the first phase of a CRM engagement or on its own: the ownership table, key design, reconciliation view, phased department moves and the integration with your accounting system. It is delivered remotely from Yerevan, Armenia, directly or white-label for Microsoft partners, by senior consultants and full-stack developers with 8+ years of Dynamics 365, Power Platform and custom CRM delivery.

What should you do this week?

1. List every customer spreadsheet and system, its owner, what the department uses it for and whether it is still written to.
2. Draft the attribute ownership table: which system or team owns identity, consent, billing details, credit, pipeline and service history.
3. Choose the deterministic keys you have in practice, such as registration number, VAT number, the finance account number and normalised email, and check how many rows in each list carry them.
4. Load untouched copies of each list into a staging area with their source row IDs.
5. Build a first reconciliation view and count matched and equal, matched with a difference, source only and master only.
6. Agree the department order, finance last, and pick the first department.
7. Confirm with finance which attributes the accounting system keeps, and where new customers will be created.
8. Defer any merge in Dataverse until the keys and reconciliation are in place, then follow a logged, reversible merge method.