How to:

Clean up a list of websites to get domains

Starting point: List of websites with 'junk' around the main domain (Ex. https://www.acme.com/home)

Ideal end state: 'Clean' view of the domain

Examples use cases: To identify duplicates, to match external lists to your Account database, to import to a 3rd party as a base list to find additional contacts (ie ZoomInfo)

Step 1: Pull your list of Accounts

Build and export an Account report. from Salesforce.com

Filter out all Accounts where Website is NULL or does not contain a period. Also recommended to filter out common personal email domains (ie gmail.com, yahoo.com, etc.)
Columns to include: Account ID, Account Name, Website

Step 2: Remove excess text before the domain

Import to a Google sheet

File >> Import >> Upload >> Insert a new sheet

In a new column, add this formula to 'clean up' the domains:

=trim(REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(A2,"https?://",""),"^(w{3}\.)?","")&"/","([^/?]+)"))

Final product: A sparking clean list of Account domains

Caveats:

You may still have some domains which represent 'junk', likely personal emails ended up Account websites. Recommendation is to identify those for your org an keep track of those for future passes.
You will also need to be mindful of where you will need to search for exact matches after the domains are cleaned since using 'CONTAINS' would filter out too many websites in error (ex. you don't want to see Accounts where the website is 'live.com' or 'me.com' but to exclude

Google Sites

Report abuse