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.

Demand Tools

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:

  1. 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).
  2. 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)
  3. Define your match steps and criteria
  4. 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.

DT matching

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.

DT Results

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.

Owning it

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.

Francis Scudellari

Francis Scudellari

One response to “Data Migration: DemandTools

  1. I have to say, Demand Tools is by far the best money for the value I’ve ever gotten from software. We are a for profit company (so the charge is a few K per year).

    Our company has gone through significant restructuring on the operations and financial sides and the ability to do complex administration with things like Mass Effect and Mass Impact has saved – no joke – weeks of time(i.e. writing changes to field values based on formulas or other field values on related objects, extracting data and using it to load child or related records, etc).

    When a new Sales territory map comes out, or Finance needs a more structured approach to sales bookings data or project managers want to implement some new process (breaking out ‘subprojects’) – I breathe a little easier knowing I have this tool.

    It does require an investment in learning the tool and exploring its capabilities – especially around writing more sophisticated matching rules for finding duplicates – but its a life saver. It’s even allowed us to make some one off reports where we need data from extended object relationships that salesforce Reports just couldn’t provide. I’m interested to try out some of the other approaches you blog about though, no single tool does everything well. Thanks for your posts!

Leave a Reply

Your email address will not be published. Required fields are marked *