what do you do with bad email addresses?

We use the deliverability stats from our bulk email server to mark the customer record with a Verify Email attribute.  When added some text shows up in the customer header so that it will prompt our staff to get an updated email address from the patraon if they call or we contact them for something. 

We use this attribute to filter our extractions and lists so that these email addresses aren't targeted.

We follow approximately the same procedures as Jon. We never delete, always inactivate if we don't want an email used, that way we have some history of it.

Heather

We follow approximately the same procedures as Jon. We never delete, always inactivate if we don't want an email used, that way we have some history of it.

Heather

We also use much the same process as Jon.

We have an attribute called "Check Email". The possible values are: "Bad Email", "Checked - OK", "Updated - OK", "Unknown".

We we get a hard bounce from WordFly we set this attribute to Bad Email.

If the value does not contain an "OK", the attribute is highlighted on the header for operators to ask the customer the next time the are in contact.

This attribute is also used to suppress Bad Emails when doing emailing lists.

thanks,

Dara

Thanks everyone for their responses on this! Sounds like people mark bad email addresses as inactive.

My follow up question: WHY? Meaning, why keep bad email addresses in the system at all? I mean, technically it's bad, useless data. Just trying to think everything through before we decide how we'll handle things.

Thanks!

Eric Mueller
Webmaster & Social Media Guy
Science Museum of Minnesota

Email: emueller@smm.org
Phone: (651) 221-9455

Unknown said:

My follow up question: WHY? Meaning, why keep bad email addresses in the system at all?

We keep them for history as others have mentioned, but also: if they were ever used for a ticketing order receipt/confirmation we'd want to retain that association; they can possibly help with duplicate merges (on name + e-mail or the like); sometimes the e-mail doesn't turn out to be bad, was mistakenly flagged that way for one reason or another we can see that we already have it and it can now be re-enabled with a click or two.

 One reason we  keep them is that records get automatically marked as bad by our process.   This process can mark an address as bad bases on several deliverability statuses, not just an invalid or no longer existing email address.   

I do see the point of why keeping them if they are bad, but maybe remove address that have been bad for a certain period of time. 

If interested, here is how we identify and handle mal-formatted e-mails.

First, the business rules for e-mail addresses:

1. it must contain one and only one @ sign ;
2. after the @ sign it must have at least one “.” ;
3. it can not start with “.” ;
4. prohibited character combinations:
..
.@
@.
5. prohibited characters:
(space)
"
(
)
,
:
;
<
>
[
\
[

6. e-mail addresses top level domain (for instance com or org) must exist in an official list, e.g. from the site: http://data.iana.org/TLD/tlds-alpha-by-domain.txt .

7. make the mal-formatted e-mails inactive;

8. the rules are applicable to ACTIVE electronic addresses which type is "E-mail" (TR_EADDRESS_TYPE.email_ind='Y').

9. create a Tessitura list of mal-formatted e-mails.

---------------
--THE WORK FLOW
---------------
1. Create two tables and two stored procedures, (see Constructor in the file attached):

C_CHC_MALFORMATTED_EMAIL
C_CHC_OFFICIAL_EMAIL_DOMAIN
--
lfn_chc_email_domain
lfn_chc_get_email_bad_char_as_tbl

2. Populate column DOMAIN of table C_CHC_OFFICIAL_EMAIL_DOMAIN by domain names from an official list for instance from the source: http://data.iana.org/TLD/tlds-alpha-by-domain.txt . 

3. Run the SQL script (run it step by step), see SQL Script in the file attached.

Unknown said:

If interested, here is how we identify and handle mal-formatted e-mails.

Great stuff, thanks for this. We've used the code below for finding bad e-mails, which has done pretty well, but your code contains some good ideas for taking this sort of task further.

select *
  from t_eaddress where 
      patindex ('%[&":;!\/()<>]%', address) > 0  -- Invalid characters
  or patindex ('[@.-_]%', address) > 0        -- Valid but cannot be starting character
  or patindex ('%[@.-_]', address) > 0        -- Valid but cannot be ending character
  or address not like '%@%.%'                 -- Must contain at least one @ and one .
  or address like '%..%'                      -- Cannot have two periods in a row
  or address like '%@%@%'                     -- Cannot have two @ anywhere
  or address like '%.@%'
  or address like '%@.%' -- Cannot have @ and . next to each other
  or address like '%.cm'
  or address like '%.co' -- Camaroon or Colombia? Unlikely. Probably typos 
  or address like '%.or'
  or address like '%.ne' -- Missing last letter
  or address like '% %' -- invalid whitespace