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
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/
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|
Step 3: Know the email update process
I’ll list the important steps here:
- Unsubscribe from MailChimp – to unsubscribe customers who updated their profile online
- Unsubscribe from Database – to update the database with customers who unsusbcribed through email
- Unsubscribe Stray emails on MailChimp – See Top Tip 7
- Add new subscribers
- 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.
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.
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.
|1||04/10/2013 10:51:email@example.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:firstname.lastname@example.org||-99||This email address looks fake or invalid. Please enter a real email address|
|9||04/10/2013 10:51:email@example.comfirstname.lastname@example.org has been banned|
|142||04/10/2013 11:31:email@example.comfirstname.lastname@example.org has bounced, and cannot be resubscribed|
Hope this helps.