Consecutive bounces in Subscribers plugin

[alejaaandro]

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 ?

[duncanc]

@alejaaandro I don’t think that this is correct in identifying consecutive bounces.

The order of processing the rows from a table is not defined. If I understand the query correctly then it is assuming that the rows are processed in subscriber order. The ORDER BY refers to ordering the results, not the incoming rows.

[alejaaandro]

@duncanc The last ORDER BY is just there for me, so I can get users with the most bounces at the top.

The ORDER BY that actually does the work is this:

@alejaaandro

ORDER BY msgs.userid ASC , msgs.entered DESC

Which sorts the messages by the time sent. I assume that’s what phplist_usermessage.entered is.
(It can also be easily changed to phplist_user_message_bounce.time - the time the bounce was received? )

You can give it at try to confirm if it works or no.
I checked it by manually counting the consecutive bounces of several users as listed in phpLists’ “Campaigns” tab of those users.

Notes:

1. We can easily get the consecutive bounces of a specific user (if we need to) by changing this

@alejaaandro

AND msgs.userid IN (SELECT id FROM phplist_user_user WHERE bouncecount > 7 ORDER BY entered DESC)

into this
AND msgs.userid IN (“specific_user_ID”)

2. There is a potential bug: if the first message of one user has bounced AND the last message of the NEXT user has also bounced, the @consecBnc will NOT reset.
   This doesn’t affect me because I only started tracking bounces about a year after I started using phpList (so the first message for any user is never a bounce). But I’m sure it can be fixed.
   Potential fixes: use a GROUP BY or use another variable which tracks the userid (if the userid changes compared to the previous row, then @consecBnc resets.

I’m not a database guru (it took me 2 days to come up with this query ) but if you think this can be of use to you and are willing to implement it, I am more than happy to work on fixing this

[duncanc]

@alejaaandro

The ORDER BY that actually does the work is this:

ORDER BY msgs . userid ASC , msgs . entered DESC

Yes, that is the one that I am referring to. The ORDER BY affects the result of the query, not the order in which mysql will process the rows from the usermessage table. The rows are not guaranteed to be provided in the order that you are expecting.

I don’t think that identifying consecutive rows that have the same value is as simple as this. Google for “mysql consecutive rows” to see other approaches.
The phplist processbounces page already has this processing. How is that different to what you are doing?

[alejaaandro]

I don’t use processbounces so I didn’t know it already counts consecutive bounces!
The reason I don’t use processbounces is that it crashes - I imagine it’s because I don’t get my bounces through an email (and that’s what that process does, retrieve them from the bounce emails, right?)

I use an smtp provider which handles bounces on their own and only report them through an API or on their website (and offer a csv export). So I’ve made a script to import them to phpList and I’m now looking on a way to figure out and handle the consecutive ones.

Anyhow, if there’s already a way to report this in phpList, I guess this is of no use to you.
Thanks for your time