Unable to Activate Matching Rule to Identify Duplicate Records in a Salesforce Organization

By default, in Salesforce the matching rule activation is blocked when the sum of all sets of 100+ identical match key values exceeds 2% of the number of total match keys.

For more about matching keys, please refer to Match Keys Used with Matching Rules.

Here is an example:

Say you have a matching rule on Account which matches only by Name. 

For this example, let's say you have 10,000 Accounts. Name has value "A" in 150 records, "B" in 300 records, and a completely unique value in each of the other 9550 records.

In this example:

• Activating the rule generates a match key value for each record, so there are 10,000 match keys.
• There are 450 (150 + 300) match keys in sets with 100+ duplicates
• 450 / 10,000 = 0.045, or 4.5%.

Result: The matching rule will fail to activate because the number of match keys with 100+ dupes is more than 2% of the number of total match keys.

For the rules with just the Exact Match as the matching scheme, we can use the following queries for an org where none of the fields are encrypted.

Another Example:

Filter Logic: (Account: NameExactMatchBlank = FALSE) and( (Account: PhoneExactMatchBlank = FALSE) or (Account: ShippingCityExactMatchBlank = FALSE))

Let's assume you have 10,000 Accounts.

Queries to Run to find the total records, this includes the records in the Recycle Bin.

SELECT Name, ShippingCity FROM Account WHERE Name != 'null' and ShippingCity != null GROUP BY Name, ShippingCity HAVING COUNT(Id) > 100 ORDER BY COUNT(Id) DESC LIMIT 10 

lets' say you get 200 record that have same name and Shipping City

SELECT Name, Phone FROM Account WHERE Name != 'null' and Phone != null GROUP BY Name, Phone HAVING COUNT(Id) > 100 ORDER BY COUNT(Id) DESC LIMIT 10 

lets' say you get 100 record that have same name and same Phone

Now find the total keys that can be generated:

SELECT Name, ShippingCity FROM Account WHERE Name != 'null' and ShippingCity != null

Lets' say this returns 5,000

SELECT Name, Phone FROM Account WHERE Name != 'null' and Phone != null

Lets' say this returns 7,000

Total keys = 5,000+7,000 = 12,000

Total Duplicates = 200 + 100 = 300

% of Duplicates = 300/12,000 = 2.5% so the Activation Fails.

Overall the subtraction has an insignificant affect on the reals data the number of duplicates generally are not on the border of 2% or 5% where the small fraction would matter so you can keep the calculation simple by just adding all the clause in the Matching Criteria.

Note: If the MatchBlank = TRUE for any of the fields of the filter logic then remove the respective " field != 'null' " check from the SOQL query since the null values will be considered in the Matching Key generation.

To activate the matching rule

In Salesforce Classic

Setup | Data Management | Duplicate Management | Matching Rule | (Select the matching rule name to Activate)

In Lightning Experience

Setup | Data | Duplicate Management | Matching Rule | (Select the matching rule name to Activate)

Option 1: Change the rule criteria to narrow the number of records with identical match key values. 

For instance, in the example above,‌ the accounts named "A" each represent the physical location of an office. They're not really 'duplicate'; they're just differentiated by a field other than Name, such as Site or Billing Zip Code. Thus adding Site or Billing Zip Code to the account matching rule would let the rule distinguish between these non-duplicate accounts which have the same Name.  This reduces the number of records with identical match key values, and can allow the rule to activate.


Option 2: Deduplicate existing data. 

For instance, in the example above,‌ all of the accounts named "B" really are duplicates, or test data. If those duplicates are removed, it reduces the number of records with identical match key values and can allow the rule to activate.

Note:

• If your org uses Person Accounts: Person Accounts have their own matching rules. Person Account records are not considered when activating matching rules for Account or Contact (and vice versa). When using queries like the one below to gauge the volume of potential duplicate Contacts or Accounts, use criteria to exclude Person Accounts from the query.
• One way to gauge how many records have the same value(s) on 100+ records is to use a SOQL query like the following:

SELECT Count(Id), Email FROM Lead GROUP BY Email HAVING COUNT(Id) > 100 ORDER BY COUNT(Id) DESC LIMIT 10 

Understand that this will only identify exact matches, so it may not catch all instances which would generate a duplicate matching key, particularly when using fuzzy matching (see the documentation linked above for information on how matching keys are generated). Even so, this type of query can help to identify many records, which will generate duplicate match keys.

Additionally, if the data is larger or the query to find duplicate sets is not working, it might be because the fields are not groupable. There is no other way to calculate the duplicate set, and you will have to work on them manually.  The query will not work. 

Option 3: If neither of the above is feasible, please contact Support to discuss a possible increase to your orgs "Identical Match Key Threshold". Please include a detailed description of the business case and reasons why options 1 and 2 above are not viable solutions. With a strong business case, Support can raise this limit from the 2% default to 5% (this is a hard limit). However, there are some considerations for this:

a) Understand that this does not remove the check for ineffective rules; It simply raises the percentage of duplicates match keys to total match keys which will prevent the activation. If rule activation continues to be blocked after this limit is raised, then option 1 or 2 must be used to reduce the number of records with identical match key values.

b) Duplicate Rules only return up to 100 possible matches when a record is saved; If there are over 100 existing duplicates, the user will not see all possible duplicates in the list given by the dupe rule. This is a reason the check for ineffective Matching Rules was designed to look at the % of records with 100+ duplicates. Raising the identical match key threshold may allow the rule to activate, but activating a rule when there is a greater percentage of records with 100+ duplicates also makes it more likely that users will encounter the scenario where some existing duplicates are not shown in the list.

For instance, in the example above raising the limit would allow the rule to activate, but if a User creates another Account named "A" they would see only 100 of the 150 possible matches in the list.

Note: Records in the recycle bin are considered for matching. If users are unable to activate the matching rule please empty the recycle bin and request Salesforce Support to run a Physical Delete job on the object.