Manually Applying Zerobounce Statuses to Groundhogg

There are no date or other filter inputs on the Zerobounce scan form

So, you may find yourself doing the Groundhogg Zerobounce Data Dance…which goes kinda like this:

  1. Set a filter for something like Unconfirmed newish, or imported, or other records you consider suspect.
  2. Export the records, at least ID and email.
  3. Go to Zerobounce, login, and upload the file
  4. Download the results
  5. Delete all but the ID, email, and ZB Result columns, and Save As CSV

For example, say that old e-commerce emails got imported as Confirmed, but many of them had aged out…you may want to go back and rescan these.  We found 5 to 7% statuses <> “Valid”.

Now, you may find, as I did, that Groundhogg, unfortunately, does not allow importing Zerobounce status directly…

Apparently, the Zerobounce integration was some kind of Google Summer of Code project, and not really intended for professional use.

Anyway…meanwhile, we will solve this with a bit of ingenuity…

In the database, you will note that the meta_key in gh_contactmeta is _zb_status and if you run a query like :

SELECT DISTINCT `meta_value` FROM `ayq_gh_contactmeta` WHERE `meta_key` = “_zb_status” the statuses

valid
abuse
invalid
unknown
do_not_mail
catch-all
spamtrap

Fortunately, these match the codes that Zerobounce returns.

In your little CSV, if you open it in Excel, you can insert a formula like this :
UPDATE `ayq_gh_contactmeta` SET `meta_value` = “ZBResultColumnVal” WHERE `meta_key` = “_zb_status” and `contact_id` = “ContactIDCol”

The final formula will look something like :

=”UPDATE `ayq_gh_contactmeta` SET `meta_value` = ‘” & C8 & “‘ WHERE `meta_key` = ‘_zb_status’ and `contact_id` = ” & A8 & “;” – You need to be a little careful with single and double quotes, and the semi-colon is to separate the update queries, because you will have a nice little stack of them.

Test one of these in PHPMyAdmin or similar, and verify that the result is as expected in the database. Then verify in Groundhogg.

All going well, copy the formula down your sheet to generate all the update statements, and run them in PHPMyAdmin or similar. Delete the very last semi-colon, it’s not necessary.

Now, you’ve got a general purpose way to use the Zerobounce results for records matching your own criteria without paying for scanning the same records repeatedly.

Cleaning the list of disengaged contacts is another subject.

You don’t need to worry about new contacts, as the Zerobounce integration is real-time :

Enjoy!

Similar Posts