Customer Duplicates Rules / Matching

[Masond3]

Team, 

I have been provided some rules that the business would like to run on our Customer data set. 

The rules are as follows ; 

FirstName EXACT MatchBlank = FALSE
AND  LastName EXACT MatchBlank = FALSE
AND  MiddleName EXACT MatchBlank = TRUE
AND  Suffix EXACT MatchBlank = TRUE
AND  Email EXACT MatchBlank = FALSE

Matching Method Definition = The exact matching method looks for strings that exactly match a pattern. 
MatchBlank Definition = If you select this option for any field, and that field is blank in both of the records being compared, the fields are considered matches.

I am struggling how to implement such rules. So far i am using the "Fuzzy Match" tool and have the following matching Criteria . But i don't believe i have set the matching rules correctly 

Field Name	Match Style	Edit Match Options
FirstName	Custom	Generate Keys for each word = False Ignore if empty = False
LastName	Custom	Generate Keys for each word = False Ignore if empty = False
MiddleName	Exact	Generate Keys for each word = False Ignore if empty = True
Suffix	Exact	Generate Keys for each word = False Ignore if empty = True
Email	Custom	Generate Keys for each word = False Ignore if empty = False

Have attached example test data along with outcomes

Any sight's / screen shots / working scenarios be much appreciated 

Regards

Masond3

[peterr_h]

Hi @Masond3,

If someone else can help with just this (which I hope they can!) then that's great; if not, could you please provide a dataset/dummy dataset to go by?

Just as there's lots of conditions to consider, so creating a dataset will take some time.

Cheers

[Raj]

@Masond3 

please add some sample input and expected output 
will be in better position to help.

[ChrisTX]

Please provide sample input and expected output.  Like @peterr_h mentioned, we cannot decipher your goal.

The rules you listed seem to be only ONE rule, because each clause is joined by "AND".

Based solely on the word "exact" throughout your rule, it seems like your business users are focused solely on various combinations of exact matches.  Fuzzy match will definitely not give you perfect results if the business expects exact matches on specific fields.

Suggestion:

1) Create a "primary data source" dataset with at least 10 or 20 rows.  Searches will execute against your primary data source.

2) Create 10 or 20 "search for this" examples. 

3) For each "search for this" example, provide the expected output when a search is run against your "primary data source" dataset.

Also, show your business users sample results using the Fuzzy Match tool.  Select the option for Output Match Score.  See if they are interested in results that will never be "perfect". 

Then decide if you should be using the Fuzzy Match tool, or if you'll be forced to develop a process with multiple Joins.

Check out:

1) an example of a "waterfall" matching method: try one type of match, then for the records not matched, try a different type of match

         Inside Alteryx Designer under Help >  Sample Workflows > Use scripting and automation tools > Build a Macro > Merge to a master file with fuzzy matching. 

         This workflow uses the macros listed under "Fuzzy Match Supporting Macros"

 

2) video: Fuzzy match example

Fuzzy Matching for Beginners
https://community.alteryx.com/t5/Videos/Fuzzy-Matching-for-Beginners/td-p/330575

3) video: Fuzzy match example

Summer Camp: Consolidating your CRM System with Alteryx
https://community.alteryx.com/t5/Videos/Summer-Camp-Consolidating-your-CRM-System-with-Alteryx/td-p/...

Chris

[Masond3]

@ChrisTX @Raj @peterr_h  All - apologises for the delay. I have attached scenarios along with expected outcomes. Let me know if your require anything else 

many thanks 

[peterr_h]

Hi @Masond3,

I'm not sure if I've misunderstood, but it appears that 'matches' only count if all fields are identical across the two records?

If that's the case, then a simple Join (on all fields), followed by a Filter (to remove a customer joining to themself) should suffice.

From the example given, it only appears that joins are valid when all fields are identical, which a Join on all fields will provide.

I've added a couple of screenshots for your reference.

Please let me know if this is incorrect and I'll do some further work.

Cheers

[Masond3]

@peterr_h @This sir is why I love these forums. As I been overthinking this for a while, whilst your solution (need to test when I’m back in the office) seems like it fulfils my requirements