How to Move Data from Point A to Point B

As organizations add various tools to their technical ecosystem, the need to move data from one system to another becomes increasingly important. However, for many of us, these data transfers can be a bit daunting.   

Regardless of how much data is being moved, or what systems are being used, the process is the same.   

Even when leveraging tools such as Importacular or ImportOmatic that aid in data mapping, it is important that you create a document that outlines all of your decision-making and logic.  This document will serve as a final record of the paths taken.  A record that will help those conducting this work in the future.   

Before we dive in let’s define some Terminology:   

• source system - lets say you are moving from your online system (source) to your database (target). Your source system is your online system.  

• target system - is where the data is going to reside, such as your database.  

• data map - a document that compares the fields in the source system (point A) to the fields in the target system (point B).  

• header row - column names on a .csv file. For example, first name, last name, address, donation amount, etc.  

 Below is an example of a data map:  

With these terms in hand, we’re ready to start.  

Step 1: Extract data from your source system into a csv file.  Do not alter the data. Furthermore, select every possible field allowed in your export. This ensures you have the most comprehensive data set, and more importantly, that you will not have any missing values.     

Step 2: Create a document that contains all the row headers from your source system.  Copy the header row of your data file and paste it into your Excel document into column A. Paste using the transpose functionality; this allows you to change the horizontal headers into a vertical list.  Column A becomes your list of source fields.   

Step 3: Data mapping means you can begin documenting how you will move data from one system to another. In the field to the right of your source field (cell B2 in the above diagram), you will enter the target field.  The field that you want the data from the csv file to land. For example, first name of your online data should be mapped to the first name field of your CRM.  Some fields, such as name, address and contact information, are easily mapped.  Sometimes you need a little more information such as:  

• Business Rules – this is the logic the data needs to follow. Understanding the data file, the target system and your processes is critical. For example, most online systems only have one address field, but your CRM may require you to label an address record as “home” or “work” address.  In this scenario, there is a business rule.  A business rule is the data rule associated to the field that you follow within your organization.  The less complicated a rule the better.  Most organizations would say the rule of thumb is that if a business name is listed on the online file, then the address is a business address in their CRM  its else home address (see cell C7 in the diagram above).  

• Clean Up – your online system and your CRM are two unique systems with their own rules. So you may be required to massage the data a little bit. For example, the first and last name in the online file is one field, “Jane Doe” but your CRM has a unique field for each. In this case, we add an extra row (cell D4 & D5 in the diagram above). Provide the logic of how the data will be split e.g. first name will be the first word and then last name will be any remaining words.    

• Other Notes – this column can provide additional information as required. See cell E3 in the diagram above.   

Step 4 – Begin testing. You do not want to start moving large amounts of data without testing things out. Take 1 or 2 rows of data from the file that needs to be moved and follow the logic documented in your data map to start moving the data. Validate that you are getting the desired results. If you are not, go back to your data file and compare what you have imported with your data map. Then look at the data itself to see what went wrong. Update your data map accordingly. The more you test, the better your overall outcome!  

Conclusion 

This process looks pretty involved, but if you follow these steps closely, you will only need to do a big lift once. Additionally, a significant amount of time should be spent on testing. Do not be afraid; mistakes will be made. Even the most seasoned pros can slip up. Be diligent in maintaining your documentation. Your data map will guide you through the process and ensure moving data is as simple as rinse and repeat.  

If you want greater insight into this topic, join us for a free webinar in July. We will take a hands-on approach to moving data. I hope to see you there. If you have any questions, please reach out to us.  

If you would like us to tackle a topic or problem of interest to you, please let us know here. 

Sentinel Consulting is a consulting firm that focuses on helping non-profits strengthen their operational efficiency. We speak the same language as our clients because we were all fundraisers at some point in our careers.