Learn more Mailchimp Import Integration.

You can create a Mailchimp list from data stored in Treasure Data. Use cases include:

  1. Personalization through Segmentation: Capture user behaviors from websites or mobile apps, collecting all user events into Treasure Data. Then, write 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 can drive growth. Identify“at-risk” users with user events stored in Treasure Data and pushed to MailChimp. Send targeted promotions to the at-risk users to re-engage them.

For sample workflows on exporting to Mailchimp, view Treasure Boxes.

Continue to the following topics:


Prerequisites

  • Basic knowledge of Treasure Data, including the TD Toolbelt.

  • A MailChimp account

  • Authorized Treasure Data account access to Mailchimp

Use the TD Console to Create Your Connection

You can use the TD Console to configure your connection. You create a new connection or if you already configured a Mailchimp data connector (an input transfer or an authentication), you can skip to Configure Export Results in Your Data Connection.

Create a New Connection

Go to Integrations Hub > Catalog and search. Locate and select the Mailchimp tile.

A dialog opens in which you provide the required credentials. Specify an Authentication Method.

Authenticating Your Connection

The method you use to authenticate Treasure Data with MailChimp affects the steps you take to enable the data connector. You can choose to authenticate using:

  • an API

  • OAuth

Using an API to Authenticate

You can specify the MailChimp API Key and credential to authorize Treasure Data access. The API key grants full access to your Mailchimp account.


Using OAuth to Authenticate

OAuth method is not supported for JP and IDCF customers.

You can select an existing OAuth connection for MailChimp from the drop-down or select the link under OAuth connection to create a new one.


Create a New OAuth Connection

When you select Click here to connect a new account, you must sign into your MailChimp account in popup window


By signing into MailChimp, you are authenticating. The action of signing into Mailchimp generates an Oauth authentication.


You are 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.


You now have an authenticated connector that you use to complete configuration for output of data in the next step.

Select Continue. Provide a name for your connector.

Configure Export Results in Your Data Connection

You create a query, or use an existing query, that retrieves data to be exported and placed into a Mailchimp list. In the query, you specify the Treasure Data Mailchimp data connector.

Create Your Query

Go to the TD Console and go to the query editor. Access or write the query that you plan to use to export data. Select Output results.

You can see a simple sample query in the following image:


Read more about writing a query for Mailchimp in the Appendix.

When you select the Output Results checkbox and select Save, The Choose Saved Connection dialog opens.

Choose Saved Connection

The Choose Saved Connection dialog opens. Type the connection name in the search box to filter and select your connection.


Select Create New Connection.

Complete Configuration Fields

Here is a sample configuration.

In this example, the default column names are overridden with “last_name” and “first_name”. Also, you can specify the Mailchimp “column name for group detail” as values. In the following example, the column names values are: “region, age”. For more information on Mailchimp groups, see the Appendix: "More About the Mailchimp Group Category".




Specify the parameters.

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 results. 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

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


Optional: Use of Scheduled Jobs for Output

You can use Scheduled Jobs with Result Output, to periodically write the output result to a target destination that you specify.

Configure Export Results in Workflow

If you work in Treasure Workflow, you can specify the use of this data connector to output data.

timezone: UTC

_export:
  td:
    database: sample_datasets

+td-result-into-target:
  td>: queries/sample.sql
  result_connection: your_connections_name
  result_settings:
    list_id: your_list_id
    email_column: email
    fname_column: first_name
    lname_column: last_name
    update_existing: true
    grouping_columns: age, region

Specify the result_settings parameters.

Parameters

Description

Default values

list_id

This is the ID of the Mailchimp list that you want to populate with Treasure Data’s query results. Here is how to look it up


update_existing

When toggled on, existing entries are updated with their email addresses as keys. If not, new entries are appended to the MailChimp List

false

email_column

The value of this columns is used to populate the email field of the target MailChimp List

email

fname_column

The value of this columns is used to populate the fname field of the target MailChimp List

fname

lname_column

The value of this columns is used to populate the lname field of the target MailChimp List

lname

merge_fields

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


grouping_columns

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_interests

When toggled on, the value of the groups for each subscriber is replaced. Otherwise, new values are appended to the subscriber’s interest group.

true

double_optin

When toggled on, each subscriber will received confirmation email and they have to confirm the subscription. Otherwise, each subscriber will be subscribed automatically

false

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.)

false

sleep_between_requests_millis

The time to wait for response from MailChimp API. This value is useful in the network issues

30000

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

Learn more about using data connectors in the workflow to export data.

Appendix

Write the Query to Populate a Mailchimp List

Here is an example MailChimp list before the query results are exported to Mailchimp:


The preceding list is 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


Back in the Treasure Data Query page, write and 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 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 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

More About the Mailchimp Group Category

In Mailchimp, from the dashboard, you can access Lists and specify group options.


In Treasure Data, you can specify the groups. The group detail names in Mailchimp are column names in the query.

  • No labels