Implementing State & Country Picklists in Salesforce: A Tutorial

If you’ve read any of my blog posts before, you probably know I talk about data quality a lot. One of the biggest categories of data quality problems that I find (over and over again) is terrible state and country hygiene. In fact, it happens so often that I’ve now made it a habit to look at an org’s State and Country data quality whenever I get access to a new org. 

Most orgs have just open text fields for State and Country standard fields, which means there could be anything in there. Interestingly, in all my years of doing this, I have found data quality problems in every single org with text fields for State and Country. That’s right, even the orgs that assured me it was not a problem because there was a flow that normalized the data, or Marketo normalizing the data, etc. So, what is an admin to do? Fortunately, there is an out-of-the-box solution provided by Salesforce, and it’s totally free… State and Country Picklists.

What Are State and Country/Territory Picklists?

The State and Country picklists is a great out-of-the-box feature that is incredibly simple and useful in keeping your data clean in Salesforce. Some new orgs may have this turned on by default. If not, and you’re in a brand-new org, go ahead and enable State and Country picklists right away. You can tell if you have State and Country Picklists enabled already by editing the standard Address fields on any Standard Object in Salesforce.

This first image shows an org with State and Country picklists enabled, and the second shows an org without. Notice the first shows a dropdown option for both and real data is present. 

In this image, you can see it’s open text, and “Anystate” isn’t even a real State in the US. If you don’t have them enabled and you’re wondering if you need State and Country picklists, just run a Lead report grouped by Country, then State. If it looks something like this, you definitely need State and Country Picklists!

The image above? Not pretty. And this is probably not even the worst of it. If your Leads look like this, try running the same report on Account Billing or Shipping Address, Contacts Mailing Address, etc. They’re probably just as bad. 

Now, you might be wondering, “can’t I just turn on the picklist and it will fix all this? The process of enabling has a data migration feature, doesn’t it?” Well, you’d be right. Sort of. However, migrating acronyms alone isn’t going to help you with problems like this:

Because you are storing data as text, acronyms and typos are only part of the problem. The other part is drastically mis-matched States and Countries, numbers, or even random characters in these fields. 

Mapping will help you change the US to the United States, and the AB to Alberta – but you still have a couple of problems here. One, Alberta isn’t actually in the US at all, it’s in Canada. And two, which one of these is correct? Is the Lead in Alberta, Canada, or somewhere in the US? Which one of these is wrong?

And to top that off, mapping and migrating using the out-of-the-box tool doesn’t actually fix the text problems. It sort of masks the problem, which leaves your users very confused when they find records that look right, or are just spelled right, but are still mixed up. You may even wind up with error messages for your users.

This is what you’re left with when you migrate data using only the out-of-the-box migration for State and Country Picklists:

Or the dreaded, “there’s a problem with your State or Country, even though it might look correct” error message. That’s a poor user experience, especially since turning on the picklists was supposed to fix the bad data!

Step 1: Planning State and Country/Territory Picklists

So, now we know we have a ton of problems with the data, and we can’t just easily migrate it, even though it looks like we can. What to do now? Well, there’s good news and bad news. The good news is that you can fix this by just updating your existing records. The bad news is that you could potentially be doing this on a lot of records, and you’re going to be spending a lot of time in Excel and using the data loading tool of your choice.

OK, let’s start over. Before we even think about enabling State and Country Picklists, we should do a few things first. I’ll address these individually, since they’re all pretty important.

• Which States and Countries are we going to use?

Salesforce has an out-of-the-box list of Countries and States. You can easily edit this list in Salesforce, Activate or Deactivate values as you choose, or rename as needed. For example, in the US, “Washington D.C.” is referred to as “District of Columbia” on the standard list. For the purposes of this article, we’ll just use the standard list.

• How big is the database?

The only objects that State and Country Picklists impact are Standard Objects – so those are the ones that you need to worry about. Check how many Accounts, Contacts, Leads, Contracts, Orders, and Quotes you have. Don’t forget that Converted Leads also need to be updated, and you may want to consider emptying your org’s Recycle Bin before doing this. The data migration will impact all records, even converted or deleted records.

• Which fields will be impacted?

Only Standard Address fields on Standard Objects. This may change with the release for Address fields, but at the time of writing, take a look at Billing or Shipping State or Country fields on Standard Objects. If there’s any data at all in them, they will need to be cleaned up.

• Of those, which fields actually have data in them?

Run some reports on your standard objects and look for “State” or “Country” that are not blank. That will give you a rough idea.

• Which integrated systems will be impacted?

This is a big one. Integrated systems are often pushing data into Salesforce. If they push data in incorrectly, or in a format that doesn’t work for picklists, then you’re going to have a problem. Get familiar with your integrated systems that push State or Country data in, how they do so, and what format it comes in as. You may need to make adjustments to your mapping. (I’m looking directly at you, ZoomInfo, Seamless, Marketo!)

• How are we going to validate if the data we have is accurate, or do we need to validate it?

Depending on your role, this may be as simple as saying, “Admin doesn’t validate data. If there is a conflict (for example, ‘Alberta, United States’) then the Country field will be kept, State value will be deleted.”

Alternatively, you may decide, “Admin does not validate data. If there is a conflict, both values will be deleted.” Or even “Admin will research each record to find accurate location information.” You might also default to a tool like ZoomInfo as the source of truth for location information.

• What are we going to do with garbage data?

A good blanket statement will usually suffice here. “Any characters, unintelligible, or otherwise unusable data, such as “Country = a:F#(j;” will be deleted.”

• How will we back up our original data before changing?

There are a lot of ways to back up data – there are plenty of apps out there. You can export records to a CSV and store outside Salesforce. Again, there are a lot of options, so find something that works for you.

A note about paid solutions: yes, there are paid solutions out there that can help clean up this type of data, perhaps standardize it while keeping it text, etc. And if you have a large enough database, and your company has a budget and the cost is justified, then feel free to use one of the many great options.

However, many companies have no budget, or have database problems that are too large to fix individually, but still too small to justify the expense of another tool. This post is for those of you who have to clean up your database or implement State and Country Picklists without any paid solutions.

The TLDR directions to do this by hand are as follows: Export your records, including the ID column, Country, and State column. Fix all the ones that are bad. Import the CSV back into Salesforce, updating all the records (or use Inline Editing to update). Repeat on all objects. Enable State and Country picklists and done!

The option you choose will depend on how bad the data is, and how many records you have.

Option 1: Inline Editing on List Views

This example is a very small list as I’m already familiar with all these locations, so I don’t need to refer to a map or pre-existing list, so this is an easy one to use Inline Editing.

Most of us don’t have a database this small. But with a couple of formulas, we can get fancy with Excel and clean this all up. 

Option 2: Excel Cleanup

With a little creative copy and pasting from State and Country/Territory Picklists in the Setup Menu, and a few Excel formulas, you can create a nice little cheat sheet for yourself, a sort of ‘master list’ of every possible valid combination. If you use the standard list from Salesforce, there are actually 587 possible combinations. Download a copy of the out-of-the-box list here.

Here’s an example:

The records that are “Valid” do not need to be modified! They already perfectly match the out-of-the-box list. The records that say “#N/A” need to be fixed so they match. Column A is holding the Record ID so I can push the corrected data back into Salesforce. Columns B and C are where I’ve added the State and Country from Salesforce. Column D is a concatenate formula (If you haven’t already, check out my post on Excel tips for Salesforce Admins.) And Column E is a VLOOKUP formula to my pre-made list of 587 valid possible combinations. 

Using this, I can filter and only fix the ones that need to be fixed, with no questions or googling geography! And since you have that static list, you can use VLOOKUP to get the full State Name when all you have is the code.

One more note about paid apps, because I know I’m going to get a comment that says: “This would be so much easier with xyz app.” I know, I know! But some companies don’t have the budget for those. If you do, that’s great! If you don’t, then I hope this will be helpful to you. 

Enable State & Country Picklists

Once all your Standard Address fields are cleaned up, navigate back to Setup, and go to State and Country/Territory Picklists in the setup menu.

Items 1-4 will need to be completed before you can enable this feature. As long as your State and Country fields are cleaned up, this should be relatively painless.

Item 1: Configure States, Countries, and Territories

This is the section where you will choose the preferred spelling and States, Countries, and Territories that appear on the dropdown lists. For example, if your company prefers to use the term “America” instead of “United States”, you can set that during this step.

Click Save when you’re finished.

Item 2: Scan for State & Country/Territory Data

This is the easiest step in the whole process. Just click the button to scan your existing database and make yourself a cup of coffee! This will take a few minutes depending on how large your database is. Remember, all standard address fields are being scanned, even on objects you might never use.

Once you get the email saying the process is completed, you can proceed on to the next item.

Item 3: Convert Identified Data

This is the data conversion step I mentioned earlier, and the truth is that this step really only masks the poor data quality; it does not actually fix it. This is where your users get that weird error where the data looks right, but it’s really not.

On this screen, you can use the Change To picklist and convert these values. Just keep in mind that the preferred method is to clean them up directly on the record, rather than using this method.

Repeat this process for States, and then confirm your changes. Click Finish and Enable Picklists (this completes Step 4 in the setup process). 

Salesforce will take a few minutes to actually enable the picklists. You can navigate back to the main State and Country/Territory Picklist screen and refresh. When it is finished and enabled, the screen will look like this:

Considerations

Enabling these picklists will give you two new fields: State Code and Country Code, which will align with whatever State or Country is selected. These fields will be paired, so you can populate either, but you must populate them correctly. If you try to push a State Name into the State Code field, you’ll get an error (and vice versa).

As long as you populate one set in the pair, the other will populate as well. For example, if you enter “US” in the Country Code, you will automatically get “United States” in the Country field. 

Another consideration is that you will only be able to enter States or Territories if you enter the Country first. So, you could not enter California without also entering the United States. This tends to not be a problem with users in the UI as it’s essentially a dependent picklist, so there’s no room for error. Where this can become a problem is integrations like ZoomInfo, Seamless, Outreach.io, Marketo, and HubSpot, etc. If an external system tries to push in a State, but not a Country, it will fail and generate an error. 

Finally, don’t forget to check any internal automation you have created directly in Salesforce. Lead or Case Assignment Rules, Territories, validation rules, etc. You may or may not need to make any updates depending on how your org is set up.

Summary

I know this seems like a lot, and can be overwhelming at first. But I promise you that once your State and Country picklists are in place, you’ll never have typos, misalignment, fake values (Westeros, really people?) ever again.

State and Country picklists make your geodata immaculate without creating a bunch of validation rule errors for your users. This is one of my all-time favorite features of Salesforce, so I’m hoping it will be yours too. Please let me know if you have any questions in the comments below!