The Challenge

A regional industrial distributor had grown through acquisition, purchasing five smaller distributors over an 18-month period. Each acquisition brought new customers, new vendor relationships, new product lines, and new territory coverage. It also brought five completely different ways of organizing data.

The acquiring company wanted to operate as a single unified business. That meant one ERP system, one CRM, one way of doing things. But the data situation was a mess:

• Six different ERP systems - The parent company plus each acquisition ran different software. Two were on older versions of the same ERP, two used competing products, and one was still running a legacy system from the 1990s.
• No common identifiers - The same customer might exist in three different systems under three different account numbers with three different spellings of their name.
• Conflicting item catalogs - Each company had their own part numbering schemes. The same product from the same manufacturer might have five different internal SKUs across the organization.
• Inconsistent vendor records - Vendor names, payment terms, and contact information varied wildly between systems.
• Years of transaction history - The company needed to preserve historical data for reporting, but that history was scattered across incompatible systems.

The parent company had already selected their target ERP and CRM platforms. What they needed was someone to make sense of the data chaos and get everything into those systems in a clean, consistent format.

The Approach

Data consolidation at this scale isn't something you can automate with a simple script. It requires understanding the business context, making judgment calls about conflicting information, and building processes that can handle exceptions gracefully.

Phase 1: Discovery and Mapping

Before touching any data, we spent time understanding each source system. What fields existed? What did they actually mean? How was the data used in day-to-day operations?

We documented the data model for each of the six systems, identified corresponding fields across systems, and flagged areas where the same concept was represented differently. A "customer" in one system might map to multiple record types in another. A "ship-to address" in one system might be embedded in the customer record in another.

This discovery phase revealed the true scope of the problem. What looked like six customer databases was actually closer to fifteen different ways of storing customer information once you accounted for all the variations.

Phase 2: Customer Master Consolidation

Customers were the most critical—and most complicated—data set. We built a matching and deduplication process that worked in stages:

Exact matching: First pass identified obvious duplicates—same company name, same address, same phone number across systems. These were easy wins.

Fuzzy matching: Second pass used algorithmic matching to identify likely duplicates with slight variations. "ABC Manufacturing" vs "ABC Mfg Inc" vs "A.B.C. Manufacturing LLC" might all be the same company.

Address normalization: We standardized addresses against USPS data, which revealed many more duplicates hiding behind formatting differences. "123 Main Street, Suite 100" and "123 Main St #100" resolve to the same location.

Manual review: Edge cases went to a review queue where staff from the acquired companies could make judgment calls. Sometimes two similar-looking records really were different customers. Sometimes records that looked different were actually the same customer who had moved.

The customer consolidation process identified that 34% of customer records across all systems were duplicates. More than a third of the combined customer database was redundant data that would have caused confusion, double-marketing, and split purchase history if loaded as-is.

Phase 3: Item Catalog Unification

The item master presented different challenges. Unlike customers, where duplicates are clearly bad, the item situation required decisions about how to structure the catalog going forward.

We worked with the operations team to establish:

• A unified part numbering scheme - New internal SKUs that would be consistent across the merged organization
• Manufacturer part number as the linking key - Since the same products appeared under different internal numbers, manufacturer part numbers became the way to identify what was actually the same item
• Category and attribute standardization - Product categories and specifications needed consistent structure for the new catalog to be searchable and useful
• Cross-reference tables - Legacy part numbers needed to map to new numbers so historical data remained meaningful and customers using old numbers could still be served

The item consolidation reduced the combined catalog from over 95,000 SKUs to approximately 62,000 unique products, eliminating duplicates while preserving the cross-references needed for historical lookups.

Phase 4: Vendor Consolidation

Vendor records required similar deduplication work, but with an additional wrinkle: different companies often had different terms, pricing, and relationships with the same vendor. The consolidated vendor master needed to preserve the best terms while establishing a single point of contact.

We created a vendor review process where purchasing staff could compare terms across the acquired companies and select the optimal setup for each vendor going forward. In several cases, the combined purchasing volume qualified for better pricing than any individual company had negotiated.

Phase 5: Historical Data Migration

Transaction history—orders, invoices, payments, shipments—needed to come along for reporting and customer service purposes. This was primarily an ETL (extract, transform, load) exercise, but complicated by the need to link historical transactions to the newly consolidated master records.

We built migration scripts that:

• Extracted historical transactions from each source system
• Mapped old customer, vendor, and item codes to consolidated records
• Transformed data formats to match the target system requirements
• Loaded history in a way that preserved audit trails and reporting accuracy

The result was a unified history where a customer's complete purchase record appeared in one place, regardless of which legacy company they had bought from.

The Implementation

The consolidation happened in waves, aligned with the company's operational integration timeline:

Months 1-2: Discovery, mapping, and process development. Built the tooling and established the matching rules.

Months 3-4: Customer and vendor consolidation. Created the unified master records in the target systems.

Month 5: Item catalog migration. Loaded the consolidated product catalog with all cross-references.

Month 6: Historical data migration. Brought over transaction history linked to consolidated records.

Ongoing: Supported the operational cutover as each acquired company transitioned to the unified systems.

The Results

Single source of truth. The company now operates from one ERP and one CRM with clean, consistent data. There's no more hunting through multiple systems to find customer information or reconciling conflicting records.

34% reduction in customer records. Eliminating duplicates means marketing doesn't contact the same customer multiple times, sales reps have complete customer history, and reporting reflects actual customer counts rather than inflated numbers.

35% reduction in item catalog. A cleaner catalog means easier inventory management, better purchasing decisions, and a more navigable product offering for sales staff and customers.

Preserved institutional knowledge. Cross-reference tables mean that old part numbers, customer codes, and vendor IDs still work. Staff who memorized legacy codes can still use them while the system translates to the new unified structure.

Foundation for growth. Future acquisitions can follow the same playbook. The processes and tooling we built are reusable, making the next integration faster and less painful.

Key Takeaways

Data consolidation is a business problem, not just a technical one. The hardest decisions weren't about data formats—they were about business rules. Which customer record is the "right" one? How should products be categorized? What vendor terms should apply? Technical tools can support these decisions, but humans who understand the business need to make them.

Perfect is the enemy of done. We could have spent months trying to achieve 100% automated matching. Instead, we built processes that handled the clear cases automatically and surfaced the ambiguous ones for human review. The result was faster completion with higher confidence in the outcomes.

Cross-references are essential. Nobody can retrain their memory overnight. Legacy codes need to keep working even as the organization moves to new standards. Building comprehensive cross-reference tables takes time but prevents enormous friction during the transition.

M&A data integration should start earlier than it usually does. Most companies treat data consolidation as an afterthought—something to figure out after the deal closes. Starting the discovery process during due diligence would have revealed the scope of work earlier and allowed for better planning.

---

Facing a data consolidation challenge from M&A activity or system migration? Learn about our data services or contact us to discuss your situation.