How to clean your SendGrid Email Marketing Lists

A few years ago I changed from Mailchimp to Sendgrid as Sendgrid has a very powerful API and was significantly cheaper (plus had a load of other nifty bits and pieces). But unfortunately it lacks a lot of great features that I had taken for granted with Mailchimp.

The main feature I missed was the ability to search contact lists for users who had not opened or clicked on an email for say, 6 months. To me these guys are dead emails and you want to either try and re-activate them by sending them a targeted email, or just prune them off your list.

By doing this you keep your lists nice and clean which increases your deliverability rates and also keeps your mailing costs down. If you have large lists, the costs and spam warnings can quickly become an issue.

The great thing about SendGrid however, is with a little programming skill, you can get this kind of info and prune your own lists.

Step 1: Event Notifications

First of all you are going to need to start logging who clicks and opens your emails. To do this log into SendGrid and select Apps. Turn on “Event Notifications” and select the actions you want to log.

Now for the tricky bit: You need to write a small script that will process this data and save it into your own database:
https://sendgrid.com/docs/API_Reference/Webhooks/event.html

Here is a PHP example that works well:

if(isset($_POST)){ include('../dbconnect.php'); // Dump body $DecodedJson = json_decode($HTTP_RAW_POST_DATA, true); foreach($DecodedJson as $key =>$value){ //Get the data we want fromt he array $email = ''; if(isset($value['email'])){ $email=mysql_real_escape_string($value['email']); } $event = ''; if(isset($value['event'])){ $event=mysql_real_escape_string($value['event']); } $url = ''; if(isset($value['url'])){ $url=mysql_real_escape_string($value['url']); } $reason = ''; if(isset($value['reason'])){ $reason=mysql_real_escape_string($value['reason']); } $type = ''; if(isset($value['type'])){ $type=mysql_real_escape_string($value['type']); } //Insert Data into DB $sql=" INSERT INTO `user_activity` (`id`, `email`, `event`, `newsletter_id`, `url`, `categories`, `dateadded`) VALUES (NULL, '$email', '$event', '$newsletterid', '$url', '', CURRENT_TIMESTAMP); "; $result=mysql_query($sql); } // close connection mysql_close(); }

Step 2: De-dupe your log table

After a few months of logging the above data you are now ready to pull your first list of inactive email users. To speed up the database queries it’s best to create a single table of de-duped email address to compare to your main list. Use the MySQL Distinct or Group functions to do this.

Step 3: Create your 'to-be-unsubscribed' list

To do this you will need 2 database tables.

1. Your entire email list
2. Your de-duped email log table

You can now LEFT JOIN these 2 tables, if there is a NULL entry for event column you know they haven’t clicked or opened anything and can kill these from your list (or try a re-activation campaign). You might also want to consider limiting the date range so newer users don’t get pruned before you have a chance to email them.

Here is a MySQL example:

SELECT user_activity.event , comp_users.country , comp_users.email FROM comp_users LEFT OUTER JOIN user_activity ON comp_users.email = user_activity.email WHERE user_activity.event IS NULL

Step 4: Unsubscribe these users from your SendGrid list

Export your list and save it as a CSV file. You will only need a list of emails, ditch all the other columns. Log back into SendGrid, find your list and select “Manage Unsubscribes”. Upload your list and you’re done!