Published . 3 min read.
How do you audit a CRM with 12,000 contacts across 20 countries?
By Dat Tran, Group AI Marketing Executive at Archetype Group, Ho Chi Minh City
Short answer
Define what a clean record is before you clean anything. Then deduplicate in two passes: automated matching first, human review wherever a match is ambiguous, because a merged record cannot be un-merged from a guess. Retire contacts with no valid route to them, and give each office entry rules and an owner. I did this for 12,000 contacts across 20 country databases.
At Archetype Group every country office kept its own contact list. Twenty country databases, one firm. The same client appeared three times under three spellings, people who had left their companies years earlier still received mail, and nobody could answer "how many clients do we actually have" without a caveat. This post sets out how I audited roughly 12,000 contacts across those databases, in the order I did it, and why the order matters. The full case study is at CRM audit.
What the real problem was
Duplicates were the visible symptom, not the cost. The cost was trust. Sales and marketing had stopped believing the numbers, and a team that does not trust its own data starts making decisions from memory. Any audit that only removes duplicates will tidy the list and leave that problem in place. The goal I set was a database the team could defend when someone challenged a figure.
Step 1: define a clean record before you clean
Cleaning without a definition of "clean" produces a list that is tidy by one person's taste and decays the moment a second person edits it. Before touching a record I fixed one schema across all twenty databases:
- which fields are mandatory, and which are optional
- what a company record means versus a contact record
- how a market, a sector and an owner are tagged
- which field is the source of truth when two disagree
This step takes the least time and prevents the most rework. Every later decision, including what counts as a duplicate, refers back to it.
Step 2: deduplicate in two passes
Automated matching on names, email domains and company identifiers finds most duplicates quickly. It is also confidently wrong about the rest: two different people at one firm can look identical, and one person under two spellings can look like two. So the second pass was human. Wherever the match was ambiguous, a person reviewed it. I held this line for one reason: a merged record cannot be un-merged from a guess. A wrong merge destroys history, such as who spoke to whom and when, and nothing in the database shows that it happened.
Step 3: retire contacts that have no valid route
A contact with no working address or phone route to them is not an asset. It is a deliverability liability, because mail sent to dead addresses damages the reputation of the sending domain and so affects the people you can still reach. In this audit about 4,000 contacts were flagged invalid. Removing them makes the headline number smaller and the true number accurate. That is uncomfortable to report and correct to do.
Step 4: make the clean state maintainable
A one-off cleanup decays within a year. What stops a database silting up again is process, so the deliverable was the process as much as the data: entry rules, and a named owner per office. Without owners, rules are suggestions. The aim was that the state holds without needing another full audit.
| Step | What I did | Why it matters |
|---|---|---|
| Define clean | One schema across 20 country databases | Every later decision refers to it |
| Deduplicate | Automated matching, then human review of ambiguous matches | A wrong merge cannot be undone |
| Retire | Removed contacts with no valid route (about 4,000 flagged) | Dead contacts hurt deliverability |
| Maintain | Entry rules and an owner per office | Stops the decay returning |
What changed afterwards
Marketing now sends against a list it can defend, and the reporting figures reconcile with each other for the first time. The newsletter list that came out of this audit is the one behind the results in how to hold a 40% newsletter open rate. A clean list is a large part of why that number holds.
Limits of this account
This is one audit, done once, on one firm's data. The step order is general. The numbers, including 12,000 contacts and about 4,000 flagged invalid, belong to this project and will differ for yours. The tools used were HubSpot CRM, Salesforce, Excel, Zapier and Looker Studio.