It is not uncommon to find yourself with multiple data lists, excel spreadsheets and different data sources. However, at some point you may want to consolidate all your data into one CRM or spreadsheet. Here are the steps to take when performing data consolidation regardless of data type and sources of data.
- Planning – Prior to beginning your data consolidation project you need to map out all data sources and assign hierarchies and flag important fields. For example, file A may contain information on registration for an event and you wish to only put a flag that they attended the event for your final file. In the planning step you should also identify hierarchy. For example, if two files contain two different address for the same person which one takes priority? Finally, you should establish a way to match data between sources, this can be done by unique identifier such as customer ID (if the data is available), email, first name/last name or even dates. Keep in mind that this is the most time consuming part of the project.
- Putting files together – next step is to put your files together, starting with the highest priority and adding fields or records based on your plan. Do not worry about creating duplicate records as that will be the next step. The goal in this step is to not have any data loss!
- De-duping – using de-duping tools or identifying duplicates manually it is now time to establish the master record and only keeping the data based on the hierarchy. The goal here is to have complete information and if duplicate data is found with different data points, using your hierarchy to only copy the correct data point.
- Standardizing – Now that you have complete and de-duped data, it is time to standardize data. For example, you may find that you have copied address from one file source with the full state name spelled out “New York”, while another file source had it the ISO format of “NY”, it is now time to standardize it so that data has the same field.
- Formatting – Similar to standardization, you may have formatting issues such as all caps or date fields being stored in different formats. It is now time to format all fields so they follow the same format so you can easily use your data.
- Testing – Quality control is key when doing data consolidation, you want to make sure all records are there, that you have no data loss and the formatting was done correctly. This can be achieved either by manually checking a few random records or systematically checking to make sure every record is there using simple formulas in excel such as the vlookup.
Data consolidation can be a very time consuming task, and if not done right can create more issues down the line with potential data loss! Hiring a data consolidation professional can save you time and money. StrategicDB is a full service data cleansing company, which focuses on data consolidation, contact us at firstname.lastname@example.org or learn more here.