Get Rid of Bad Email Addresses

Ron Morelli · November 2018

I have over 4,000 email addresses that our mailing system identified as hard bounces. My goal is to delete those email addresses from my database, and I'm having problems deleting them. This is what I've done.

I created a query that was used for the mailing. I exported the query using constituent ID, phone type, phone number, phone import ID. I then took the report from NetCommunity that identified the hard bounces. NetCommunity didn't have any ID, just the email and the individual.

I smushed the two reports into a .cvs and basically took out unique emails leaving just the duplicates (which would tell me which ones from my export would be the hard bounces from the NetCommunity report.

I then deleted the emails in the PhoneNum column. My thought was if I'm updating the records, and RE sees that the PhoneNum field is blank, it would erase the bad emails.

I went to import. I clicked update records. I made sure constituent id was the primary identifier key. I made sure things were mapped correctly. I created my control report, saved the query, clicked update . . . received no error . . . but none of the emails were deleted. I suspect removing the emails from PhoneNum meant that RE just skipped over the field. Since it was empty, it felt there was nothing to change.

So my question is: How do you get rid of bad email addresses in RE using import? I don't want to manually delete 4,000 email addresses. Any suggestions?

John Heizer · November 2018

You may be able to delete the information using the "magic" caret character ^ in the phone number field, but this won't remove the phone record, just replace the existing information with a blank. You can then run the Delete Empty Phones plugin.

https://kb.blackbaud.com/articles/Article/65552

Joshua Bekerman bCLO bCRE · November 2018

Same as John said, although our policy is to keep the old and bounced emails and mark them inactive and change the type. Either way here's another article to help:

https://kb.blackbaud.com/articles/Article/46058

Damon Rosenthal · November 2018

Yes we have found the same challenge in deleting/removing information from RE. We thought about replacing the blank cell entry with a <space>, but then RE query criteria "Not Blank" would be true for those <spaces> and that is not desired result. We are currently looking at a conditional option with Omatic but it is not functioning as it was instructed to us. Also another item we've been thinking of is if we really want to delete a currently bad address as the next data health process may just repopulate as a good current e-mail based on public data. At least if it does, your hardbounce backs just drive you to delete again as long as you are doing systematically and not manual, not much concern I suppose.

Donna Mainord · November 2018

My Manager wants the address fields to be blank if we have an undelivable address. That way we are not accentently sending mail to people without good addresses. (I'm not the only person who pulls lists and not every knows how to filter out No Valid Address).

I copy the "bad" address over "former", but I cannot get RE or Importacular to import blank fields. I'm going to try the Magic "carrot" ^ on a couple test records to see if that will get ride of them.

Ron Morelli · November 2018

The ^ trick worked for me!

Damon Rosenthal · November 2018

So with the ^ trick, if you now do a query on that field and have criteria of "blank" (looking for no address items) would that item/constituent be included in your list/results? Unfortunately with a <space> character, even though it looks blank, the logic says it isn't and you would not know that constituent is an exception from your list to find those with a missing address.

Ron Morelli · November 2018

I re-ran the report, and the "bad" addresses no longer pull up.

Make sure you run the plug-in delete phone and check mark blank, though.

It appears they were indeed purged in RE using the carrot trick. Also I believe if you do a criteria query and say you want an email address with just a space, I believe it throws an error that you need a character.

Damon Rosenthal · November 2018

Good to know. We will do some testing and validate results.

Thank you very much Ron!

Jane Marvin · July 2020

I just tried the "magic caret" import, but I got "not a unique import ID" all - the constituents do have other email addresses, but only one of the type I'm looking to delete-- and nothing updated to blank. I'm going the zen route of manually doing for now, but would love to learn the trick for the future.

JoAnn Strommen · July 2020

Ugh! Just lost message - jumping in mid thread. I just finished phone clean up project. Saw that error when I forgot to check update existing records and it was looking to create new records. Hence the ID already existed.

Just a thought.

Jane Marvin · July 2020

I think you're right, I did have "create" checked off. Hopefully next time I need to do this I'll remember this!! I'll star my email for sure, thanks!

Rachel Lee 2 · July 2020

Sorry to jump in mid thread, I have not heard of the Magic caret import. but like the idea of being able to remove all invalid email addresses.

Would someone mind telling me how to do this.

Many thanks

Rachel

JoAnn Strommen · July 2020

I'm a stickler for clean data and working to finish phone # clean up. Email clean up is on the future project list. Couple of questions.

Are you removing all emails with a hard bounce? What is your determining factor if email is invalid? Or are you just creating an email type that can be excluded?

If removed, do you run into questions from the constituent of 'how come I didn't get XYZ email?' and then have no record of the email that was used and bounced? Do you get the same email when you run search programs like EmailFinder and just get same email back in your system without knowing it bounced?

Trying to think through what our process should be.

Jane Marvin · July 2020

The first reply on the thread addressed it. Although *DISCLAIMER* I haven't made it work for myself yet, you export the phone type, import ID and email address. Replace all emails with "^" and import as "update records." According to the reports, this will cause those email addresses to be blank, which you can delete with "delete phones" plug-in, choosing "blank phones."

Daniel R. Snyder · July 2020

JoAnn Strommen‍, for many of the reasons you mentioned, we mark these hard bounce emails as inactive and leave them with the email type they are in when the email was made inactive. Then, in order to explain why it was inactivated, we enter information in the comments field. This can then be reviewed when the questions about not receiving an email come up.

The reason we do it this way is when we run a service like EmailFinder, all those emails come in with a specific EmailFinder (or something like that) type. Then we can globally change the type to Email1 and if that email is already on the record with the same type you will get an exception and those are generally emails that have already been marked as inactive and can be reviewed.

JoAnn Strommen:

I'm a stickler for clean data and working to finish phone # clean up. Email clean up is on the future project list. Couple of questions.

Are you removing all emails with a hard bounce? What is your determining factor if email is invalid? Or are you just creating an email type that can be excluded?

If removed, do you run into questions from the constituent of 'how come I didn't get XYZ email?' and then have no record of the email that was used and bounced? Do you get the same email when you run search programs like EmailFinder and just get same email back in your system without knowing it bounced?

Trying to think through what our process should be.

Get Rid of Bad Email Addresses

Comments

Categories