Ufs, this took me some time to figure out.
Create 2 new custom lead fields, one called “unsubscribed” and other called “bounced” (use their names as “alias”).
The data type doesn’t matter, this will be changed later directly in the database. Make sure to make it available for smart lists.
Go to the “leads” table in Mautic’s database and make sure both custom fields (columns) look like this:
Still in the database, run the following SQL query:
CREATE TRIGGER new_donotemail_added
AFTER INSERT ON
email_donotemail for each row
SET bounced = NEW.bounced, unsubscribed = NEW.unsubscribed
where leads.id = NEW.lead_id;
(tested with mysql version 5.5.42-cll, phpmyadmin version 22.214.171.124)
This will automatically update the newly created custom fields "bounced" and "unsubscribed" with the information from the "donotemail" table (the one that is automatically updated by the email server with bounced/unsubscribed info).
Go to your smart list and add the following filters:
With this, whenever an email bounces or unsubscribes the related custom field will change from "0" to "1" and the email will automatically be excluded from the smart list.