Work snippet
Duplicate contact merge across ServiceM8, HubSpot and Xero
Concept DesignIllustrative integration walkthrough. Business details, figures and records are worked examples.
A service group with 35 technicians whose customers appear up to three times across ServiceM8, HubSpot and Xero. One registry keeps the master record, likely duplicates are scored in pairs, and a person approves each merge so jobs and invoices stay with the right customer.
The problemThe same customer exists three times across three systems.
What changesOne master record, and every merge approved so jobs and invoices stay attached.
- Area
- Data quality across three systems
- Tools
- ServiceM8 · HubSpot · Xero · Airtable
- Typical scope
- US$1,800-3,200
- Timeline
- About 5 weeks · fixed milestones
The problem
A customer is created in the job system, again by a web form in the CRM, and again when the first invoice goes out. Each system then holds part of the history, and a search for the customer returns three answers.
- Job history sits on one record and invoices on another
- Sales talks to a contact that the field team cannot find
- A merge done in one system leaves orphans in the other two
- New duplicates appear the week after a clean-up
What discovery would examine
A count of contacts, clients and customers across ServiceM8, HubSpot and Xero for a 35-tech service group, plus a review of 80 apparent pairs.
| ID | Finding | Evidence | Risk if left |
|---|---|---|---|
| F1 | The same email exists in all three systems under two or three display names | Of 80 pairs reviewed: 31 exact email matches with different names | Job history and invoices sit on different records |
| F2 | A prior merge in HubSpot was not repeated in ServiceM8 or Xero | HubSpot merge log vs still-open ServiceM8 clients | Sales and the field team do not share a record |
| F3 | Shared site phones and group reception numbers create false matches | Several pairs shared a switchboard number only | A merge would attach the wrong jobs |
| F4 | New duplicates appear in the week after a manual clean-up | Intake zaps create contacts without a lookup | The clean-up does not hold |
| F5 | Xero invoices were left behind on the discarded customer in an earlier attempt | Open invoice on a merged-away contact | Aged receivables cannot be collected cleanly |
Approach
- Build a cross-system registry
Every source id is kept, even after a merge.
- Score, then ask a person
High-confidence pairs still go through approval, in batches.
- Merge in a fixed order
ServiceM8, then HubSpot, then Xero, so jobs and invoices stay attached.
- Stop new duplicates at the door
Entry rules check the registry before a new record is created.
Current state and future state
Current state
Future state
Future-state modules
How it is built
- Module ARegistry load
Every contact, client and customer is loaded with its source id, and those ids are never discarded.
- Module BScoring
Name, phone, email and address each add to a score. Above a threshold is a likely match; the band below it is for review.
- Module CApproval
A person sees the two records side by side and approves, rejects or parks the pair.
- Module DMerge order
ServiceM8 first, so job history stays on the kept client, then HubSpot, then Xero, so invoices stay attached.
- Module EEntry rules
A lookup against the registry runs before a new contact is created in any of the three systems.
Field mapping
| Signal | Weight | Kept on the surviving record | Note |
|---|---|---|---|
| Email exact | High | The verified email | A shared group inbox is parked, not auto-approved |
| Mobile exact | High | The mobile used in the field | A switchboard number is a weak signal on its own |
| Legal or trading name close | Medium | The longer official name, with the other stored as an alias | A person confirms a trading-name change |
| Street address + unit | Medium | All sites combined | Same street, different unit, is not a merge on its own |
| Source ids | Always kept | All three source ids on the master record | Needed for rollback and for entry rules |
Worked exhibits
| Match reason | Pairs scored | Approved | Rejected | Parked |
|---|---|---|---|---|
| Email exact, name close | 612 | 601 | 7 | 4 |
| Mobile exact, name close | 428 | 390 | 14 | 24 |
| Trading name change, same email | 96 | 88 | 3 | 5 |
| Same street, different name | 154 | 47 | 53 | 54 |
| Total in these bands | 1,290 | 1,126 | 77 | 87 |
After approval, both job histories sit on client 10428, both HubSpot deals on contact 8831, and the $420 invoice stays open on the surviving Xero customer. Both records share the same accounts mailbox.
Exceptions and runbook
| Case | Rule | Who acts | Rollback |
|---|---|---|---|
| Shared switchboard number only | Park, do not approve on that signal alone | Data owner | N/A, no merge yet |
| Property group, many sites, one billing contact | Keep one master, attach all sites; do not merge two legal entities | Data owner with finance | Sites can be moved back; legal entities stay separate |
| Open invoice on the record that will be discarded | Move invoices with the Xero merge, then check the open balance | Finance | Named backup of the invoice list taken first |
| Wrong approval noticed after the batch | Use the snapshot and the source ids to split the records | Data owner and me, inside the 14-day fix window | Jobs and invoices still carry their source tags |
How to read the exhibits
- The totals are counts of records and approved pairs, not a claim about revenue.
- The review desk shows why a trading-name difference still needs a person.
- The order is the safety rule: jobs first, invoices last, with a rollback snapshot at each step.
Test plan
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Exact email pair | Approve a scripted pair | One master, all source ids kept, jobs and invoices attached | A source id is lost, or an invoice is left behind |
| T2 | Switchboard-only pair | Submit a pair that shares only a reception number | The pair is parked, not auto-approved | A merge runs unattended |
| T3 | Entry rule | Create a contact with an email already in the registry | The existing master is reused | A new duplicate is created |
| T4 | Merge order | Run a three-system pair in a copy | ServiceM8 first, then HubSpot, then Xero | Xero is merged before jobs are combined |
| T5 | Rollback export | Take the snapshot, merge, then restore a test pair | The two records can be separated | A job has no client |
Scope
| Area | Included |
|---|---|
| Discovery | Counts and a review of pairs in ServiceM8, HubSpot and Xero, with written findings |
| Registry | Airtable master record with all source ids |
| Scoring and approval | Written weights, and a side-by-side queue |
| Merges | Carried out in the agreed order, with job and invoice links kept |
| Entry rules | Checks that stop a new duplicate being created |
| Rollback export | A snapshot before each batch |
| Handover and support | SOPs, a recorded walkthrough, 14 days of fixes, and an optional monthly hour block |
| Not included | Merging records the owner has rejected, or cleaning data in other products |
Rollout, handover and monthly support
| Milestone | Deliverable | Timing | Share of fee |
|---|---|---|---|
| M1 Discovery, registry and scoring | Findings, loaded records and the scoring rules | Week 1 | 25% |
| M2 First approval batch | Queue live, first batch merged in a copy then live | Weeks 2 to 3 | 35% |
| M3 Remaining batches and entry rules | Approved pairs done, new-duplicate checks on | Week 4 | 25% |
| M4 Handover | SOPs, export, 14 days of fixes | Week 5 | 15% |
Handover
- Written operating procedures for the happy path and each exception in the runbook
- A recorded walkthrough of the architecture, the queue and how to replay an item
- A credentials and ownership list, with every connection in your name
- Fourteen days of fixes after go-live, for defects in the agreed scope
Monthly support
A set block of hours each month, at US$35 per hour, agreed in writing before it starts. Typical use of the block: new pairs in the review queue, entry-rule exceptions, and any change to scoring weights
- Review of the exception queue and the weekly digest
- Small mapping or rule changes, agreed before they are made
- A short written note on what was done and what is still open
Typical scope for this kind of project: US$1,800-3,200
About 5 weeks · fixed milestones. The systems health check (US$150, fixed) comes first and confirms the scope. Extra hourly work is US$35 per hour, agreed before it starts. After handover, a set block of hours each month is available at the same rate, agreed in writing, to watch the flows, handle changes and work the exception queue.
Discuss an engagement.
Outline the operational problem through the contact form. I respond with a recommended starting point, including whether the US$150 systems health check is the appropriate first step.