Why every department sends you a different version of the same Excel file
You ask three departments for the customer list. Three files arrive. The columns are different. Sales has region and segment. Finance has account manager and country, with full country names instead of two-letter codes. Marketing has all three, with different segment values from Sales.
You ask which one is correct. Each department says theirs is. They are all telling the truth, from where they sit.
This is structural, not accidental, and that is worth knowing before you spend a week writing a normalisation script.
Every department exists to do something specific. Sales sells. Finance bills. Marketing runs campaigns. Each activity needs slightly different metadata about the same underlying customer. Sales needs segment because it drives pipeline review. Finance needs account manager because invoices go to them. Marketing needs both, plus campaign tags. The column lists drift because the activities drift. The drift is local; the data is shared.
Each department adds new columns when they need them, renames values when their team adopts new terminology, and drops columns that no longer matter to them. None of those changes are wrong. They are correct, locally, and they are why "the customer list" is not one list.
The fix is not a master file
The first attempt at consolidation is to take the three files and merge them. The merge fails because the joins are dirty. Customer numbers in Sales have a prefix that Finance does not. Marketing uses a different unique ID. You spend two days writing a normalisation script. By the time you are done, Sales has updated their file, and your script is out of date.
The right move is not to make one file. It is to separate shared attributes from local ones.
| Shared (central, one owner) | Local (department, their own) |
|---|---|
| Customer ID | Sales segment |
| Legal name | Account manager |
| Country | Pipeline stage |
| Currency | Campaign tags |
| Status | Service tier |
Shared attributes belong in a central registry. One source of truth, read by everyone. Local attributes stay with the department that uses them, exposed through a documented interface but maintained by that team.
The hard part is deciding what is shared. Most attributes look obviously shared until you look closer. Country, for example, is layered: country of registration, country of billing, country of operations. Different departments care about different ones. Register all three under clear names; let each department pick.
What it looks like when it works
Once the split exists, the question shrinks. Sales sends customer IDs and their segments. Finance sends customer IDs and their account managers. Marketing sends customer IDs and their tags. Everyone joins on the customer ID, which is the same everywhere because it lives in the central system. The merge is trivial. The normalisation step is gone.
If your central team is still merging departmental files every week, you are paying for the absence of a registry. The cost does not show up on a budget line. It shows up as senior analyst time spent reconciling instead of building.