In the Dedupe module, matching rows depends on the columns you compare and how you compare them. The “how” is determined by the comparison type you choose for each column.
Comparison types are defined below. Where an action is described, DemandTools File performs the action, then uses the information from that action to make the comparison. The action does not commit changes.
Acronym: Creates an acronym for a Company/Account Name by using the first letter of every word in the name.
- If the Company/Account Name contains only one word, it will be considered an acronym by default.
Example:
International Business Machines
Input: I.B.M.
Result: IBM
Acronym (Cleaned Name): Creates an acronym for a Company/Account Name AFTER applying Cleanse Rules. See the Cleaned Account Name mapping type below for more details.
Cleaned Account Name: Uses built-in Cleanse rules to match similar names based on common punctuation, abbreviations, and common business prefixes and suffixes. These rules are customizable for language(s) and/or line of business. Learn more at Managing Cleanse Rules.
Country Match: Standardizes column values for the recognized countries of the world. It will recognize the long name of a country, the 2-digit ISO short form, the 3-digit ISO short form, and the numeric ISO country value as possible matches of each other.
Date: Allows dates with different formats to be matched and allows matching date to date/time columns (ignores the time).
Domain: Allows for independent analysis of the domain information contained within a website URL or email address. For email addresses, it uses any information to the right of the @ sign. For websites, it returns everything after the http:// and/or www. (for example, xyz.com). Can also be used to match an email address to a website.
- Domain will see xyz.com and xyz.com/contact as two different URLs. To reduce a URL to just xyz.com, no matter what comes after, use the Relaxed Domain comparison type.
Email Plus Address: Relaxed email address match that includes the plus convention. Will match on both firstname.lastname@validity.com and firstname.lastname+modifier@validity.com.
Exact: 100% match of every character, assuming no additional comparison options apply.
First Name: Uses built-in Cleanse rules to match long forms of a first name to their common abbreviation. These rules are customizable for localization. Learn more at Managing Cleanse Rules.
First X Letters: Compares the first X characters in a text column. When selected, a window appears prompting you to specify the number of characters on which to match.
First X Words: Compares the first X words in a text column. When selected, a window appears prompting you to specify the number of words on which to match.
Normalized Postal Code: Matches USPS 5- and 9-digit zip codes without the need to standardize them to a common number of digits.
Numeric: Compares only the numeric values in a column (0-9, decimal points, and a single dash if it is as the beginning of the column). All other characters, such as spaces or punctuation, are ignored. A column with a value of "Apt # 31" is seen as the numeric characters "31", ignoring "Apt #". Numeric is also commonly used with phone numbers to ignore punctuation and alpha characters.
- Single dashes at the beginning of a column can be used to indicate a negative number, e.g. -54; therefore, they are not ignored with the Numeric mapping type. To ignore decimal points (periods) or dashes, check the Alpha Clean box as an additional matching option.
Regular Expression: Allows you to build a regular expression (regex) to create custom comparison types for any text column. Knowledge of regular expressions is required to use this comparison type.
- Regular Expression uses the first match located in the string.
Example:
Regular Expression: [0-9]+
Input data: 123 S Main St
Result: 123
Input data: 45 E. Center
Result: 45
Regular Expression Yes/No: Checks a regular expression (regex) you build and returns a Yes value if the data passes the regex or a No value if it does not.
- This comparison type returns only Yes or No, not the matching value. If you want to return the matching value, use the Regular Expression comparison type.
Example:
Regular Expression checking that a column contains exactly 5 digits OR 5 characters OR is blank
23232 = Yes
232323 = No
Aabbc = Yes
Bob = No
Relaxed Domain: Matches top-level domains for websites by parsing the word before the .com, .co, .org. .edu, etc. Helpful for matching email addresses to websites.
Example:
www.validity.com
validity.com
http://www.validity.com
validity.co.uk
www.validity.com/downloads
john.sample@validity.com
Are all seen as "validity"
- If there are two words in the main body, the matching word will be the first word.
Example: www.shop.validity.com will match on the word "shop"
https://www.live.validity.com will match on the word "live"
Relaxed NA Phone Match: Removes all non-numeric characters and spaces, leading 0's and 1's, area codes and extensions, returning the 7 primary digits of the phone number. If just 7 digits are left, use those 7 digits; else, just return digits 4 - 10. If the phone number is entered as a phoneword (e.g., 1-800-55-PHONE), this comparison type will ignore the alpha characters (PHONE) and only look at the numeric portion (55).
Example:
+1 879 555 1212 ext 500
(879) 555 1212
1.879.555.1212 ext 408
Are all seen as "5551212"
- This comparison type is based on a 10-digit North American phone number. Although it can be used with phone numbers outside North America, it will just return 7 digits in the middle. The Numeric comparison type is recommended to be used with non-North American phone numbers.
Relaxed Street Address Match: Parses the street address to the lowest common denominator.
Example:
123 NW Pavillion Ave
123 Pavillion St, Suite 400
123 Pavillion Avenue, Fl 4
123 Pavillion Rd.
Are all seen as "123 Pavillion"
- If there no street designators (such as St, Rd, or Ave), then the address will not be parsed into its individual components. In this case, the street address is returned with no changes for matching.
State Match: Matches US state and Canadian province abbreviations to their long names.
Street Address Match: Slightly more rigid criteria than the Relaxed Street Address Match comparison type. This type ignores the differences in street type short forms (such as crescent -> cres, road -> rd, street -> st) and matches all standard abbreviations in the street address to their long forms (e.g., South -> S, Floor -> Fl).
- For an address to match, all the components of the address need to be in both rows. If one has "North," the other needs at least "N"; if one has a "Suite 100," the other needs at least "Ste 100".
Additional Mapping Options
Blank Values: When turned on for a column that has been chosen as a match condition, Demand Tools allows rows with blank column values to be matched to other rows with blank column values for the column specified.
Fuzzy Matching: Phonetics engine capable of analyzing words for how they sound when pronounced. Fuzzy matching uses a technique of removing vowels and looking at the remaining consonants, which works well for matching columns with spelling mistakes.
- Fuzzy Matching ignores numbers when matching. For example, Girl Scout Troop 100 and Girl Scout Troop 780 will be considered a match if Fuzzy Matching is checked. Do not use when matching phone numbers or other columns containing numeric characters you don’t want to ignore (like street addresses).
Transposed Values: The transpositional engine allows for columns to appear as duplicates even when there are differences in the word order. For example, "Jones, Smith and Jackson" will appear to be a duplicate of "Jackson, Smith and Jones".
Alpha Clean: The alpha clean engine extends some of the capabilities of the account name cleaner to other columns for easier matching. Alpha Clean is used for ASCII (North American) data to ensure the only characters that are analyzed are the 26 characters of the English alphabet, numbers 0-9, space, and ampersand (&). Any other character the column contains is ignored.
- Spaces can be ignored by checking the Fuzzy Matching option.
Advanced Comparison Techniques to Find Additional Matches
Exact matching is not the only way to match! Use the various comparison types and options available within DemandTools File to help identify similar Account/Company names, nicknames for first names, similar addresses, similar phone numbers, perform phonetic matches etc.
Review and manage your Cleanse rules to include abbreviations, suffixes, prefixes specific to your industry.
Looser techniques are best used when more than one column is being matched on.
Additional tips for specific scenarios:
- First Name matches
- Use the FirstName mapping type to match nicknames, e.g. "Mike" to "Michael"
- The “middle initial” dilemma: Try matching on just the first letter or first word (mapping type First XX Letters or First XX Words) and Last Name
- Last Name matches
- Add Alpha Clean to match Smith-Jones -> Smith Jones
- Add Fuzzy Matching to catch spelling errors
- Company/Account Name Matches
- Use the Cleaned Account Name mapping type to find similar names
- Match abbreviations to long forms, e.g. Saint -> St
- Ignore common suffixes and prefixes, e.g. match The Hewlett Packard Company -> Hewlett Packard Inc
- Add Transposed Values to catch where the order of the words in the name are different but the words themselves match, e.g., University of North Carolina -> North Carolina, University of
- Add Alpha Clean to catch slight differences in punctuation
- Add Fuzzy Matching to catch spelling errors
- Not recommended if all that is being matched in a step is the company or account name and Cleaned Account Name is the mapping type
- Street Address Matching
- Use the Street Address Match mapping type to match abbreviations to their long forms (e.g. Street -> St)
- Use the Relaxed Address Match mapping type to additionally match one street address with a Suite # to another without (e.g. 100 Main Street -> 100 Main St Suite 234)
- City matches
- Use Alpha-Clean to match “St. Charles” with “St Charles”
- Add Fuzzy Matching to catch spelling errors
- Phone Number matches
- For North American phone numbers, use Relaxed NA Phone Match to ignore punctuation, leading 1's, area codes and extensions
- match +1 (781) 458-9999 to 6174589999 x123
- Since this mapping type ignores area codes and returns just the "555-1212" portion of the phone number, it should not be used by itself. Only use when additionally matching on other columns to avoid updating the wrong row.
- For North American phone numbers, use Relaxed NA Phone Match to ignore punctuation, leading 1's, area codes and extensions
- Email to Website matches
- Use the Domain or Relaxed Domain mapping type to match an email address to a website in Lead to Account Matches
