Matching Account/Company data can be quite complex, especially when dealing with international data. This article covers the best practices to help you achieve the best matching results.
General tips
- Apply the same data preparation/cleansing tasks to all the datasets you want to match. For example, your CRM account data, your ERP account data, and data from a spreadsheet you’re trying to load. This ensures maximum consistency across the data you’re trying to match.
- To optimize matching, you need to generate alternative versions of a data attribute like company name. Store these alternative versions in a custom field that is accessible to Openprise via API, but doesn’t have to be visible in your CRM’s user interface. Storing this data in your CRM can be useful for other technologies that can also benefit from this alternative data. This is not necessary for Openprise usage.
- For simpler matching scenarios with smaller datasets (less than one million records in the reference/master data), a simple waterfall job is usually sufficient. For very large reference datasets over one million records, such as purchased files from companies like Dun & Bradstreet, HG Insight, and ZoomInfo, consider using more elaborate matching tasks to generate all possible matches, then score each matched candidate to find the best match.
Data cleansing and preparation tips
The following data preparation and cleansing tips can help improve matching results. The more preparations you implement, the better your results will be.
- Company Name
- Remove corporate entity words like Inc., LLC, Pvt Lmtd. Use the Company Name Clean Up task template.
- Blank out junk values like N/A, none, Null, TBD, etc. Use the Remove Junk task template.
- Remove punctuations and other extraneous characters like -, *, &, (”, “). Use the Search and Replace task template to replace these characters with a blank value.
- Remove stop words like the, to, as. Use the Search and Replace task template and the reference data source: Reference - Stop Words.
- Replace diacritics with ASCII equivalents using the Search and Replace task template and the reference data source: Reference - Diacritic to ASCII.
- Diacritics often get corrupted during the import process to something like a black diamond symbol. Use the Search and Replace task template and the reference data source: Reference - Diacritic Encoding Error Fixes.
- Company URL and Domain
- Clean up URL using the task template URL Format Clean-Up. Be consistent with whether you are keeping the prefix http and www, as well as using the full or root domain. For matching it is recommended you remove all prefixes and just keep the root domain. i.e. acme.com.
- Identify free and disposable domains like gmail.com and hotmail.com and exclude them from matching. Use the Infer task template with reference data source: Reference - Free and Disposable Email Providers.
- For SMB company data, especially retail entities, the URL is often a Facebook or Yelp URL. Exclude these URLs for matching purposes. They perform poorly for matching.
- Country
- Standardize the country to ISO2 format using the Infer task template and the reference data source: Reference - Countries - Multilingual.
- If Country data is missing, try inferring from URL domains that contain .ie, .de, .mx or even phone number country prefix.
- State, Province, and Region
- Standardize the State and Province to eliminate diacritics like the German umlaut (ä, ö, ü). Use the Infer task template and the reference data source: Reference - States, Provinces, and Regions.
- Countries like France changed their regions in the last decade, make sure you standardize to the latest version.
- If state data is missing, but you have postal code data, you can infer the missing state data from postal code using the Infer task template and the various postal code reference data sources available in Openprise's data catalog.
- City, Town, and Parish
- If city data is missing, but you have postal code data, you can infer the missing city data from postal code using the Infer task template and the various postal code reference data sources available in Openprise's data catalog.
- Fix diacritic damages and replace diacritics with ASCII equivalents as recommended above.
- Postal Code
- For US ZIP codes, trim off the +4 digits and only keep the first 5 primary digits.
- For US ZIP codes, add back leading zeros that are often dropped during spreadsheet import processes. e.g. 02116 for Boston becomes just 2116. Use the Concatenate task template.
- Even if the postal code is not used for matching, but is used to infer missing state and city data, you should prepare the postal data first before using it for inference.
- Street Address
- Street addresses are often not needed to match enterprise company data, but can be useful for retail locations and SMB data matching.
- Instead of using the full street address, trim the data to the first 12 characters and use that for matching. This eliminates the very noisy part of the address data involving road/street and suite/unit where variations and abbreviations run wild. Use the Sub-string task template to trim the street data.
- Industry, SIC, NAICS
- Standardized industry code to either SIC or NAICS before attempting any matching. Note: Some data vendors provide additional granularity and extend SIC and NAICS code in a proprietary manner. It’s okay to use this proprietary data as standard, just make sure it is applied consistently.
- Vendor unique ID
- For vendor unique identifiers such as DUNS number and HG ID, make sure you understand its convention such as fixed number of digits and any potential for dropped leading zeros. Fill in missing zeros as appropriate using the Concatenate task template.
- For vendor unique identifiers such as DUNS number and HG ID, make sure you understand its convention such as fixed number of digits and any potential for dropped leading zeros. Fill in missing zeros as appropriate using the Concatenate task template.