Eloqua Data Cleanup Basics

Any Eloqua admin worth their salt knows the importance of clean, validated data when it comes to getting optimal results from the platform. Data management on any marketing automation platform is a lot easier and more accessible – as long as it’s done properly and with regular data cleansing.

Data cleansing is a common challenge for most marketers today, and it can impact every aspect of your job including segmentation, lead scoring, reporting, and building great campaigns.

How can you solve the issue of dirty data in an automated fashion without monopolizing your entire day?

The answer? Eloqua lookup tables and update rules!

These are great tools that automate data cleansing as new contacts or field values enter the system.

In this post, I’ll walk you through how to cleanse your Country data, but you can apply similar logic to almost any field within your database. Let’s get started!

Related: Get expert Eloqua consulting – our team’s ready!

How to normalize fields in your Eloqua instance

Normalize The Country Field

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 an Excel 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 several “dirty” values used for United States while the right column shows the “normalized” value. We’re going to replace dirty (existing) values with the normalized (new) value. You can use the same process for any other field you want to clean in your database.

In the step-by-step guide that follows, we’ll use these values to create a lookup table that stores the clean and dirty values as well as an update rule that will reference it in order to to replace the dirty values.

An update rule is a data tool used for data cleansing and normalization of Contact, Account, or Custom Object fields.

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:

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

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

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

7. Click Save (which, in case you have trouble finding it, is waaaaaay down at the bottom right of the screen).

Once you’ve saved your lookup table, you can add values to the lookup table manually by entering the lookup value and the replacement value, and then clicking Add.

You can also upload entries from an existing file by going to Manage Entries > Upload Lookup Table Entries. A new Upload Wizard screen will open.

8. Give the upload a name, for example Country Data Clean Up, then click Next.
9. Select the file you want to upload and click Upload and Preview Data then click Next.
10. Make sure the Source fields (which are your Excel headings) match your Target Fields, then click Next.

11. Click Finish.
12. Click Save.

CREATE AN UPDATE RULE TO USE THE CREATED LOOKUP TABLE:

1. In Eloqua, go to Audience > Tools > Data Tools as you did when creating your lookup table.
2. This time, choose New Update Rule Set from the Data Tools menu.

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

4. Select Contacts as the Entity Type.
5. Click Add Update Rule.

6. 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.
7. Select the field and then click OK.
8. From the Select an update action list, select Overwrite value from Lookup Table Field.
9. 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.
10. Click the Select a lookup table field to open the Lookup Table Search window, and then search for your created lookup table.

11. Select the lookup table and click OK.
12. 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.
13. Click Apply, and then click Save.

Now you’ve built all the pieces needed to create the program to automate this process. Let’s walk through how to use Program Canvas functionality to get it done!

Program Canvas

Program Canvas is the simple drag-and-drop functionality that allows you to transform data in real-time. With Program Canvas, you can build programs that automate cumbersome data cleansing tasks (It simplifies other Eloqua best practice functionality too!).

Previously, you would have used Program Builder to achieve these workflow automation goals. However, Program Canvas offers you the flexibility of Program Builder and some additional benefits:

• Improve your speed-to-lead time by using CRM cloud apps for integration instead of external calls.
• Maintain a cleaner contact database by easily integrating the Contact Washing Machine app with your programs.
• A new listener architecture that triggers program actions in real time based on lead score changes or new contact creation.
• Integration with the Oracle Marketing AppCloud so you can easily sync data from external systems with Oracle Eloqua.
• An improved user interface, making it easier to quickly build your programs.

Build an EloQua DATA CLEANSING Program with Program Canvas

1. Navigate to Orchestration, then click Programs.

2. For the purposes of this exercise, you’ll need to choose Create a Contact Program, but it’s important to note that you can also create Custom Object Programs as well.
3. If your organization has templates built, you can choose one of those or start with a blank contact program.
4. Give your program a name by double-clicking on Untitled Program at the top of the canvas.

5. The left pane shows the program steps available to you. By default, popular steps are shown, but if you’d like to view all the available steps, click the arrow next to the Program Steps heading.

6. Choose the type of contact entry point you’d like to use for your program and drag it onto the canvas. Listeners are great if you need immediate processing from a specific source like a form or a CDO, while segments are better if you want to pull in contacts based on specific criteria.

Note: You can’t add sources to a feeder from the canvas—you can only view them there. Also, if you choose a segment, know that they only re-evaluate once a day.

7. Add an Update Rule step. Once you’ve added this step to your canvas, double click on it to configure it by giving it a name (optional) and choosing the update rule from the dropdown menu.

8. Add a wait step at the end of the program for testing purposes. This is incredibly helpful for confirming that your program is working as expected and can be useful if you need to troubleshoot it down the line. Once you’ve confirmed the program is working, you can either remove the wait step or just shorten it.
9. After you’ve configured and connected all the required steps, you can click Save.
10. Activate your program.

Making the Complex Simple

Eloqua’s data tools offer you incredibly flexible ways to automate time-consuming stuff, and it’s handier than ever now with integration apps that connect to key platforms like Salesforce.

The first step to mastering it, however, is learning how to cleanse your existing data properly. The steps in this article should get you over that hurdle with no problem.

Want more? Check out our Eloqua getting started blog or see how our Eloqua consulting can help!