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.
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.
Aaron Crear View All
Aaron is Founder & Principal at Hat-Trick Consulting. He works with companies around the world to help them achieve their Salesforce goals through administration, development and training services. A former sales director, Mr. Crear has extensive functional and technical expertise translating business requirements to technical solutions. Aaron currently holds eight Salesforce certifications including Salesforce Certified Data Architect, Sharing & Visibility Architect, Sales Cloud Consultant, Service Cloud Consultant, Community Cloud Consultant, Platform App Builder, User Experience Designer, Advanced Administrator and Administrator.
He is also the leader of the Lowell, MA Admins Community Group and is a co-organizer of Northeast Dreamin’. Mr. Crear is a frequent speaker, having presented at Dreamforce, Big Sky Dreamin’, Czech Dreamin’, dreamOle’, Florida Dreamin', French Touch Dreamin’, London's Calling, Midwest Dreamin’, North Africa Dreamin', Phillyforce, Snowforce, Southeast Dreamin’, True North Dreamin, YearLeadin’and Salesforce World Tours.
Is it possible to report over duplicates if I chose to block the creation and modification of such duplicate records instead of just giving a warning? In this case I can choose to report the attempt but if I totally block creation/edit of duplicates, I don’t have this option anymore. Which kind of makes sense since no duplicates should be created. But in real systems, there will be duplicates coming from its legacy. And actually it’s possible for Salesforce to determine those records, since you can see them in the Related List under Potential Duplicates (given that they match your Matching Criteria). Do you have any clue if this is possible and if yes, how?
Thank you! This was very helpful
What is you have a duplicate rule that pulls matches Contacts to Leads, how would you build a report to show both the Lead & Contacts in the associated record set?
Sorry for the delay on this. You’d have to use two separate reports for that, one for each object. The dupe set itself would identify person.