We have a great tool that I had my programmer write years ago. It's called Duplicate Remover Expert for Act. We sell a lot of this nifty product. I had a client call me recently, told me they loved the program, but were very frustrated that they always seemed to have duplicates. He also wanted some tips on finding those duplicates that the normal internal process didn't detect.
After giving him my answers, I realized they needed to be in a blog post and this is that article.
First off, there will always be duplicates. Always. This is for a variety of reasons. Number one reason - human. People will not search the database first, and even if there are rules to detect a possible duplicate, people tend to not ask how to correctly spell a name, so they enter it with a slightly different spelling. Or, they add a suffix, like Jr. You get the idea.
Another reason for duplicates is machine-created. Like Act, there are lots of CRMs that interface with Google or Outlook. Again, if a name is slightly misspelled or duplicate management is turned off, as soon as you set up the integration between the two systems, you get duplicates. Lots of them.
Importing data will often lead to duplicates when duplicate checking is turned off, or again, there are slight differences in the names or other fields.
The reasons stated above are why I say there will always be duplicates, even if only one person uses the database.
Now that we have that resolved, this is where my duplicate remover tool comes into play. It allows you to search on up to 6 fields (I'll explain where that helps a bit later), search for names that "sound" the same (ie Bob, Bobby, Pat, Patricia), and leave out fields that are blank (like Company in a financial planner database).
The reason we set up the app to search on up to 6 fields is because data is different. Some databases, like mentioned earlier, have no company fields. Some may have fields like counties. There may be account number fields brought in from external systems. The idea is to try to narrow down as many duplicates as possible. The more fields you search, the deeper you dig down into the data to identify duplicates.
Now I'll show you some tips on finding as many duplicates as possible. People's names can be a variety of things, including middle initials, suffixes, etc. One trick is to search for First Name and Last Name instead of Contact. Many systems, like Act, are very anal in what they consider a duplicate. John Smith and John Smith, III are not a duplicate. Nor is Patricia Egen and Patricia R. Egen. That's why the First Name, Last Name gets around that particular situation.
Another trick is to search by email only. This will not find all the possible dups because many organizations will use the same email for every person in that company, but it is a way to isolate potential duplicates.
The next trick is to search by Last name, Email and Phone. This one catches the situation where the first name is different and causes the program to think it's not a duplicate. An example of this is Robert and Bob. Our program uses something called Soundex which only looks at the first letter so it misses the Robert and Bob situation. Searching on Last Name with other fields gets around that.
The biggest trick, though, is to run multiple passes against the data using all the tricks noted above. You are trying to find as many duplicates as possible that are "hiding in plain sight."
One of the things I like about our tool is I can run a search using examples noted above and save them to a group in Act. Then, you can go back into the Group, look for obvious non-duplicates, remove them from the group (while keeping our tool open), go back to the duplicate remover program and then dedup that group. I find this very useful.
I hope you find these tips helpful. In summary, make sure you check the database first before entering a new contact - do searches by last name, email or phone, not just the contact name. When integrating with external contact systems, try to make sure they are current and don't have duplicates themselves. Otherwise, your contacts start replicating like rabbits.
Happy deduping.
Want to learn more about our duplicate remover for Act? Check it out here: Duplicate Remover Wizard Expert