Skip navigation

Do It

3 Posts authored by: 1082817

The next step of the journey to completing your Data Cleansing Program is the Preparation phase. Probably the most time consuming phase of this process as you will need to create lists of values for both the “incorrect” values and their corresponding “correct” values for each field in your program.

The values for the incorrect fields can be supplemented with the information you gathered by analyzing your current data using reports (as seen in the Phase 1 Blog Post). Also there are two invaluable Blog posts on Topliners already by Chad Horenfeldt:

Normalizing the Title Field

Normalizing the State/Province Field

These posts are great for step by step information on how to setup individual fields. In the theme of this series I will not go into detailed steps for every area but instead try to provide accompanying information for your data cleansing program.



The preparation phase requires a bit of work but first ask yourself these questions before forging ahead:

  • What lists (one per field) do I need to build?
  • What fields do I need to create (Contact, Prospect or Company)?
  • Have I collected all the “incorrect” values I need to account for?


Below are some Tips/Tricks to help you answer these questions.


List Creation

Creating lists for your Data Cleansing program can be a arduous process if done from scratch but thankfully, (well thankful only in this case ••J), we already have a great source of “incorrect” values in our respective Eloqua databases.

If you export one of the reports we looked at in “Phase 1” called "Contact Field Values" for each field you need cleansed then you have a great starting point for your cleansing lists.

Field Value Sample.jpg


This example shows Country values in both Full name and Code format, but let’s say we wanted to make sure all our Country values were in Code format.

The first step would be to export this file so we can manipulate the data in Excel. In the top left corner of the reporting area you should see an Export dropdown which provides various formats, but I would recommend exporting to CSV.


Export Sample.jpg


The exported report when opened in Excel should appear as below. The highlighted yellow areas can be removed from the excel file:


CSV sample.jpg


Now remove any values that are actually formatted in the manner that is correct. In our example we would remove all the Country code values that are correct such as “TR”,”ES”,”DE”, et cetera.

The remaining values should be the “incorrect” values that need corrected. At this point, go ahead and rename the column header to a header name that makes more sense, such as “Incorrect Field Value”. Also add a second Header value called “Correct Field Value”.


At this point you will want to begin filling in the correct match for each incorrect value:


Country Sample.jpg

Once you have completed your list you will need to now upload these lists into Prospect/Company Groups. There should be one group for each list of values and each contact field that will be normalized.


This approach is applicable to any of the standardized fields that you are looking to cleanse and should help provide a good starting point for your list creation.


Field Creation

As mentioned in the Chad’s Blog Posts you will need to create a field on the Prospect/Company record for both the “incorrect” and “correct” values.The same pair of Prospect/Company fields will be used across all the Contact fields that need to be standardized. There is no need to create more than a pair of Prospect/Company fields.


When you are uploading the lists of bad values I would strongly recommend, (enforce it if I could), that you upload each list into its own Prospect/Company group. This helps you have a natural way to segment out the Prospect/Company records from other records.

Uploading into a group becomes critical when creating a Match rule as you specifiy which group of Prospects/Companies contains the corresponding Incorrect/Correct record list.


Read the final Phase 3 blog coming out shortly and I would always recommend you accompany this information with our Eloqua University Resource Center.

Data Quality is a constant thought in most marketer's minds -- or at least it should be. Recently Eloqua started compiling figures on the impact to Lead Quality for clients who leverage Data Validation and the figures are very promising. Clients who are leveraging Data Cleansing have seen a substantial increase (upwards of 3x) in the number of Leads who are considered Qualified.


While it’s great to talk about the benefits of Data Cleansing the hard question is how do you begin the process of analyzing, preparing and implementing a Data Cleansing program?


In this 3 part series we will go through those 3 phases (Analyze, Prepare and Implement) and provide you with guidance and knowledge to develop your own Data Cleansing process.







When looking at your data you should ask yourself some of the following questions:


  • What data is crucial to my business processes (Lead scoring, Lead Creation, Nurturing, etc)?
  • What fields do I want standardized? ex. title, country, etc.
  • Do my Eloqua standardized field values match the standardized values from my CRM?


Once you have answered these questions then you can begin using Eloqua reporting to investigate your data quality. Two of the reports I recommend as starting points are:




Contact Field Values




This report will display all the values in a specific field and the percentage of total contacts that have that value.

Contact Field Values.jpg


This data is invaluable for assessing whether there are values in your system that are not part of your standardized lists or irrelevant to this particular field.


There are also two variations of this report “by Contact Filter” and “by Contact Group” which allow you to segment your data if you wish to contain this analysis to a specific group of contacts instead of your whole database of contacts.



Contact Field Completeness


This report is useful for determining if your data is being populated at all within specific fields. This report functions best when you create a “Contact View” with the fields you need to report on. Once your contact view is created you can run this report and select the contact view you just created.

Contact Field Completeness.jpg

The percentage is a total against all the contacts in your database. In this screenshot above the Job Role field is only populated in 7.5% of all contacts and that can include bad data. If Job Role was integral as part of your Lead Scoring you can see how this could severely skew your Lead Scores/Ratings.


These two reports are a good start to begin the analysis of your data and selecting some of the fields you will need to incorporate into your Data Cleansing program.


In our next entry (Prepare) we will look at how to create the different components required for your Data cleansing program and also discuss data entry points.

Have you been enjoying Eloqua's Contact Filter options but want some ideas for incorporating them into your email marketing, beyond simple segmentation?
A great way to incorporate the filter options into your email campaigns is to use the concept of Email Frequency or as one of my client’s has coined it: “The Stop Light Program.” This allows you to address issues with over-saturating prospects/contacts with emails by automatically having them removed from email sends once they reach an appropriate number of email receptions.


The need to create a program is no longer required but the idea is still much the same: Create a series of Filters based on email frequency ranges that assign contacts a Red, Yellow or Green value. Based on the color assigned to the contact you can then implement these filters into various areas of the application to either block or allow email sends to these contacts.


Prerequisites for Filters


What you want to do is to decide beforehand what amount of email sends should classify a contact to be placed into the Red, Yellow or Green categories. You will need to input those values in your filters when you create them.There are valuable reports in Eloqua that will help you decide as an organization what are acceptable email ranges to assign to your different colors.


An example would be as follows:


  • Red – received over 10 emails in last month
  • Yellow – received between 4 and 9 emails in last month
  • Green – received between 1 and 3 emails in last month


A valuable report is the Email Frequency by Contact Group (which is also a great report to look at on your Contact Group Dashboards) which will show you all the contacts in that group and how many emails they received within a certain time range:



Creating Filters


To create a new filter click Communicate > Email Marketing > Contact tab and select the Contacts dropdown. Select "Create a New Contact Filter."


Once you are in the Contact Filter canvas you can see all your criteria options on the right hand side which can be dragged and configured on your canvas area.


The criteria we are interested in is the “Have Been Sent an E-Mail”:


Click and drag that criteria onto the canvas and a configuration window will pop up:


Each color will be its own filter and the email amounts can be altered at any point as your initial ranges may be re-thought.


Additional criteria such as Contact Data may be used to further segment out your contacts to target specific industries, companies or countries, i.e. adding a criteria to only see contacts that are part of a certain company:

Another good tactic is to incorporate other activity based criteria into the filter such as Opened Emails, Clicked and E-mail, etc. This allows for granular filtering and also then allows for more flexibility in how you leverage these new filters.


Leveraging the Filters


Now that you have these filters built you want to know how to implement them into your current process to possibly prevent “Red Stop Light” contacts from receiving any emails until they are Yellow or placing your “Green Stop Light” contacts into a nurturing campaign. Below are some suggestions that might help you decide how you are going to incorporate these filters into your system:


- Master Exclude List - If you are experiencing issues where you don’t want any contacts who have received over a certain amount of emails to be emailed at all until they are within Yellow or Green ranges then placing the “Red Stop Light” filter into the master exclude area would be an option. The master exclude list can be accessed by Customer Administrators under the Setup->Management ->System Management tab but be warned that any contacts added to this list are EXCLUDED from any email sends in your system.


- Default Distribution Lists– Another exclusion option is to add the “Red” filter into default distribution list’s excluded area which would mean any NEW distribution lists will automatically have that filter as an excluded asset. The Default distribution list configuration is again only accessible by Customer Administrators and can be found in the Setup->Management->User management tab and from there just click the User Defaults and Settings menu:


- Nurturing Campaigns – What to do with the Red and Yellow filters is generally obvious but there are possibilities for your Green filter where you can combine activity criteria (opens, clicks,etc) as mentioned earlier in the post to pull contacts into a lead nurturing program. This allows you to engage a target audience who you know is interested in your product/service but has not been bombarded with recent emails.


Hopefully this had shed some light on the power and flexibility of our new Contact Filters and encourages you to try and think outside the proverbial box.

Filter Blog

By date: By tag: