Smart matching is the default and the only email correlation strategy shipped with CRM 4. Smart matching uses the subject of the current email along with the email recipients of the current email and generates subject hash and address recipient hash and uses these hashes to compare against old emails to find a match for the subject hash and at least HashMinAddressCount number of address recipients to correlate with an old email. HashMinAddressCount is reg key based and has a default value of 2. Smart Matching is not always the solution that customers are looking for.
How Smart matching works
In cases we have emails that have same subject and atleast 2 (HashMinAddressCount) recipients that match, we treat this as a correlation and set the same regarding object for the new email to the old email which may not be right.
An example can be a Service based CRM setup might have Service Details in the subject and this email is sent to customer service representative and the case is related to customer Bob and there could be a second email that is addressed to the same CSRs with the same subject line but belongs to Customer John Doe. In this case Smart matching wrongly ties these 2 emails together.
Another scenario could be a case where HR sends a New Hire subject line email to a Manager and each email could be related to a different new Hire but Smart Matching will go wrong in this case.
In all these cases, users will have to use caution and always generate unique email subjects to avoid these wrong correlation problems which cannot be that feasible and hence the reason for choosing Tracking Token strategy used in V3 where the token is automatically generated and appended to the subject and emails are now correlated based on the tracking token only.
How to use the tracking token-based solution
1. There is a trackingtoken column that is indexed in EmailBase table that is used for correlating new emails with old emails when using the Tracking Token strategy instead of the Smart Matching strategy. This column is not filled for all emails in CRM v4 as we use Smart matching.
To get this tracking token column populated for the past emails in the database, please use the tool that comes with hotfix KB# 969946 that uses SQL CLR to use the power of regular expressions which are lacking in SQL Server but are available and are used by the CRM Server code to extract the tracking token out of the subject of the past emails in an efficient manner.
For this tool to work correctly in case CRM Server and SQL Server box are different, please make sure to set up same directory structure where the tool got dropped on the CRM Server in the SQL Server box as well. The reason being , the DB Scripts in the Tool install the assembly EmailTrackingTokenExtractor in the SQL Server and creates a user defined function in SQL Server that links to the external assembly function and hence SQL Server looks for this assembly on the SQL Server box.
if CRM is 32bit, and SQL is 64bit you will need both the 32bit Hotfix as well as the 64bit Hotfix. The 32bit dll and the exe must be placed on the CRM server, and the 64bit dll must be placed in the same path but on the SQL server. We found that when SQL is 64bit and you attempt to execute a SQL CLR type from a 32bit assembly you will get an error.
This tool uses the same regular expressions that the CRM server code uses to extract the tokens and so it honors all the different tracking token prefixes and other tracking token settings set in the past.
Once you have the tool extract all the tracking tokens from the subject and populate the tracking token column in the email base you are ready to enable tracking token strategy.
The tool has to run at a time when there is no activity (to reduce the contention on Email table and also not have a flood of emails in between) and once the tool run is complete, the hot fix can be installed and smart matching can be disabled. There is a chance that you might have some emails in that small delta of time so you can re-run the tool again to cover these emails if needed.
Without the power of SQL CLR we would have been forced to retrieve all the emails from the database and process them through the regular expressions in C# layer and bulk update these rows back to the database.
With the power of SQL CLR we can run updates as simple as:
update email set trackingtoken = dbo.ExtractToken(subject)
where trackingtoken is null
and subject is not null
and subject <> ”
where dbo.ExtractToken is a User defined function that refers to the C# function EmailTrackingTokenExtractor.[EmailTrackingTokenExtractor.TrackingTokenExtractor].ExtractToken which is defined in the EmailTrackingTokenExtractor.dll and is now installed in SQL Server.
This eliminates all the round trips between the mid-tier and SQL Server and all the processing including the regular expression processing is happening on SQL Server itself.
2. Follow the instructions of Knowledge Base article KB 958084 and set the DisableSmartMatching registry key to 1 to turn off smart matching after installing KB 958084 hotfix.
For this hotfix to work, please make sure that this registry key DisableSmartMatching is set to 1 and the Use Tracking Token option is set in the Settings. This will direct CRM email tracking manager to correlate emails using the tracking token (CRM V3 strategy) instead of the default CRM v4 Smart matching strategy.
There is no impact to switch back to Smart matching as we still store the address hashes and the subject hashes.