Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker.

Bulk Remove Unconfirmed Sendy Subscribers After A Certain Number Of Days

TwitterFacebookRedditLinkedInHacker News

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 2.1.2.8. 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.

Conclusion

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.

Nic Raboy

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.