Streamlining Guest Support: Love INC's Data Migration

Challenges

Love INC’s center of operations is its Help Center, where requests for assistance are received, assessed, referred to the most appropriate resource, and followed up on. People who request assistance are referred to as “guests” and are tracked within a client management system. Love INC was looking to move to a new client management system that would provide numerous benefits over the current system. These benefits included ease of use, more customization, improved workflows for staff and volunteers, and better out-of-the-box reporting. This would ultimately lead to a more streamlined and pleasant guest experience.  

The primary challenge was how to migrate over 6500 case files from the previous system to the new one, as there was not an integration or migration tool between the two platforms. Thus, Love INC was looking at either manual data entry or working with the data as an exported CSV file, which was estimated at over a hundred hours of work. 

One of the main technical hurdles for the migration was formatting the data to preserve the integrity and nuisance of the original system. The data was exported as CSV files from the original system with key fields that served as the links between the underlying tables in the source system. Beyond that, however, there was no data dictionary. Love INC would have to build out requirements on what data was needed for the migration. In addition, some of the data had become misshapen, condensed, or expanded because of underlying JSON structural hierarchies and other architectural constraints of the source systems. These would have to be navigated to shape the data so it may be imported cleanly into the new system. 

The new system did not have an out of the box conversion tool to prepare the CSV files for migration. Without a technically skilled person on staff to work with the data, Love INC was looking at potentially spending hundreds of hours of staff or volunteer time to manually edit and prepare the CSV files. The manual efforts would also extend to testing and validation of the data upon review and migration prep. Love INC did not budget for a tool specifically designed to assist with the migration so open-source software was preferred.

This is where SME Solutions Group helps out.

Solution Overview

After gathering both the technical and business requirements from Love INC, SME developed a data transformation solution using lightweight, open-source technology. Because the data consisted only of CSV files, SME decided to use DuckDB, an in-process SQL database management system for simplicity. DuckDB can be run easily using the command line, in Python, or via a database IDE like DBeaver, which is how SME chose to run it. Data did not have to be ingested via pipeline. Rather, DuckDB could read the data via SQL Select statements directly into in-memory tables and write transformed and processed data to new files when ready. The overhead of the solution was very low, needing only the data files and the SQL script to maintain. To top it off, DuckDB’s processing speed is incredibly fast for running on local hardware, making the most of development and testing time. This allowed SME to build a local instance of a data warehouse to handle the migration use case. 

The solution's requirements would need the ability to perform transformations such as joins, filtering, text manipulation, date casting, and unnesting list structures.  DuckDB has extensive support for complex SQL queries and has a dialect that closely follows the convention PostgreSQL. This made it easy for SME to pick up the tool and instantly start working with it from day one. SME also utilized their data quality scripts that they run for every data warehouse implementation. This made sure the data had consistency and integrity by checking for nulls, duplicates, and values out of range. Finally, SME utilized open-source diagramming technology to generate ERD diagrams and documentation for the end-result data model. This allowed for a seamless handoff between SME and the vendor migrating the data.  

Key Initiatives

The primary focus of the project was to try to eliminate as much manual effort as possible when it came to preparing the data. The SME solution was able to work with the data in bulk and implement logic via SQL as opposed to needing to go row by row in Excel or use “Ctrl + F” to find everything that needed changing. More importantly, by working with the data in a query-based approach, there was a lot more flexibility in terms of shaping and modeling the data. The CSV files could be split, joined, combined, or duplicated with ease. This allowed for the data to form a guest-centric, many to one data model, with the demographics, household, verifications, needs met, and other attributes linking to their respective guest/case. In going with this approach, the SME team spent less than a fifth of the time it would have taken to process the data manually, a considerable time savings. 

Because SME completed all the technical development and validation of the data for the migration, the Love INC team focused their time on training and adoption of the new case management platform. With some test cases created, the team is able to teach staff volunteers how to navigate the new system, understand the differences from the old system, and do some dummy data entry to get more comfortable with it. With this, the cutover time during the actual data migration was minimized and the end users were able to start working with live data in the new system immediately.  And because of the data quality and validation testing that went into the development cycle, SME delivered a single migration round, reducing the costs from the new system vendor. 

Business Impacts

By migrating to the new platform, Love INC developed a better digital workflow and ultimately is able to provide a more comprehensive level of care to a larger number of children and families. The new system’s interface is more appealing and user friendly for staff and volunteers and gives them the ability to customize questions used during guest intake, which allows for a digital capture of data as opposed to written paperwork that is then transcribed to data. The migration to a new system also had a significant impact on how reporting was done at Love INC. Love INC will be able to more accurately track the value of the needs met for each guest and perform more accurate monthly reporting of key metrics. The new system also clearly outlines goals and the care plans that will help each guest meet the goals they set for themselves. And because the historical data was preserved post-migration, Love INC can use it to build their monthly analytical reporting, which allows for year-over-year comparisons measuring their annual impact.

Conclusion

The successful migration to the new client management system has significantly enhanced Love INC of Metro Tampa's ability to serve its community. By leveraging SME's solution, which utilized DuckDB for efficient data transformation, Love INC minimized manual data processing, allowing staff to focus on training and adoption of the new system.

As Love INC's Executive Director noted, “We’re so grateful for the talent and expertise George and SME Solutions Group has brought to this project. We only utilized our previous client management system to help as a digital backup to a paper intake and case management system. Because of this, we were drowning in case files.”

The new platform's user-friendly interface and customizable features have streamlined workflows and improved data capture and reporting, enabling more precise tracking of services and outcomes. Overall, this transition not only optimized operational efficiency but also positioned Love INC to provide more effective and holistic support to families and children in need.