How to Dedupe Your SugarCRM Accounts Module
It’s time for some CRM spring cleaning
Performing a full cleanup of the Accounts module in your CRM is like organizing your garage or purging that one overfilled closet in your house; if you’re a CRM admin, it has likely been on your to-do list for a while. You know it will vastly improve your environment once it is done, but the job can seem too daunting to even begin to tackle.
For most clients on SugarCRM, the Accounts module (sometimes called “Companies” or “Clients” module) is king. It’s the parent or grandparent for all other records and the backbone on which your data gets organized. Removing duplicates also requires merging all the child records linked to those Accounts to ensure no important data gets lost in the process. Chances are if you’ve had your CRM in use for a while or if multiple users have had access to create Account records, you may find that even the best efforts of standardization and user training can lead to outdated or duplicate records.
Looking for more CRM tips? During the first quarter of the year, many organizations find themselves evaluating their sales team, tools, and targets. Here are three ways to get more mileage out of your system:
While there are some third-party tools out there to help you identify duplicates in Sugar, as long as you have a good understanding of your data, some basic MS Excel skills, and few hours to dedicate to the task, you can perform a pretty successful Account cleanup using the following steps without any additional tools.
Step 1: Create a Report that Exports only Relevant Account Fields
You’ll have to do a full export of your Accounts module, but don’t panic! You’ll only need to pull the fields that will help you identify the uniqueness of the record. This will vary based on your business, but typically the Name, Street Address, Phone Number, Email, and/or Website fields can be a good place to start. Add these fields in a rows and columns report and make sure to include the ID field in your report (this will be important later).
Step 2: Do Some Excel Magic
Now that you have the important fields in an Excel sheet, you can start to identify the possible duplicates. Create a new column called “Possible Duplicate” in your sheet and put a “1” in that column for every row that might be a duplicate (make sure to mark both/all rows that look like duplicates for a given Account, not just the one you want to delete). This is how we’re going to identify the records once we’re back in Sugar.
Identifying the right records can be a bit more art than science, but I would start by highlighting one column at a time and using the conditional formatting highlight rules to identify exact duplicates in each column. Then you can filter the column to only look at the highlighted cells, sorting alphabetically by one column at a time.
Remember, it’s possible that a company name could have changed due to a merger or a rebrand, but if it still has the same street address or phone number you should still be able to identify the duplicate if you go through all the columns for duplicates.
You should also do some data cleanup here to try to encourage more matches. For the Account Name column, use Find and Replace tool to remove words like “The,” “Corp,” “LLC,” and “Inc,” as well as any punctuation like periods, commas, dashes, etc. (This will make entries like “The Acme Company, Inc.” and “Acme Company” match, for example.) In the phone number column, remove all spaces, dots, parentheses, and dashes. In the street address column, remove words like “Street,” “Avenue,” and “Boulevard” as well as “St.,” “Ave.,” and “Blvd.”
Note: You will not be reimporting any of these values back into Sugar, so you don’t have to worry about accuracy here, you’re just trying to match as many as possible.
Again, do NOT remove any records/rows at this point. If you find duplicates you want to mark them both with a 1 in the “Possible Duplicate” column since you do not yet know which of the two records in Sugar may have child contact/opportunity records that you’ll want to preserve and merge.
Once you have a “1” in your Possible Duplicate column for as many records as you can identify, then remove all your other filters and filter only the rows where you have a 1 in the added column. Copy the ID column and the Possible Duplicates column only to a new sheet and save as a .CSV file. In other words, you should end up with a file that has 2 columns only — ID and Possible Duplicate — where ALL rows in the Possible Duplicate column have the value of 1.
Step 3: Create a Checkbox Field in Sugar
In Studio, create a new field in the Accounts module:
- Data type: Checkbox
- Field Name: possible_duplicate
- Display Name: Possible Duplicate.
Make sure that “Allow Imports” is set to “Yes.” All other settings you can leave as is.
You do not have to add this field to the record or list view layout, but make sure you add it to “Default” column in the Search layout.
Step 4: Reimport Your Records
Using the Import Wizard, import your .CSV file back into Sugar and make sure to select “Create new records and update existing records.” As long as you have that ID column and nothing has been deleted in Sugar between step 1 and step 4, you should not actually be creating any new records with this import, only updating existing ones.
It’s possible that your CRM is configured to require additional fields for import (i.e. name), meaning you’ll get an error message when you try to only map the two columns. The easiest thing to do here is to temporarily switch those fields from import “Required” to “Yes” in Studio (making a note of which ones you modified) and then flip them back to required after this import. Otherwise, you’re going to have to make sure that all those values come in from your initial report and you will NOT be able to do any data cleanup in Excel unless you either do it in a fresh column, or you’re ok with those cleansed values ending up back in Sugar (i.e. the removal of “Inc.,” “The,” etc. in your Company names).
Step 5: Merge and Purge Records
Now that you have your records identified in Sugar, it’s time to merge and purge! This step will be the most time consuming; depending on how many records you identified in Step 3, it should probably be broken up into multiple sittings.
Create a filter that will look at only the records with the “Possible Duplicate” field checked. Then you can start going through the list using Sugar’s List View Merge Tool to identify the Primary record, grab any relevant field data from the non-primary record and Merge all related records to the Primary. Make sure to add any fields that you were using for duplicate identification to the List View before starting this step (you likely do not have Billing Street Address as a column in your list view, for example). Sorting by Account Name A-Z is a good way to start, but keep in mind you may also want to sort by Phone Number or Street Address, too, since it’s possible an Account could have two records with names that do not line up Alphabetically (i.e. “The Acme Group” and “Acme” Group or when working with a merger or rebrand). Again, commit to going through a certain set each day (“Today I will merge all Accounts that start with the letters D and E!”) and you’ll get through them faster than you think.
Want more data cleanup techniques or tips on encouraging best practices of the standardization of your data?
Contact our Highland CRM team at sugarCRM@highlandsolutions.com — we’d be happy to help you with your CRM cleanup project!
