Ufs, this took me some time to figure out. ![:)]( ":)")
Step 1:
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.
Step 2:
Go to the “leads” table in Mautic’s database and make sure both custom fields (columns) look like this:
Step 3:
Still in the database, run the following SQL query:
Code:
DELIMITER $$
CREATE TRIGGER new_donotemail_added
AFTER INSERT ON email_donotemail for each row
begin
UPDATE leads
SET bounced = NEW.bounced, unsubscribed = NEW.unsubscribed
where leads.id = NEW.lead_id;
END $$
DELIMITER ;
(tested with mysql version 5.5.42-cll, phpmyadmin version 4.0.10.7)
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).
Step 3:
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.
Ufs, this took me some time to figure out. 
Step 1:
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.
Step 2:
Go to the “leads” table in Mautic’s database and make sure both custom fields (columns) look like this:
Step 3:
Still in the database, run the following SQL query:
[code]DELIMITER $$
CREATE TRIGGER new_donotemail_added
AFTER INSERT ON email_donotemail for each row
begin
UPDATE leads
SET bounced = NEW.bounced, unsubscribed = NEW.unsubscribed
where leads.id = NEW.lead_id;
END $$
DELIMITER ;[/code]
(tested with mysql version 5.5.42-cll, phpmyadmin version 4.0.10.7)
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).
Step 3:
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.
Hi Lourenco
Thanks for writing this guide. Right now, officially we would actually not encourage anyone to do this. We dont’ know what will happen to the columns when mautic is updated.
Symfony has a built in database field manager which is handled entirely by our custom field mappers. So any columns that show up in a table that we don’t have mapped out, are (probably) going to be removed when Mautic’s installer / updater code runs.
Hi Chad, thank you for your response.
The columns are normal custom lead fields, how could they be wiped off by an update? They were created as custom fields within Mautic’s dashboard, the only difference is that a database trigger was set up to automatically populate them with the information coming from the “unsubscribed” and “bounced” tables in “email_donotemail”.
Are you referring to the fact that I changed the column type from “text” to “tinyint”?
Also, a couple questions:
Does Mautic auto-update?
Would you recommend a better solution for automatically removing bounced and unsubscribed people from smart lists?
Hi Lourenco
First off - sorry! I didn’t see that there were custom fields! You actually should be safe there! Also Mautic doesn’t auto update, it does have an updater button which you would use instead.
Mautic does allow you to remove people from lists if they visit your unsubscribe form:
In your email use:
To set up an unsubscribe form. Then in the form:
Set up an an action to remove the lead.
PS: I realize this doesn’t handle bouncing… I’m not sure about that yet. I’ll do some investigation. Perhaps we should add a ‘bounced lead’ action in the email to do something with this.
Ah, I see! Good!
Yes! The ‘bounce lead’ action is one of the solutions I was thinking of as possible. Together with the possibility of the mandrill’s callback to return the information directly to the ‘leads’ table, instead of the ‘email_donotemail’ table, in order to populate the ‘bounce’ and ‘unsubscribed’ custom fields directly.
In the end the database sync trigger seemed the simplest solutions.
This bounce management is very important if you don’t want to have to lead with double opt-ins. I have an 26% opt-in rate that drops to 12.5% when the double opt-in is added. With this automatic exclusion of bounced emails I managed to use the single opt-in without risking getting banned by Mandrill due to a high bounce rate.
Definitely something you guys should look into for future developments.
Anyway, the product is really great and I’m happy to see it’s growing.