I’ve been working on a way to get consecutive bounces.
The total # of bounces displayed in the “Bounce count” report is no longer a good indicator for me for subscribers I should consider inactive and unsubscribe.
I came up with this query:
set @consecBnc := 0; SELECT *, max(`consecBounces`) as `maxConBncs` FROM ( SELECT * , CONVERT(@consecBnc := IF (`bncs`.`id` IS NOT NULL,@consecBnc+1 ,"0"),unsigned) as `consecBounces` FROM `phplist_usermessage` as `msgs` LEFT JOIN `phplist_user_message_bounce` as `bncs` ON (`msgs`.`messageid` = `bncs`.`message` AND `msgs`.`userid` = `bncs`.`user` ) WHERE `msgs`.`status` LIKE 'sent' AND `msgs`.`userid` IN (SELECT `id` FROM `phplist_user_user` WHERE `bouncecount` > 7 ORDER BY `entered` DESC) ORDER BY `msgs`.`userid` ASC , `msgs`.`entered` DESC ) as `final` GROUP BY `userid` ORDER BY `maxConBncs` DESC
- I am limiting the search to users with more than 7 bounces… This can be easily adapted
- It is decently fast I believe. I have ~4000 subscribers and >7000 bounces in ‘phplist_user_message_bounce’ and the query takes 1.2887 sec in phpmyadmin
I’m planning to make a script for myself so I can export these subscribers, and then process them as I wish. But I thought I’d share with the community in an effort to give back and improve phpList
@duncanc would you consider integrating this to improve the “Bounce count” report ?