Excel Tutorial: Cross check hard bounced contacts against active contacts in a portal
Jan 17, 2018 3:23 PM - edited Oct 31, 2018 3:39 PM
DISCLAIMER: This post requires knowledge of non-HubSpot tools. Specifically Excel's VLOOKUP function. Please do not attempt this if you are unfamiliar with this Excel feature.
This tutorial shows a workaround it is not supported by HubSpot support. If you do not have familiarity with Excel please do not attempt. The end result is a list of hard bounced contacts that are also active in your portal.
This community post was written as a helper doc to the knowledge article: How do I create a list of hard bounced email addresses? When you get to this step, it's time for the Excel magic:
- Create a HubSpot list of all contacts currently in your portal with an email address. Criteria: Email address is known.
- Export that list to CSV.
- Name this new CSV 'active-contacts.csv'.
- You will be checking if any of the active-contacts are also present within the hard-bounced contacts csv you already generated using Excel's VLOOKUP function. If an active email is in the export of unsubscribes and bounces (now rid of unsubscribes), then it must be a hard-bounced, ineligible contact record currently within your portal.
- Name column B, in the active-contacts.csv file, 'ineligible?'
- To the right of the first email address input this formula: =VLOOKUP(A2,'hard-bounced-contacts.csv'!$A:$A,1,FALSE)
- Replace 'hard-bounced-contacts.csv' with the name of the csv you removed unsubscribes from earlier.
- Drag or copy paste the formula for all the way down column B
- If it results in "#N/A" then the email is not hard bounced, if it matches an email on the hard-bounced sheet then it will display that email
- Sort column B so that all the "#N/A"'s are at the bottom
- Copy all matched email addresses and paste into a new sheet. These are all the active contacts thatr are also hard bounced
- Import this new CSV of active-hard-bounced-contacts.csv to collect them all in a HubSpot list.