You may or may not know this, but I use Sendy to send out my monthly newsletters because it is incredibly cheap in comparison to competitors like Mailchimp. Anyone who has done list building before knows that over time you’re going to end up with a lot of stale subscribers that need to be cleaned in an effort to save space and keep things organized.
My email lists require a secondary opt-in to prevent SPAM subscriptions. The problem with this is I still get a lot of SPAM subscriptions even if they are never included in my newsletters. These subscriptions have no business being in my list, so it is best to remove them after so long.
We’re going to see how to use SQL to remove unconfirmed email subscribers in Sendy after so many days.
Before getting into the logic, it is important to note that this is unofficial. I came up with the code after reviewing the data in the database. It worked without errors for me and I’m using Sendy 22.214.171.124. Always backup the database before running a destructive script. I’m not responsible if you wipe out your list.
All subscriber data in Sendy exists in a MySQL table called subscribers and this data includes information such as when they subscribed, if they are confirmed, and a bunch of other stuff.
If we wanted to remove all subscribers that have been unconfirmed for longer than 30 days, we could execute the following SQL command:
DELETE FROM `subscribers` WHERE confirmed != 1 AND unsubscribed = 0 AND ((UNIX_TIMESTAMP(NOW()) - timestamp) / 60 / 60 / 24) > 30
In the above statement we are looking for subscribers that have not yet confirmed and have not unsubscribed. Given the current Unix timestamp and the stored Unix timestamp, we can do some simple math to convert it into days. Then we just plug in the magic number.
As far as I can tell, the only other table that references the subscribers is the links table. There is a column called clicks which I believe is subscriber ids. Since we’re removing unconfirmed subscribers, it shouldn’t matter to us.
You just saw how to make your Sendy email list a little more tidy. By removing unfinished or unconfirmed subscribers from your list after a certain number of days, you can save space. This isn’t the only step you should take towards having a clean email list. While not covered in this article, you should clean subscribers that have not interacted with your campaigns for a while.
Stay up to date on the latest in web, mobile, and game development, plus receive exclusive content by subscribing to The Polyglot Developer newsletter.