Bouncing Emails / Scrub Email List - My Plan For Bounced Bounces

[ProCom]

Ok, I'm finally doing something about my own forum email list which has NEVER been scrubbed for bounces in the MANY years I've had my forum.

I've been sending all my xenfro emails for my smaller sites, including newsletters, through my default php settings on my server. I worried that I have a million bounces, so I finally got a bounce email account setup and in the past week I've received 20 legit bounced emails, like "This account does not exist". Right now I have my VA manually going through the bounced emails and changing members to “Awaiting email confirmation (from edit)”. This is going to be a nightmare to manually remove these as they come in.

Yesterday I signed up for Mandrill and I'm happy to see it sure has a pretty nice interface!

I started out with SUPER high delivery rate, 99.3% and they started to increase my hourly send limit. So, I decided to push the envelope. I installed BD Mails plugin and started sending out my newsletter.

I sent it to all members with over 100 posts. Things were looking good, but now I can see my delivery % dropping as the bounces come trickling in. I'm now at 95.4% delivery rate!

The plus side is I'll finally be able to start removing these bounced email addresses and cleaning up my list. The bad news: I don't know at which point Mandrill will tell me I can't send any more emails through their system.

I still have about 80% of my members to send mails to. I'll start the next batch with members that have 10 - 99 posts and then 1-9 posts.

I'm very scared to mail the zero post members since I'm guaranteed to see a TON of bounces. I guess I need to use one of those email list scrubbing services or just not send newsletters to any members with zero posts.

I stumbled on this page on Mandrill's site: http://help.mandrill.com/entries/24788267-High-Bounce-Rate

They say, "Clean your list prior to sending. Mandrill should not be used to clean your list. There are a number of online services that can do this for you. The MailChimp knowledge base offers details for list cleaning services."

That page lists these services:

• http://www.briteverify.com/brite-pricing
• http://www.datavalidation.com/email-validation-pricing/
• http://www.leadspend.com/mailchimp-promo

It's about $50 to scrub my list. Not a big deal, but I don't know anything about these services and if one is better than any other?

Another idea I have is based on the discussion in this thread and set the user state to "Awaiting email confirmation (from edit)". Basically my idea is to simply search for all members with zero posts and that have not logged in for over 6 - 12 months and set user_state = 'email_confirm_edit'

Example select query:

SELECT *
FROM `xf_user`
WHERE `message_count` <=10
AND `last_activity` <=1370044800​

(before June 1, 2013)

I'm leaning toward simply doing that for the thousands of zero post members who haven't logged in for 7 months vs. going through the process of scrubbing my list.

Any thoughts on this process or other ideas on handling bounced emails?

 

[Dakis]

What I've done is I bought sendy.co, and sent a first newsletter to everyone. I had obviously many bounces, but sendy cleans them up automatically from my list, so I have now a clean (almost) list.

The only downside is, every time I have to send a new newsletter, I need to add to my list any new members that have subscribed to my forum since last time.

 

[rdn]

You don't have to query, you can use Batch Update user.

 

[ProCom]

Wow, thanks @RoldanLT , I didn't even know that was a thing! /admin.php?users/batch-update

I'm still debating if I should use an email scrub service or just mark all these old users as "Awaiting email confirmation (from edit)"

I've sent a few mass mailings over the past year, so I figure if they haven't logged on since then, they probably won't in the future with more mass mailings.

Any suggestions?

 

[ProCom]

Ok, here's what I did:

I sent out my newsletters on 3 sites to all users with 1+ posts. My average bounce rate (on my list that hasn't been scrubbed in about 8 years) is about 8%. I'm sure it would jump to 20% or more if I sent to all the thousands of zero post members.

I used the promo link to http://www.leadspend.com/mailchimp-promo
(coupon code Chimp)

Then I found out it is $50 for a list up to 10k emails and it has to be a single list.

I went to my top 3 sites and exported all email addresses for users with zero posts. Removed all non-validated emails and all banned members.

I have a LOT of users (1,200) that are signed up on two or more sites, so I used a pivot table in Excel to combine duplicate email addresses.

A little bit more scrubbing of the list and I got it down to 9,800 email addresses to be scanned.

I signed up and uploaded my file. The bummer: they said it will take until FRIDAY to get the results... lame!

... but at least I'm getting 10k emails from as far back as 2007 scrubbed.

I wish I would have done this ages ago. Who knows how all the ISP's view my site, email address, IP, etc. for all the emails I blasted out to bounced emails.

 

[Reeve of Shinra]

thanks for sharing this -- I am scared to look at mine but need to do the same.

 

[ProCom]

Wow, on one of my sites, just sending to members with 1+ posts: 87.1% Deliverability

What a mess these lists are! About time they got a good scrubbing!

 

[Dakis]

To make you feel better, on one of the sites I administer I sent out a newsletter for the first time a few weeks ago, and out of 30,000 emails, I got 4,500 bounces

 

[ProCom]

So far this is working great!

I had a TON of bounced email addresses in Mandrill after sending out my newsletter. I went into their "activity" and exported all the bounced email addresses, put them into excel, and used a concatenate to do the following:

=CONCATENATE("UPDATE `xf_user` SET user_state='email_confirm_edit' WHERE `email` = '",A2,"';")

Which gives me this:

UPDATE `xf_user` SET user_state='email_confirm_edit' WHERE `email` = 'emailaddress@yahoo.com';

I updated hundreds of user's and moved them all into “Awaiting email confirmation (from edit)”

Today I received a PM / conversation from a member who said,

"I'm having trouble w/my account, and this seems to be the only way I can contact you. I couldn't even find a way to start a new conversation. There's a message at the top of my screen that says "Your account is currently awaiting confirmation. Confirmation was sent to xyz.com. Resend Confirmation Email"

This e-mail addy is wrong and I'm afraid to change it right now just in case it makes things worse. I can't post in all the forums either. I don't know if anyone else is having this problem or not.

My correct e-mail addy, if you need it is yxz.com

Thanks."

I think I just need to update the text in the announcement and link to very clear instructions on how to update one's email address and this solution will work well.

 

[Dakis]

Thanks for sharing, cool stuff.

 

[ProCom]

I edited my notice / phrase for your_account_is_currently_awaiting_confirmation_confirmation_sent_to_x

Here's what it looks like:

 

[Razasharp]

I've been thinking about doing the same, like many of you I have a lot of users on more than one of my sites (old ones on vB, newest on XF), and recently I started up a new site with all the members of another (40,000).

So I am thinking about creating a script that goes through bounced emails, then connects to a database (or in my case a number of databases) tries to find a user with each bounced email, and then puts them in a special usergroup - which in turn prompts users to update their email address.

I thought about using a cleaning service, and then using Mandrill - but I am not sure we need to spend money on such services when we run our own dedicated servers already.

Would anyone else be interested in such a script? The main benefit is that it won't matter what software you are using, as it will be connecting to the database directly. The script will be in Ruby so you will need that on your server.

If XF had this in the default setup, it would be a killer feature. (cc @Mike, @Kier)

 

[Joe Link]

Anyone done this lately? I'm getting setup with Mandrill, and I'd like to scrub my list first.

 

[Ocean44]

@ProCom - Thanks for the detailed write-up!
I would like to follow your guide to clean my emails - would you still do it this way a year later?

Once you have your queries like:
UPDATE `xf_user` SET user_state='email_bounce' WHERE `email` = 'emailaddress@yahoo.com';

I'm assuming you execute them in phpMyAdmin or something similar (I use Navicat).

Do you have to run the queries one at a time or is there some way to do them in a batch?
I am expecting a list of thousands to change the user_state on.

 

[ProCom]

Hey @Ocean44 , thanks for the comments!

I'm not sure if this is still 100% the best way, especially now that there are more "user states" like "email invalid (bounced)".

So, I'd probably do the same process again, but this time maybe change the state to "email invalid (bounced)".

Regarding how I executed them: Yup, I just threw them into phpMyAdmin. I'm no expert, but with the ";" at the end of each line, I was able to copy/paste thousands of rows directly in and run them all at once.