Support Portal ContactGet in touch

Correcting Duplicate Data in Dynamics

   Words by Paul McQuillan

   on 20/02/2019 10:00:00

Duplicate data in any CRM system can make a single view of the customer difficult and crucially hinder user adoption.

In a way, duplicate data fits a similar fashion as the ‘broken window’ theory of policing, small doses are often unavoidable but letting the problem pile up can result in the failure of the project.

Fortunately Dynamics 365 gives us some simple powerful tools to find and eliminate duplicates and stay on top of the problem.

Data Management - Our first port of call is visiting the Data Management area of Dynamics to look at our rules for identifying duplicate data.

image

Duplicate Detection Rules - within this area of Settings, we can navigate into the Duplicate Detection Rules area.

image

Here we have a list of the rules for how Dynamics will compare Record A to other Records in the system – with each rule defining a criteria for the Entity Type of Record A.

Initially this is best to focus on a single Entity at a time to look at what possible records we want to search against to find a potential duplicate.

In the example below, we focus on four rules we have on the Account Entity to build our list of rules for which records might be a duplicate that we want to review, cleanse or merge.

image

We can build each Rule to define what Dynamics will think is a potential duplicate to our Account Record A:

image

The rule itself here can match our Source Entity Type to any other Entity Type within Dynamics – and then use the list of Criteria to determine how Fields in our Source Entity Type (in this case, Account) should be compared to Fields in the Matching Entity Type.

This article by one of my colleagues can be quite useful to help understand this screen: http://www.crmcs.co.uk/content/avoiding-duplicates-in-dynamics.aspx 

Once we have our Rules defined, we can then put these to use.

Duplicate Detection Jobs – we can navigate into this area within Data Management and use this to produce a Report on Duplicates within a particular Entity that we can use to cleanse these duplicates.

We can start here by clicking NEW to create a new Job.

image

This then allows us to define what Records we want to check for Duplicates – using the familiar Dynamics Advanced Find interface to build our criteria for what Records to check for Duplicates.

image

NOTE: this screen does allow us to search our entire CRM Database for Duplicates (within one entity) but Dynamics will raise an Error if it finds over 5000 Duplicates for a single Job, and so you may need to run separate Jobs for smaller Criteria within that Entity if your CRM has a considerable amount of data ithin that Entity.

image

Finally we can then give our Duplicate Detection Job a Name for reference and whether to Email a Dynamics User on Job Completion.

image

This will run for a period of time depending on the Number of Records being compared against Duplicates – and then ultimately finish leaving us with a Report that we can use to review, merge or delete the Duplicate Records found for this Criteria within CRM.

image

Here we can then decide what to do with the Record and its potentially Duplicate Record.

The most obvious action here is to Merge the two records together:

image

Here we can either:

Select Master – this will allow us to pick the most up to date record to keep, and also allow us to pick between which Fields to keep between the Main Record and the Duplicate Record.  This is the slower more manual way to correct the potential duplicates but is also the most accurate.

image

Merge Automatically – this is more the blunt tool to bring the data inline more quickly but less accurately – this takes the merging of individual fields and automates this as the following:

  • Master Record has the Field populated – then the Master is always used in favour of the Child
  • Master Record has the Field blank, but the Child Record has the Field populated – then the Child value is used for this Field

This assumes that the default Master Record is the record being selected at the top of the Dedupe Report Screen, and so the record finding the duplicates is the Master and the duplicate identified is the Child.

This gives us a way of running Jobs on our CRM Database to build these Deduplication Reports – and then working through the Report to Merge the Records with the Duplicate that have been identified. (or in some cases, simply deactivate the found duplicate)

Whilst a pain-staking job, this can be useful for getting the data right and giving our Sales, Marketing or Service Teams a lower barrier to engaging with CRM and making the solution a success.

The following two points can be common errors when working with the Deduplication Report screen:

Why is my Duplicate Detection Job showing 0 Matches when I highlight a Record?

If the Job has returned a Potential Duplicate but then is showing No Potential Duplicates, this doesn’t make sense, but this is where CRM has performed the Duplicate Detection Job against a particular set of Deduplication Rules – but then these Rules have since changed.

So CRM has still found that this Account potentially has 1 or more Duplicates as the Rules were when running the Job, but cannot find these Duplicates under the Rules as they are in place now.

image

If we reactivate the Rules from when the Job was run, this will then resume showing these duplicates.

Cyclical Parenting?

If we see the following error, then this means that the Duplicate Check has found 2 Accounts that are already related to each other as Parent-Child Accounts in a Hierarchy.

Here, merging would create a loop in Hierarchy of Parent-Child-Parent and so disrupt how the Accounts are being stored.

image

Going Further

Ultimately manually deduping a database in bulk can be a long and tedious task – and so the challenge here is to review the quantity and type of duplicate data and then look at whether the Data Cleansing could be better automated.

This involves building a list of rules for what data should be automatically merged or removed from CRM and then running intelligent processes to cleanse the data.

This can be particularly effective when coupled with external data-checking sources such as DDL, Linkedin or Companies House that provide an external validation of the data within the cleansing process.

There are a number of tools and developments that can be useful to this aim – from large scale data migration and cleansing systems (such as Scribe, Inaplex and others) to specific routines run through Dynamics Custom Development or Workflow to help automate cleansing within CRM.

At CRMCS we use a pattern called Overtime Tasks that run a scan over particular records in Dynamics (driven by particular Advanced Finds in CRM) to then process each record in turn and apply the Cleansing or Data Validation Rules as defined.

Obviously this can only automate the automatable, using rules, logic and external services to check the data – as the application of common sense is still difficult for systems to apply! (even in this age of increasing Machine Learning and AI)

But this can be used in conjunction with Human Intelligence to review how a Human would cleanse the duplicates in CRM, draw this down into an automatable process, and then apply this process across many hundreds, thousands or tens of thousands of records in quick succession – in a way that would be very time consuming for a human to review each record by.

Deduplication and Data Cleansing is an area that CRM and various line of business systems have focused on for some time, and so the following articles may be useful when looking at performing a Cleansing Job on CRM Data:

  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.