Work snippetData quality
Duplicate contact merge across ServiceM8, HubSpot and Xero
How I would approach one customer existing three times for a 35-tech group, with a cross-system registry, scored candidate pairs, merges that keep history, and entry rules that stop the duplicates coming back.
Work snippet with example figures.
- Area
- Data quality
- Tools
- ServiceM8 · HubSpot · Xero · Airtable
- Typical scope
- US$1,800–3,200
- Timeline
- About 5 weeks · 4 fixed milestones
Context and systems map
A 35-tech HVAC and plumbing group with four branches. The same customers exist in ServiceM8 as clients, in HubSpot as companies and contacts, and in Xero as contacts. Each system has its own duplicates and none of them agree on names, addresses or who the billing contact is.
The systems in play
In the example a registry gives every real customer one master record with a link to each system, duplicates are merged with approval inside each system, and entry rules stop new duplicates being created.
| System | What it does for the business |
|---|---|
| ServiceM8 | Clients and job addresses; the working record for field staff. |
| HubSpot | Companies, contacts and deals; the sales and marketing record. |
| Xero | Contacts for invoices and statements; the billing record. |
| Cross-system registry | One row per real customer with the id in each system, a master name and a status. An Airtable base in the example. |
| Review queue | Candidate pairs with a score, the evidence and a decision field. |
| Office manager and finance lead | Approve merges. The finance lead owns the billing identity. |
How the data moves
- Export from the three systems
- Normalise
- Match and score
- Review queue
- Approved merge
- Registry updated
- Entry rules prevent new duplicates
Problem
What the owner sees
- Statements sent to the wrong contact
- Marketing emails to customers who have just had a complaint
- Sales and billing that disagree on a customer’s name
- Reports that count one customer three times
Native sync copies a contact across but does not decide whether it is the same customer. A one-off tidy fixes the problem for a month. The lasting fix is a registry, merge rules that keep history, and rules at the point of entry so the same customer cannot be created twice.
| Everyday situation | What goes wrong |
|---|---|
| Same company with two spellings | Two clients, two jobs lists, two Xero contacts |
| Billing contact differs from the site contact | Statement goes to the tenant instead of the owner |
| Person works for two companies | One HubSpot contact has two names and two emails |
| Phone formats differ | Duplicates are missed by an exact match |
| Merged in one system only | The other systems recreate the merged record from the sync |
| Deleted instead of merged | History is lost and the number reused |
Approach
Four steps, in this order. Each one ends with a result that is checked.
- LookRead-only export of all three systems with counts, blanks and a first pass at likely duplicates.
- Agree the rulesMatch keys, survivorship, who approves, and the order of merge across systems are written down.
- Match and reviewCandidate pairs are scored and reviewed by a person. Nothing is merged on score alone.
- Merge and protectApproved merges run system by system with the registry updated, then entry rules and a weekly duplicate report keep it clean.
| Item | Example setting |
|---|---|
| Match keys, in order | Tax ID, then email domain with normalised name, then phone, then name and postcode |
| Score bands | Above 95 listed as likely; 70 to 95 as possible; below 70 not shown |
| Survivor | The record with the most history; the billing identity is always taken from Xero |
| Fields that survive | Name and tax ID from Xero, site details from ServiceM8, marketing consent from HubSpot |
| Approval | A person approves every merge; none are automatic |
| Order of merge | Xero first, then ServiceM8, then HubSpot, so billing is never left pointing at a removed record |
| History | Merged, never deleted, so invoices and jobs follow the survivor |
| Entry rules | Search before create in each system, and a daily list of new near-matches |
Flow and data mapping
The registry is the map between systems. The tables show what is matched and which system wins each field.
| Step | Action | Output | Owner |
|---|---|---|---|
| 1 | Export and normalise names, phones, emails, addresses | Clean comparison set | Manaz Afzal |
| 2 | Score pairs within and across systems | Candidate pairs | Manaz Afzal |
| 3 | Review each pair | Merge, keep apart or link | Office manager |
| 4 | Merge in Xero | One billing contact | Finance lead |
| 5 | Merge in ServiceM8 | One client with all jobs | Office manager |
| 6 | Merge in HubSpot | One company with all contacts | Sales lead |
| 7 | Update the registry | One row, three ids | Manaz Afzal |
| Field | Winning system | Reason |
|---|---|---|
| Legal name and tax ID | Xero | Used on invoices and statements |
| Billing address and email | Xero | Where documents are sent |
| Site address and access notes | ServiceM8 | Where the work happens |
| Contact person for the site | ServiceM8 | Updated by field staff |
| Marketing consent and source | HubSpot | Compliance record |
| Lifecycle stage | HubSpot | Sales record |
| Payment terms | Xero | Credit decision |
Edge cases handled
Agreed in advance with the finance lead and the operations manager, and tested before go-live.
| Situation | How it is handled |
|---|---|
| Two companies with the same owner | Kept as separate customers and linked by a group field |
| Customer renamed after a sale of the business | Master name updated, previous name kept as an alias |
| Same phone, different companies | Not merged; shown as a shared phone for review |
| Contact has changed company | Moved with a dated note, not merged into the new company |
| Merge would join different tax IDs | Blocked and listed for the finance lead |
| Duplicate with open invoices in both | Merged only after the invoices are checked, so statements stay right |
| Sync recreates a merged record | Registry id blocks the create; the sync reads the registry first |
| Customer asks not to be marketed to | Consent is preserved from the record with the opt-out |
Worked example
The tables below show a first pass for an example group. They show the shape of the checks, not a client result.
| System | Records | Likely duplicates | Possible duplicates | Records after merge |
|---|---|---|---|---|
| ServiceM8 | 3,100 | 212 | 148 | 2,888 |
| HubSpot | 2,740 | 167 | 121 | 2,573 |
| Xero | 2,480 | 131 | 88 | 2,349 |
| Field | Record A | Record B |
|---|---|---|
| Name | Customer A Property Group | Customer A Property Grp. |
| Tax ID | Same | Same |
| Email domain | customer-a.example | customer-a.example |
| Phone | 0400 000 111 | +61 400 000 111 |
| Postcode | 3000 | 3000 |
| Score | 96 | Likely: approve |
| Registry field | Value |
|---|---|
| Master name | Customer A Property Group |
| Xero id | Survivor, with 2 merged ids in the history |
| ServiceM8 id | Survivor, with 1 merged id |
| HubSpot id | Survivor, with 1 merged id |
| Aliases | Customer A Property Grp. |
| Status | Merged and checked |
| Section | Content |
|---|---|
| New near-matches | Created this week with a score above 70 |
| Open review items | Pairs awaiting a decision, oldest first |
| Blocked merges | Different tax ID or consent conflict |
Monitoring and alerting
After the clean-up, the job is to keep it clean. The registry is checked daily, and the entry rules produce a short list rather than a long report.
| Signal | Threshold | Alert goes to | First action |
|---|---|---|---|
| New near-match created | Score above 70 | Office manager daily list | Merged, linked or marked different |
| Registry mismatch | A system id not in the registry | Manaz Afzal | Registry row created or record merged |
| Sync recreates a merged record | Any | Finance lead and Manaz Afzal | Record merged again, rule corrected |
| Review queue age | Older than 5 business days | Office manager | Pairs decided |
| Merge job failure | Any failed merge | Manaz Afzal | Stopped, failed record shown, re-run alone |
| Duplicate rate | Above 1% of records in any system | Owner monthly | Entry rules reviewed |
Test plan
Every check has a written pass condition and a written fail condition before it is run. A FAIL stops the next step until it is cleared and re-run.
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Normalisation | Fifty known pairs with different spellings | All pairs scored above 70 | Any missed pair |
| T2 | False match | Fifty known different customers | None above 95 | Any above 95 |
| T3 | Survivor rules | Merge a test pair | Each field comes from the named system | Any field from the wrong system |
| T4 | History | Check invoices and jobs after a merge | All follow the survivor | Any left behind |
| T5 | Consent | Merge a pair with an opt-out | Opt-out kept | Opt-out lost |
| T6 | Order of merge | Run Xero, ServiceM8, HubSpot on test data | No record points to a removed one | Any dangling reference |
| T7 | Recreate guard | Create a merged customer again in a test | Blocked by the registry | Created |
| T8 | Counts | Records before and after against the plan | Equal, difference equals merges | Any other difference |
Merge run and rollback
Merges run in batches of 50. Before each batch the records are exported, so a batch can be reviewed and, where a system allows it, reversed.
| Step | Owner | Gate to continue | Rollback trigger |
|---|---|---|---|
| Export before the batch | Manaz Afzal | File saved and counted | Export incomplete |
| Merge in Xero | Finance lead | T4 on five spot checks | Any invoice not following the survivor |
| Merge in ServiceM8 | Office manager | Job counts equal | Any job lost |
| Merge in HubSpot | Sales lead | Contact and deal counts equal | Any consent lost |
| Registry updated | Manaz Afzal | T8 passes | Counts do not tie |
| Rollback | Owner | Batch stopped, records restored from the export where the system allows, otherwise recreated and relinked | Decision recorded with the reason |
Scope and milestones
A fixed scope agreed in writing before anything starts. Each milestone is paid when you sign off its deliverable.
| Area | Included |
|---|---|
| Systems Audit | Read-only export of all three systems with counts and a first pass at likely duplicates, a 45-minute call, written findings and a fixed quote |
| Registry | One row per customer with ids and master fields |
| Matching and scoring | Rules and a review queue |
| Merges | Batches in Xero, ServiceM8 and HubSpot with approval |
| Survivorship rules | A signed field-by-field table |
| Entry rules | Search before create, plus a daily near-match list |
| Duplicate report | A weekly list and a monthly rate |
| Handover | Written SOPs, a recorded handover video and files you own, plus 14 days of fixes after go-live |
| Not included | Tax filing and statutory returns. |
| Also outside the scope | Data enrichment or purchased lists. Marketing campaigns. Changing which systems you use. Software subscriptions (billed to you by the vendors). |
| Milestone | Deliverable | Timing | Share |
|---|---|---|---|
| M1 Audit and rules | Export, fixed quote, rules in writing | Week 1 | 20% |
| M2 Match and review | Registry built, queue populated and reviewed | Weeks 2 to 3 | 30% |
| M3 Merges | Batches merged in all three systems and checked | Week 4 | 35% |
| M4 Protect and handover | Entry rules, weekly report, SOPs, handover video, 14 days of fixes | Week 5 | 15% |
Typical scope for this kind of project: US$1,800–3,200
About 5 weeks across 4 fixed milestones. The Systems Audit (US$300, fixed) comes first and confirms the scope.
Describe what is going wrong between your job system and your books, and I will say where I would look first.
Send a short note through the form on the home page. I will reply with where I would start, and whether the US$300 Systems Audit is the right first step.