Top 10 Tips when synchronising your database and Mailchimp

Okay, sooner or later someone is going to need to synchronise all those people who registered on your company website with an email service provider to do a bit of marketing. In my case, the latest email provider to come my way is MailChimp. MailChimp is a very popular email service provider, which was used primarily by small businesses who could not afford the typical email giants like Experian and CheetahMail. However, the industry has changed and now they service many major companies.

To do so, you will need to use the MailChimp API. The latest as of the time of writing is v2.0 and can be found here: http://apidocs.mailchimp.com/api/2.0/

On to the tips!

1. Try not to update incrementally using dates
2. Do a full list compare between the MailChimp lists and your own database
3. Re-subscribing requires 2 actions
4. Use 1 or 0 for boolean parameters
5. Remember localisation
6. Remove left and right space paddings and tab characters for email address before uploading or comparing email
7. Do not forget to handle stray emails
8. Use Webhooks to unsubscribe people at real-time
9. Remember to close the loop by unsubscribing people from your database too
10. Keep records of invalid emails – and do something about them

 

1. Try not to update incrementally using dates

Mailchimp supports incremental update by using the Since parameter in some of their API. If speed is of the essence, then this may make sense. However, from experience, there are always some records which are left out if implemented this way. My aim is for 99.9999% accuracy, so I always do a full compare, which brings me to the next point.
 

 2. Do a full list compare between the MailChimp lists and your own database

I download 3 lists from MailChimp – Subscribes, unsubscribes and cleaned, and then compare the lists with my database. Subscribers and unsubscribers must match up on both platforms. Cleaned lists might need to be actioned upon by the data owners. Simple and clean.
 

 3. Re-subscribing requires 2 actions

MailChimp does not allow an unsubscribed email address to be subscribed unless the subscriber tells MailChimp directly he wants to re-subscribe through a form. Therefore to re-subscribe someone in an automatic way, the email entry needs to be completely removed from MailChimp and the added again. This means when using the /lists/batch-unsubscribe and /lists/unsubscribe methods, you need to set the delete_member flag to true (i.e. delete_member = 0 see below)
 

 4. Use 1 or 0 for boolean parameters

I found that parameters like “double_optin” and “update_existing” in the batch-subscribe method and “delete_member” and “send_goodbye” in the batch-unsubscribe method accepts 1 or 0 as values instead of true/false.
 

 5. Remember localisation

This gave me a ton of headache. If you use a language that is not English, this will be one of the most obvious things to remember. For us English speaking natives, however, it is not so obvious to remember to localise. This means in your code, use Unicode encoding when preparing the API to send, and then decoding the response using UTF-8. This is because email addresses allow the use of international characters like umlauts before the @ sign. This also helps in the matching process when comparing MailChimp lists with your database.
 

 6. Remove left and right space paddings and tab characters for email address before uploading or comparing email

This is fairly obvious for comparing lists. In SQL Server, you can set it so that space padding is ignored, but the default is that is not ignored. To set it -> SET ANSI_PADDING OFF. A word of caution when doing this as in some cases you do want to capture the trailing blanks.

Also, there may be tab and even linefeed and carriage return characters in the data which is invisible to the naked eye. Clean them out first to prevent unnecessary headache. In C#, tab = char(9), linefeed = char(10) and carriage return = char(13).
 

 7. Do not forget to handle stray emails

This happened to me and may happen to you too. Normally, in the company database, the Primary Key is NOT the email address. Registrants can and will change their email addresses on your website now and again. HOWEVER, for MailChimp, and any email service provider for that matter, the email address IS the “Primary Key”. That is to say that everything hinges on the email address and it is therefore unique. So, when someone changes his email address, his customer ID does not change, the MailChimp subscribe list will now have both his old and new email addresses which is not correct. Therefore, add a check to see which is the current email address and delete the stray one. What I do is to compare the downloaded subscribe list with my database and those that only show up in the subscribe list will be deleted from MailChimp. As I said, I like 99.99999% accuracy.
 

 8. Use Webhooks to unsubscribe people at real-time

This is not necessary, since unsubscribers will not receive emails any more, but it is powerful. When unsubscribers log into their accounts after unsubscribing, they will immediately see that their email status has been set to false. Unsubcribing people at real-time keeps the database clean. And it impresses the business users. 😉
 

 9. Remember to close the loop by unsubscribing people from your database too

Even if you use webhooks to unsubscribe people at real-time, unsubscribing people from your database during the daily run catches any that fell through the cracks and serve as a second layer of prevention from enraging people who will still receive your emails when they specifically and purposefully unsubscribed.

Tip: Put in several filters on your unsubscribe page before unsubscribing to reduce unsubscription rate. Ask potential unsubscribers if they are sure, ask them why they are unsubscribing, or ask them if they prefer to reduce the email frequency or just subscribe to a smaller number of emails. Business owners love smaller unsubscription rates.
 

 10. Keep records of invalid emails – and do something about them

MailChimp does an amazing job providing email errors after attempting to subscribe emails. Errors thrown after subscribing are very useful. There are different error codes and they tell you what the error is. You can see the list of error codes here: http://apidocs.mailchimp.com/api/1.3/exceptions.field.php

After adding subscribers, I get the response back in XML which SQL Server parses very happily and stores them in a separate table. I then write my daily script such that it does not try to subscribe the erroneous email addresses again, so that I do not look like I am trying to do some dodgy spamming activity. Also, this list should be sent to the data owner for further action.

If you need help to get started, I have blogged about my email synchronisation process here.

 

2 thoughts on “Top 10 Tips when synchronising your database and Mailchimp

  1. MailChimp using the email address as primary key is seriously a bad choice. It’s a lack of knowledge of how you efficiently manage data. It makes it really difficult to integrate to it. They at least should have a sync option that would compare against a custom ID and update matches with the new data.

Leave a Reply

Your email address will not be published.