Thanks for your help @duncanc, much appreciated.
I tried this:
SELECT u.email
FROM phplist_user_message_bounce umb
join phplist_user_user u on u.id = umb.user
join phplist_listuser lu on lu.userid = umb.user
where umb.message = 168 and lu.listid = 7
168 being the campaign, and List 7 being one of the lists this campaign was sent to.
But I always got 0 results (which I think is weird)
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0001 seconds.)
Next I tried:
SELECT u.email
FROM phplist_user_message_bounce umb
join phplist_user_user u on u.id = umb.user
join phplist_listuser lu on lu.userid = umb.user
where umb.message = 168
believing that would return the data regardless what list, but for campaign 168 (that was sent to several lists)
Am I assuming this correctly ?
This gave me a list of email addresses and after testing a few of the emails I think my theory is correct, but kindly confirm.
However, I noticed that some email addresses were listed 2,3 or more times. When I checked, these email addresses were present in several lists. Some of these lists were part of the campaign, some were not (as in that list was excluded in fact) Is this correct, to be expected behaviour ?
At least one of the listed addresses was blacklisted. Last bounce had triggered the blacklisting because 5 consecutive bounces had been recorded. So that seems to be in order.
Am I correct to assume that if I use the phplist-plugin-campaigns plugin to re-send the email to the bounced email addresses, that blacklisted email addresses will not be retried (even though provided in the list ?)
That would be Ok though. If previous 4 emails failed, this email would not have made the difference.
Last, I ended up with 2 email lists for 2 campaigns that were affected by the ‘reputation’ issue).
One short list with 100-ish emails and one longer list, a few thousand.
I tried the short list using the plugin and the plugin provides an overview ‘campaign will be resent to these subscribers’, ‘bounces deleted for’ and ‘campaign was not originally sent to these subscribers’.
It’s the latter list that I thought was odd ?
If the extraction from the database worked as it should, these addresses should not have been listed there ?
On closer inspection. Two of these emails had been blacklisted due to previous email bounce and their 5 consecutive bounces, so I guess that’s OK. However there were also a few email addresses for emails that had bounced AND for emails, as far as I can tell, that were sent successfully ? All these email addresses were part of the proper lists, so they would have been sent. 5-6 emails all in all, so no biggie, but still weird.