[solved] SQL queries to Export Bounces

[kennyg]

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

[jknight]

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

[kennyg]

Thanks Jeffrey,

That worked!

[jknight]

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]

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



[dataseek]

how you export 40000 records like that on phpadmin?

[fepa]

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

[ganar]

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

[DarkArchon]

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!

[asauterChicago]

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.

[H2B2]

Related feature request: http://mantis.phplist.com/view.php?id=15513

[joeicooper]

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

[Dragonrider]

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.

[jenpen]

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?

[duncanc]

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