Creating and Updating Mailchimp List with Treasure Data

This article expplains 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


  • Basic knowledge of Treasure Data and an account (Request access)
  • Mailchimp account
  • A desire to create more engaging email campaigns with data =)

Step 1: Prepare the target list on Mailchimp’s side

It is assumed that the target list exists on your Mailchimp account. The list can be empty, but it must exist.

Also, this integration allows you to populate list with multiple groups. To use this feature, please make sure that you have groups configured on Mailchimp’s side.

Step 2: Configure Mailchimp as a result output destination

Select Mailchimp from Result Output as follows:

There are several parameters to fill out:

  • Mailchimp Apikey (required): This is Mailchimp’s API key.
  • Mailchimp List Id (required): 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 Exisitng (optional, default Yes): When toggled on, existing entries are updated with their email addresses as keys. If not, new entries are appended to the Mailchimp List.
  • column name for email (optional, default “email”): The value of this columns is used to populate the email field of the target Mailchimp List.
  • column name for first name (optional, default “fname”): The value of this columns is used to populate the first name field of the target Mailchimp List.
  • column name for last name (optional, default “lname”): The value of this columns is used to populate the last name field of the target Mailchimp List.
  • column name for group details (optional): If you have groups configured on your Mailchimp List, the values of these columns are used to populate groups in the target Mailchimp List (See below for an example). Multiple groups can be configured by separating them by “,”, i.e., if the columns “region” and “age” correspond to the groups “Region” and “Age”, “region,age” should be entered into this field.
  • Replace group (optional, default Yes): When toggled on, the value of the groups for each subscriber is replaced. Otherwise, new values are appended to the subscriber’s interest group.

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

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:


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

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

SELECT email, fname, lname, region, age FROM (
  VALUES ('', 'Kiyoto', 'Tamura', 'Asia', '41 and above'),
         ('', 'Rob', 'Parrish', 'Americas', '21-30'),
         ('', 'Michele', 'Caramello', '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:

Last modified: Oct 10 2016 17:43:30 UTC

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