Duplicate records kill your Salesforce data quality and cause many issues once they go further into your org’s processes. Duplicate leads are the most common complaint, with new records being created from various entry sources (integrations and manual entry).
There is one bewilderingly simple way to check how many duplicates you have. ‘Show Unique Count’ allows you to count the number of unique records in a Salesforce report. While the best way to prevent duplicates is by activating Salesforce duplicate rules (but these rules are sometimes not perfect*), this reporting hack is a quick option to run a check.
How to Check Duplicates with Salesforce Reports
Use case: as a marketer, you consider any lead with the same email address a duplicate. You want to check how many leads have the same email address.
Below I have a Salesforce leads report. You can see that I have 9 leads, and 3 of these leads have the same email address. Although there are 9 leads, I only have 7 unique leads (because the 2 duplicates have been counted out).
Click ‘Edit’, then on the dropdown arrow on the column you are targeting (in this case, email), click ‘Show unique count’:
The unique count will appear at the bottom of the report:
Unique Count Tips & Considerations
As I said before, the best way to prevent duplicates is by activating Salesforce duplicate rules but it will enable you to run a quick check.
Cross-object matching
This hack only looks at the data you have in that report, at that time. You will be counting the unique record by object – which means you can’t take advantage of cross-object matching. You may want to do more sophisticated matching, for example, checking leads with leads and leads with contacts. Salesforce duplicate rules make cross-object matching possible.
Deliberate Duplication
This solution works well for small or mid-sized companies that have a relatively simple org data sharing model. There is something called ‘deliberate duplication’ where duplicates are allowed because they need to be processed as two separate records, for example, leads could belong to different business units. It’s quite common to have a scenario where brand A and brand B have completely separate lead lifecycles and restricted data sharing between the two brands. If ‘lead 1’ enquired for brand A and enquired for brand B, too, they would be duplicated to respect the way your org is partitioned.
Observant people may have spotted that I have two lead record types (‘Sponsor’ and ‘Author’). Imagine that someone could enquire as an author, and be handled by the author relations team. The same lead could also enquire as a sponsor, which would be handled by the sponsor relations team. Each team is not allowed to see the other’s data.
In this scenario, I would have needed to first filter my report by lead record type to show only ‘Sponsor’ or only ‘Author’. ‘Show unique count’ for ‘Author’ would have identified one duplicate, whereas there are no duplicate email addresses for ‘Sponsor’ leads.
This is why it’s important to understand how your org data is structured to take deliberate duplication into consideration.
*duplicate rules are sometimes not perfect. Sometimes, your definition/marketing’s definition of a duplicate is different from other stakeholders in the business, which makes it tricky to agree on a unified way to define the duplicate rules.