Multi-source Data Imports at Human Services Organizations

Posted in: Blog

By: Kat Evers, Peter Bender

For large multi-service nonprofits, one of the benefits of using an agency-wide system is the ability to see and analyze all of your program data in one place. Beyond a count of unique participants served, an agency-wide database helps you track longitudinal data and change over time. With a whole agency platform like Salesforce, you can see how entire families interact with your organization and the services you provide, giving you a better sense of your organization’s true impact in a community. But importing large quantities of data from multiple sources into Salesforce in a traditional way can be challenging and time-consuming.

The Challenge

You’ve made the decision to implement Salesforce across all programs in your organization. The problem is, many programs in your organization use funder mandated databases; others have no database at all. You don’t want to burden staff with double data entry, but not all funder mandated databases allow integration with other systems. You are concerned about the adoption of Salesforce and data quality if information remains siloed in different systems, especially if Salesforce is not the single source of truth for your participant data. So how do you get data from funder mandated databases into Salesforce?

The process usually involves extracting data from the source, cleaning and deduplicating it, aligning all fields with what you have in Salesforce, matching participants in your data source to existing participants, importing the data into multiple objects in Salesforce, and finally auditing the incoming data to check for duplicates. Not to mention that funder mandated databases are often updated, changing the format of any data you extract. From our personal experience, we know that this traditional import process could take weeks, if not months.

The Solution

To tackle this issue, Exponent Partners created a custom, scalable data import solution that processes incoming data from different sources. There are three major steps:

1. Export/Acquire data from sources into CSV files. This will differ for each source. Some may have pre-formatted reports you can easily run anytime, while for others you may have to request a file from the system owner. This solution is source-agnostic, giving you the flexibility to get around the sometimes intractable issues of systems outside of your control.
2. Import the data to Salesforce. Using whatever data loader with which you are comfortable, import all records into a single custom “staging” object. We used the free Jitterbit Data Loader for Salesforce and created reusable mappings of fields for our customer’s typical file formats. But this is really just a matter of matching up the source and destination fields, which can be as easy as dragging & dropping the “First Name” field from one place to the other. The import itself should be painless and quick.
3. Process the data. This is where the magic happens! Up until this point, it really has just been moving data into a common location as easily as possible, without worrying about data quality or different types of data in your data sources. Now that all the data is there, it takes just a few clicks to say “GO” and automation will do the rest!

Here are some of the best features of the processing system:

• Asynchronous – You don’t need to hang around. The system will notify you when processing has completed.
• User-Friendly – If any particular record produced an error, that will be logged for your review. If it is a simple data problem, like “Ethnicity” has an unexpected value, just fix it and click to continue processing the record when you are ready. No data corruption or loss! In addition, you can process data as often as you like, without fear of creating bad data, because the system keeps track of where you were in the process and can recognize duplicates.
• Configurable Options – We created a way for a non-technical, non-admin staff member to configure options telling the system how to process each kind of record. Maybe clients from a particular source need to have open cases for different programs based on some field specific to that system, or some should be ignored and not processed at all. No problem!
• Data Cleansing – Every system has its own way of tracking demographics or programs. The processing step has one place you can look at for translating all of those “Ethnicity” values from the various source system values to the actual ones you want to use consistently in your reporting across all systems. Importing the same people from two separate source systems? No problem! This solution finds matching records in a variety of ways to prevent duplication, letting you get a reliable count of unique clients, households, and cases. We even plugged in an address verification solution to standardize messy addresses, making them more reliable, more likely to match each other, and even USPS compliant.
• Clicks-Not-Code Customizable – This system uses only platform native declarative features, such as Flow and Process Builder, and no apex code. That means an experienced admin can make fixes or adjustments without hiring a software developer, lowering your TCO. The operations are “componentized”, so that individual pieces can be updated and tested separately, with version numbers and comments.

With this solution, your organization will now have access to data from multiple sources all right in Salesforce, giving you the ability to create the reports and dashboards you need to measure impact.

Contact Exponent Partners today to learn more about this custom data import solution and register for the next webinar featuring Exponent Case Management (ECM). Designed for large human services agencies and nonprofits, ECM is ideal for case workers, program managers, and agency leaders, the application is a fully-featured, customizable solution with intake, assessment, service tracking, case records, outcomes reporting, referral tracking, and more.