AI-Powered Legacy Data Migration and Schema Mapping for Rapid Customer Onboarding

Overview

About the Client

A rapidly expanding Waste management SaaS platform for municipal authorities along with commercial garbage haulers was faced with significant scaling issues in the onboarding of customers.

The platform was able to provide extensive routing optimization, billing for customers as well as asset tracking and compliance management for regulatory compliance as well as attracting new customers moving from competing systems and legacy systems. But, every new customer had historical data that was in highly inconsistent formats, including Excel spreadsheets, database exports, scanned papers records as well as data from over 15 competing systems.

The manual data cleansing and import process took between 40 and 60 hours for each customer, resulting in the possibility of a bottleneck which impeded new customer acquisition, caused delays in time-to-value, and stretched engineering resources that were frequently removed from product development in order to deal with the data transfer.

Challenges Faced

The SaaS provider was plagued by severe problems with data quality and consistency in customers’ migrations. The old customer data was delivered with inconsistent formatting of addresses with PO boxes rural route numbers and abbreviations not standard, which affected geocoding and route optimization algorithms. Service frequency data was available in a variety of formats, including “every other Tuesday, 2x/week’, and other customized codes that required manual interpretation.

Hierarchies of customer accounts were not recorded and it was difficult to link addresses for service to billing entities as well as the parent companies. Asset data for equipment, if available, was based on particular vendor-specific parts numbers and descriptions that needed to be translated to the platform’s standard asset catalog. The team that was onboarding spent most of their time cleaning spreadsheets, standardizedizing data fields, and solving doubts through phone messages and calls. The data quality issues that did not get addressed in the cleaning process led to issues downstream, including failing routing optimization, inaccurate billing, and complaints by customers regarding service history that was not available. The manual procedure of 40-60 hours per customer meant that the business could only accept 3-4 new customers a month which severely limited growth and causing pressure to hire more implementation personnel instead of automating the basic data problem.

Our Solution

Blueflame Labs deployed automated data processing solutions specifically designed for multiple-source SaaS customers’ onboarding. Our advanced data mapping engine analysed the data files of customers arriving to detect patterns in schema and identify the purpose of columns through semantic analysis of sample values and headers and then map the fields from source to the schema of the target platform with confidence scoring to allow for human review.

The data cleaning layer is automated and standardizes the address formats using USPS validated geocoding and additional validation that is specifically tuned for waste management areas, normalized service frequency descriptions into standardized platform codes, and deduplicated customer records by using fuzzy matching on addresses, names and account numbers and verified data’s completeness, flagging the absence of required fields prior to import.

Our AI automated data pipeline processed various formats from sources such as Excel files with uncoordinated order of columns CSV exports which have variable delimiters, encoders, database dumps from old Waste management software, as well as scans of PDF route sheets that have OCR processing.

The data migration services for the past component dealt with vendor-specific translations, such as equipment asset mappings from competitor part numbers to catalog items for platforms, as well as translation of service codes from older platforms to the current terminology of the platform and route structure conversions of various optimization methods.

Real-time data analytics supplied teams for onboarding with dashboards for data quality and automated validation reports that highlighted the issues that require clarification from the customer, and a progress tracker that showed how much time was spent and the estimated time from production.

Results

The SaaS platform has seen significant improvements in the efficiency of onboarding and scalability

• 85% reduction in time to onboard customers, reduced from 40-60 hours to 6-8 hours for each customer
• 5x more onboarding capacity in a month, without the need for additional implementation staff
• 97% accuracy of data quality after migration, which reduces customer ticket support by up to 68 percent
• 73% faster time-to-value increase for new customers, resulting in higher customer satisfaction and retention
• Annual savings of $420,000 in labor costs for implementation
• Reusable migration templates reduce the effort to repeat system migrations by 90percent
• Self-service Onboarding Portal that allows customers to make their own decisions about migration
• Engineering resources are freed from the migration process and returned to prioritizing product development