Aaaah, New England...

Sometimes the world makes things easy, sometimes it doesn't.

The wise creators of Excel thought that numbers starting with zero didn't make much sense - 00345 is really the same as 345. They had a point. But, they forgot New England. There, zip codes tend to start with zeros. So, whenever they end up in an Excel spreadsheet, the leading zero gets truncated. And, we all know that marketing lists end up in spreadsheets a lot.

So, on the Digital Body Language blog, I talked about the Contact Washing Machine, and mentioned this (http://digitalbodylanguage.blogspot.com/2008/12/contact-washing-machine.html). Let's look at fixing it. It's not an exercise for the faint of heart, but it explores a few concepts and approaches that you might find useful in a few data management scenarios.

First, you probably want to build a decision rule to make sure you only look at addresses where

this is an issue, namely states that are in New England. Create a Decision Rule that looks at whether the contact flowing through the Program is in a Contact Filter. For that Contact Filter, create a list-based filter that looks at the state field to see if it is in affected New England states (CT, MA, ME, NH, NJ, RI, VT specifically).

If you find this, the next step is where you look for truncated zip codes and do an update. This is done with a reference table that contains all the New England zip codes in both truncated and corrected form. Create a "fake" set of Companies that has a "Zip Fix" field and a "Zip" field for each possible truncation.

Don't worry, give your Eloqua CSM a call if you want the list of zip codes. No need to re-invent that.


Once you have that list uploaded, as a group of fake companies called "Zip Fix", you then need to match your contacts to the invalid zip codes and update them with corrected zip codes if found.

Step one is a match rule. You're matching contacts (that are going through the contact washing machine program) with companies (that are in our group of Zip Fix fake companies). Do an exact match to the truncated zip code field.

Program Builder can automatically run both the match rule (to find the match) and the handler (what happens when you find a match). Set up the handler to update the contact's zip code with the correct zip code from our group of companies.

That's it, you've fixed the truncated leading zero problem with New England zip codes. I did promise it wasn't for the faint of heart, but once this is set up in your data normalization Program, it runs automatically on all your contacts and you end up with clean data should you need it.

The concept of this though is very useful in many circumstances; match your data against a reference list and update a field from the reference list when you find it. A bit like a VLookup in Excel. Once you've mastered that, you're well on your way to becoming an Eloqua Artisan...