By Francis Scudellari
The number one question I hear when dealing with clients who want to add new data to Salesforce is, “How can I make sure I’m not creating duplicate records?” There isn’t a perfect solution to the problem, but there is a pretty darn good one, and although it won’t cost you any money (assuming you’re a qualified non-profit), it requires a little investment of your time and brain power.
CRM Fusion’s Demand Tools is another extremely powerful third-party application, which includes a full suite of tools that will help you not only keep your data clean, but mostly duplicate free.
Like the Data Loader, it lives on your desktop. Unlike Data Loader, it stores both your login name and password, so you don’t have to retype your credentials whenever you start up the app. You can also point it at a Sandbox, but this isn’t as easy to set up as it is with the Data Loader.
When you Demand a full-blooded ETL Tool
What really separates Demand Tools from the Data Loader is that it’s a true ETL (Export, Transform, Load) app, not just an importer. In addition to the importer “MassEffect” (which sounds more to me like the name of a bad 90s boy band), there are modules to:
- Mass update fields, based on criteria, to a constant, expression, or the value of another field (called “MassImpact” — the boy band after its lead singer went solo?)
- Mass merge duplicate records (Be careful with this one if you’re merging Contacts using the Nonprofit Starter Pack’s one-to-one account model. It’s best to merge the owning accounts first, and then the Contacts, but there can still be issues getting all of the donations transferred with their Contact roles intact)
- Mass backup data from all the standard and custom objects
- Mass transfer ownership for all records or a subset based on criteria
- Mass convert Leads to Contacts (another one that doesn’t always play nice with the NPSP)
- Download specific fields and records to an Excel-like Power Grid based on criteria. Once the data is in the Grid, you can perform some pretty handy operations to clean up the values and then post them back to Salesforce.
Matchmaker, matchmaker make me a match
Far and away the module I use most often, however, and the inspiration for this post, is the less creatively but very accurately named “Find/Report IDs.” It’s a great way to compare people or organizations in a spreadsheet to your records in Salesforce and find out which, if any, already exist. How does it work?
The interface is a little clunky, and takes some getting used to, but the general flow is:
- Specify the file with the records you want to search (this can be an Excel spreadsheet, a CSV file, an Access database, or a couple of other not so common formats).
- Select the Salesforce object you want to compare them to, and the fields you want to display in the match report. (Note: You can only compare them to one object at a time. For example, if you want to check names against your Contacts and Leads, you’ll need to do this in two separate operations)
- Define your match steps and criteria
- Find, review and export the results
Demand Tools gives you a lot of flexibility in deciding how to search for matches. You can for example define matches on:
- First Name, Last Name and Email
- First Name, Last Name and Phone
- First Name, Last Name and Zip
- First Name… you get the point.
Better yet, you’re not just comparing these at face value. Demand Tools includes several “functions” that make the comparisons more intelligent. For example, the First Name function will check to see if one is a shortened or alternate version of the other (equating Chris to Christopher, Liz to Elizabeth, and Joe to Joey or Joseph). There are similar functions for Zip, Phone and Street Address.
In addition to these functions, you have 3 other pretty powerful match options:
- Allow a field to match if one of the records has a blank value
- Make it a “fuzzy” comparison that checks for phonetic similarities between words. You should use this one judiciously, since it can be a very loose match with some false positives.
- Transpose the word order, which is a good trick for catching variations in the way street addresses can be entered, for example
- Strip out punctuation marks and non-alphanumeric characters, which is a good option for checking Organization names
You can include multiple match steps in each operation, and of course multiple criteria per step. Once you’ve got all the comparisons setup, Demand Tools will pull down the data from the selected object and show you what matched and what didn’t.
The results listing indicates both how many steps a record matched on (if you included multiple), and how many unique records in Salesforce the record matched to (ideally this is only one, but if you have duplicates in your Salesforce data, or the match points are too broad, you’ll have to do some investigating to see which if any are accurate).
Exporting to import
Your last step is to export out the results. Ideally you’ll only want to write one match per record if you’re going to use the export files for your import, and DT gives you this option. As long as there’s one unique match per record, you won’t need to include the information from additional steps (if there are others).
You also have the option to export both the matches and non-matches to either an Excel spreadsheet or a CSV file. I’d choose the latter if you want to use the file(s) for your imports. The exported file will include all of the fields from the original spreadsheet, plus the fields from the Salesforce object that you selected to display in the first step of the process, plus the Salesforce IDs and details specific to how the record was matched.
Having the matches and non-matches in separate files works especially well if you’re going to use DT for your importing, since it doesn’t support upserts (combining inserts and updates in a single operation) like the Data Loader does.
You can download Demand Tools from the CRM Fusion website (http://www.crmfusion.com/demandtools/index.php). When you do, it will ask you if you’re a nonprofit organization, and if you check yes, you’ll immediately receive an email with instructions on how to apply for the free nonprofit license. The trial period for the software is very short (5 days), so you’ll want to submit your application to CRM Fusion asap. Please note that CRM Fusion no longer offers support for the free version, but it does have a pretty easy to follow Help file.