This article explains how to create a MailChimp list based on the data stored in Arm 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.
You can use this same connection to import MailChimp data into Arm Treasure Data. See import from MailChimp.
- 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. Select an existing MailChimp connection. If you do not have a Saved Connection already setup, 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 following dialog opens.
From here, you can select Authentication Method. If you select
API Key, you must 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
Sign into your MailChimp account in popup window
You will be redirected back to the Treasure Data Connections page. Repeat the first step (Create a new connection) and choose your new OAuth connection, then finish creating your connection.
From here, you can use the new MailChimp connection in Choose Save Connection.
*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|
|Atomic Upsert?||A composite of UPDATE and INSERT operations. If the UPDATE fails because the target row does not exist, the INSERT is automatically executed. When 'yes' (true), the query results returns the job's status as 'success' when all the records are processed successfully within MailChimp. (An error indicates a target row does not exists and an INSERT is automatically executed but failed.)||No|
|The timeout expires in Milliseconds||The time to wait for response from MailChimp API. This value is 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 is 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 preceding 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 ('email@example.com', 'K', 'T', 'Asia', '41 and above'), ('firstname.lastname@example.org', 'R', 'P', 'Americas', '21-30'), ('email@example.com', 'M', 'C', 'EMEA', '41 and above') ) tbl (email, first_name, last_name, region, age)
The preceding query requires no source table (for the ease of testing out this feature), but you still must 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
If you have a MERGE field that is using the type
address, you must put the values into a JSON type. The following query with MailChimp exports data with the MERGE field’s type
SELECT 'firstname.lastname@example.org' 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