Creating and Updating MailChimp List with Treasure Data

This article explains how to create a MailChimp list based on the data stored in Treasure Data. Use cases include:

  1. Personalization through Segmentation: For web and mobile products, user behaviors are best captured in the apps themselves. Collect user events into Treasure Data, write the personalization logic in SQL, and export the segmented mailing list to Mailchimp for targeted campaigns.
  2. Customer Retention: For SaaS and subscription e-commerce businesses, customer retention is a key driver for growth. Using Treasure Data with Mailchimp, “at-risk” users can be identified with user events stored in Treasure Data and pushed to Mailchimp. Then, send targeted promotions to these at-risk users to re-engage them.

Table of Contents

Prerequisites

  • Basic knowledge of Treasure Data, including the toolbelt.
  • A MailChimp account who can grant permissions to Treasure Data.

Usage

Visit Treasure Data console, go to query editor, and enter your query into the query editor. Click on Output results.



Choose saved connection

A dialog Choose Saved Connection will be displayed. You will need to select an existing mailchimp connection (see image below). If you do not have a Saved Connection already setup, please follow the next step on how to create a new connection within the Sources Catalog.



Create a new connection

Visit Treasure Data Connections search and select MailChimp. The dialog below will open



From here, you can select Authentication Method. If you select API Key, you have to enter your API Key.

Otherwise, you can select an existing OAuth connection for MailChimp, or click the link under OAuth connection to create a new one.



Create a new OAuth connection

Please sign in to your MailChimp account in popup window



You will be redirected back to the Treasure Data Connections page. Please repeat the first step (Create a new connection) and choose your new OAuth connection, then finish creating your connection.



From here, you will be able to use the new mailchimp connection in Choose Save Connection.

*Please note that OAuth method is currently not supported for JP and IDCF customers.

Additional configuration

After create new mailchimp connection or select an existing one, you will see the Additional configuration popup.

Parameters Description Default values
MailChimp list ID This is the ID of the Mailchimp list that you want to populate with Treasure Data’s query result. Here is how to look it up
Update existing entries? When toggled on, existing entries are updated with their email addresses as keys. If not, new entries are appended to the MailChimp List Yes
Column name for email The value of this columns is used to populate the email field of the target MailChimp List email
Column name for fname The value of this columns is used to populate the fname field of the target MailChimp List fname
Column name for lname The value of this columns is used to populate the lname field of the target MailChimp List lname
Additional MailChimp fields and MERGE tags The values of additional merge fields are used to populate groups in the target MailChimp List. Multiple fields can be configured by separating them by “,”. E.g: WEBSITE,GENDER
Group categories The values of interest categories are used to populate groups in the target MailChimp List. Multiple groups can be configured by separating them by “,”. E.g interests,location
Replace group? When toggled on, the value of the groups for each subscriber is replaced. Otherwise, new values are appended to the subscriber’s interest group. Yes
Double opt-in? When toggled on, each subscriber will received confirmation email and they have to confirm the subscription. Otherwise, each subscriber will be subscribed automatically No
The timeout expires in Milliseconds The time to wait for response from MailChimp API. This value will be useful in the network issues 60000
Max records per request The max records per batch request from MailChimp API. MailChimp API enables max records is 500 per batch request. This value will be useful when you upload the large data. 500

Here is a sample configuration: Note that the default column names have been explicitly overridden with “email”, “last_name” and “first_name”.

And here is a sample value in MailChimp dashboard

Also, although you cannot see it, the “column name for group detail” has been configured with the value “region,age”.

Step 3: Write the Query to Populate a Mailchimp List

Here is an example Mailchimp list before outputting a query result:

The above list has been configured with two Mailchimp Groups: Age and Region. They have the following categories:

Age

  • 0-20
  • 21-30
  • 31-40
  • 41 and above
Region
  • Americas
  • Asia
  • EMEA

Then, back on Treasure Data, run the following query with Mailchimp as Result Export:

SELECT email, first_name, last_name, region, age FROM (
  VALUES ('k@gmail.com', 'K', 'T', 'Asia', '41 and above'),
         ('r@gmail.com', 'R', 'P', 'Americas', '21-30'),
         ('m@gmail.com', 'M', 'C', 'EMEA', '41 and above')
)  tbl (email, first_name, last_name, region, age)

The above query requires no source table (for the ease of testing out this feature), but you still need to choose your database, so pick “sample_datasets” or any other arbitrary table. Also, make sure that Presto is chosen as the SQL dialect.

The query should complete in a few seconds. After that, check Mailchimp’s List:

MERGE field’s type is address

In case you have a MERGE field that is using the type address, you need to put the values into a JSON string. The following query with MailChimp will export data with the MERGE field’s type address:

SELECT
  'y@gmail.com' as email,
  'y' as fname, 'L' as lname,
  CAST(MAP(ARRAY['addr1', 'city', 'state', 'zip', 'country'], ARRAY['1234', 'mountain view', 'CA', '95869', 'US']) as JSON) as address,
  'US' as location

Last modified: Oct 03 2017 03:45:08 UTC

If this article is incorrect or outdated, or omits critical information, let us know. For all other issues, access our support channels.