Excel Tutorial: Cross check hard bounced contacts against active contacts in a portal

HubSpot Product Team

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:

 

cross-check-step.png

 

  1. Create a HubSpot list of all contacts currently in your portal with an email address. Criteria: Email address is known.
  2. Export that list to CSV.
  3. Name this new CSV 'active-contacts.csv'.
  4. 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.
  5. Name column B, in the active-contacts.csv file, 'ineligible?'
  6. To the right of the first email address input this formula: =VLOOKUP(A2,'hard-bounced-contacts.csv'!$A:$A,1,FALSE)
  7. Replace 'hard-bounced-contacts.csv' with the name of the csv you removed unsubscribes from earlier.
  8. Drag or copy paste the formula for all the way down column B
  9. 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
  10. Sort column B so that all the "#N/A"'s are at the bottom
  11. Copy all matched email addresses and paste into a new sheet. These are all the active contacts thatr are also hard bounced
  12. Import this new CSV of active-hard-bounced-contacts.csv to collect them all in a HubSpot list.
6 Replies 6
Esteemed Advisor

If you're doing a lot of work in Excel, INDEX and MATCH is more robust than VLOOKUP, but the general approach you've outlined is great advice. At the cost of a slightly-longer formula and a bit more setup work, this combination of functions effectively does VLOOKUP forwards or backwards, without having to count columns. 

If this is a one-time sort of thing, VLOOKUP as a throwaway probably makes more sense. Whichever method you use, if you have to share the workbook, using named ranges can make your work a lot more readable to others. 


Brad Mampe, Salesforce Analyst, Fidelity
I'm probably wrong. I may not be right about that.
Reply
0 Upvotes
HubSpot Product Team

@bradmin is correct. Formula using this method would be:

=INDEX('hard-bounced-contacts'!$A:$A,MATCH(A2,'hard-bounced-contacts'!$A:$A,0),1)

Occasional Contributor

Maybe you could try and make this harder? LOL.  Yikes!  What a bunch of jibberish.  I don't understand why Hubspot on one had alerts me to a high bounce rate and when I try and fix it I have to read a couple of "expert level" support articles and spend an afternoon trying to figure this stuff out in excel.  And then in turn ask us to do things that are not supported.  This article and others on this topic, make zero sense to the average business owner. There has to be an easier way. The tutorials on this topic are not written for the mere mortal and I have nobody to help me.  Why do you display different data on the excel sheet than I can see on my lists (ie- hard bounce, portal bounce, global bounce)? If I had access to that data inside hubspot, this would be a cinch. Not only that I have contacts who have hard bounced for some reason and yet we email them every day. So if I delete them, I delete active customers.  So I'm up to three hours of trying to figure out how to filter out contacts that are not vaild and got nowhere. I give up. 

HubSpot Product Team

This article and this process are a bit dated now as you pointed out. You are also correct that you absolutely should have some experience with VLOOKUP's and removing duplicates in order to implement this.

 

Sorry for the misinformation.

 

As for making this easier in app this is on the product team's plate. Working out an all encompassing solution will take timebut we are aware this is a huge challenge for our customers.

Reply
0 Upvotes
Occasional Contributor

 Thanks for the reply. If you are going to post an article that only 2% of your customers will understand or be able to administer...why don't you suggest a integration partner for the rest of us dum-dums?  Hubspot doesn't have to code itself out of every problem. 

 

I found out about these guys by total fluke. And in 5 seconds I can integrate my account and review every list in my portal. 

 

https://kickbox.com/

 

And yet they are not listed in your integrations section and could be considered the best/easiest solution out there.  It **bleep** having to pay for it though.  Hubspot- you should buy Kickbox!

 

 

Hubspot is about creating solutions, not baffling us with overly technical, complicated explanations.  This is the only article in your support area that trys to deal with this. This is the first time in 5 years of searching Hubspot KB that I can't comprehend a word of an article. 

 

List quality is really important, especially for the thousands of us that are moving from older, legacy CRMs and importing into Hubspot. 

 

Highlighted
HubSpot Product Team

Hi Craiger, yes Kickbox is great! I wrote this postwhen I was in Support for something to link customers to who wanted to go down this path. I should add a disclaimer at the top for people who find this through another post or by searching. Thank you for the heads up and I am glad you found a solution.

Reply
0 Upvotes