MailChimp Database Synchronisation Process

After giving you the Top 10 Tips when synchronising your database with MailChimp, I thought why not write up the process as well to help you along.

Step 1: Download MailChimp Lists
Step 2: Create the Master Comparison Table
Step 3: Know the email update process
Step 4: Run the API to update Mailchimp and your database
Step 5: Monitor progress
 

 Step 1: Download MailChimp Lists

Download the subscribe, unsubscribe and cleaned lists from MailChimp using the MailChimp API.

You can view the MailChimp API here: http://apidocs.mailchimp.com/api/2.0/

How you code it depends on what language you use.
 

 Step 2: Create the Master Comparison Table

To achieve a successful comparison, I joined my database table with the 3 lists downloaded from MailChimp. Notice that I removed the leading and trailing spaces and tab characters before comparison (Top Tip 6). The unsubcribe date is added so that I can compare with the database modified date to see whether a record is to be re-subscribed or to unsubscribe from the database.

SELECT LTRIM(RTRIM(REPLACE(V.[EMAIL], char(9), ''))) AS DatabaseSubscribedEmail
,V.EmailOptIn AS DatabaseEmailOptIn
,V.LastModifiedDate AS DatabaseLastModifiedDate
,LTRIM(RTRIM(REPLACE(M.[email], char(9), ''))) AS MailchimpSubscribedEmail
,LTRIM(RTRIM(REPLACE(C.email, char(9), ''))) AS MailchimpCleanedEmail
,LTRIM(RTRIM(REPLACE(U.email, char(9), ''))) AS MailchimpUnsubscribedEmail
,U.[Date] AS MailchimpUnsubscribeDate
FROM [dbo].[DatabaseContacts] V
LEFT OUTER JOIN [dbo].[MailChimpSubscribes] M ON LTRIM(RTRIM(REPLACE(V.[EMAIL], char(9), ''))) = LTRIM(RTRIM(REPLACE(M.[email], char(9), ''))) LEFT OUTER JOIN [dbo].[MailChimpCleaned] C ON LTRIM(RTRIM(REPLACE(V.[EMAIL], char(9), ''))) = LTRIM(RTRIM(REPLACE(C.email, char(9), ''))) LEFT OUTER JOIN [dbo].[MailChimpUnsubscribes] U ON LTRIM(RTRIM(REPLACE(V.[EMAIL], char(9), ''))) = LTRIM(RTRIM(REPLACE(U.email, char(9), ''))) WHERE V.[EMAIL] <> ''

The resulting table:

Database Subscribed Email Database Email OptIn Database Last Modified Date Mailchimp Subscribed Email Mailchimp Cleaned Email Mailchimp Unsubscribed Email Mailchimp Unsubscribe Date
zzz@yahoo.com 1 26/07/2011 zzz@yahoo.com NULL NULL NULL
xxx@hotmail.com 1 07/10/2013 NULL NULL xxx@hotmail.com NULL
yyy@gmail.com 0 18/10/2012 NULL NULL NULL NULL

How do you use this? See below.
 

 Step 3: Know the email update process

I’ll list the important steps here:

  1. Unsubscribe from MailChimp – to unsubscribe customers who updated their profile online
  2. Unsubscribe from Database – to update the database with customers who unsusbcribed through email
  3. Unsubscribe Stray emails on MailChimp – See Top Tip 7
  4. Add new subscribers
  5. Add re-subscribers – if they have unsubscribed before

Here’s how to use the Master Comparison Table to achieve these steps. I’ve added the WHERE clause as well if you do a SELECT statement of the Master Comparison Table.

1. Unsubscribe from MailChimp

From the Master Comparison Table, filter out contacts which exists in MailChimp Subscribed List but have opted out of the database.

WHERE [Mailchimp Subscribed Email] IS NOT NULL  AND [Database Email OptIn] = 0

2. Unsubscribe from Database

Filter out contacts which are in the Mailchimp Unsubscribe list and is opted into the database and the MailChimp unsubscribe date is later than the database modified date.

WHERE [Mailchimp Unsubscribed Email] IS NOT NULL
AND[ Database Email OptIn] = 1
AND [Mailchimp Unsubscribe Date] > [Database Last Modified Date]

 3. Unsubscribe Stray emails on MailChimp

Filter out contacts which does not exist in the database but is still in the MailChimp subscribe list.

WHERE [Database Subscribed Email] IS NULL
AND [Mailchimp Subscribed Email] IS NOT NULL

4. Add new subscribers

Filter out contacts which is opted into the database and does not exist in any of the MailChimp lists.

WHERE [Database Subscribed Email] IS NOT NULL
AND [Mailchimp Subscribed Email] IS NULL
AND [Mailchimp Cleaned Email] IS NULL
AND [Mailchimp Unsubscribed Email] IS NULL
AND [Data Cleaned] IS NULL
AND [Database Email OptIn] = 1

5. Add re-subscribers

Filter out contacts which are unsubscribed in MailChimp but are opted into the database. The database modified date should be later than the unsubscribe date to confirm that the user has re-subscribed.

WHERE [Mailchimp Unsubscribed Email] IS NOT NULL
AND [Database Email OptIn] = 1
AND [Mailchimp Unsubscribe Date] < [Database Last Modified Date]

 

 Step 4: Run the API to update Mailchimp and your database

Run the API to update MailChimp and your database. This depends on the language used and your database setup so please search for more resources on how to achieve this. There are plenty of resources online.

Note, please try out Webhooks if you want unsubscribes to flow through to your database in real-time.
 

 Step 5: Monitor progress

Capture the response and use it to tell you more about the sync progress. I simply dumped the XML response as a BLOB into SQL Server and then use XML string manipulation to get the unsubscribe/add/update/error counts as shown below. As you can see, the 5 steps email update steps are shown in the Package column.

ID Date Package Unsubscribe count Add count Update count Error count XML Response
357 16/10/2013 05:02:41 Resubscribe – Subscribe to MailChimp 0 1 0 0 <result type=”dict”><add_count type=”integer”>1</add_count><adds type=”array”></result>
356 16/10/2013 05:02:41 Resubscribe – Delete From MailChimp 1 0 0 0 <result type=”dict”><success_count type=”integer”>1</success_count><error_count type=”integer”>0</error_count><errors type=”array” /></result>
355 16/10/2013 05:02:23 Subscribe 0 1 0 0 <result type=”dict”><add_count type=”integer”>1</add_count><adds type=”array”></result>
353 16/10/2013 05:01:33 Unsubscribe Strays From MailChimp 0 0 0 0 No unsubscribers to update
352 16/10/2013 05:01:29 Unsubscribe From MailChimp 0 0 0 0 No unsubscribers to update
348 15/10/2013 16:22:38 Unsubscribe From Database 1 0 0 0 <result><success_count>1</success_count><error_count>0</error_count></result>

This shows a good run with no errors.

It tells me at a glance how each step is doing.

  •  The Unsubscribe from MailChimp step tells me who has unsubcribed through his online profile.
  • The Unsubscribe From Database step tells me who has unsubscribed on MailChimp. A zero value shows that my webhook is working.
  • The Unsubscribe Strays from MailChimp step shows me how many stragglers there were.
  • The Subscribe step shows subscribe status.
  • The Re-subscribe step is shown in two parts – first delete the record, and then subscribe record. (Top Tip 3)

For subscribe and re-subscribe, there may be subscription errors. These are capture in the XML and then output into a separate table for further action. Tip: You can use a database trigger to insert errors into the error table when the response is inserted into the monitoring table above.

Error Table:

ID Date email code error
1 04/10/2013 10:51:48 xxx.yyy@yahoo.com -99 The username portion of the email address is invalid (the portion before the @: xxx.yyy )
3 04/10/2013 10:51:48 yyy_gmail.com -99 An email address must contain a single @
5 04/10/2013 10:51:48 spam@hotmail.com -99 This email address looks fake or invalid. Please enter a real email address
9 04/10/2013 10:51:48 zzz@bluewin.ch 220 zzz@bluewin.ch has been banned
142 04/10/2013 11:31:52 aaa@aol.com 213 aaa@aol.com has bounced, and cannot be resubscribed

Hope this helps.

One thought on “MailChimp Database Synchronisation Process

Leave a Reply

Your email address will not be published.