Two-Letter Country Code to Full Names in HubSpot [Hacks]
A recent post in the "MOPerations Nation" (a Whatsapp group for Marketing Ops professionals in Israel) suggested a solution to the problem of leads arriving with Two letter Country codes (ISO Alpha-2) instead of full Country names from Facebook ads to HubSpot.
The problem with Facebook Ads (lead generation forms) and HubSpot is unique - If you use a Country dropdown list on HubSpot that contains only full country names - two letter codes can't even be pushed to HubSpot, resulting in missing country data.
The solution suggested by one of the participants was to use Zapier to listen for a form submission from Facebook, locate the value of the two-letter country in a Google Sheet, and then return the matching value of the Full country from that Google Sheet, and push it to HubSpot after a Contact has already been created on HubSpot.
Automatic Translation of Value X to Value Y
First, let's discuss the general approach of converting two-letter codes to full names. There are two options as a general approach, but only one of them is valid for the use case above:
- Internally in HubSpot - you can use the capabilities of the Operations Hub programmable steps in HubSpot if this tier is available to you. This, however, does not apply to the above situation as the data itself is not available in HubSpot due to a field validation (the two-letter codes are not a part of the Dropdown list in HubSpot - thus they are rejected altogether). ❌
- Externally to HubSpot - Using an external tool such as Zapier/Integromat (Make) and similar tools is a great option as they are very good in easy manipulation of data (which, sadly, most marketing automation systems aren't) - and can capture the data in parallel from Facebook directly. ✅
Therefore, the approach suggested is right! 👍
The solution suggested in the group was to listen to a Form submission from Facebook, add a delay step to make sure the Contact is already available in HubSpot - and then take the value of the two-letter country from the Facebook submission, lookup that value in a Google Spreadsheet that contains both the two-letter countries and the full-name values - get the full name out and push it back to HubSpot.
This solution is great but can be improved - the Integration with google spreadsheet might limit performance (and the potential reliability of the Zap). Google Sheet is an external API to Zapier that has rate limits (which means that if many requests are made at the same time, not necessarily from this specific Zap, the API might not be available at the time of execution), it also takes more time to execute compared to Zapier internal steps - and it's not needed for a solution to this problem.
The use of Google Sheet in this scenario is a nicely-thought-out, no-code approach, to store a sort of an index or a table, where, each Two Letter code (IL, for example) is stored in its own column and is translated to the equivalent Full name (Israel, for example) which sits in a different column. The Google Sheets approach is effectively using an online spreadsheet as a database.
Suggested Alternatives 🦾
There are many ways to translate one value to another without using an external API to Zapier - in this blog-post I'll focus on the most straight forward one:
Using the Zapier Code step - and storing the country and values in a simple array. The code step will translate the two-letter code value to the full country name and return it to be used in the later Zapier steps. This represent the very-very-very-low-code approach. It is better than any external API, it is VERY easy to setup, it's also easy to understand for non-developers.
The use of Zapier Code step makes lots of sense:
* We only need to store a small amount of data (comparatively)
* This data does not change - we don't need to update it often, we just need to read it from time to time. Technically speaking, this means that the data is best served via an Array, and not an actual database which is a bit of an overkill for this purpose (either a database or spreadsheet that 'acts' as one).
Zapier supports both JavaScript and Python as languages for its code steps. In this example I'll use JavaScript and I'm more familiar with it, but it can be easily done in Python as well, of course.
We need very few lines of code to make this happen, as we have very little steps we need for this to work:
1. Take the inputData from the Zapier step (the Country) and pass it to a JavaScript variable.
2. Create a JavaScript array of all countries.
3. Fetch the array specific country based on the inputData two-letter country
4. Pass the result back to the Zapier step.
The full code for this approach is attached here:
var twoLetter = inputData.Country;
const countryList = {
"AF": "Afghanistan",
"AL": "Albania",
"DZ": "Algeria",
"AS": "American Samoa",
"AD": "Andorra",
"AO": "Angola",
"AI": "Anguilla",
"AQ": "Antarctica",
"AG": "Antigua and Barbuda",
"AR": "Argentina",
"AM": "Armenia",
"AW": "Aruba",
"AU": "Australia",
"AT": "Austria",
"AZ": "Azerbaijan",
"BS": "Bahamas (the)",
"BH": "Bahrain",
"BD": "Bangladesh",
"BB": "Barbados",
"BY": "Belarus",
"BE": "Belgium",
"BZ": "Belize",
"BJ": "Benin",
"BM": "Bermuda",
"BT": "Bhutan",
"BO": "Bolivia (Plurinational State of)",
"BQ": "Bonaire, Sint Eustatius and Saba",
"BA": "Bosnia and Herzegovina",
"BW": "Botswana",
"BV": "Bouvet Island",
"BR": "Brazil",
"IO": "British Indian Ocean Territory (the)",
"BN": "Brunei Darussalam",
"BG": "Bulgaria",
"BF": "Burkina Faso",
"BI": "Burundi",
"CV": "Cabo Verde",
"KH": "Cambodia",
"CM": "Cameroon",
"CA": "Canada",
"KY": "Cayman Islands (the)",
"CF": "Central African Republic (the)",
"TD": "Chad",
"CL": "Chile",
"CN": "China",
"CX": "Christmas Island",
"CC": "Cocos (Keeling) Islands (the)",
"CO": "Colombia",
"KM": "Comoros (the)",
"CD": "Congo (the Democratic Republic of the)",
"CG": "Congo (the)",
"CK": "Cook Islands (the)",
"CR": "Costa Rica",
"HR": "Croatia",
"CU": "Cuba",
"CW": "Curaçao",
"CY": "Cyprus",
"CZ": "Czechia",
"CI": "Côte d'Ivoire",
"DK": "Denmark",
"DJ": "Djibouti",
"DM": "Dominica",
"DO": "Dominican Republic (the)",
"EC": "Ecuador",
"EG": "Egypt",
"SV": "El Salvador",
"GQ": "Equatorial Guinea",
"ER": "Eritrea",
"EE": "Estonia",
"SZ": "Eswatini",
"ET": "Ethiopia",
"FK": "Falkland Islands (the) [Malvinas]",
"FO": "Faroe Islands (the)",
"FJ": "Fiji",
"FI": "Finland",
"FR": "France",
"GF": "French Guiana",
"PF": "French Polynesia",
"TF": "French Southern Territories (the)",
"GA": "Gabon",
"GM": "Gambia (the)",
"GE": "Georgia",
"DE": "Germany",
"GH": "Ghana",
"GI": "Gibraltar",
"GR": "Greece",
"GL": "Greenland",
"GD": "Grenada",
"GP": "Guadeloupe",
"GU": "Guam",
"GT": "Guatemala",
"GG": "Guernsey",
"GN": "Guinea",
"GW": "Guinea-Bissau",
"GY": "Guyana",
"HT": "Haiti",
"HM": "Heard Island and McDonald Islands",
"VA": "Holy See (the)",
"HN": "Honduras",
"HK": "Hong Kong",
"HU": "Hungary",
"IS": "Iceland",
"IN": "India",
"ID": "Indonesia",
"IR": "Iran (Islamic Republic of)",
"IQ": "Iraq",
"IE": "Ireland",
"IM": "Isle of Man",
"IL": "Israel",
"IT": "Italy",
"JM": "Jamaica",
"JP": "Japan",
"JE": "Jersey",
"JO": "Jordan",
"KZ": "Kazakhstan",
"KE": "Kenya",
"KI": "Kiribati",
"KP": "Korea (the Democratic People's Republic of)",
"KR": "Korea (the Republic of)",
"KW": "Kuwait",
"KG": "Kyrgyzstan",
"LA": "Lao People's Democratic Republic (the)",
"LV": "Latvia",
"LB": "Lebanon",
"LS": "Lesotho",
"LR": "Liberia",
"LY": "Libya",
"LI": "Liechtenstein",
"LT": "Lithuania",
"LU": "Luxembourg",
"MO": "Macao",
"MG": "Madagascar",
"MW": "Malawi",
"MY": "Malaysia",
"MV": "Maldives",
"ML": "Mali",
"MT": "Malta",
"MH": "Marshall Islands (the)",
"MQ": "Martinique",
"MR": "Mauritania",
"MU": "Mauritius",
"YT": "Mayotte",
"MX": "Mexico",
"FM": "Micronesia (Federated States of)",
"MD": "Moldova (the Republic of)",
"MC": "Monaco",
"MN": "Mongolia",
"ME": "Montenegro",
"MS": "Montserrat",
"MA": "Morocco",
"MZ": "Mozambique",
"MM": "Myanmar",
"NA": "Namibia",
"NR": "Nauru",
"NP": "Nepal",
"NL": "Netherlands (the)",
"NC": "New Caledonia",
"NZ": "New Zealand",
"NI": "Nicaragua",
"NE": "Niger (the)",
"NG": "Nigeria",
"NU": "Niue",
"NF": "Norfolk Island",
"MP": "Northern Mariana Islands (the)",
"NO": "Norway",
"OM": "Oman",
"PK": "Pakistan",
"PW": "Palau",
"PS": "Palestine, State of",
"PA": "Panama",
"PG": "Papua New Guinea",
"PY": "Paraguay",
"PE": "Peru",
"PH": "Philippines (the)",
"PN": "Pitcairn",
"PL": "Poland",
"PT": "Portugal",
"PR": "Puerto Rico",
"QA": "Qatar",
"MK": "Republic of North Macedonia",
"RO": "Romania",
"RU": "Russian Federation (the)",
"RW": "Rwanda",
"RE": "Réunion",
"BL": "Saint Barthélemy",
"SH": "Saint Helena, Ascension and Tristan da Cunha",
"KN": "Saint Kitts and Nevis",
"LC": "Saint Lucia",
"MF": "Saint Martin (French part)",
"PM": "Saint Pierre and Miquelon",
"VC": "Saint Vincent and the Grenadines",
"WS": "Samoa",
"SM": "San Marino",
"ST": "Sao Tome and Principe",
"SA": "Saudi Arabia",
"SN": "Senegal",
"RS": "Serbia",
"SC": "Seychelles",
"SL": "Sierra Leone",
"SG": "Singapore",
"SX": "Sint Maarten (Dutch part)",
"SK": "Slovakia",
"SI": "Slovenia",
"SB": "Solomon Islands",
"SO": "Somalia",
"ZA": "South Africa",
"GS": "South Georgia and the South Sandwich Islands",
"SS": "South Sudan",
"ES": "Spain",
"LK": "Sri Lanka",
"SD": "Sudan (the)",
"SR": "Suriname",
"SJ": "Svalbard and Jan Mayen",
"SE": "Sweden",
"CH": "Switzerland",
"SY": "Syrian Arab Republic",
"TW": "Taiwan",
"TJ": "Tajikistan",
"TZ": "Tanzania, United Republic of",
"TH": "Thailand",
"TL": "Timor-Leste",
"TG": "Togo",
"TK": "Tokelau",
"TO": "Tonga",
"TT": "Trinidad and Tobago",
"TN": "Tunisia",
"TR": "Turkey",
"TM": "Turkmenistan",
"TC": "Turks and Caicos Islands (the)",
"TV": "Tuvalu",
"UG": "Uganda",
"UA": "Ukraine",
"AE": "United Arab Emirates (the)",
"GB": "United Kingdom of Great Britain and Northern Ireland (the)",
"UM": "United States Minor Outlying Islands (the)",
"US": "United States of America (the)",
"UY": "Uruguay",
"UZ": "Uzbekistan",
"VU": "Vanuatu",
"VE": "Venezuela (Bolivarian Republic of)",
"VN": "Viet Nam",
"VG": "Virgin Islands (British)",
"VI": "Virgin Islands (U.S.)",
"WF": "Wallis and Futuna",
"EH": "Western Sahara",
"YE": "Yemen",
"ZM": "Zambia",
"ZW": "Zimbabwe",
"AX": "Åland Islands"
};
var Country = countryList[twoLetter];
output = {Country};By using this Zapier code step, we do not need to relay on any external system to Zapier to process the data, and translate the countries. This approach can also work perfectly for US Two-letter states to full state names - or ANY OTHER static pair of values you wish to automatically convert between one and the other.
But why bother?
Technically speaking, executing an automation the right way - will improve its reliability and performance.
The availability of no-code and low-code solutions allow us to create automated business processes without the need for developers. This is great - but with great power, comes great responsibility - Let's 'up' our game by learning some tricks from our fellow developers, and make our automations faster and more reliable.
Useful Resources
An array of two-letter US state values to full state names:
An array of two-letter Country codes to full Countries names - based on the default Salesforce country list:
Wish to easily create your own array from a an Excel table?
- Create a table with two columns:
2. Paste the table by pasting-as-transpose:
3. Copy the reformatted table into https://tableconvert.com/excel-to-json
4. Voila! TableConvert will generate a JSON for you - copy the lines between the curly brackets and paste them into one of the previous examples I've created.