Ask the Experts: Data Migration for Nonprofits
For the next installment of our Ask the Experts series, we’re tackling the big bad data migration question. Data migration likely strikes fear into the hearts and – mostly – the minds of non-data professionals whose day to tackle the project nears (and which is probably long overdue for most, as we’ll hear shortly). We chatted with Tompkins Spann, a data and Salesforce expert and Vice President of KELL Partners, an Austin-based consulting group, to get some much needed advice on data migration for nonprofit organizations.
1. Migrating data seems like a scary and daunting task. What are a couple things you should absolutely be sure about before undertaking the migration?
Wait what?! Data migration is hard? Isn’t there an app for that? Just kidding of course.
Indeed, migrating data is typically about 50% of the effort and cost of any implementation project. Truth be told it doesn’t matter whether you’re migrating to Salesforce or any other database program, it’s always hard! The reason is because you’re shoving about 1,000 square pegs into holes of all different shapes. No two migrations are the same!
To help prepare, first get to know your own data and understand the process you are about to embark upon.
To understand your data, you don’t need to be a super geek or a DBA, but you need to have a good sense of the types of data you’re storing currently.
To understand your data, you don’t need to be a super geek or a DBA, but you need to have a good sense of the types of data you’re storing currently: Contacts? Check. Households? Check. Donations? Check. And so on. Get a good sense of how these data types relate to each other and whether you have any special rules (written or in your head) for how data is being managed.
A common rule nonprofits must consider with their migration is about householding. When do you want two contacts to be linked together within a household? It’s not as easy a decision as you might think!
The process will more or less follow this pattern:
1. Analysis – your partner will first analyze your legacy data. This involves getting to know the structure of all tables and fields and how they relate to each other; as well as, assessing the integrity of the data and deduplification and cleanup work that will be required. Every database has dirty and duplicate data, and it’s impossible to eradicate this 100%, but there’s a LOT that a data expert can do to improve the quality of your data.
2. Mapping – this is the slog, the really hard work that will take your time. Your migration expert (if they’re good) will walk through every table, every field and seek to understand its purpose and structure so that they can determine how this old data should live in the new system and establish migration rules. Buckle up for a few weeks, this takes time to get right and it’s absolutely critical that it’s done well.
3. Conversion – the rules defined in the mapping stage are scripted. Your partner will create conversion scripts to transform the old data to the new system based on the mapping and transformation rules. During this phase you may not hear from the partner for a period of time as they’ll have their heads down writing SQL scripts and the like. Woohoo!
4. Validation – now you get to see the results. But fair warning, you’re gonna work it. Here you’ll want to do some side by side comparisons of your old database with the new one, making sure the mapping rules were applied correctly and that you approve this new structure. Head’s up, you will find things you’ll want to change. It’s a lot like moving into a new house, after you see your furniture and art on the walls in the new place, you may want to move them around to get it just right.
5. Cleansing – Deduping, data appends, normalizing common values, fixing errant weird characters and more. This is where your data starts to get squeaky clean and you’ll begin loving all the new reports you can run!
6. Differential – it’s probably been weeks since you gave your partner your database to convert. Well you probably didn’t stop using it did you? That’s right, a differential allows your partner to rerun all of the conversion and cleansing scripts on an updated copy of your database and add or modify any records that have changed in the time since it was previously delivered.
2. In your experience, what are some top reasons organizations move platforms? Since it is such a huge project, can you give us some examples that would definitely warrant the undertaking?
The most common is modernization. Many nonprofits have been enduring a “database” that doesn’t meet their need for far too long. Whether it’s a homegrown product or series of spreadsheets, or an off-the-shelf application built in 1984, the choice to use Salesforce is about modernizing their database and tapping into the power of a CRM.
Admittedly, many groups first think of Salesforce as simply another donor database, albeit in “the cloud”. But once they begin using it and understanding the capabilities of Salesforce, their eyes light up and they seek to embrace the true potential of CRM across their entire organization.
3. On average, how long might it take an organization with 10 years of donor data to switch platforms?
A common flaw is thinking about volumes of data as a proxy for complexity of migration.
It’s relevant, but not the most significant factor in scoping migrations.
Take this example:
Let’s say a nonprofit has an excel spreadsheet of 10,000 contacts. If those contacts contain only very basic information like first name, last name, address, and email, then it doesn’t matter if there are 100,000 or 1,000 rows, the effort to migrate them is essentially the same. They will be more cleansing required, but the total effort is not 10x the original. But let’s say this spreadsheet has columns for “2012 Annual Event Attendance” and “Historical Membership Level”. Well then we’re going to be manipulating this flat file into a relational database to capture event history records, historical memberships and more. And that can impact the effort greatly.
Have more than one data source? Well, then…
If you throw another source in the mix…“Oops, we just found an old archive of 500 donations, can you also migrate these?” Well, then you’re asking your partner to apply that same process to two sources and not just one, which can significantly impact the work required, even though there may only be 500 records in that second source.
4. Which leads us to our next question: Let’s say you aren’t ready yet. Are there any fields that generally come pretty standard on most CRMs and their integrations that would allow you to at least begin preparing and cleaning up data to make a future move easier?
That’s a reasonable question, but the answer is unfortunately not that easy. Every time we’ve had organizations attempt to bridge the gap, maintaining parts of their old data in the legacy system and migrate only a subset into the new system, it’s a mess.
At some point you need to bite the bullet and do the work. A hybrid approach can work, but it usually shifts the burden from one bucket to another, causing frustration and confusion.
5. Once you’re sure about moving all your data – you’ve decided on the platform and you’re mentally, and logistically, prepared to move on – how do you make it as easy for yourself and your team as possible? Any tips and tricks you’ve learned over the years?
Carve time in your schedule to work with your partner. They’ll tell you what they’ll need from you. You’ll be asked to make a 1,000 small decisions (a lot like renovating a kitchen), so make sure you’ve got the time available to focus on this project. You cannot totally hand it off and expect the results to meet your standards.
6. If an organization is working with a consultant, what are some questions they should absolutely ask before switching platforms and migrating data?
Ask them to describe their process and ask them about how they’ll perform validation.
And most importantly, ask them about who will be performing the mapping. The mapping process requires individuals with a unique set of skills. They’re not typically the person who can write SQL scripts, they’re more of a nonprofit expert, but they must have a solid understanding of the technical process so that they can convey the rules to the people writing complex scripts. Just like with any road trip, it doesn’t matter if you’re driving a VW, a Tesla or a UHaul, if your map is wrong you’re doomed!
There has never been a technology so transformative to the nonprofit industry as Salesforce.com since the introduction of the internet and email. Salesforce has the power to affect how every organization manages its constituent relationships, programs, donations, communities, events and much more. I love talking to nonprofits about ways Salesforce can bring efficiency and more insight to their operations.
Tompkins “Tommy” Spann has been working with nonprofits since he started his own organization in 2000. He has been working with nonprofits and Salesforce.com since 2007, when he helped develop a product on the Salesforce.com platform while at Convio. Now the Vice President of KELL Partners, he’s focused on helping nonprofits implement and navigate Salesforce.
Harness the Power of CRM + Fundraising Data
Image Credit: Loco Steve