Contact Washing Machines are a great tool that automates this process and continues to clean your database as new contacts or field values enter the system. It’s easy to use.
I will walk you through how to cleanse your Country data, but you can apply similar logic to any field within your database.
Normalize Country field with a Contact Washing Machine
First, you’ll need to export your entire database with the Country and State/Province fields included in your export view. Once the file is exported, use pivot tables to determine how many different variations of the Country value for United States are currently in your database. Repeat this step for the State/Province values. These “dirty” values will be needed when creating the Data Cleansing File.
Create a Data Cleansing File
The next step is to create a new CSV file that will contain all “dirty” values in one column and the normalized values in the next column over (same row). In the example provided below, the left column includes the numerous “dirty” values used for United States. The right column indicates the “normalized” value. We’re going to replace dirty (existing) values with the normalized (new) value. The same process will be used for any other field you want to clean in your database.
These values will be used to create the lookup table that the update rule will reference to clean the database.
An update rule is a data tool used for data cleansing and normalization of Contacts, Companies, Prospects, or Custom Objects. Here we will create an update rule to normalize a contact field (Country) based on a lookup table field.
A lookup table is simply a list of two columns of values, one column for values to perform the lookup on, and one for values that will be used to replace the original values.
Create a Lookup Table that will be used in the Update Rule:
- In Eloqua, go to Audience > Tools > Data Tools
- Click the Data Tools menu, and then click New Lookup Table.
- Enter an appropriate Display Name such as Normalize Country.
- Enter an appropriate description (optional).
- Enter a Lookup Value Column Name. This is the name of the column of field values that you are replacing, for example, Country.
- Enter a Replacement Value Column Name. This is the name of the column of field values that will replace the original field values, for example, Normalized Country.
- Click Save.
Add values to the lookup table manually by entering the lookup value, the replacement value, and then clicking Add. You may also upload entries from an existing file by going to Manage Entries > Upload Lookup Table Entries. A new Upload Wizard screen will open.
- Give the upload a name, for example Country Data Clean Up then click Next
- Select the file you want to upload and click Upload and Preview Data then click Next
- Make sure the Source field (which is your .xls headings) match your Target Field (Lookup Value Column Name and Replacement Value Column Name) then click Next
- Click Finish
- Click Save.
Create an update rule to use the created lookup table:
- In Eloqua, go to Contacts > Data Tools.
- Click the Data Tools menu, and then click New Update Rule Set.
- Enter an appropriate Update Rule Set Name, such as Normalize Contact Country.
- Select Contacts as the Entity Type.
- Click Add Update Rule.
- Click the Select a field to update field to open the Contact Field Search window, and then search for the contact field that you want to normalize.
- Select the field and then click OK.
- From the Select an update action list, select Overwrite value from Lookup Table Field.
- Make sure that the field that you are normalizing is selected in the Select a lookup field list.
Note: This will perform the lookup on a different field than it is replacing. This will retain the original field.
- Click the Select a lookup table field to open the Lookup Table Search window, and then search for your created lookup table.
- Select the lookup table and click OK.
- Select Overwrite if lookup value not found to set a static value if the lookup value is not found in the lookup table. We have left this unchecked.
- Click Apply and then click Save.
Now you’ve completed building all the pieces that will be needed to create the Program to automate this process. In the next blog post, I will walk you through creating the Program as well as testing and activating it.
Stephanie Pence is a Revenue Engineer for The Pedowitz Group. She has an Eloqua Master Certification along with 8 years experience in Sales, Marketing and PR.
- Posted by Stephanie Pence
- On 07/07/2016
- 5 Comments