This article offers tips for identifying and resolving hidden data-quality issues when implementing Microsoft Dynamics CRM or Dynamics 365 CE.
Originally published in H1 2018 D365UG/CRMUG Magazine
Data quality is critical to the success of any CRM implementation. Some data quality issues can be obvious, but some can be causing you problems while hidden from sight. After performing many data cleansing jobs for Microsoft Dynamics CRM Users, the data quality analysts of Data8 share some of their top tips below:
Duplicates
Duplicated records can be, at the same time, the most obvious and hidden data quality problems. For the User managing contact data on a day-to-day basis, exact duplicates of contacts in an account really stand out. After all, they’ve got to pick which version they record activities against!
For marketing, business intelligence, and other Users who take a higher-level view, the problem can be more hidden. When looking at reports of the total number of contacts, contacts with permission to be contacted, contacts who responded to a campaign, etc., the duplicate problem is hidden, and very misleading statistics can be presented.
Even for the hands-on Users, some duplicates aren’t so obvious. A lead can be a duplicate of a contact, and since you can’t see them both together, they might not ever notice without appropriate rules in place. Contacts can be duplicated with different names, nicknames, and other details which also makes them harder to spot.
With the impending introduction of General Data Protection Regulation (GDPR), getting your duplicates sorted is a more pressing concern than ever. Should you be including that contact in your marketing campaign if one version says you can contact them and other one doesn’t?
Data Held in Hidden Fields
Entities such as Lead and Contact are provided in CRM with more than 200 fields, only a fraction of which are normally displayed to a User. So what happens to the data in those other fields?
Just because a User can’t see them doesn’t mean they’re not in use. Other systems such as the App for Outlook, Server-Side Synchronization, and third-party tools such as Scribe can all access and update the data stored in these fields.
This can lead to having data you don’t even realize you have. We commonly see this with fields suchas “Email Address 3”. Because you can’t see this data, you can have integrations doing things that aren’t immediately obvious because they’re working off more data than you have. The data stored in these fields can also go out of date, with you having no practical way to update it.
You can use tools such as XrmToolBox (the Attribute Usage Inspector plug-in) to see how populated each field in your system is and find any that regularly have data that shouldn’t and investigate why.
Also bear in mind that just because a field isn’t on a form, this doesn’t mean that sufficiently advanced Users can’t access or even update the data, so don’t use this as an alternative to field-level security!
Data Held in Old Fields that Can Be Deleted
Often a new project can involve creating new entities or fields in CRM. However, when the project ends, you can now be left with these fields in your system that over time, no one can quite remember what they were for in the first place. Depending on how clearly documented your fields are in CRM, this additional data can be misleading, especially to new Users.
Since the project has finished, no one will now be keeping this data updated, which adds to the potential for confusion.
If you don’t need it, get rid of it!
Copied Data
We have seen several instances of data being de-normalized, e.g. an “Account Type” field being added to the Contact entity, and workflows being used to copy the value from the Account record to each Contact in it.
While de-normalization can have its place in data warehousing, this is rarely the answer in a CRM system. The copied data can become outdated without very careful attention being paid to the workflows or plug-ins which are updating it.
If it is necessary to have data from one record available on another, use the Calculated Fields feature available in Microsoft Dynamics CRM 2016 or later to ensure it is always up-to-date rather than taking an actual copy of the data. Otherwise, just remove the copy and train Users on how to use related entities in Advanced Find to find what they need.
Changing Validation Rules
Validation rules can change over time, leaving data in the system that is no longer considered valid.
Data can also be entered in a number of different ways. If validation rules are implemented using client-side business rules or JavaScript, invalid data will still be allowed through via integration mechanisms.
Consider implementing validation rules as server-side business rules, plug-ins or workflows, and when rules change, consider what should happen to any existing data which is now invalid.
Bear in mind that reducing the maximum length of a field or removing allowable values in an Option Set field does not affect any existing data, so you’ll have data which is now too long for the field or is not a known item in your option set. Both can cause unpredictable problems, especially if you have a lot of integrations.
Using Inactive or Merged Records
When using Advanced Find, it’s easy to forget the filter on Status = Active. This can lead to disqualified leads or inactive contacts being included in marketing campaigns. If you’ve merged records, then the deactivated duplicate records can also be included.
Even worse, if your campaign automation software then updates the original records, then those disqualified leads may be reactivated or duplicates created, and merged records will be unmerged.
Make sure your marketing Users are trained to always filter on Status = Active, and wherever possible, ensure that your marketing automation software will automatically ignore any inactive records that have been included in your lists by accident.