The next step of the journey to completing your Data Cleansing Program is the Preparation phase. Probably the most time consuming phase of this process as you will need to create lists of values for both the “incorrect” values and their corresponding “correct” values for each field in your program.
The values for the incorrect fields can be supplemented with the information you gathered by analyzing your current data using reports (as seen in the Phase 1 Blog Post). Also there are two invaluable Blog posts on Topliners already by Chad Horenfeldt:
These posts are great for step by step information on how to setup individual fields. In the theme of this series I will not go into detailed steps for every area but instead try to provide accompanying information for your data cleansing program.
The preparation phase requires a bit of work but first ask yourself these questions before forging ahead:
- What lists (one per field) do I need to build?
- What fields do I need to create (Contact, Prospect or Company)?
- Have I collected all the “incorrect” values I need to account for?
Below are some Tips/Tricks to help you answer these questions.
Creating lists for your Data Cleansing program can be a arduous process if done from scratch but thankfully, (well thankful only in this case ••J), we already have a great source of “incorrect” values in our respective Eloqua databases.
If you export one of the reports we looked at in “Phase 1” called "Contact Field Values" for each field you need cleansed then you have a great starting point for your cleansing lists.
This example shows Country values in both Full name and Code format, but let’s say we wanted to make sure all our Country values were in Code format.
The first step would be to export this file so we can manipulate the data in Excel. In the top left corner of the reporting area you should see an Export dropdown which provides various formats, but I would recommend exporting to CSV.
The exported report when opened in Excel should appear as below. The highlighted yellow areas can be removed from the excel file:
Now remove any values that are actually formatted in the manner that is correct. In our example we would remove all the Country code values that are correct such as “TR”,”ES”,”DE”, et cetera.
The remaining values should be the “incorrect” values that need corrected. At this point, go ahead and rename the column header to a header name that makes more sense, such as “Incorrect Field Value”. Also add a second Header value called “Correct Field Value”.
At this point you will want to begin filling in the correct match for each incorrect value:
Once you have completed your list you will need to now upload these lists into Prospect/Company Groups. There should be one group for each list of values and each contact field that will be normalized.
This approach is applicable to any of the standardized fields that you are looking to cleanse and should help provide a good starting point for your list creation.
As mentioned in the Chad’s Blog Posts you will need to create a field on the Prospect/Company record for both the “incorrect” and “correct” values.The same pair of Prospect/Company fields will be used across all the Contact fields that need to be standardized. There is no need to create more than a pair of Prospect/Company fields.
When you are uploading the lists of bad values I would strongly recommend, (enforce it if I could), that you upload each list into its own Prospect/Company group. This helps you have a natural way to segment out the Prospect/Company records from other records.
Uploading into a group becomes critical when creating a Match rule as you specifiy which group of Prospects/Companies contains the corresponding Incorrect/Correct record list.
Read the final Phase 3 blog coming out shortly and I would always recommend you accompany this information with our Eloqua University Resource Center.