This article explains how to create a MailChimp list based on the data stored in Treasure Data. Use cases include:
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.
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.
- Basic knowledge of Treasure Data, including the toolbelt.
- A MailChimp account who can grant permissions to Treasure Data.
Choose saved connection
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.
After create new mailchimp connection or select an existing one, you will see the
Additional configuration popup.
|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||
|Column name for email||The value of this columns is used to populate the email field of the target MailChimp List|
|Column name for fname||The value of this columns is used to populate the fname field of the target MailChimp List||
|Column name for lname||The value of this columns is used to populate the lname field of the target MailChimp List||
|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:
|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
|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”.
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:
Region. They have the following categories:
- 41 and above
Then, back on Treasure Data, run the following query with Mailchimp as Result Export:
SELECT email, first_name, last_name, region, age FROM ( VALUES ('firstname.lastname@example.org', 'K', 'T', 'Asia', '41 and above'), ('email@example.com', 'R', 'P', 'Americas', '21-30'), ('firstname.lastname@example.org', '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
SELECT 'email@example.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