An update rule is a data tool used for data cleansing and normalization of Contacts, Companies, Prospects, or Custom Objects. In this example, 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.

 

For example:

Existing values

New values

United States

USA

U.S.

USA

U.S.A.

USA


First, we will create a Lookup Table that will be used in the Update Rule.

  1. In Eloqua, go to Contacts > 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. Check or clear the Values are case-sensitive field. In this example, we will leave this cleared.
    • Note: This setting can only be changed when the table is empty.
  8. Click Save.
  9. 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.
  10. Click Save.

You may end up with a lookup table that looks similar to this:

norm country.png

 

Next, we will 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.
  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 the same field that it is replacing. You may want to take a less destructive approach by updating to a new field, such as Updated Country, to retain the original field values.
  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.
    • Select Overwrite if lookup value not found to set a static value if the lookup value is not found in the lookup table. In this example, we will leave this unchecked.
    • Select Make this rule Conditional to make the update based on the conditions of a certain field, for example, if the Title is equal to CEO. In this example, we will leave this unchecked.
  12. Click Apply and then click Save.
    • Note: An update rule set may contain one or more Update Rules which are run in top to bottom order; you may change the ordering by dragging and dropping Update Rules in the Update Rules list.


Once you have created an Update Rule set, you can run it on a shared list:

  1. In Eloqua, go to Contacts > Shared Library.
  2. Click Shared Lists.
  3. Open the shared list that you want to run the update rule on.
  4. Click gear menu and select Run Update Rule.
  5. Select the Update Rule from the list and then click Continue.
  6. Change the email address if necessary, and then click Continue. A notification will be sent by email when the update is complete.

 

In a future article, we will discuss how to use an update rule in Program Builder.