Every good marketer hates having bad data in their Marketing Automation Platform. It can be increasingly difficult to manage data quality, when there is a globally shared instance of Eloqua and a variety of data sources. One of our hurdles was cleaning and keeping clean data. To effectively address this problem, we had create an Eloqua program that had the capability of performing the following:
- Check country assignment and normalize data
- Check zip codes and normalize data
- Assign Regional ID, Territory and Branch based on zip codes
- Update State or Province from zip code
The benchmark for success is that we reduce the number of contacts that either contain incorrect or missing data in the fields noted above. Since contacts can be uploaded into Eloqua or brought in from different data sources, we needed to create the program to correct, or add in missing data. If you will be implementing a program like the example below, please make sure you audit your data and account for the different data sources prior to creating the program. This will be critical to help you identify the data format in the "Compare Contact" steps in the program below.
The program below contains the following steps to address our challenge.
Segment: Create a feeder that pulls in the contacts you would like the program to evaluate. In this example, we pulled in any contact that could be in the United States or Canada. You can separate these programs regionally.
Compare Contact Step: Contacts who have a “Country” that is equal to “United States”. Since this is our preferred format, if yes, send to Has 5 digit zip code. No, routes to Compare Contact step to check for Zip Code.
Compare Contact Step: US with Space? Evaluates whether the data contains a space. Yes, send to Fix Country Rule Set. Send back to United States? Step. No, Send to Canadian Check. (As shown in Part #2).
Compare Contact Step: Has 5 digit zip code? This step checks for a wildcard pattern for 5 digit format. If yes, send to the Update Rules where we use the zip code to Zip/Postal to assign APR/Territory/Branch. If no, send to Compare Contact Step: Has 5+4 Zip Code.
Compare Contact Step: Has 5+4 Zip Code? This step checks to see if the record has full zip code of 5 + 4. If yes, send to the Obtain 5 digit zip Contact Washing Machine. This is necessary to normalize the data. Send back to the Update Rules where we use the zip code to Zip/Postal to assign APR/Territory/Branch. If no, send to Compare Contact Step: Has 9 Zip Code.
Compare Contact Step: Has 9 Zip Code? This step checks to see if the record has full zip code with 9 characters. If yes, send to the Obtain 5 digit zip Contact Washing Machine. This is necessary to standardize the data. Send back to the Update Rules where we use the zip code to Zip/Postal to assign APR/Territory/Branch. If no, send to Wait Step.
Wait Step: This step is necessary to wait for 1 day and then push contact to the Compare Contact Step: United States.
Compare Contact Step: Has State? This step checks to make sure the state field is not blank. If yes, send to Wait Step. If no, send to the Update Rules Step and update the contact with the US Zip Code to State based on Zip Code.
Compare Contact Step: Compare Contact Field. This step checks to see if the contacts “Last Modified Date” is within the last 4 hours. If yes, the contact will be sent back to the first Compare Contact Step to make sure that we re-check all of the fields we checked in the previous steps. If no, send back to previous wait step.
We then took a similar approach to build out the Canadian portion of the program.
There are a few Oracle Marketing Cloud Academy courses that have directly influenced this campaign. Those are: B2B Data Cleansing and Program Builder Overview.
This program has drastically improved the quality of our data and is easy to test and build. From a business perspective, this has allowed us to correctly route contact data to sales staff. In addition, this program has allowed us to consider using field merges from the correct contact data. Lastly, this has greatly impacted our ability to segment correctly. We have been able to pull in contacts regionally, to deliver regionally relevant content. Overall, I would recommend this program as an easy way to standardize your data in Eloqua.