Why should you care about External IDs?

So, I've been studying for an integration certification (and also because I just love studying) and something keeps coming up: External IDs.

What is an External ID?

External IDs are custom fields in Salesforce that hold unique values that correspond to unique record data.

What else should you know about External IDs?

• Each object can have up to seven External ID fields.
• External IDs should (usually) be unique
• External IDs are indexed; meaning queries against them will run a bit faster (yay, large data volumes)
• External IDs are indexed and thus searchable

So, why should you care?

External IDs make your life easier, when planned correctly. Instead of pulling everything into a VLOOKUP, and aligning records to the source systems via record details or Salesforce IDs, you can use External IDs to ensure you have an ID that can be referenced both inside and outside of Salesforce.

The key to this statement is "planned correctly".

In my recent past, an admin had decided that the Salesforce record IDs could be used as External IDs with integrations to other systems, and as unique keys in tools like data warehouses. While this is a direct, relatively simple decision to make - lots of us have made it! - it's not always scalable. Why, you ask? There are a few reasons, but ultimately we'd choose to work from a net new External ID to preserve uniqueness (no other field will contain this data - reducing database redundancy) and minimize end user and admin confusion.

Here are a few questions to consider when choosing an External ID.


Unique? 

• When planning an External ID, it may shock you to learn that they aren't automatically required to be unique in Salesforce. If you intend to use this ID for integrations or data manipulations (or as an External ID, lol), my recommendation is to always mark the field unique. This will preserve your data integrity (and your admin's sanity!). 

Where do we find out about the majority of this-type-of-record? 

• Determining the system of truth (master data management strategy) is really just an exercise of determining where you find out about a record, who is finding out about it, and how much you know when that happens. So, if you learn about customers when they sign up on your website, your system of truth for an External ID would likely be the marketing system that collects their information as soon as they complete the web form on your website. Your marketing team likely has access to this information, and you know whatever you've made required on the web form.
• If you learn about customers while they're on the line with a support agent, your system of truth for an External ID would likely be your Service Cloud (or -puke- your Zendesk or Dynamics). Your service team likely has access to this information, and you know whatever fields you've made required in the tool. 
• This means the system of truth isn't always going to be Sales Cloud. It's very likely not Sales Cloud. Ensure that wherever the External ID is created, that it's a completely unique identifier, not just a Marketing Cloud ID or Zendesk ID. It also means your organization as a whole may not have access to the External IDs until that information is integrated across the system landscape. With External IDs, I like to combine key record attributes to build something unique, but meaningful. On that note...

Who, if anyone, is reading this ID, and when are they reading it?

• One of the cool things about External IDs is that they're indexed. Since they're indexed, you can run reports on them much faster. If you build meaningful External IDs, one or many teams can use them to extract valuable information without pulling crazy reports. Think about common use cases where a significant portion of the workforce continually looks at several fields on individual records as they're doing their job. 
• When is the External ID needing to be accessed? To prevent duplicates, Salesforce recommends planning integrations and record access to surface relevant data as soon as it's uncovered, granting visibility to those who need it, while building a 360 degree view of the customer... This includes integrating and surfacing External IDs!
• Maybe our users need to confirm record create date, lead source, lead last name, and city of origin. As an admin, I might grab a report for them to review - include things like created date, lead source, and city into different columns... But, what if all of this data was visible in a single field? An External ID field with formatted like MMDDYY-LeadSourceCode-LastName-City-{000}? 
• Maybe they need to confirm a UPC, delivery center, and shipment received date? An External ID field with format like MMDDYY-UPC-DeliveryCenterCode-{000} may do the trick. 
• This doesn't mean we'll create reports with just our External ID fields. HOWEVER...
• If you have a few use cases where 40% or more of your total workforce is looking at several common fields on a record to make a decision, and those fields are populated during or near new record creation, consider using those fields to create a meaningful External ID. 

External ID = Salesforce ID? 15 character or 18 character?

• Well, we all know the heartache of using 15 character record IDs, right? (The 15 character IDs are case sensitive and therefore are not unique enough to use in formulas within many tools such as Excel...) We know they're a pain because we're admins and developers. Have you ever had to help a user through the 15 character saga? What about a Sales Manager who has gone rogue in reporting? 
• If you are using Salesforce IDs as the record key for other systems, you will need to explain that the IDs on reports are not the same as the IDs you plan to use (because you will be using the 18 character ID). 
• Though you can train to the confusion, an alternative could be to avoid it. 😅

External ID = Salesforce ID? More than 1 Salesforce instance? 

• Larger organizations, especially global organizations, may experience added stress if the Salesforce ID is the unique identifier because there's likely a need for multiple Salesforce instances. It can be done (with additional logic to prevent incorrect record ID becoming the unique External ID), but it will require training. 
• Specifically upon sharing to other salesforce instances, using a record ID attribute is tricky. Salesforce to Salesforce will sync on record details, but the ID will not change. Storing multiple Salesforce IDs on a record isn't ideal for your users (or admins) - it's confusing at best.
• Though you can pad data migration exercises with ample time to double check your ID situation and train to the confusion it may cause, an alternative could be to avoid it. 😅

Ok, I get External IDs but I'm still not convinced I need to use them.

Let's take a look at why you really need an External ID: time consuming data management.

Example: My ancient Frankenmonster system has patient records with ID sformatted like Z001X. In Salesforce, patients are stored as contacts, with contact IDs formatted like 0033000000GvZjL. 

Let's say I wanted to pull all records from Frankenmonster with last name "Smith" and add them to a campaign in Salesforce. If the record doesn't exist in Salesforce, I want to add the record to Salesforce and then add it to the campaign.

As an admin, I will likely have a multi-step process to update the data. 

1. Pull the information from Salesforce via DataLoader
2. Pull the information from Frankenmonster 
3. Determine matching criteria with VLOOKUPS in Excel, to prevent creating duplicates
4. Update the Frankenmonster file with correct Salesforce IDs
5. Create separate file for any records that are in Frankenmonster but not in Salesforce yet
6. Run Update from DataLoader on the existing Salesforce records
7. Insert brand new records in Salesforce via DataLoader

Oh, our Sales Manager wants customer data from our legacy VampireSys tool to be brought into Salesforce and added to this campaign, too?

No problem. I'll just pull the same information again, and repeat the steps above with slightly different matching criteria since VampireSys doesn't have the same kind of data that Frankenmonster does. Not painful at all.

Sound familiar? If I had to guess, I've spent at least 500 hours of my life marrying data from legacy systems with data in Salesforce. The worst part is that it is NOT 100% accurate, and using record attributes to create uniqueness always results with some (maybe insignificant, maybe not) amount of fallout...

If we don't have a Master Data Management solution (that link sends y'all to a 34 page textbook excerpt on MDM principles - good stuff to bring up at your next IT offsite) and we find out about the customer further downstream than our system-of-truth marketing tool, it's not a problem. We'll just make sure our Salesforce field level security doesn't let anyone modify that field on any new records created in Salesforce. Our integration with the marketing tool can stamp our customer's records as soon as we realize they have unique data that doesn't already exist in the marketing tool.

If I use one unifying External ID per record, I can simply include the External ID in my process and conduct an upsert. So, my steps are drastically reduced.

1. Pull the information (including External IDs) from Salesforce
2. Pull the information from Frankenmonster
3. Match Frankenmonster and Salesforce records using the External IDs 
4. Run an Upsert in DataLoader using the External IDs

Upserts are a combo of insert and update. What an upsert does (in a nutshell; this is a little over simplified) is update records that exist, and insert records that don't exist. It does this using an ID. In our case, it will use the External ID when we prompt it to... Check out this Dreamforce presentation about time saving to be had with upserts using External IDs, shoutout to Doug Ayers!

Oh, our Sales Manager wants data from space to be brought into Salesforce too? So long as space has an External ID, no problem! I won't have to match against anything. Data analysis is a thing of the past!

In conclusion, defining defining a single, unique, meaningful, unifying External ID will set you up for time-saving, complexity-reducing success.