MailChimp Responder user guide - snowplow-archive/sauna GitHub Wiki

HOME > GUIDE FOR ANALYSTS > RESPONDERS > MAILCHIMP RESPONDER USER GUIDE

This responder has not yet been implemented. If you are interested in contributing this or sponsoring us to build this, please get in touch.

See also: MailChimp Responder setup guide

• 1. Overview
• 2. Responder actions
  • 2.1 Upload subscribers (batch)
    • 2.1.1 Overview
    • 2.1.2 File landing path
    • 2.1.3 File input format
    • 2.1.4 Response algorithm
      • 2.1.4.1 Splitting into groups of 1,000
      • 2.1.4.2 Making the batch operation requests
      • 2.1.4.3 Handling failed operations
    • 2.1.5 Troubleshooting
    • 2.1.6 Usage examples
      • 2.1.6.1 Local folder
      • 2.1.6.2 Redshift UNLOAD
      • 2.1.6.3 Implementation

This Sauna responder lets you export user-level data from your event warehouse and upload this data to MailChimp for use in email marketing.

Currently this responder only supports one action:

This responder path lets you upload email subscribers into one or more MailChimp lists. This responder path uses the MailChimp Lists API, with MailChimp's [batch operations][mailchimp-batch-operations] capability for improved performance.

The format for the path where files should land is:

<sauna_landing_root>/com.mailchimp/lists/v1/tsv:<attribute_1>,<attribute_2>,<attribute_3>,.../<sub_folders/...>

Notes on this:

• Your <sauna_landing_root> will be an S3 bucket/path or local folder as appropriate
• com.mailchimp/lists specifies our responder action
• This is v1, version 1, of this responder path
• Currently tsv (for tab-separated values) is the only supported input format
• You must list each of the fields you want to upload after the tsv: prefix. You must include the list_id, email_address and status fields
• For the full list of supported attributes see "Request body parameters" under "Add a new list member"
• The location attributes should be prefixed with location., e.g. location.latitude, location.country_code
• Any attribute names which are not recognized will be treated as custom aka "merge" fields
• You can add as many sub-folders as required to prevent clashes with other Sauna users in your organization

At the responder path, you must upload files which contain:

• Tab (\t)-separated values encoded in double quotation marks (")
• Rows separated by newlines (\n)

Fields should be as follows:

"<attribute_1>"\t"<attribute_2>"\t"<attribute_3>"\t"<...>"\n

Where:

• The order of <attribute>s exactly matches the order given in your tsv: file landing path
• One of the columns must be for the list_id, one for email_address, and another must be for the status
• For the full list of supported attributes, and their expected data types, see "Request body parameters" under "Add a new list member"
• A subscriber's interests should be provided as a comma-separated string, like "9143cf3bd1,3a2a927344,f9c8f5f0ff"

Sauna will take the file(s) found at the file landing path and:

1. Read each file and split it into batches of 1,000 subscribers
2. For each group, make a [Batch Operations Request][mailchimp-batch-operations] containing POST requests to the /lists/{list_id}/members API endpoint
3. Transparently respect MailChimp rate limiting
4. Parse the MailChimp response to identify any failed rows and record those to the <sauna_failure_path> (out of scope for Sauna v1)

Let's go through each of these steps in turn:

Sauna will split each file into groups of 1,000 email subscribers. Manoj we can adjust this 1,000 number - it is arbitrary.

For each group of subscribers, Sauna will make a [Batch Operations Request][mailchimp-batch-operations] containing 1,000 POST requests to the /lists/{list_id}/members API endpoint.

In terms of specific type transformations:

• Date and timestamps will be converted into "yyyy-MM-dd HH:mm:ss" (e.g. 2015-09-16 19:24:29)
• The comma-separated list of interests will be converted into a JSON object where each interest is the key and the value is the boolean true
• Any attribute names which are not recognized will be treated as custom aka "merge" fields, with their values treated as strings

Because Batch Operations Requests are asynchronous, Sauna will poll the /batches/{batch_id} endpoint until the batch of operations has been completed.

MailChimp's response to each Batch Operations Request reports on a count of failures in the batch and log the failed operations.

Make sure that your listing of attributes in your file landing path exactly matches the column order of your exported data.

Don't attempt to reformat your timestamps into the format of the attribute's datetime; leave it as-is and Sauna will handle the conversion.

Assuming that your Sauna root is set to local folder /opt/sauna, and the file lands as:

/opt/sauna/com.mailchimp/lists/v1/tsv:list_id,email_address,status,timestamp_signup,FNAME,LNAME,interests/ua-team/joe/warehouse.tsv

The contents of warehouse.tsv are as follows:

 "57afe96172" "[email protected]" "subscribed"  "2013-12-15 14:05:06.789" ""  ""  "Bob"  "Brown"	"9143cf3bd1,3a2a927344,f9c8f5f0ff"
 "57afe96172" "[email protected]" "cleaned"  "2014-06-10 21:48:32.712"  "51.5074" "-0.1278" "Karl"  "Miller"	""
 "57afe96172" "[email protected]" "pending"  "2015-01-28 07:32:16.329"  "-33.8651"  "151.2099"  "Edward"  "Smith"	"f231b09abc"

Here we can see that Joe in the User Acquisition team wants to upload three contacts to MailChimp as email subscribers.

Some notes on data formats:

• The timestamp format must be ISO 8601 with milliseconds but no timezone information
• Use an empty string "" for a null value

UNLOAD is a Redshift SQL command which lets you export the contents of a SELECT statement to Amazon S3 for further processing.

Assuming that your Sauna root is set to S3 folder s3://my-sauna-bucket/prod and you run the following SQL:

CREATE TABLE email_subscribers (
  list_id          varchar(256) NOT NULL,
  email            varchar(256) NOT NULL,
  status           varchar(12)  NOT NULL,
  timestamp_signup timestamp    NOT NULL,
  interests        varchar(256) NOT NULL,
  latitude         double       NULL,
  longitude        double       NULL,
  FNAME            varchar(256) NULL,
  LNAME            varchar(256) NULL,
  CUST_FIELD3      varchar(256) NULL
);

INSERT INTO email_subscribers VALUES
  ('57afe96172', '[email protected]', 'subscribed', '2013-12-15 14:05:06.789', '9143cf3bd1,3a2a927344,f9c8f5f0ff', NULL, NULL, 'Bob', 'Brown', NULL),
  ('57afe96172', '[email protected]', 'cleaned', '2014-06-10 21:48:32.712', NULL, 'Karl', 'Miller', 51.5074, -0.1278, 'London'),
  ('57afe96172', '[email protected]', 'pending', '2015-01-28 07:32:16.329', 'f231b09abc', -33.8651, 151.2099, 'Edward', 'Smith', 'Sydney');

UNLOAD ('select list_id, email, status, timestamp_signup, interests, FNAME, LNAME from email_subscribers')
  TO 's3://my-sauna-bucket/prod/com.mailchimp.contactdb/subscribers/v1/tsv:list_id,email_address,status,timestamp_signup,interests,location.latitude,location.longitude,FNAME,LNAME/ua-team/joe/'
  CREDENTIALS 'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
  DELIMITER AS '\t'
  ADDQUOTES
  PARALLEL OFF;

Again, this will export 3 customer profiles in a TSV to Amazon S3, where Sauna is waiting to detect this file landing and upload to MailChimp. Some notes:

• The order of our fields in UNLOAD ('select must match the order given in the tsv:... file landing path
• Although we have the CUST_FIELD3 column defined in our email_subscribers table, we choose not to upload it to MailChimp
• Note that the column email must be referred to as email_address in the Sauna landing path (tsv:email,...)
• Note that the location attributes are prepended with location. to identify them as such

From the example above, Sauna will make the following POST call to MailChimp, creating three new subscribers in the MailChimp Contacts Database:

$ curl --request POST \
--url 'https://usX.api.mailchimp.com/3.0/batches' \
--user 'anystring:apikey' \
--header 'content-type: application/json' \
--data '{"operations" : [{"method" : "POST", "path" : "lists/57afe96172/members", "body": "{\"email_address\":\"[email protected]\",\"status\":\"subscribed\",\"timestamp_signup\":\"2013-12-15 14:05:06\",\"interests\":{\"9143cf3bd1\":true,\"3a2a927344\":true,\"f9c8f5f0ff\":true},\"merge_fields\":{\"FNAME\":\"Bob\",\"LNAME\":\"Brown\"}}"}, {"method" : "POST", "path" : "lists/57afe96172/members", "body": "{\"email_address\":\"[email protected]\",\"status\":\"cleaned\",\"timestamp_signup\":\"2014-06-10 21:48:32\",\"location\":{\"latitude\":51.5074,\"longitude\":-0.1278},\"merge_fields\":{\"FNAME\":\"Karl\",\"LNAME\":\"Miller\"}}"}, {"method" : "POST", "path" : "lists/57afe96172/members", "body": "{\"email_address\":\"[email protected]\",\"status\":\"pending\",\"timestamp_signup\":\"2015-01-28 07:32:16\",\"interests\":{\"f231b09abc\":true},\"location\":{\"latitude\":-33.8651,\"longitude\":151.2099},\"merge_fields\":{\"FNAME\":\"Edward\",\"LNAME\":\"Smith\"}}"}]}' \
--include

Here are the three JSON bodies, pretty-printed:

{
  "email_address": "[email protected]",
  "status": "subscribed",
  "timestamp_signup": "2013-12-15 14:05:06",
  "interests": {
    "9143cf3bd1": true,
    "3a2a927344": true,
    "f9c8f5f0ff": true
  },
  "merge_fields": {
    "FNAME": "Bob",
    "LNAME": "Brown"
  }
}

{
  "email_address": "[email protected]",
  "status": "cleaned",
  "timestamp_signup": "2014-06-10 21:48:32",
  "location": {
    "latitude": 51.5074,
    "longitude": -0.1278
  },
  "merge_fields": {
    "FNAME": "Karl",
    "LNAME": "Miller"
  }
}

{
  "email_address": "[email protected]",
  "status": "pending",
  "timestamp_signup": "2015-01-28 07:32:16",
  "interests": {
    "f231b09abc": true
  },
  "location": {
    "latitude": -33.8651,
    "longitude": 151.2099
  },
  "merge_fields": {
    "FNAME": "Edward",
    "LNAME": "Smith"
  }
}