Data is a four letter word, but it doesn’t have to be a dirty one. Clean and complete data means trustworthy data, and trustworthy data is what will ensure user adoption at your organization.
Importing and maintaining clean data can feel like a daunting chore, especially when you’re just getting up and running in Salesforce, but there are a number of tools to lighten your load. These range from the basic import wizards and merge tools that are built into Salesforce, to a number of more powerful applications. And the really good news is, all of it, even the third-party programs, are free to Nonprofits, so there’s no added cost to making sure you avoid the heavy price of dirty data.
Best Practices: Start by Improving the quality of your data files
Whether you need to load data records into Salesforce for the first time, or you want to improve the quality of the data that’s already in your system, or you need to augment that data with new information, there are some best practices you should follow before running any imports.
No tool will perform its functions well if the file you’re importing with it contains sloppy or incomplete data. Your first step should always be to do some basic level of cleanup on the data files you plan to import. This can include:
- Put First Names and Last Names in separate fields. Unless your constituents are pop singers and Brazilian soccer players, they probably have at least two names, and you should load them that way. Last Name should be 100% populated.
- Keep the data in your columns consistent. As the Radiohead song goes, everything in its proper place. Don’t put Emails in phone fields, or Spouse names in Middle Name fields, or URLs in Email fields.
- Make sure all values are in the proper format. This is especially important for Email fields, Date fields, and Number fields. Salesforce will not allow a record to be imported with a partial date or a badly formed email address.
- Fix any inconsistencies in the way Organization names are listed. Acronyms can be fun, and save on typing, but it’s usually better to spell out the full name. Setting guidelines for punctuation is also helpful. This will allow you to avoid duplicates.
- Include all required fields. They’re “required” for a reason. If it’s marked with a red bar on the Salesforce page layouts, put it in your file, and make sure there’s a value for every record.
- Try to catch duplicates before you bring them into the system, not after.
- Use the Salesforce field names for your column headings. All the import tools require some level of field mapping, and this will make your mapping task much easier.
- Include an identifier for the “Record Owner” if you want to assign the data to a specific user.
There are also some advanced techniques you can use in Excel to help you manipulate the values there, including functions such as Concatenate, Trim, Substitute and Vlookup. The Microsoft Office site has a full list of all Excel’s functions and how to use them. My colleagues Trish Perkins and Thomas Taylor will also be writing posts over the next couple weeks with some handy Excel tricks, so keep an eye out for those.
See to the CRM too
In addition to making sure everything is in order in your data files, you’ll also need to do a little prep work in Salesforce before you import:
- Be a good match maker.There should be a matching fields in the CRM for every data column you want to import. Also make sure the data types of your Salesforce fields are appropriate to the values you’re importing. For example, if you’re importing Boolean values (TRUE/FALSE), the target Salesforce field should be a checkbox. If you’re importing Yes/No values, you’ll probably want to set up a picklist.
- Complete those lists. If you’re importing into a single-select or a multi-select picklist, make sure all of the possible values are included in the field’s definition. For the multi-select fields, also make sure that in your data file you’ve used a semi-colon as a delimiter between values.
- Don’t get caught short. Depending on the length of your values, you may need to adjust the length of Text fields, or set up Text Areas instead.
The wizards can perform some simple magic
For the less-experienced user, or for one with not very complicated import needs, the built-in wizards can handle most of the work. There are separate wizards for importing Accounts (Organizations) & Contacts, Leads, and Custom Objects (these are the non-standard objects that you, a consultant, or a third-party app developer has added to the Salesforce data model). To find the wizards, go to Setup>Administration Setup>Data Management.
The main advantage to using these wizards is they’re pretty user-friendly. They’re also native to Salesforce, so nothing needs to be downloaded or installed. The wizards do require that the data files be in CSV (Comma-Separated Values) format, which you can export from Excel and most other spreadsheet and database programs.
If you are doing regular imports of Contacts and Leads, the wizard can perform a basic level of matching on name or email address, so you won’t have to worry about creating duplicates (assuming you have relatively complete data in both the import file and in Salesforce).
Another nice feature of the wizards, is they allow you to specify whether or not to trigger workflow rules when the data is imported.
The wizards do have their limitations though. One big disadvantage is that a wizard doesn’t exist for every object. For example, there is no wizard to import data into the Opportunities/Donations object. You’re also limited to importing less than 50,000 records in one file. Finally, you won’t be able to use them to perform an upsert operation (adding and updating records from a single file) using an External ID.
Get ready for something a little more advanced
Do you have more advanced needs? Do you want to get your hands dirty (but not your data), with some more complex database operations? Don’t worry, the wizards are just the beginning. In my next post, I’ll focus on a set of more powerful tools, such as the Salesforce Data Loader, Apsona, CRM Fusion’s Demand Tools suite, and Jitterbit. They’ll allow you to perform operations such as mass record and field updates, mass deletions, exports, and record merges. Each app has its strengths, and I’ll outline some scenarios where you’ll want to use each of them.
In the meantime, you can familiarize yourself with the basic Salesforce database structure, which will help give you a clearer picture of how these tools work, and how they need to be used. For example, when importing new data, you’ll need to add information to each object in a specific order, depending on how they relate to each other. For adding data to “Child Objects,” those database tables that are connected to another object in the UI via a lookup field (and show up on the “Parent Object” as a related list), you’ll have to include an identifier that references their Parent.
M. Sridhar, from Apsona, has prepared a very helpful Entity Relationship Diagram (ERD) that illustrates the Objects and Relationships at the heart of the Nonprofit Starter Pack, and he’s graciously allowed me to share that with you.
There are a number of other good resources out there to help you get up to speed on the Salesforce data model. Salesforce Help & Training is a great place to start. To learn more about the Nonprofit Starter Pack you can go to the Salesforce Foundation site. Salesforce has also produced an extremely helpful six-part video series predictably called How to Import Data into Salesforce.
