HubSpot–Epicor Integration Case Study: One Customer Record for a Building Products Distributor

Summary

Thurlow Building Products distributes roofing and cladding through six branches, at roughly 5,100 order lines a month. Epicor Prophet 21 held the operating record. Each branch had created its own customer accounts over fifteen years, so a single contractor could exist four times with four credit positions and four pricing agreements.

The engagement resolved identity across Epicor and HubSpot, established a survivorship rule, and made the resolved identity durable rather than recomputed. Duplicate customer identities fell from 1,840 across 4,600 accounts to 61, credit decisions taken against the wrong entity fell from an estimated 30 a month to under 3, and branch-level pricing disputes fell by 71 percent.

Background: Four Customers Who Are One Contractor

The duplication was not carelessness. A branch opening an account for a walk-in contractor had no practical way to know the same business bought from another branch two counties away, and for most of the period there was no commercial reason to care.

The reason arrived with a group credit policy. A contractor with a $40,000 limit at one branch and $40,000 at three others carried $160,000 of exposure against a limit nobody had set. Two write-offs in eighteen months traced to exactly that shape.

Wand and Wang (1996) treat an information system as a representation of the real world and define a data quality problem as a breakdown in the mapping between the two. One contractor represented four times is that breakdown, and every downstream figure — exposure, lifetime value, margin by customer — inherits it without any of them looking wrong.

The Audit

Five weeks against Epicor extracts, the HubSpot portal and the credit ledger.

Duplication. 1,840 of 4,600 customer accounts were duplicates of another account, established by matching on normalised trading name, address and tax registration in combination rather than on any one of them.

Exposure. Aggregating duplicates showed 214 contractors whose combined limit exceeded the limit the credit committee believed it had granted, by a median factor of 2.4.

Pricing drift. 38 percent of duplicated pairs carried different pricing agreements, so the price a contractor paid depended on the branch that took the order.

Identity coverage. No shared key existed between Epicor and HubSpot. Matching had been performed by company name at import, which is what created the HubSpot-side duplicates on top of the Epicor-side ones.

Identity Established Once, Then Held

The decision that shaped the project was that identity would be resolved once and stored, rather than recomputed on each sync.

A dedicated property on the HubSpot company holds the Prophet 21 customer identifier, with unique values enforced. A second property holds the group identifier, which is the surviving account after merge. Every downstream read joins on the stored key rather than on any attribute that a person can edit.

Pipino, Lee, and Wang (2002) set out how data quality can be assessed in practice and give uniqueness a metric of its own rather than treating it as a by-product of careful work. Storing the key is what makes that metric computable: a weekly job now counts accounts with no group identifier, and the number is reported to the credit committee rather than assumed.

The Build

Eleven weeks, five phases, using Prophet 21 REST endpoints, a matching pass run in the client's own warehouse, and a private HubSpot app.

Phase one, candidate matching. Normalised name, address and tax registration produced candidate pairs with a confidence score. Nothing merged automatically. Pairs above the threshold went to a branch manager for confirmation, and the confirmation is retained.

Phase two, survivorship. The surviving record is the oldest account with an active credit agreement, and the rule is written down. Where two candidates both held active agreements the pair escalated to credit rather than resolving by date, because the alternative silently discards a negotiated position.

Phase three, group identifier. Merged accounts retain their original identifiers and gain a shared group identifier. Nothing was deleted in Epicor, because order history references those accounts and deletion would have broken it.

Phase four, credit at group level. Exposure is computed against the group, and the credit check at order entry reads the group position rather than the account position.

Phase five, pricing reconciliation. Divergent pricing agreements were resolved to one per group, in review with each branch, over four weeks.

Outcomes

Duplicate identities. 1,840 before, 61 after, the residual being pairs the branches could not confirm as the same legal entity and which were deliberately left unmerged.

Credit exposure. Contractors whose group exposure exceeded their intended limit fell from 214 to 0 at go-live, and the weekly report has held under 5 since.

Wrong-entity credit decisions. An estimated 30 a month before, under 3 after.

Pricing disputes. Branch-level pricing disputes fell 71 percent over two quarters.

Write-offs. No write-off attributable to fragmented exposure has occurred in the eleven months since. The prior period carried two.

Order entry time. Median order entry fell by 90 seconds, because the account search now returns one result rather than four.

What Resisted

The merge itself resisted, and not technically. A branch manager reading a merge proposal sees an account they opened and have serviced for a decade being absorbed into another branch's record, and the first round of proposals returned a confirmation rate under 40 percent.

The mechanism that changed it was showing the exposure figure alongside each proposal. A manager who can see that the contractor carries $160,000 across the group against a $40,000 limit confirms readily; the same manager shown only two similar-looking records does not. Umble, Haft, and Umble (2003) catalogue what determines whether an enterprise-system implementation succeeds, and sustained involvement from the people affected appears more reliably than any technical factor.

The second difficulty was that Prophet 21 does not model a customer group natively in the way the credit policy needed. Sia and Soh (2007) classify exactly this: a misalignment between what a package assumes about a business and what that business actually is, distinct from any defect in either. Rather than modify the package, the group identifier lives in HubSpot and is read by the order-entry check, which keeps the change outside the ERP and made it acceptable to the team maintaining it.

Limits

This does not prove that identity resolution improves credit outcomes generally. Thurlow's exposure problem was created by branch autonomy over account creation, and a business with centralised account setup would not have accumulated the same condition and would gain far less from resolving it.

The matching pass also relied on tax registration being present on most Epicor accounts. It was present on 81 percent. A dataset without that field would rest on name and address alone, which is materially weaker and would likely require a higher confirmation threshold and more manual review.

Figures are drawn from the client's own credit ledger and order history. The write-off comparison covers eleven months against a prior eighteen, which is not a like-for-like period.

Conclusion

Every figure above follows from one decision: that identity is established once, stored, and joined on thereafter. Nothing in the project required a new capability from either system.

The general form is that duplicate records are rarely a data-entry problem. They are ordinarily a consequence of a structure that gave several people a legitimate reason to create the same thing, and the resolution has to address the structure rather than the symptom.

References

Karimi, Jahangir, Toni M. Somers, and Anol Bhattacherjee. 2007. "The Role of Information Systems Resources in ERP Capability Building and Business Process Outcomes." Journal of Management Information Systems 24 (2): 221–260. https://doi.org/10.2753/MIS0742-1222240209

Sia, Siew Kien, and Christina Soh. 2007. "An Assessment of Package–Organisation Misalignment: Institutional and Ontological Structures." European Journal of Information Systems 16 (5): 568–583. https://doi.org/10.1057/palgrave.ejis.3000700

Pipino, Leo L., Yang W. Lee, and Richard Y. Wang. 2002. "Data Quality Assessment." Communications of the ACM 45 (4): 211–218. https://doi.org/10.1145/505248.506010

Soh, Christina, and Siew Kien Sia. 2004. "An Institutional Perspective on Sources of ERP Package–Organisation Misalignments." Journal of Strategic Information Systems 13 (4): 375–397. https://doi.org/10.1016/j.jsis.2004.11.001

Umble, Elisabeth J., Ronald R. Haft, and M. Michael Umble. 2003. "Enterprise Resource Planning: Implementation Procedures and Critical Success Factors." European Journal of Operational Research 146 (2): 241–257. https://doi.org/10.1016/S0377-2217(02)00547-7

Wand, Yair, and Richard Y. Wang. 1996. "Anchoring Data Quality Dimensions in Ontological Foundations." Communications of the ACM 39 (11): 86–95. https://doi.org/10.1145/240455.240479

Conflict of Interest

RevOps HQ is a HubSpot Solutions Partner and was engaged and paid by the client described.

Acknowledgments

Prepared by RevOps HQ.