Cleaning Isn't the Same as Remembering: The Case for MDM

Many organizations don't precisely know how many customers they have. Not because nobody counted - because the same person is sitting in the system twelve times under twelve slightly different names. 

The problem everyone has and nobody names

Sales keeps a list of customers. Support keeps their own. Marketing exports one from an email tool. Someone uploads a spreadsheet from a trade show. Each list is fine on its own - until you ask how many customers you actually have.

	Name	City	Phone
1	Robert Shaw	New York	555-0134
2	Rob Shaw	New York	555 0134
3	R. Shaw	NY	(555) 0134
4	Robert Shawe	New York	555 0134

That's one person. Your systems count four. So you email him four times, support can't see the ticket sales raised, and every report about him is a quarter of the truth. A human reads that table instantly. The computer doesn't, because it's looking for values that are equal, and none of these are.

Cleaning a file isn't the same as fixing the problem

The usual first move is deduplication: run the file through a tool, keep one of each near-duplicate. That helps - and it's a real step in our own flow.

But it only cleans that file. Next month a new file arrives with Robert spelled a fifth way, and the tool has no memory of last month. It cleans the new file from scratch, and you're back where you started.

What's missing isn't better cleaning. It's memory. That's Master Data Management (MDM): not a fancier duplicate-finder, but a system that remembers who it has already seen, across every upload, forever.

The one idea: a permanent Identifier

 Every real-world thing - a customer, a supplier, a product - gets one ID that never changes, and every record about it is filed under that ID.

Robert Shaw becomes customer 1042. All four rows point at 1042. Next month's "Rob Shaw" is filed under 1042 too. The ID doesn't care how the name was spelled today.

 It's not clever, and that's the point. Once every record carries one, hard questions become one line of a report: how many customers? - count the IDs. Show me everything about this one - everything with that ID. Is this lead already a customer? - check whether it lands on an existing ID.

How data flows through it

 Tidy up inside the file first. If Robert appears three times in this one upload, we need to know before anything else - miss it and he gets three IDs on day one.

 Compare each record against what we already know. Not a search for an exact match, which would find nothing. A comparison across several fields at once, scored out of 100. Same phone, same city, very similar name - that scores high.

 Old ones keep their ID, new ones get one. A recognised record inherits the ID that thing already has. A genuinely new one gets the next ID. That's the whole decision.

 Everything is filed, nothing is thrown away. The row is stored with its ID, the file it came from, when it arrived, and the score that linked it.

One list of people, one list of records

 You end up with two things, and mixing them up is where the confusion starts.

Who exists - one row per real-world thing. Robert appears here exactly once, no

matter how many files he shows up in. Short, slow-growing, and the answer to "how

many do we have."

What came in - one row per record, per upload, each stamped with its ID. Robert

has many rows here. This is the answer to "show me everything we've received about

this customer, and where each piece came from."

The second one surprises people. It's tempting to think MDM squashes everything down

and throws the messy originals away. It doesn't - nothing gets deleted. The

original rows stay exactly as they arrived; you've only added a label saying which

ones belong together. So if a link turns out to be wrong, you can see what was

joined and how confident the system was. There's nothing to undo, because nothing

was destroyed.

The honest part

Matching is a judgement, not a fact. Two records aren't equal, they're similar, and someone has to decide how similar is similar enough. That line is a setting - ours starts at 80 out of 100.

Set it too low and you start joining people who merely look alike; two different Robert Shaws in one city become one customer, which is the expensive mistake because it's hard to spot and hard to unpick. Set it too high and you barely join anything - safe, but you've bought an MDM system and kept your duplicates.

There's no universal right answer. What we do is keep the decision visible: every link stores the score that caused it, so you can look at the borderline cases and move the line on evidence rather than a feeling.

What changes once it's running

 Questions stop being projects. "How many customers?" becomes a number you trust. Support's data and sales' data can finally sit side by side, because both carry the same ID - that join didn't exist before, there was nothing reliable to join on. New files stop creating new piles: known things attach to what you already have, and only truly new things are new. And every link keeps its source and score, so "why are these the same customer?" always has an answer.

If you're considering it: start with one domain - customers, or suppliers, or products, not all three. Compare on fields that actually separate people; a country or a status is the same for everybody and does no work. And expect to tune the threshold once you've seen your own borderline matches.

Want to know if this fits your data?

If you recognise the four-Roberts problem - counts nobody believes, systems that can't be joined, duplicates that come back every month - we'd be glad to look at your data and talk through whether this is the right fix.