HubSpot Duplicate Management Case Study: Reconciling Identifiers Across Three Systems at an Automotive Services Network

Summary

Radius Wheel Restoration repairs and refinishes alloy wheels for dealerships, collision centres and fleets, dispatching technicians to customer sites across a network of locations.

It runs three systems that each hold a version of the same commercial reality. An enterprise resource planning system carries financials and the customer master. A field service platform carries work orders, technicians and the service departments jobs are dispatched to. HubSpot carries the commercial relationship.

A prior integration between the field service platform and HubSpot had produced duplicate records across four object types: companies, contacts, deals and a custom department object. Deduplicating those records was the visible work and the smaller half of the engagement.

The larger half was that a duplicate is an identity problem rather than a data-entry problem. Three systems had each minted their own identifier for the same real-world thing, and nothing declared which system was authoritative for which object type. Until that was decided, every merge was a guess about which record should survive.

Client details are pseudonymised at the client's request. Figures are as measured.

Background: three systems, three identifiers, no arbiter

The pattern here is specific and common in field service businesses running an enterprise system alongside a dispatch platform.

A dealership exists as a customer in the enterprise system with a customer number. It exists in the dispatch platform as an account with its own identifier, because the dispatch platform created it when the first job was booked. It exists in HubSpot as a company, created by the integration, with a HubSpot identifier assigned at creation.

None of those three identifiers knows about the other two unless something writes them onto each other. Where that writing is incomplete, partial or conditional, a second sync creates a second record rather than updating the first, because the integration has no reliable way to recognise what it has already created.

Batini, Cappiello, Francalanci and Maurino (2009) separate data-quality work into assessment and improvement as distinct phases requiring distinct techniques, and the distinction matters here more than usual. The improvement phase is a merge operation, which is destructive and irreversible. Running it before the assessment phase has established survivorship rules destroys the wrong records.

Pre-engagement audit

The diagnosis phase was scoped at six hours and produced four things before any record was changed.

Object types carrying duplicates: four. Companies, contacts, deals, and a custom department object representing the service departments jobs are dispatched to.

Cross-system identifiers per record: three. An enterprise system identifier, a dispatch platform identifier, and the HubSpot identifier, each of which had to be present and correct on the record that survived a merge.

Root cause: a defect in the migration and synchronisation logic between the dispatch platform and HubSpot, rather than user behaviour. That distinction determines the remedy: user-created duplicates are prevented by training and validation, and integration-created duplicates recur on the next sync no matter how thoroughly they are cleaned.

Survivorship rules: none existed. The question of which system holds the canonical record was answerable per object type and had never been asked.

Redman (1998) frames poor data quality as a cost borne continuously and recognised rarely, and duplicate records are its most legible form: every report grouped by company over-counts, every contact list double-sends, and no single event makes either visible.

The build

Phase one: deciding who owns identity, per object type

The decision that governed everything else is that survivorship is set per object type rather than globally.

A single rule saying that the enterprise system always wins would be simple and wrong. The enterprise system is authoritative about a customer's legal entity, billing address and account status. It knows nothing useful about a service department, which exists because a dispatcher created it to route a technician. HubSpot is authoritative about a commercial relationship and about nothing financial.

Each object type was therefore assigned a canonical system, and the merge direction follows from that assignment rather than from which record happens to be older or more complete.

Reinartz, Krafft and Hoyer (2004) treat customer relationship management as a set of processes rather than a system, and the per-object rule is that treatment applied to identity. The process that creates a service department is dispatch. The process that creates a customer is finance. A survivorship rule that ignores which process authored a record is a rule about storage rather than about the business.

The rules were documented and signed off before a single record was merged. Pipino, Lee and Wang (2002) argue that data-quality assessment must combine objective measurement with subjective judgement, because a metric cannot establish whether a shortfall matters to anyone. Which system owns a department is exactly that kind of judgement, and it belongs to the client rather than to the implementer.

Phase two: the merge, and what a merge destroys

Deduplication ran across the four object types in twelve hours, in descending order of blast radius: companies first, then contacts, then deals, then departments.

Merging in HubSpot preserves associations and resolves conflicting property values to the primary record. The secondary record's differing values are gone. In a business whose customer history is its commercial asset, an unreviewed bulk merge is a data-loss event with a friendly interface, and the controls used were correspondingly unglamorous: a confirmed export point before any merge, staged execution reviewed with the client before running at scale, and an explicit instruction to pause and re-estimate if the duplicate volume proved materially larger than what was observable at diagnosis.

Each surviving record was then written with all three identifiers, which is the step that converts a cleanup into a reconciliation.

Redman (1998) puts the cost of poor data quality on the operating account rather than on a hygiene line, and the three-identifier write is what moves it back. A deduplicated record with one identifier is correct in one system and orphaned in the other two, which is a cheaper form of the same problem rather than a solution to it.

Phase three: fixing the thing that made them

Two hours of the engagement went to the synchronisation logic itself.

That allocation looks small against twelve hours of cleanup and is the only part of the work with a permanent effect. A cleanup without a source fix produces a portal that is correct on the day of handoff and degrades from the next sync onward, and the client pays for the same engagement again within a year.

Phase four: proving it across three systems

Validation sampled records and traced each one across all three systems, confirming that the identifiers reconcile end to end rather than confirming that HubSpot looks tidy.

A record can be perfectly deduplicated inside HubSpot and still carry an enterprise identifier belonging to a different customer. Nothing inside HubSpot can detect that. Only a check that leaves the system and comes back can.

Associations were spot-checked after the merges to confirm that the company, contact, deal and department relationships survived intact, because the association graph is what a merge is most likely to damage silently.

Outcomes

Object types deduplicated: 4. Companies, contacts, deals and departments.

Cross-system identifiers reconciled on each surviving record: 3. Enterprise system, dispatch platform and HubSpot.

Survivorship rules defined: from 0 to one per object type, signed off before execution rather than inferred during it.

Root cause: from unknown to identified and fixed at source. The defect in the synchronisation logic was corrected so the duplicates do not recur.

Engagement shape: 20 hours at 250 United States dollars an hour, divided six hours to diagnosis, twelve to execution, two to validation and handoff.

Records changed before the plan was approved: zero. Every merge and delete followed a documented, signed-off cleanup plan.

Systems checked during validation: 3. Confirmation that identifiers reconcile end to end, rather than that one system looks correct.

Lessons learned

A duplicate is a symptom, and the disease has a name. The underlying condition is that no system had been declared authoritative for any object type. Cleaning duplicates without answering that question produces a tidy portal and the same portal again in six months.

Survivorship belongs to the client and the rule belongs to the object. The implementer knows how a merge behaves. Only the business knows whether the dispatch platform or the enterprise system is right about what a service department is. Asking that question per object type takes an hour and prevents the class of error that cannot be undone.

Twelve hours of merging was made safe by six hours of not merging. The diagnosis phase produced no visible change and every control that made the execution phase reversible in principle and reviewed in practice.

The two hours on the sync defect are the only permanent part. Everything else is a one-time correction of a state the integration would otherwise reproduce. An engagement that had spent all twenty hours on cleanup would have delivered more visible progress and less value.

Validation has to leave the system. Wang and Strong (1996) define data quality by fitness for the consumer's use, and the consumer here is a dispatcher, a finance clerk and a salesperson looking at what each believes is the same customer. A check confined to one system cannot establish that they are.

Limits

Record counts are not published here. The engagement's deliverables include pre and post counts by object type. Those figures belong to the client and are not reproduced, so this document reports the structure of the work rather than its volume.

No business outcome is claimed. Deduplication removes a known source of error from reporting and from outbound communication. Whether any commercial measure improved as a result is not established here, and the engagement did not define a baseline against which it could be.

Historical backfill beyond the affected records was out of scope. Records not touched by the integration defect were left as they were, so the portal is consistent with respect to this defect rather than clean in general.

The source fix addresses one defect. It prevents recurrence of the duplicate-creation pattern that was diagnosed. It does not make the integration correct in every other respect, and no claim to that effect is made.

Ongoing hygiene was explicitly excluded. This was a bounded remediation, not a managed service. The controls that prevent a different class of duplicate arising through user behaviour were not part of it.

Three systems is the interesting number. Two systems with a single integration have one identity question. Three have three pairwise relationships, and the complexity of survivorship grows faster than the count of systems suggests.

Conclusion

The request that started this engagement was a request to remove duplicates.

The work that mattered was deciding, for four object types, which of three systems is telling the truth about what a record is. That decision had never been made, which is why the integration could not recognise its own output, and why every merge before the decision would have been a coin toss with a destructive outcome.

Batini and colleagues (2009) describe assessment and improvement as separate phases with separate techniques, and the proportions in this engagement make the case as well as the literature does: six hours deciding, twelve hours executing, two hours making sure it stays fixed, and none of it in the wrong order.

References

Batini, C., Cappiello, C., Francalanci, C., & Maurino, A. (2009). Methodologies for data quality assessment and improvement. ACM Computing Surveys, 41(3), 1–52. https://doi.org/10.1145/1541880.1541883

Pipino, L. L., Lee, Y. W., & Wang, R. Y. (2002). Data quality assessment. Communications of the ACM, 45(4), 211–218. https://doi.org/10.1145/505248.506010

Redman, T. C. (1998). The impact of poor data quality on the typical enterprise. Communications of the ACM, 41(2), 79–82. https://doi.org/10.1145/269012.269025

Reinartz, W., Krafft, M., & Hoyer, W. D. (2004). The customer relationship management process: Its measurement and impact on performance. Journal of Marketing Research, 41(3), 293–305. https://doi.org/10.1509/jmkr.41.3.293.35991

Wang, R. Y., & Strong, D. M. (1996). Beyond accuracy: What data quality means to data consumers. Journal of Management Information Systems, 12(4), 5–33. https://doi.org/10.1080/07421222.1996.11518099

Conflict of Interest Statement

RevOps HQ is a HubSpot Solutions Partner and was paid to perform the diagnosis, cleanup and source fix described here, on a time-and-materials basis at a stated hourly rate against a twenty-hour estimate. Phase structure and hour allocations come from the signed scope of work. Record volumes are the client's and are withheld. No business outcome is claimed.

Acknowledgments

The client designated the canonical system for each object type, which is the decision the entire cleanup depends on. The export point taken before execution was confirmed by the client's own administrators.