For many, Mailchimp is a reliable mailer for sending newsletters and other automated email campaigns. And for some it might even be cost effective but unless you are sending very few emails I’d suggest not. Whatever your views on costs it is very easy. However, if you’re already using Amazon’s AWS then there are other services that, with a little effort, might well be cheaper. These take advantage of the fact that you get a generous free sending limit with your account.
Sendy is one such service which does much of what Mailchimp does. It can send nicely formatted emails in html and plain text, run multiple lists, help you to be GDPR compliant, manage unsubscribes and much more. If you are willing to go through the setup and, perhaps, host it yourself it is a good option.
While Sendy is feature packed it has always surprised me that it is missing something that I consider to be important – the ability to see when someone clicked a link. To be clear you can get a list of who clicks on a link in a campaign but you have no idea when. This makes any follow up actions, such as creating an activity record in your CRM for that time (which is what I needed to do) impossible. So I decided to try and fix this deficiency.
The issue, to my mind, is how Sendy stores the clicks. This is done as a comma separated list of subscriber IDs in a longtext column in the links table. The latest click of a link just sees the latest subscriber ID added to the end of the list. What I really needed was a table that held the individual clicks along with WHEN the person actually clicked. So I created a table for this:
CREATE TABLE campaign_click (
id INT NOT NULL AUTO_INCREMENT,
subscriber_id INT NOT NULL,
clicked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
link_id INT,
primary key (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Then I created a trigger that fires every time the links table is updated and the clicks column has changed:
DELIMITER #
CREATE TRIGGER tr_links AFTER UPDATE ON links
FOR EACH ROW
BEGIN
IF (old.clicks!= new.clicks) THEN
INSERT INTO campaign_click(subscriber_id, link_id)
VALUES (SUBSTRING_INDEX(new.clicks, ',', -1), new.id);
END IF;
END#
DELIMITER ;
What this does is whenever the links table is updated and the click column has changed details of the click will be inserted into the campaign_click table. Bingo! We finally have a record of who click which link when.
Now you can do things such as list all subscribers that clicked a link in the last hour:
SELECT c.`clicked_at`, l.`link`, a.`title`, s.`email`
FROM `campaign_click` c, `links` l, .`ares_emails` a, `subscribers` s
WHERE c.`clicked_at` >= DATE_SUB(NOW(),INTERVAL 1 HOUR)
AND c.`link_id` = l.`id`
AND l.`ares_emails_id` = a.`id`
AND c.`subscriber_id` = s.`id`
You can make this more sophisticated should you require but you get the idea.
I should say that Ben, the author of Sendy, may well not approve of this hack and any update that is released in future may way overwrite this but I suspect that it will be ok. Use with caution, you have been warned!