[solved] SQL queries to Export Bounces

Solutions for other advanced phplisters

[solved] SQL queries to Export Bounces

Postby kennyg » 1:47pm, Fri 08 Sep, 2006

It would be advantageous if the program would provide the ability to export the bounce count on the export users page. This way you could correct an email before you exceeded the bounce count setting.

Thanks in advance.
Ken
kennyg
phpList newbie
 
Posts: 4
Joined: 4:58pm, Sat 02 Sep, 2006

Export Bounces

Postby jknight » 6:38am, Sun 10 Sep, 2006

Ken
I was also looking for a way to export bounce data from phpList. Since there didn't seem to be any bounce-export functionality, I just queried the DB directly. See if this SQL helps you out (try it in phpMyAdmin):

Code: Select all
select puu.email, pm.sendstart, pb.status,
case
when instr(data, "Delivery to the following recipients failed.") > 0 then "Delivery failed."
when instr(data, "Unknown user") > 0 then "Unknown user"
when instr(data, "User unknown") > 0 then "Unknown user"
when instr(data, "malformed address") > 0 then "malformed address"
when instr(data, "No such user") > 0 then "No such user"
when instr(data, "mailbox unavailable") > 0 then "mailbox unavailable"
when instr(data, "retry timeout exceeded") > 0 then " retry timeout exceeded"
when instr(data, "Mailbox unknown or not accepting mail.") > 0 then "Mailbox unknown or not accepting mail."
else "General Failure ..."
end as reason,
substring(data,1,1000)
from phplist_user_message_bounce as pumb
join phplist_user_user as puu on pumb.user = puu.id
join phplist_bounce as pb on pb.id = pumb.bounce
join phplist_message as pm on pm.id = pumb.message
jknight
PL Nut
 
Posts: 15
Joined: 6:34am, Sun 10 Sep, 2006
Location: New York, NY

Postby kennyg » 8:54am, Mon 11 Sep, 2006

Thanks Jeffrey,

That worked!
kennyg
phpList newbie
 
Posts: 4
Joined: 4:58pm, Sat 02 Sep, 2006

Postby jknight » 5:19am, Tue 12 Sep, 2006

Here's a little more complex example of how you can querry the DB directly to run reports on your bounces. This will give you some idea of the difference between a bad address vs. you being blocked as spam.
Code: Select all
select puu.email, 
case
when instr(data, "Unknown user") > 0 then "Unknown user"
when instr(data, "not listed") > 0 then "Unknown user"
when instr(data, "bad address") > 0 then "Unknown user"
when instr(data, "not a valid mailbox") > 0 then "Unknown user"   
when instr(data, "User unknown") > 0 then "Unknown user"
when instr(data, "Does not exist") > 0 then "Unknown user"
when instr(data, "no mailbox here by that name") > 0 then "Unknown user"
when instr(data, "No such user") > 0 then "Unknown user"
when instr(data, "No such recipient") > 0 then "Unknown user"
when instr(data, "REJECT No user") > 0 then "Unknown user"
when instr(data, "Mailbox unknown or not accepting mail.") > 0 then "Unknown user"
when instr(data, "mailbox not available") > 0 then "Unknown user"
when instr(data, "mailbox unavailable") > 0 then "Unknown user"
when instr(data, "Unable to process recipient") > 0 then "Unknown user"
when instr(data, "unknown or illegal alias") > 0 then "Unknown user"

when instr(data, "571 spam source blocked") > 0 then "Spam Source Blocked"
when instr(data, "Rule imposed mailbox access") > 0 then "Spam Source Blocked"   
when instr(data, "is restricted") > 0 then "Spam Source Blocked"
when instr(data, "This message appears to be unsolicited bulk mail") > 0 then "Spam Source Blocked"
when instr(data, "Requested action not taken: message refused") > 0 then "Spam Source Blocked"
when instr(data, "rejected for policy reasons") > 0 then "Spam Source Blocked"
when instr(data, "Access denied") > 0 then "Spam Source Blocked"
when instr(data, "Connection not authorized") > 0 then "Spam Source Blocked"
when instr(data, "Relaying denied. Proper authentication required") > 0 then "Spam Source Blocked"
when instr(data, "Recipient address rejected: Blocked") > 0 then "Spam Source Blocked"
when instr(data, "550 Mailbox unavailable or access denied") > 0 then "Spam Source Blocked"
when instr(data, "message refused") > 0 then "Spam Source Blocked"
when instr(data, "Message rejected") > 0 then "Spam Source Blocked"

when instr(data, "retry time not reached for any host after a long failure period") > 0 then "retry time not reached for any host after a long failure period"
when instr(data, "Address rejected") > 0 then "Address rejected"
when instr(data, "Delivery to the following recipients failed.") > 0 then "Delivery failed."
when instr(data, "malformed address") > 0 then "malformed address"
when instr(data, "retry timeout exceeded") > 0 then "retry timeout exceeded"
when instr(data, "mailbox name not allowed") > 0 then "553: mailbox name not allowed"
when instr(data, "554 transaction failed") > 0 then "554: transaction failed"
when instr(data, "554 TRANSACTION FAILED") > 0 then "554: transaction failed"
else "General Failure ..."
end as reason,
substring(data,1,2000)
from phplist_user_message_bounce as pumb
join phplist_user_user as puu on pumb.user = puu.id
join phplist_bounce as pb on pb.id = pumb.bounce
join phplist_message as pm on pm.id = pumb.message
jknight
PL Nut
 
Posts: 15
Joined: 6:34am, Sun 10 Sep, 2006
Location: New York, NY

Postby jknight » 5:21am, Tue 12 Sep, 2006

On more useful one: this will tell you bounces *by domain*. I don't know why phpList doesn't have this type of reporting.
Code: Select all
-- bounces per domain
select SUBSTRING_INDEX(email, '@', -1) as domain, count(*) as bounces
from phplist_user_user PUU
join phplist_user_message_bounce PUMB on PUU.id = PUMB.user
group by domain order by bounces desc

jknight
PL Nut
 
Posts: 15
Joined: 6:34am, Sun 10 Sep, 2006
Location: New York, NY

Postby dataseek » 11:00pm, Thu 28 Jun, 2007

how you export 40000 records like that on phpadmin?
dataseek
PL Nut
 
Posts: 19
Joined: 6:37am, Sun 17 Dec, 2006

Help for a newbie

Postby fepa » 1:57pm, Fri 16 May, 2008

Could you please give me step by step advise on how to implement this. I'm not experienced in SQL.

I would appreciate your help.


I was thinking that a simple solution would be to edit the export.php file and include the bounce count field. Can that be done?


Thankyou.

Fernando
fepa
phpLister
 
Posts: 5
Joined: 8:12pm, Thu 15 May, 2008

Postby ganar » 5:29pm, Mon 16 Mar, 2009

Great post! I manage to get a much better view of the problems in my configuration using this queries.

Managing a mailist is becoming more difficult by the day
ganar
phpLister
 
Posts: 11
Joined: 9:14pm, Tue 15 Mar, 2005

Re: [solved] SQL queries to Export Bounces

Postby DarkArchon » 5:05pm, Fri 22 May, 2009

jknight, your a lifesaver. I don't know why this hasn't been added in already. I have one favor to ask you though (if your still around). Is there any way to add another column to your sql script that displays how many bounces each email has? If anyone else knows how to do this, that would be great. Thanks!
DarkArchon
phpList newbie
 
Posts: 3
Joined: 6:09pm, Tue 19 May, 2009

Re: [solved] SQL queries to Export Bounces

Postby asauterChicago » 10:29pm, Wed 19 May, 2010

Does anyone have an update for this query? This does not work with my version of PHPlist (version 2.10.12). Being able to see which ones were rejected for spam, or which emails are just bad, would be very helpful.
asauterChicago
phpLister
 
Posts: 8
Joined: 12:31am, Wed 05 May, 2010

Re: [solved] SQL queries to Export Bounces

Postby H2B2 » 5:00pm, Sun 04 Jul, 2010

H2B2
Moderator
 
Posts: 7188
Joined: 1:51am, Wed 15 Mar, 2006

Re: [solved] SQL queries to Export Bounces

Postby joeicooper » 5:46pm, Fri 20 Aug, 2010

Can anyone offer help as to how to add user info beyond email to this export? User attribute info would be great to get in the same export. I've created fields for first_name, last_name, phone_work, etc. This would actually allow us to do something about people whose email has bounced.

Thanks.

Joe
joeicooper
phpList newbie
 
Posts: 1
Joined: 5:42pm, Fri 20 Aug, 2010

Re: [solved] SQL queries to Export Bounces

Postby Dragonrider » 6:25pm, Fri 20 Aug, 2010

If you have added the attributes to the list, and your subscribe page, then new signers will have the info you request. Unfortunately, this does not backdate as it were to previous sign ups.
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom

Re: [solved] SQL queries to Export Bounces

Postby jenpen » 5:19am, Tue 28 Aug, 2012

I just ran this SQL query and got the following:
MySQL returned an empty result set (i.e. zero rows).

Does that mean there were no bounces or did I do something wrong?
jenpen
PL Geek
 
Posts: 80
Joined: 1:48am, Thu 06 Aug, 2009

Re: [solved] SQL queries to Export Bounces

Postby duncanc » 11:05pm, Tue 28 Aug, 2012

This plugin provides an easier way to run the queries, and you can export the results
http://forums.phplist.com/viewtopic.php?f=7&t=37570
duncanc
Moderator
 
Posts: 2440
Joined: 6:34am, Sat 08 May, 2010
Location: London

Next

Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 1 guest

cron