Project 06Data & CRM
CRM audit
Twelve thousand contacts, audited record by record, because a database nobody trusts is a database nobody uses.
- 12,000contacts audited
- 20country databases
- 1shared schema
01
At a glance
The whole project in three steps.
- The problem
Twenty country lists full of duplicates and dead contacts, and a sales team that had stopped trusting the numbers.
- What I did
Defined one schema and reconciled 12,000 contacts, with human review wherever a match was ambiguous.
- The result
One database the team can defend, reporting figures that reconcile, and rules that keep it clean.
Skills it shows
- Data governance
- CRM management
- Data quality
- Process design
- Marketing operations
Tools
- HubSpot CRM
- Salesforce
- MS Excel
- Zapier
- Looker Studio
02
The challenge
Why the project had to happen.
Every office kept its own list. The same client appeared three times under three spellings, people who had left their companies years ago were still receiving mail, and nobody could answer "how many clients do we actually have" without a caveat.
Read the full context
The duplicates were the smaller cost. The sales and marketing teams had stopped believing the numbers, and a team that does not trust its own data starts making decisions from memory instead.
03
How I did it
4 steps. Open any one for the detail.
Establish what a record should be
Defined one schema for twenty country databases before cleaning anything.
How
Before cleaning, define clean. One schema across twenty country databases: what fields are mandatory, what a company record means versus a contact record, how markets are tagged.
Deduplicate, then verify
Matched duplicates automatically, then reviewed the ambiguous ones by hand.
How
Automated matching finds most duplicates and is confidently wrong about the rest. Twelve thousand records were reconciled with human review where the match was ambiguous, because a merged record cannot be un-merged from a guess.
Retire the dead weight
Removed contacts with no valid route, so the numbers became true.
How
Contacts with no valid route to them are not assets, they are a deliverability liability. Removing them improves the numbers by making them true, which is uncomfortable and correct.
Make the clean state maintainable
Set entry rules and ownership per office so the clean state holds.
How
A one-off cleanup decays within a year. Entry rules and ownership per office are what stop it silting up again. The deliverable was the process as much as the database.
04
See it
12,000 contacts — one dot per twenty
As found
- Duplicates
- merged on review
- Unreachable
- retired
- Remaining
- one schema, 20 markets
05
What it achieved
Twelve thousand contacts audited and reconciled onto one schema across twenty country-level databases.
Marketing sends against a list it can defend, and the reporting figures reconcile with each other for the first time.
The maintenance rules mean the state holds without another full audit.
06
What I took from it
A merged record cannot be un-merged from a guess. Automated matching finds most duplicates and is confidently wrong about the rest.
Removing dead contacts improves the numbers by making them true. That is uncomfortable and correct.
A one-off cleanup decays within a year. Entry rules and ownership per office are what keep a database clean.