How to normalize fields with an Eloqua Contact Washing Machine

How to normalize fields with an Eloqua Contact Washing Machine

Dirty data can cause turmoil in your Eloqua instance. Data cleansing is a common pain point for most marketers today. This impacts every aspect of your job including segmentation, lead scoring and reporting. How do we solve the issue of dirty data in an automated fashion without monopolizing your entire day?

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.

Example of existing values and new values

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.

Eloqua Guide Download

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:

  1. In Eloqua, go to Audience > Tools > Data Tools

Creating a Lookup Table: Audience > Tools > Data Tools

  1. Click the Data Tools menu, and then click New Lookup Table.

Under Data Tools enter the Lookup Table details

  1. Enter an appropriate Display Name such as Normalize Country.
  2. Enter an appropriate description (optional).
  3. Enter a Lookup Value Column Name. This is the name of the column of field values that you are replacing, for example, Country.
  4. 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.
  5. 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.

Upload Lookup Table Entries

  1. Give the upload a name, for example Country Data Clean Up then click Next
  2. Select the file you want to upload and click Upload and Preview Data then click Next
  3. 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
  4. Click Finish
  5. Click Save.

Create an update rule to use the created lookup table:

  1. In Eloqua, go to Contacts > Data Tools.
  2. Click the Data Tools menu, and then click New Update Rule Set.

New Update Rule Set under Data Tools

  1. Enter an appropriate Update Rule Set Name, such as Normalize Contact Country.

Enter an appropriate Update Rule Set Name

  1. Select Contacts as the Entity Type.
  2. Click Add Update Rule.
  3. 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.
  4. Select the field and then click OK.
  5. From the Select an update action list, select Overwrite value from Lookup Table Field.
  6. 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.
  7. Click the Select a lookup table field to open the Lookup Table Search window, and then search for your created lookup table.
  8. Select the lookup table and click OK.
  9. 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.
  10. 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.

Practical Guide to Getting Started with Eloqua

Practical Guide to Getting Started with Eloqua

Getting started with Eloqua can feel a bit overwhelming. There’s so much to learn and so many things to do with this robust tool that it can be hard to know where to start first. We’ve made the task less daunting with our Practical Guide which will help you with information about Tactical Knowledge, Strategic Planning, Campaigns, Quick Wins, and Educational Resources.

About Stephanie Pence
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.

Related Resources

  • Posted by Stephanie Pence
  • On 07/07/2016
Tags: Eloqua, data cleansing, contact washing machine, dirty data, database remediation


Hi! How would you update State or Country field with the new Contact Washing machine? We have a lookup table from the previous Eloqua deployment but it is not available in the list of lookup tables in Cloud Action configuration. Thank you!
Stephanie Pence
Hi Maria, Thanks for reaching out. As of right now, the new Contact Washing Machine App does not allow the use of existing lookup tables. Today, there are only 3 lookup tables that are available to use. I would recommend using program builder or program canvas and lookup tables to accomplish this cleansing. Thanks!
Harald Stoll
Hi Stephanie, I followed along and now have a working cleaner set up for language, country, gender, title. If anyone reads this, look for the Eloqua App called 'washing machine', it has some of the functions described above. Also, use the 'program' flow to automatically check for bad values in your db using segments, which then feed into these update rules. Pretty nifty! Thanks
Stephanie Pence
Hi Harald, thanks so much for the note. I'm glad this was helpful for you. That's a great point about using segments to automatically feed into your program to keep your database as clean as possible. I'm so glad you mentioned the Contact Washing Machine App, I just posted a blog about that this week. Feel free to check it out here: .
Hello! Brilliant post, it helps to answer some common, but easy questions for pros. Did you publish the next part of the blog? I am interested to see your approach for the program.# Thanks! Mihai

Leave Reply

Your email address will not be published. Required fields are marked *