• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Email Newsletter List Bounce Cleanup / Scrubber Services

ProCom

Well-known member
#1
I recently converted a large forum to XF. When I run the "Email Users" script, the list of valid email addresses that I get from XF is 350,000. Unfortunately the list that my old provider was sending through SendGrid was only 270,000. That 80k difference is a concern to me and I'm worried that the data that I got from my previous provider didn't have all the proper bounce details.

I'm worried that my imported list isn't as clean as the provider said it would be, so I decided to run my "0 post members" (about 150k) through a list scrubber to reduce the potential bounces.

As a test, I submitted the same list of 25 email addresses (ones that I knew were a good mix of valid, invalid, catch-all, etc.) to three companies:

https://www.emaillistverify.com/
https://www.hubuco.com/email_verification
https://proofy.io/price
(https://kickbox.io/ was almost a consideration too)

The first two companies processed the lists pretty quickly. The third one is still "processing" a day later. I emailed support 2 days ago to find out what was going on and still no reply.

Results from the other two:

upload_2017-5-24_10-39-23.png

hubuco is definitely way cheaper, but their results are not as robust.

Have any of you used any of these services?

I'm debating if I should run the rest of my list though this system too, and not just the zero-post members. Maybe expand it to all old-members regardless of post-counts?
 

eva2000

Well-known member
#2
I've only ever used emaillistverify and works nicely for vB and XF email list scrubbing :)

as you can save the emaillistverify results to text file you can do all sorts of manipulation of the data to figure stuff out

first what the email fields mean
List and description of sub-statuses:
"ok" - all is ok. server is saying that it is ready to receive a letter to this address, and no tricks have been detected
"error" - server is saying that delivery failed, but no information about the email exists
"smtp_error" - SMTP answer from the server is invalid, destination server reported an internal error to us
"smtp_protocol" - destination server allows us to connect, but SMTP session was closed before the email was verified
"unknown_email" - server saying that delivery failed, and the email does not exist
"attempt_rejected" - delivery failed, reason similar to "rejected"
"relay_error" - delivery fail because a relaying problem took place
"antispam_system" - some anti-spam technology is blocking the verification progress
"email_disabled" - email account is suspended/disabled/limited and can't receive emails
"domain_error" - email server for the whole domain is not installed or is incorrect, so no emails are deliverable
"ok_for_all" - email server is saying that it is ready to accept letters to any email
"dead_server" - email server is dead, no connection to it exists
"syntax_error" - syntax error in email address
"unknown" - email delivery failed, but no information about why
profile my emaillistverify detailed.txt processed list to see what type of emails make up my list
Code:
awk -F ',' '{print $2}' detailed.txt | sort -u | uniq
"dead_server"
"ok"
"ok_for_all"
"p_antispam_system"
"p_smtp_error"
"p_unknown_email"
"smtp_protocol"
"t_email_disabled"
"unknown"
"unknown_email"
figure out only bad emails
Code:
egrep -v '\"p_antispam_system\"|\"antispam_system\"|\"ok\"|\"ok_for_all\"' detailed.txt | awk -F ',' '{print $1}' | sed -e 's|\"||g' | sort > bademails.txt
20 bad emails on the list :)
Code:
wc -l bademails.txt
20 bademails.txt
find bad users from xenforo exported xenforo.txt file with username, email fields
Code:
while read u; do grep $u xenforo.txt; done < bademails.txt
will give me a list of all xenforo users with bad emails in xenforo.txt that match bademails.txt list I got from emaillistverify :)

Run a SQL query against xenforo user table for usernames and emails in formatted output required for your newsletter/sendy importing.

You just do the same in inverse for good emails and you will have a filtered text list of good emails for your newsletter/sendy etc :)
 
Last edited:

ProCom

Well-known member
#3
Ugh, @eva2000 you're such a geek! ;) Of course, you're able to do amazing stuff with your queries that takes me about 3x longer in excel :(

I'm exporting the list of "Email Users" to a csv, which contains their email and username. Once I get the results back and sorted, I'm going to take all the invalid emails and figure out the best course of action.

Which user setting would you toggle in xf for these invalid emails? Email Invalid Bounced?

Would it work just to run a SQL query like: where email = xyz set email-stat to "Invalid Bounced" ?
 

eva2000

Well-known member
#4

ProCom

Well-known member
#5
Code:
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'example@example.com';
You can make a copy of your database to test run queries against too
Thanks!!

What I usually do (since I'm an excel guy, not a shell / sql guy) is to take all the email addresses and the sql command, then do a "concatenate" that creates a SQL set command line for each item, and copy / paste them into PhpMyAdmin.

... definitely a hacked-together approach, but it's the best way I can do it without worrying that I'm doing something wrong.

For example, in cell A1 I have example@example.com, then I have this concatenate:
Code:
=CONCATENATE("UPDATE xf_user SET user_state = 'email_bounce' WHERE email = '",A1,"';")
... which spits this out:
Code:
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'example@example.com';
 

ProCom

Well-known member
#6
WOW, based on my list so far, I'm going to have at least 20k - 25k emails to set as "bounce" :eek:

My questions:
  1. Is running my huge list (copy / paste the command) into PhpMyAdmin a viable way to set so many of these as "invalid / bounce"?
  2. I'm sure a lot of valid / active members are going to get caught in this process and have their emails marked as "invalid / bounce". Will that impact their engagement in the forum or present them with any notices (or should I create a notice for them)?
UPDATE: I just set a test user as email = invalid / bounce and got the automated notice:

"Attempts to send emails to bounce@email.com have failed. Please update your email.
Update your contact details"

... which makes it so their account is set so they can't post: "(You have insufficient privileges to reply here.)"


 
Last edited:

eva2000

Well-known member
#7
i usually don't use phpmyadmin just log in via ssh and run on command line i.e.
Code:
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'example@example.com';
becomes
Code:
mysql -e "UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'example@example.com'; DATABNAME"
 

semprot

Active member
#8
Just came to this thread from Google.
If there is any add on to verify the email before sending it in XF, that will be very very handy :(