Reporting on Duplicate Records

As admins we are responsible for maintaining the Salesforce Org and keeping it configured in an optimal manner. We are also tasked with dealing with sometimes less than enthusiastically diligent users. One area where this is common is the creation of duplicate records. In order to keep a pulse on the level of duplication as well as the mitigation of these unwanted records we can use reports to surface the problem. There are four areas that we’ll need to touch upon in order to do so.

1) Matching Rules
Matching rules determine the criteria used to identify potentially duplicate records. These rules utilize matching keys and equations in making the determination. The key combines data value for the referenced fields to pre-identify possible duplicates before the matching equation is applied. The equation then adds criteria logic, such as OR/AND, along with whether an exact match is required or should fuzzy logic prevail. There are standard rules that come out of the box for Accounts, Contacts and Leads. You can also create your own customized versions.

2) Duplicate Rules
Duplicate rules determine what happens when a matching rule identifies a potential duplicate. Within the rule you have the option to allow or prevent the creation of the duplicate, as well as providing a warning message to the user. For our conversation related to reporting, the most important part of the duplicate rule is to make sure that you checked the ‘report’ box on both create and edit. Without this there is now way to run duplicate reports based on the rules. Standard duplicate rules for Accounts, Contacts and Leads come out of the box. You can also create your own customized rules.

3) Custom Report Types
Once we’ve established or matching and duplicate rules and made sure that they have been activated our next step will be to create a series of custom report types to allow us to be able to monitor the level of duplication. These report types can be used to make any adjustments needed to our rules in order to improve their level of success and our data quality as a whole. There a a few Objects that we need to include in our report types based on what we want to examine:

Duplicate Record Set – This shows which duplicate rule added a record to the report. It also provides a count of how many records have been flagged by this rule

Duplicate Record Item – This ties a specific record, such as an Account to a Duplicate Record Set

You can create your custom report type in one of two ways. First you can select the Duplicate Record Set as Object A and the Duplicate Record Item as Object B. In this case you’d want All ‘A’ records with at least one related ‘B’ record. This is a general report that will give you some high level insight as to the duplicate rule and its usage. It does not however, allow you to pull in fields from the Object/record that was flagged as a duplicate. Another option is to select your record/Object as the ‘A’ Object, such as Account and the ‘B’ Object would then be the Duplicate record Item. Again you would want to select the setting to return only ‘A’ records with at least one related ‘B’ record. Since we can add fields to the report type layout via Lookups we will still be able to see the Duplicate Record Set as well as the specific rule. A good practice when using this method is to pull in whatever Account fields are being used in your matching rules to analyze for any needed tweaks.

4) Reports
Our final step is to create reports based on the custom report types that we created in step 3. These will be the basis on our monitoring of our rules and data hygiene related to duplicates. Grouping these by the duplicate set can silo out the different rules to see which ones are firing most often. To see who the most common culprit is you could also group by user based on created by or last modified by. These reports should be reviewed on a consistent basis and can be added to a dashboard to track things in one place. They should be part of every admin’s data governance program.

Take a look at this short demo.