Skip navigation

The working scenario is that we want to update the values we have in the Country contact field. Besides that the standard list is over 200 entries, we have hundreds of non-standard values across a few million contacts.

The first step would be to go to Settings/Picklists and create a picklist with the standard country values. You can add entry by entry, though you should be making a 2 columns excel file and upload. In the upper right corner there is the upload the values.

Second step is to obtain a full listing of the non-standard values and map them to correct values. The format should be an excel, left column being non standard values, and the right column being their mapping to a correct value. In order to get the list of non-standard values you can go to Setting/Field and Views, select your contact field, and click View field population details in the lower left corner

Another way to obtain the values is a Contact Field Values report from Insight.

Now, there is a bit of manual work here in creating the mapping file. You should assign a correct value for all non-standard values thus obtaining an excel mapping file.


Navigate to Contacts/Data Tools. In the upper left corner pick Data Tools/New Lookup Table. Create the table and in the upper right corner you have a Manage Entries button and upload the mapping table. It should look like below.


You can also add manual entries to this table.


Now create a new update rule made to run based on the Update Table you just made. See below.

Now in order to do the actual cleansing, create a program builder to run the update rule against the desired audience.

In the first step add the contacts with either a filter or feeder. The second step is running the update rule set. The last step is an exit.That's all you need, though the above setup checks for values not covered by the Update Tables and adds them to a wait (later on I am manually adding new values in the LookUp Table and circle the contacts back to the top).


Note: for the field you want to cleanse, you could make an eloqua picklist with the standard values. Then create a compare contact fields filter, asking for the contacts having Country field values NOT in that picklist nor blanks. And that's your audience to cleanse.



In order to send the same email at different hours on different timezones we can follow the next steps:

- create picklists with countries corresponding to each timeframe you want to address (let's say EMEA, AMS, APJ)

-  create shared filters for each timeframe you made country assignments for (criteria is Country in list and you select your picklist);


We assume that country field is populated for the canvas routing to happen correctly. Also, if it is not, let one timeframe to be last in the routing and play the role of a default timeframe.

For the canvas you can follow the below setup:

The same email is selected in all email boxes. Each has the scheduling set corresponding to the timeframe. The last email corresponds to the last timeframe and we also leave it as a default for the ones that don't fit any category (country not in list, misspelled, or missing).

You can set up your timeframes anyway you want, to contain what countries you want. In short, you can scale.


Filter Blog

By date: By tag: