Country and State Standardization

[Anonymous]

A lot of our forms are free form fill outs and can cause states/countries etc. to be misspelled, abbreviated, etc.  I was wondering if someone had a list that they use to standardize these across?

[Anonymous]

Question for the group - how did you handle territories (British, US, etc.)? Did you essentially treat the list from Salesforce as the only data set, or did you omit some countries/remove because of them being territories? Any insight to territories or how you edited that Salesforce list would be helpful. Thanks!

[Casey_Grimes]

Hi Alayna,

In my experience this is very much a business process decision and how things specifically affect your company: to use a bland example, does it make more sense for your company to treat Puerto Rico as part of the United States or a standalone location? Depending on how you do billing, shipping, territory management and so forth, you're going to come to different answers here. It's a conversation that needs to involve people from several departments.

Generally, when having this conversation, if a company doesn't know much either way I'll start with countries.csv  |  Dataset Publishing Language  |  Google Developers as a list to work with. Alternately, I've used the list of Marketo's Inferred Countries and the UN.

Basically, only your company is going to know if you care whether the Åland Islands (not a country by Google, country by Marketo, not a country by the UN) should in fact be a valid country value.

[Jessica_Kao3]

I would also recommend removing embargo countries as well.  Something that is easy to overlook. 

[Kristin_Johnso2]

Hey All,

I began creating the batch and trigger campaigns to clean my data, however with 200+ countries the smart campaign continues to crash my instance. I put in a support ticket and their advice was not to have as many flow steps. How have you guys gotten around this? 

[Dan_Stevens_]

We had to break this up into multiple smart campaigns: APAC, Europe, North America and Other

[Kristin_Johnso2]

Thanks Dan! I figured this was going to be the case but was hoping to potentially get around that.

[Frank_Breen2]

Also you should think of running these as recurring batch campaigns if the data just needs standardized and isn't required before you can sync to your CRM.

[Kristin_Johnso2]

Unfortunately the way our SFDC instance is configured it will not sync unless spelled out. I wish I could have these campaigns as batch rather than trigger, but it doesn't look like it will be possible until our SFDC admin is willing to change things.

[Frank_Breen2]

I find in this case that I add wait steps to my actual program in the Flow, then that will allow Marketo to do the changes you have set then the sync will work.

[Anonymous]

We did something similar by broken out by alphabetical groupings.

[Frank_Breen2]

Is there are reason you use free form fields instead of a drop down? You can set this up easily in Marketo forms.

If you use Salesforce, they have a standard country list found here: Standard Countries for Address Picklists. If you go into Setup in Salesforce, you'll also see which countries have recognised states too for added value (United States, Canada, Australia, Brazil, China, India, Ireland, Italy and Mexico). Attached is a spreadsheet (2 tabs) I created from here to do my data normalisation and my dropdown creation, this is made up from the above link and the Salesforce State and Country Picklists section (you may need Admin access to see this or I use my Salesforce Developer version [Developer Info - Salesforce Developers | API Documentation, Developer Forums & More / Signup for free - Salesforce Developers​]).

Salesforce -> Setup - > Salesforce State and Country Picklists

[Bjorn_Erik_Ekst]

Thanks for the helpful Excel, @Frank_Breen2 ! 

[Grégoire_Miche2]

Hi Liz,

You could use this : ISO 3166-1 - Wikipedia, the free encyclopedia

Also, this is one topic where data management smart campaigns come very handy to fix erroneous entries.

-Greg

[Anonymous]

Curious how this would help if they are entered as leads and not associated with an ISO code and if they are misspelled ex: Utd Kingdom, etc. and thanks for your feedback!

[Nina_Valtcheva5]

Hi Liz,

Once you have a standardized list of countries & states, you can run cleanup smart campaigns to fix any leads that do not conform to the standard values.

Suggested steps:

1) Establish a standardized list of countries & states (Greg's ISO link is great)

2) Run smart list reports where Country NOT EQUALS standard values, export and map the variations to what the correct country value should be

3) Run clean up smart campaign fixing current leads who have the incorrect country values using Change Data Value flow step. It would look something like this:

4) Once you clean up the current leads in your database, you can set up a trigger campaign (just copy the batch campaign you used to fix the leads) to catch any newly created leads that come in with incorrect country & state values.

This can get tedious if you keep having to add more and more variations so the best way to ensure this is fixed going forward is to create country & state picklists for all of your forms.

[Grégoire_Miche2]

Data management campaigns will not solve your entire problem. Setting your forms with select fields instead of free entry ones will do on the long term.

But Data Management campaigns will help cleanse the data you have in the DB. You can start by replacing values you have in the db with correct ones, and continue to enhance your smart lists of bad data that will be corrected. You will not solve all of them, but you will reach quite quickly about 90% clean data.

-Greg