Skip to Content

Mailchimp Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8895 of the connector.

Get Started

Mailchimp Version Support

The connector defaults to version 3 of the Core Mailchimp API.

Establish a Connection

Connect to Mailchimp

Mailchimp supports the following authentication methods:

  • APIKey
  • OAuth
API Key

The easiest way to connect to Mailchimp is to use the API Key. The APIKey grants full access to your Mailchimp account. To obtain the APIKey:

  1. Log into Mailchimp.
  2. Navigate to Account > Extras > API Keys.
  3. Note the value of the API Key.

Once you have the value of the API Key:

  1. Set APIKey to the value of the API Key.
  2. Set AuthScheme to APIKey.
OAuth
Desktop Applications

To authenticate with the credentials for a custom OAuth application, you must get and refresh the OAuth access token. After you do that, you are ready to connect.

Get and refresh the OAuth access token:

  • InitiateOAuth = GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
  • OAuthClientId = the client ID assigned when you registered your application.
  • OAuthClientSecret = the client secret that was assigned when you registered your application.
  • CallbackURL = the redirect URI that was defined when you registered your application.

When you connect, the connector opens Mailchimp's OAuth endpoint in your default browser. Log in and grant permissions to the application.

After you grant permissions to the application, the connector then completes the OAuth process:

  1. The connector obtains an access token from Mailchimp and uses it to request data.
  2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes the access token automatically.

Create a Custom OAuth Application

Create a Custom OAuth Application

There are two authentication methods available for connecting to Mailchimp: You can use the APIKey or use OAuth.

OAuth can be used to enable other users to access their own data. It is also useful if you want to:

  • control branding of the authentication dialog;
  • control the redirect URI that the application redirects the user to after the user authenticates; or
  • customize the permissions that you are requesting from the user.

To register a custom OAuth application in Mailchimp and obtain the OAuth client credentials, the OAuthClientId and OAuthClientSecret:

  1. Log into your Mailchimp account.

  2. Navigate to Account > Extras > API Keys > Register and Manage Your Apps.

  3. Enter the information you want to be displayed to users when they are prompted to grant permissions to your application. This information includes your app name, company, and website.

  4. If this is a Desktop application, specify a Redirect URI of http://127.0.0.1.

    If this is a Web application, specify a Redirect URI where you would like users to be redirected after they grant permissions to your application.

After you have created and registered a custom OAuth app, users can connect to Mailchimp as described in "Connecting to Mailchimp".

Important Notes

Configuration Files and Their Paths

  • All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Mailchimp connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Mailchimp and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views

The Mailchimp connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

  • Create a JSON-formatted configuration file defining the views you want.
  • DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Lists WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

The Data Model has three parts: Tables, Views, and Stored Procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

The Mailchimp connector models the Mailchimp API in Tables so that it can be easily queried and updated.

The connector dynamically retrieves custom fields for the ListMembers tables when you connect; any changes you make to these custom fields, such as adding a new field or changing a custom field's data type, are reflected when you reconnect.

Dynamic tables

Along with the default static tables, the connector also allows querying on dynamic tables. These are tables that are created based on the "audiences" (also called "lists") in your Mailchimp account.
For example, suppose you have these 3 audiences in your account: Old Audience, New Audience, VIP Audience. For each of these audiences the connector creates 2 new tables: One starting with "ListMembers_" and the other with "ListMergeFields_". So, for the case in hand, these 6 tables will be created:

  • ListMembers_OldAudience
  • ListMembers_NewAudience
  • ListMembers_VIPAudience
  • ListMergeFields_OldAudience
  • ListMergeFields_NewAudience
  • ListMergeFields_VIPAudience

The above 6 tables are created by removing spaces from the audience's name and appending the result to "ListMembers" or "ListMergeFields" with an underscore.

Tables starting with "ListMembers_" display all members for a specific audience along with custom fields' values.
The ones starting with "ListMergeFields_" display all custom fields' names for members in that audience.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Dynamic views

Along with the default static views, the connector also allows querying on dynamic views. These are views that are created based on the "audiences" (also called "lists") in your Mailchimp account.
For example, suppose you have these 3 audiences in your account: Old Audience, New Audience, VIP Audience. The connector lists 3 views based on them: ListMemberTags_OldAudience, ListMemberTags_NewAudience, ListMemberTags_VIPAudience.
The above 3 views are created by removing spaces from the audience's name and appending the result to "ListMemberTags" with an underscore. They return the tags that are assigned to each member of the audience you've specified.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information

in the data source.

Tables

The connector models the data in Mailchimp as a list of tables in a relational database that can be queried using standard SQL statements.

Mailchimp Connector Tables

Name Description
AutomationEmailQueues A summary of the queue for an email in an automation workflow.
AutomationsRemovedSubscribers A summary of the subscribers removed from an automation workflow.
CampaignFeedback A summary of the comment feedback for a specific campaign.
CampaignFolders Folders for organizing campaigns
Campaigns A summary of the campaigns within an account.
EcommerceCartLines A list of an ecommerce cart's lines.
EcommerceCarts A list of an account's ecommerce carts.
EcommerceCustomers A list of an account's ecommerce customers.
EcommerceOrderLines A list of an ecommerce order's lines.
EcommerceOrders A list of an account's ecommerce orders.
EcommerceProducts A list of an account's ecommerce products.
EcommerceProductVariants A list of an ecommerce product's variants.
FileManagerFiles A listing of all avaialable images and files within an account's gallery.
FileManagerFolders A listing of all avaialable folders within an account's gallery.
ListInterestCategories A listing of this list's interest categories.
ListInterests A list of this category's interests
ListMemberEvents Events information for a specific list.
ListMemberNotes The last 10 notes for a specific list member, based on date created.
ListMembers Individuals who are currently or have been previously suscribed to this list, including members who have bounced or unsubscribed.
ListMemberTags Tags assigned to a certain member/members.
ListMergeFields The merge field (formerly merge vars) for a given list. These correspond to merge fields in MailChimp's lists and subscriber profiles.
Lists A collection of subscriber lists associated with this account. Lists contain subscribers who have opted-in to receive correspondence from you or your organization.
ListSegmentMembers Individuals who are currently or have been previously suscribed to this list, including members who have bounced or unsubscribed.
ListSegments A list of available segments.
ListsWebhooks Webhooks configured for the given list.
TemplateFolders Folders for organizing templates
Templates A list an account's available templates.

AutomationEmailQueues

A summary of the queue for an email in an automation workflow.

Table Specific Information

SELECT and INSERT are supported for AutomationEmailQueues.

Select
Select * from AutomationEmailQueues
Insert

The WorkflowId, EmailId, and EmailAddress are required for INSERTs.

INSERT INTO AutomationEmailQueues (WorkflowId, EmailId, EmailAddress) VALUES ('myWorkflowId', 'myEmailId', 'myEmailAddress')
Columns
Name Type ReadOnly Description
Id [KEY] String True The MD5 hash of the lowercase version of the list member's email address.
WorkflowId [KEY] String False A string that uniquely identifies an automation workflow.
EmailId [KEY] String False A string that uniquely identifies an email in an automation workflow.
ListId [KEY] String True A string that uniquely identifies a list.
EmailAddress String False Email Address
NextSend String True Next Send

AutomationsRemovedSubscribers

A summary of the subscribers removed from an automation workflow.

Table Specific Information

SELECT and INSERT are supported for AutomationsRemovedSubscribers.

Select
Select * from AutomationsRemovedSubscribers
Insert

The WorkflowId, and EmailAddress are required for INSERTs.

Insert Into AutomationsRemovedSubscribers (WorkflowId, EmailAddress) Values('3456df3456','test1@mail.com');
Columns
Name Type ReadOnly Description
Id [KEY] String True The MD5 hash of the lowercase version of the list member's email address.
WorkflowId [KEY] String False A string that uniquely identifies an automation workflow.
ListId [KEY] String True A string that uniquely identifies a list.
EmailAddress String False Email Address

CampaignFeedback

A summary of the comment feedback for a specific campaign.

Table Specific Information

SELECT, INSERT, UPDATE, and DELTE are supported for CampaignFeedback.

Select
Select * from CampaignFeedback
Insert

The CampaignId and Message are required for INSERTs.

INSERT INTO CampaignFeedback (CampaignId, Message) VALUES ('myCampaignId', 'myMessage')
Columns
Name Type ReadOnly Description
FeedbackId [KEY] Integer True The individual ID for the feedback item.
ParentId [KEY] Integer True If a reply, the ID of the parent feedback item.
BlockId [KEY] Integer False The block ID for the editable block that the feedback addresses.
Message String False The content of the feedback.
IsComplete Boolean False The status of feedback.
CreatedBy String True The login name of the user who created the feedback.
CreatedAt Datetime True The date and time the feedback item was created.
UpdatedAt Datetime True The date and time the feedback was last updated.
Source String True The source of the feedback ('email', 'sms', 'web', 'ios', 'android', or 'api').
CampaignId [KEY] String False The unique ID for the campaign.

CampaignFolders

Folders for organizing campaigns

Columns
Name Type ReadOnly Description
Id [KEY] String True A string that uniquely identifieds this campaign folder
Name String False The name of the folder
Count Integer True The number of campaigns in the folder

Campaigns

A summary of the campaigns within an account.

Table Specific Information

SELECT, UPDATE and DELETE are supported for Campaigns.

Update
UPDATE Campaigns SET Settings_Title = "Test" WHERE Id = "1234"

UPDATE Campaigns SET Recipients_SegmentOpts = "{"match":"any","saved_segment_id":314699}" WHERE Id = "cfb12c2228"


UPDATE Campaigns SET Settings_Title = "Test", Recipients_ListId = "1234", RssOpts_FeedUrl = "exampleUrl", Type = "rss", RssOpts_Frequency = "daily" WHERE ID = "1234"

Note: UPDATE operation cannot be performed on already SENT campaigns. Also, the type of a campaign cannot be updated once it is set. Depending on the campaign type, specific options can be updateable only for specific campaign types. For example: If a campaign is of type "rss" then only the Rss Options fields can be updateable for this campaign. Variant and AbSplitOps settings will not be updateable in this case.

Columns
Name Type ReadOnly Description
Id [KEY] String True A string that uniquely identifies this campaign.
Type String False The type of campaign (regular, plaintext, absplit, or rss).
CreateTime Datetime True The date and time the campaign was created.
ArchiveUrl String True The link to the campaign's archive version.
LongArchiveUrl String True The original link to the campaign's archive version.
Status String True The current status of the campaign ('save', 'paused', 'schedule', 'sending', 'sent').
EmailsSent Integer True The total number of emails sent for this campaign.
SendTime Datetime True The time and date a campaign was sent.
ContentType String False How the campaign's content is put together ('template', 'drag_and_drop', 'html', 'url').
Recipients_ListId String False The ID of the list.
Recipients_ListName String True The name of the list.
Recipients_SegmentText String False A string marked-up with HTML explaining the segment used for the campaign in plain English.
Recipients_RecipientCount Integer True Count of the recipients on the associated list. Formatted as an integer
Recipients_SegmentOpts String False Segment options.
Settings_SubjectLine String False The subject line for the campaign.
Settings_Title String False The title of the campaign.
Settings_FromName String False The 'from' name on the campaign (not an email address).
Settings_ReplyTo String False The reply-to email address for the campaign.
Settings_UseConversation Boolean False Use MailChimp Conversation feature to manage out of office replies.
Settings_ToName String False The campaign's custom 'to' name. Typically something like the first name merge var.
Settings_FolderId String False If the campaign is listed in a folder, the ID for that folder.
Settings_Authenticate Boolean False Whether or not the campaign was authenticated by MailChimp. Defaults to 'true'.
Settings_AutoFooter Boolean False Automatically append MailChimp's default footer to the campaign.
Settings_InlineCss Boolean False Automatically inline the CSS included with the campaign content.
Settings_AutoTweet Boolean False Automatically tweet a link to the campaign archive page when the campaign is sent.
Settings_AutoFbPost String False An array of Facebook page ids to auto-post to.
Settings_FbComments Boolean False Allows Facebook comments on the campaign (also force-enables the Campaign Archive toolbar). Defaults to 'true'.
Settings_Timewarp Boolean True Send this campaign using 'timewarp.' For more info, see the Knowledge Base article: http://eepurl.com/iAgs
Settings_TemplateId Integer False The ID for the template used in this campaign.
Settings_DragAndDrop Boolean True Whether the campaign uses the drag-and-drop editor.
VariateSettings_WinningCombinationId String True ID of the combination that was chosen as the winner
VariateSettings_WinningCampaignId String True ID of the campaign that was sent to the remaining recipients based on the winning combination
VariateSettings_WinnerCriteria String False How the winning campaign will be chosen
VariateSettings_WaitTime Integer False The number of minutes to wait before the winning campaign is picked
VariateSettings_TestSize Integer False The percentage of subscribers to send the test combinations to, from 10 to 100
VariateSettings_SubjectLines String False Possible subject lines
VariateSettings_SendTimes String False Possible send times
VariateSettings_FromNames String False Possible from names
VariateSettings_ReplyToAddresses String False Possible reply To addresses
VariateSettings_Contents String True Descriptions of possible email contents
VariateSettings_Combinations String True Combinations of possible variables that were used to build emails
Tracking_Opens Boolean False Whether to track opens. Defaults to 'true'.
Tracking_HtmlClicks Boolean False Whether to track clicks in the HTML version of the campaign. Defaults to 'true'.
Tracking_TextClicks Boolean False Whether to track clicks in the plain-text version of the campaign. Defaults to 'true'.
Tracking_GoalTracking Boolean False Whether to enable Goal tracking. For more information, see this Knowledge Base article: http://eepurl.com/GPMdH
Tracking_Ecomm360 Boolean False Whether to enable eCommerce360 tracking.
Tracking_GoogleAnalytics String False The custom slug for Google Analytics tracking (max of 50 bytes).
Tracking_Clicktale String False The custom slug for ClickTale Analytics tracking (max of 50 bytes).
Tracking_Salesforce String False Salesforce tracking options for a campaign. Must be using MailChimp's built-in Salesforce integration.
Tracking_Highrise String False Highrise tracking options for a campaign. Must be using MailChimp's built-in Highrise integration.
Tracking_Capsule String False Capsule tracking option sfor a campaign. Must be using MailChimp's built-in Capsule integration.
RssOpts_FeedUrl String False The URL for the RSS feed.
RssOpts_Frequency String False The frequency of the RSS-to-Email campaign ('daily', 'weekly', 'monthly').
RssOpts_Schedule String False The schedule for sending the RSS campaign.
RssOpts_LastSent String True The date the campaign was last sent.
RssOpts_ConstrainRssImg Boolean False If true we will add css to images in the rss feed to constrain their width in the campaign content.
AbSplitOpts_SplitTest String False The type of AB split to run ('subject', 'from_name', or 'schedule').
AbSplitOpts_PickWinner String False How we should evaluate a winner. Based on 'opens', 'clicks', or 'manual'.
AbSplitOpts_WaitUnits String False How unit of time for measuring the winner ('hours' or 'days'). This cannot be changed after a campaign is sent.
AbSplitOpts_WaitTime Integer False The amount of time to wait before picking a winner. This cannot be changed after a campaign is sent.
AbSplitOpts_SplitSize Integer False The size of the split groups. Campaigns split based on 'schedule' are forced to have a 50/50 split. Valid split integers are between 1-50. Ex. A 10% split would result in two groups of 10% of the subscribers plus a winner sending to the remaining 80%.
AbSplitOpts_FromNameA String False For campaigns split on 'From Name', the name for Group A.
AbSplitOpts_FromNameB String False For campaigns split on 'From Name', the name for Group B.
AbSplitOpts_ReplyEmailA String False For campaigns split on 'From Name', the reply-to address for Group A.
AbSplitOpts_ReplyEmailB String False For campaigns split on 'From Name', the reply-to address for Group B.
AbSplitOpts_SubjectA String False For campaings split on 'Subject Line', the subject line for Group A.
AbSplitOpts_SubjectB String False For campaings split on 'Subject Line', the subject line for Group B.
AbSplitOpts_SendTimeA Datetime False The send time for Group A.
AbSplitOpts_SendTimeB Datetime False The send time for Group B.
AbSplitOpts_SendTimeWinner Datetime False The send time for the winning version.
SocialCard_ImageUrl String False The URL for the header image for the card.
SocialCard_Description String False A short summary of the campaign to display.
SocialCard_Title String False The title for the card. Typically the subject line of the campaign.
ReportSummary String False For sent campaigns, a summary of opens, clicks, and unsubscribes.
DeliveryStatus String False Updates on campaigns in the process of sending.

EcommerceCartLines

A list of an ecommerce cart's lines.

Table Specific Information
Select

The connector will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

The StoreId can be retrieved by selecting the ECommerceStores view.

  • StoreId supports the = operator.
  • CartId supports the = operator.
  • Id supports the = operator.

For example:

SELECT * FROM EcommerceCartLines WHERE StoreId = 'Test_Store123' and CartId = '44'

SELECT * FROM EcommerceCartLines WHERE StoreId = 'Test_Store123' and CartId = '44' and ID = '88'
Delete

Note : API will throw error if the cart contains only one line item. You will have to delete the cart to delete all the lines.

DELETE FROM EcommerceCartLines WHERE StoreId = 'Test_Store123' and CartId = '44' and ID = '88'
Columns
Name Type ReadOnly Description
StoreId [KEY] String False The StoreId for the table.
CartId [KEY] String False The CartId for the table.
Id [KEY] String False A unique identifier for the cart line item.
ProductId [KEY] String False A unique identifier for the product associated with the cart line item.
ProductTitle String True The name of the product for the cart line item.
ProductVariantId [KEY] String False A unique identifier for the product variant associated with the cart line item.
ProductVariantTitle String True The name of the product variant for the cart line item.
Quantity Integer False The quantity of a cart line item.
Price Decimal False The price of a cart line item.

EcommerceCarts

A list of an account's ecommerce carts.

Table Specific Information
Select

The connector will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

The StoreId can be retrieved by selecting the ECommerceStores view.

  • StoreId supports the = operator.
  • Id supports the = operator.

For example:

SELECT * FROM EcommerceCarts WHERE StoreId = 'Test_Store123'

SELECT * FROM EcommerceCarts WHERE StoreId = 'Test_Store123' and Id = '44'
Columns
Name Type ReadOnly Description
StoreId [KEY] String False The StoreId for the table.
Id [KEY] String False A unique identifier for the cart.
Customer String False Information about a specific customer. Carts for existing customers should include only the ID parameter in the customer object body.
CampaignId [KEY] String False A string that uniquely identifies the campaign associated with a cart.
CheckoutUrl String False The URL for the cart.
CurrencyCode String False The three-letter ISO 4217 code for the currency that the cart uses.
OrderTotal Decimal False The order total for the cart.
TaxTotal Decimal False The total tax for the cart.
Lines String False An array of the cart's line items. The column will not work for Update. Lines can be updated using EcommerceCartLines table.
CreatedAt Datetime True The date and time when the cart was created.
UpdatedAt Datetime True The date and time when the cart was last updated.

EcommerceCustomers

A list of an account's ecommerce customers.

Table Specific Information
Select

The connector will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

The StoreId can be retrieved by selecting the ECommerceStores view.

  • StoreId supports the = operator.
  • Id supports the = operator.

For example:

SELECT * FROM EcommerceCustomers WHERE StoreId = 'Test_Store123'

SELECT * FROM EcommerceCustomers WHERE StoreId = 'Test_Store123' and ID = '44'
Columns
Name Type ReadOnly Description
StoreId [KEY] String False The StoreId for the table.
Id [KEY] String False A unique identifier for the customer.
EmailAddress String False The customer's email address.
OptInStatus Boolean False The customer's opt-in status. This value will never overwrite the opt-in status of a pre-existing MailChimp list member, but will apply to list members that are added through the e-commerce API endpoints.
Company String False The customer's company.
FirstName String False The customer's first name.
LastName String False The customer's last name.
OrdersCount Integer True The customer's total order count.
TotalSpent Decimal True The total amount the customer has spent.
Address_Address1 String False The mailing address of the customer.
Address_Address2 String False An additional field for the customer's mailing address.
Address_City String False The city the customer is located in.
Address_Province String False The customer's state name or normalized province.
Address_ProvinceCode String False The two-letter code for the customer's province or state.
Address_PostalCode String False The customer's postal or zip code.
Address_Country String False The customer's country.
Address_CountryCode String False The two-letter code for the customer's country.
CreatedAt Datetime True The date and time the customer was created.
UpdatedAt Datetime True The date and time the customer was last updated.

EcommerceOrderLines

A list of an ecommerce order's lines.

Table Specific Information
Select

The connector will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

The StoreId can be retrieved by selecting the ECommerceStores view.

  • StoreId supports the = operator.
  • OrderId supports the = operator.
  • Id supports the = operator.

For example:

SELECT * FROM EcommerceOrderLines WHERE StoreId = 'Test_Store123' and OrderId = '44'

SELECT * FROM EcommerceOrderLines WHERE StoreId = 'Test_Store123' and OrderId = '44' and ID = '88'
Delete

Note : API will throw error if the Order contains only one line item. You will have to delete the order to delete all the lines.

DELETE FROM EcommerceOrderLines WHERE StoreId = 'Test_Store123' and OrderId = '44' and ID = '88'
Columns
Name Type ReadOnly Description
StoreId [KEY] String False The StoreId for the table.
OrderId [KEY] String False The OrderId for the table.
Id [KEY] String False A unique identifier for the order line item.
ProductId [KEY] String False A unique identifier for the product associated with the order line item.
ProductTitle String True The name of the product for the order line item.
ProductVariantId [KEY] String False A unique identifier for the product variant associated with the order line item.
ProductVariantTitle String True The name of the product variant for the order line item.
Quantity Integer False The quantity of an order line item.
Price Decimal False The price of an ecommerce order line item.

EcommerceOrders

A list of an account's ecommerce orders.

Table Specific Information
Select

The connector will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

The StoreId can be retrieved by selecting the ECommerceStores view.

  • StoreId supports the = operator.
  • Id supports the = operator.

For example:

SELECT * FROM EcommerceOrders WHERE StoreId = 'Test_Store123'

SELECT * FROM EcommerceOrders WHERE StoreId = 'Test_Store123' and ID = '44'
Columns
Name Type ReadOnly Description
StoreId [KEY] String False The StoreId for the table.
Id [KEY] String False A unique identifier for the order.
Customer String False Information about a specific customer. Orders for existing customers should include only the ID parameter in the customer object body.
CampaignId [KEY] String False A string that uniquely identifies the campaign associated with an order.
FinancialStatus String False The order status. For example: `refunded`, `processing`, `cancelled`, etc.
FulfillmentStatus String False The fulfillment status for the order. For example: `partial`, `fulfilled`, etc.
CurrencyCode String False The three-letter ISO 4217 code for the currency that the store accepts.
OrderTotal Decimal False The order total for the order.
TaxTotal Decimal False The tax total for the order.
ShippingTotal Decimal False The shipping total for the order.
TrackingCode String False The MailChimp tracking code for the order. Uses the 'mc_tc' parameter in eCommerce360-enabled tracking URLs.
ProcessedAtForeign Datetime False The date and time the order was processed.
CancelledAtForeign Datetime False The date and time the order was cancelled.
UpdatedAtForeign Datetime False The date and time the order was updated.
ShippingAddress_Name String False The name associated with an order's shipping address.
ShippingAddress_Address1 String False The shipping address for the order.
ShippingAddress_Address2 String False An additional field for the shipping address.
ShippingAddress_City String False The city in the order's shipping address.
ShippingAddress_Province String False The state or normalized province in the order's shipping address.
ShippingAddress_ProvinceCode String False The two-letter code for the province or state the order's shipping address is located in.
ShippingAddress_PostalCode String False The postal or zip code in the order's shipping address.
ShippingAddress_Country String False The country in the order's shipping address.
ShippingAddress_CountryCode String False The two-letter code for the country in the shipping address.
ShippingAddress_Longitude Double False The longitude for the shipping address location.
ShippingAddress_Latitude Double False The latitude for the shipping address location.
ShippingAddress_Phone String False The phone number for the order's shipping address
ShippingAddress_Company String False The company associated with an order's shipping address.
BillingAddress_Name String False The name associated with an order's billing address.
BillingAddress_Address1 String False The billing address for the order.
BillingAddress_Address2 String False An additional field for the billing address.
BillingAddress_City String False The city in the billing address.
BillingAddress_Province String False The state or normalized province in the billing address.
BillingAddress_ProvinceCode String False The two-letter code for the province or state in the billing address.
BillingAddress_PostalCode String False The postal or zip code in the billing address.
BillingAddress_Country String False The country in the billing address.
BillingAddress_CountryCode String False The two-letter code for the country in the billing address.
BillingAddress_Longitude Double False The longitude for the billing address location.
BillingAddress_Latitude Double False The latitude for the billing address location.
BillingAddress_Phone String False The phone number for the billing address.
BillingAddress_Company String False The company associated with the billing address.
Lines String False An array of the order's line items. The column will not work for Update. Lines can be updated using EcommerceOrderLines table.

EcommerceProducts

A list of an account's ecommerce products.

Table Specific Information
Select

The connector will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

The StoreId can be retrieved by selecting the ECommerceStores view.

  • StoreId supports the = operator.
  • Id supports the = operator.

For example:

SELECT * FROM EcommerceProducts WHERE StoreId = 'Test_Store123'

SELECT * FROM EcommerceProducts WHERE StoreId = 'Test_Store123' and ID = '44'
Columns
Name Type ReadOnly Description
StoreId [KEY] String False The StoreId for the table.
Id [KEY] String False A unique identifier for the product.
Title String False The title of a product.
Handle String False The handle of a product.
Url String False The URL of a product.
Description String False The description of a product.
Type String False The type of product.
Vendor String False The vendor for a product.
ImageUrl String False The image URL for a product.
Variants String False An array of the product's variants.
PublishedAtForeign Datetime False The date and time when the product was published.

EcommerceProductVariants

A list of an ecommerce product's variants.

Table Specific Information
Select

The connector will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

The StoreId can be retrieved by selecting the ECommerceStores view.

  • StoreId supports the = operator.
  • ProductId supports the = operator.
  • Id supports the = operator.

For example:

SELECT * FROM EcommerceProductVariants WHERE StoreId = 'Test_Store123' and ProductId = '44'

SELECT * FROM EcommerceProductVariants WHERE StoreId = 'Test_Store123' and ProductId = '44' and ID = '88'
Delete

Note : API will throw error if the Product contains only one variant. You will have to delete the product to delete all the variants.

DELETE FROM EcommerceProductVariants WHERE StoreId = 'Test_Store123' and ProductId = '44' and ID = '88'
Columns
Name Type ReadOnly Description
StoreId [KEY] String False The StoreId for the table.
ProductId [KEY] String False The ProductId for the table.
Id [KEY] String False A unique identifier for the product variant.
Title String False The title of a product variant.
Url String False The URL of a product variant.
Sku String False The stock keeping unit (SKU) of a product variant.
Price Decimal False The price of a product variant.
InventoryQuantity Integer False The inventory quantity of a product variant.
ImageUrl String False The image URL for a product variant.
Backorders String False The backorders of a product variant.
Visibility String False The visibility of a product variant.
CreatedAt Datetime True The date and time when the product was created.
UpdatedAt Datetime True The date and time the product was last updated.

FileManagerFiles

A listing of all avaialable images and files within an account's gallery.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for FileManagerFiles.

Select
SELECT * FROM FileManagerFiles
Insert

The Name, FolderId, and FileData are required for INSERTs.

INSERT INTO FileManagerFiles (Name, FolderID, FileData) VALUES ('myNewFolder', 'myFolderID', 'myBase64EncodedFileData')
Columns
Name Type ReadOnly Description
Id [KEY] String True The unique ID given to the file.
FolderId [KEY] Integer False The ID of the folder.
Type String True The type of file in the gallery: Image or file.
Name String False The name of the file.
FullSizeUrl String True The URL of the full-size file.
ThumbnailUrl String True The URL of the thumbnail preview.
Size Integer True The size of the file in bytes.
CreatedAt Datetime True The date and time a file was added to the gallery.
CreatedBy String True The username of the profile that uploaded the file.
Width Integer True The width of the image.
Height Integer True The height of an image.
FileData String False When adding a new file, the base64-encoded file. Required for INSERT statement.

FileManagerFolders

A listing of all avaialable folders within an account's gallery.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for FileManagerFolders.

Insert

The Name is required for INSERTs.

INSERT INTO FileManagerFolders (Name) VALUES ('myNewFolder'')
Columns
Name Type ReadOnly Description
Id [KEY] String True The unique ID given to the folder.
Name String False The name of the folder.
FileCount Integer True The number of files within the folder.
CreatedAt Datetime True The date and time a file was added to the gallery.
CreatedBy String True The username of the profile that created the folder.

ListInterestCategories

A listing of this list's interest categories.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for ListInterestCategories.

Select
Select * from ListInterestCategories

Select * from ListInterestCategories where ListId='abc'
Insert

The Title, Type, and ListId are required for INSERTs.

INSERT INTO ListInterestCategories (Name, Type, ListID) VALUES ('myNewListInterestCategory', 'myType', 'myListID')
Columns
Name Type ReadOnly Description
ListId [KEY] String False The ID for the list that this category belongs to.
Id [KEY] String True
Title String False The text description of this category. This field is displayed on signup forms and is often phrased as a question.
DisplayOrder Integer False Order in which the categories display in the list. Lower numbers display first.
Type String False Determines how this category's interests are displayed on signup forms.

ListInterests

A list of this category's interests

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for ListInterests.

Select
Select * from ListInterests

Select * from ListInterests where ListId='abc'
Insert

The Title, CategoryId, and ListId are required for INSERTs.

INSERT INTO ListInterests (Name, CategoryId, ListID) VALUES ('myNewListInterest', 'myCategory', 'myListID')
Columns
Name Type ReadOnly Description
CategoryId [KEY] String False The ID for the interest category.
ListId [KEY] String False The ID for the list that this interest belongs to.
Id [KEY] String True The ID for the interest.
Name String False The name of the interest. This can be shown publicly on a subscription form.
SubscriberCount String True The number of subscribers associated with this interest.
DisplayOrder Integer False Order in which the interests display.

ListMemberEvents

Events information for a specific list.

Table Specific Information

SELECT and INSERT are supported for ListMemberEvents.

Select
Select * from ListMemberEvents
Insert

Name column is required when INSERTing.

Columns
Name Type ReadOnly Description
Name String False The name of the event.
OccurredAt Datetime False The occurred datetime of the event.
Properties String False Properties of the event in an aggregate JSON Format.
ListId [KEY] String False The unique ID for the list.
MemberId [KEY] String False The MD5 hash of the list member's email address.

ListMemberNotes

The last 10 notes for a specific list member, based on date created.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for ListMemberNotes.

Select
Select * from ListMemberNotes
Insert

No fields are are required when INSERTing.

Columns
Name Type ReadOnly Description
Id [KEY] Integer True The note's ID.
CreatedAt Datetime True The date the note was created.
CreatedBy String True The author of the note.
UpdatedAt Datetime True The date the note was last updated
Note String False The content of the note.
ListId [KEY] String False The unique ID for the list.
MemberId [KEY] String False The MD5 hash of the list member's email address.

ListMembers

Individuals who are currently or have been previously suscribed to this list, including members who have bounced or unsubscribed.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for ListMembers.

Select
Select * from ListMembers
Insert

The ListId, EmailAddress, and Status are required for INSERTs.

INSERT INTO ListMembers (ListId, EmailAddress, Status) VALUES ('myListId', 'myEmailAddress', 'subscribed')
Columns
Name Type ReadOnly Description
Id [KEY] String True The MD5 hash of the list member's email address.
EmailAddress String False Email address for a subscriber.
UniqueEmailId [KEY] String True An identifier for the address across all of MailChimp.
EmailType String False Type of email this member asked to get ('html' or 'text').
FullName String True The contact's full name.
Status String False Subscriber's current status ('subscribed', 'unsubscribed', 'cleaned', 'pending' or 'transactional'). The allowed values are subscribed, unsubscribed, cleaned, prending, transactional.
StatusIfNew String False Subscriber's status ('subscribed', 'unsubscribed', 'cleaned', or 'pending'), to be used only on a PUT request if the email is not already present on the list.
Interests String False The key of this object's properties is the ID of the interest in question.
Stats_AvgOpenRate Double True A subscriber's average open rate.
Stats_AvgClickRate Double True A subscriber's average clickthrough rate.
IpSignup String False IP address the subscriber signed up from.
TimestampSignup Datetime False Date and time the subscriber signed up for the list.
IpOpt String False IP address the subscriber confirmed their opt-in status.
TimestampOpt Datetime False Date and time the subscribe confirmed their opt-in status.
MemberRating Integer True Star rating for this member between 1 and 5.
LastChanged Datetime True Date and time the member's info was last changed.
Language String False If set/detected, the language of the subscriber.
Vip Boolean False VIP status for subscriber.
EmailClient String True The email client the address as using.
Location_Latitude Double False
Location_Longitude Double False
Location_Gmtoff Integer True
Location_Dstoff Integer True
Location_CountryCode String True
Location_Timezone String True
LastNote_NoteId Integer True The note's ID.
LastNote_CreatedAt String True The date the note was created.
LastNote_CreatedBy String True The author of the note.
LastNote_Note String True The content of the note.
ListId [KEY] String False The ID for the list.
TagsAggregate String False Tags of the member, displayed as an aggregate.

ListMemberTags

Tags assigned to a certain member/members.

Table Specific Information

SELECT and INSERT are supported for ListMemberTags.

Select
Select * from ListMemberTags
Insert

Name, ListId and MemberId are required when Inserting.

INSERT INTO ListMemberTags (Name, ListId, MemberId) VALUES ('test', '434gfsd4', 'esdfsd4563vfghxgsf45645sdfgdfg')
Columns
Name Type ReadOnly Description
Id [KEY] String True The unique ID of the tag.
Name String False Name of the tag. When inserting, if the name doesn't exist, it will be created and then assigned to the member specified.
TimeAdded Datetime True Date and time the tag was added to the member.
ListId String False The ID of the list on which the member of this tag belongs to.
MemberId String False The ID of the member this tag is assigned to.

ListMergeFields

The merge field (formerly merge vars) for a given list. These correspond to merge fields in MailChimp's lists and subscriber profiles.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for ListMergeFields.

Select
Select * from ListMergeFields

Select * from ListMergeFields where ListId='abc'
Insert

The Name and ListID are required for INSERTs.

INSERT INTO ListMergeFields (Name, ListID) VALUES ('myNewListMergeField', 'myListID')
Columns
Name Type ReadOnly Description
MergeId [KEY] Integer True An unchanging ID for the merge field.
Tag String False The tag used in MailChimp campaigns and for the /members endpoint.
Name String False
Type String False The type for the merge field.
Required Boolean False Boolean value for if the merge field is required
DefaultValue String False The default value for the merge field if null.
Public Boolean False Whether or not the merge field is displayed on the signup form.
DisplayOrder Integer False The order on the form where the merge field is displayed.
Options_DefaultCountry Integer False In an address field, the default country code if none supplied.
Options_PhoneFormat String False In a phone field, the phone number type: US or International.
Options_DateFormat String False In a date or birthday field, the format of the date.
Options_Choices String False In a radio or dropdown non-group field, the available options for members to pick from.
Options_Size Integer False In a text field, the default length of the text field.
HelpText String False Any extra text to help the subscriber.
ListId [KEY] String False A string that identifies this merge field collections' list.

Lists

A collection of subscriber lists associated with this account. Lists contain subscribers who have opted-in to receive correspondence from you or your organization.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for Lists.

Insert

The Name, PermissionReminder, EmailTypeOption, Contact_Company, Contact_Address1, Contact_City, Contact_State, Contact_Zip, Contact_Country, CampaignDefaults_FromName, CampaignDefaults_FromEmail, CampaignDefaults_Subject, and CampaignDefaults_Language are required for INSERTs.

INSERT INTO Lists (Name, PermissionReminder, EmailTypeOption, Contact_Company, Contact_Address1, Contact_City, Contact_State, Contact_Zip, Contact_Country, CampaignDefaults_FromName, CampaignDefaults_FromEmail, CampaignDefaults_Subject, CampaignDefaults_Language) VALUES ('myName', 'myPermissionReminder', 'true', 'myCompany', 'myAddress', 'myCity', 'myState', 'myZip', 'myCountry', 'myFromName', 'myFromEmail', 'myDefaultSubject', 'myDefaultLanguage')
Columns
Name Type ReadOnly Description
Id [KEY] String True A string that uniquely identifies this list
Name String False The name of the list.
Contact_Company String False The company name associated with the list.
Contact_Address1 String False The street address for the list contact.
Contact_Address2 String False The street address for the list contact.
Contact_City String False The city for the list contact.
Contact_State String False The state for the list contact.
Contact_Zip String False The postal or zip code for the list contact.
Contact_Country String False A two-character ISO3166 country code. Defaults to US if invalid.
Contact_Phone String False The phone number for the list contact.
PermissionReminder String False The permission reminder for the list: a line of text that appears in the footer of each campaign that explains why subscribers are receiving the email campaign.
UseArchiveBar Boolean False Whether or not campaigns for this list use the Archive Bar in archives by default.
CampaignDefaults_FromName String False The default from name for campaigns sent to this list.
CampaignDefaults_FromEmail String False The default from email (must be a valid email address) for campaigns sent to this list.
CampaignDefaults_Subject String False The default subject line for campaigns sent to this list.
CampaignDefaults_Language String False The default language for this lists's forms.
NotifyOnSubscribe String False The email address to send subscribe notifications to, when enabled.
NotifyOnUnsubscribe String False The email address to send unsubscribe notifications to, when enabled.
DateCreated Datetime True The date and time that this list was created.
ListRating Integer True An auto-generated activity score for the list (0-5).
EmailTypeOption Boolean False Whether or not the list supports multiple formats for emails.
SubscribeUrlShort String True Our eepurl shortened version of this list's subscribe form.
SubscribeUrlLong String True The full version of this list's subscribe form (host will vary).
BeamerAddress String True The email address to use for this list's Email Beamer.
Visibility String False Whether this list is public (pub) or private (prv). Used internally for projects like Wavelength.
Modules String True Any list-specific modules installed for this list.
Stats_MemberCount Integer True The number of active members in the given list.
Stats_UnsubscribeCount Integer True The number of members who have unsubscribed from the given list.
Stats_CleanedCount Integer True The number of members cleaned from the given list.
Stats_MemberCountSinceSend Integer True The number of active members in the given list since the last campaign was sent.
Stats_UnsubscribeCountSinceSend Integer True The number of members who have unsubscribed since the last campaign was sent.
Stats_CleanedCountSinceSend Integer True The number of members cleaned from the given list since the last campaign was sent.
Stats_CampaignCount Integer True The number of campaigns in any status that use this list.
Stats_CampaignLastSent Datetime True The date and time the last campaign was sent to this list.
Stats_MergeFieldCount Integer True The number of merge vars for this list (not including the required EMAIL one).
Stats_AvgSubRate Double True The average number of subscriptions per month for the list (not returned if we haven't calculated it yet).
Stats_AvgUnsubRate Double True The average number of unsubscriptions per month for the list (not returned if we haven't calculated it yet).
Stats_TargetSubRate Double True The target numberof subscriptions per month for the list to keep it growing (not returned if we haven't calculated it yet).
Stats_OpenRate Double True The average open rate (a percentage represented as a number between 0 and 100) per campaign for the list (not returned if we haven't calculated it yet).
Stats_ClickRate Double True The average click rate (a percentage represented as a number between 0 and 100) per campaign for the list (not returned if we haven't calculated it yet).
Stats_LastSubDate Datetime True The date and time of the last time someone subscribed to this list.
Stats_LastUnsubDate Datetime True The date and time of the last time someone unsubscribed from this list.

ListSegmentMembers

Individuals who are currently or have been previously suscribed to this list, including members who have bounced or unsubscribed.

Table Specific Information

SELECT, INSERT, and DELETE are supported for ListSegmentMembers.

Select
Select * from ListSegmentMembers
Select * from ListSegmentMembers where ListId = '5152'

Select * from ListSegmentMembers where SegmentId = '2623'
Insert

The Name and ListID are required for INSERTs.

INSERT into ListSegmentMembers (EmailAddress,ListId,SegmentId) values ('abc@gmail.com','44a64c46cb','7032720')
Columns
Name Type ReadOnly Description
Id [KEY] String True The MD5 hash of the list member's email address.
EmailAddress String False Email address for a subscriber.
UniqueEmailId [KEY] String True An identifier for the address across all of MailChimp.
EmailType String True Type of email this member asked to get ('html' or 'text').
Status String True Subscriber's current status ('subscribed', 'unsubscribed', 'cleaned', or 'pending').
StatusIfNew String True Subscriber's status ('subscribed', 'unsubscribed', 'cleaned', or 'pending'), to be used only on a PUT request if the email is not already present on the list.
Interests String True The key of this object's properties is the ID of the interest in question.
Stats_AvgOpenRate Double True A subscriber's average open rate.
Stats_AvgClickRate Double True A subscriber's average clickthrough rate.
IpSignup String True IP address the subscriber signed up from.
TimestampSignup Datetime True Date and time the subscriber signed up for the list.
IpOpt String True IP address the subscriber confirmed their opt-in status.
TimestampOpt Datetime True Date and time the subscribe confirmed their opt-in status.
MemberRating Integer True Star rating for this member between 1 and 5.
LastChanged Datetime True Date and time the member's info was last changed.
Language String True If set/detected, the language of the subscriber.
Vip Boolean True VIP status for subscriber.
EmailClient String True The email client the address as using.
Location_Latitude Double True
Location_Longitude Double True
Location_Gmtoff Integer True
Location_Dstoff Integer True
Location_CountryCode String True
Location_Timezone String True
LastNote_NoteId Integer True The note's ID.
LastNote_CreatedAt String True The date the note was created.
LastNote_CreatedBy String True The author of the note.
LastNote_Note String True The content of the note.
ListId [KEY] String False The ID for the list.
SegmentId [KEY] String False The ID for the segment.

ListSegments

A list of available segments.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for ListSegments.

Select
Select * from ListSegments

Select * from ListSegments where ListId = '5152'
Insert

The Name and ListID are required for INSERTs.

INSERT INTO ListSegments (Name, ListID) VALUES ('myNewListSegment', 'myListID')
Columns
Name Type ReadOnly Description
Id [KEY] String True An integer to uniquely identify the segment.
Name String False The name of the segment.
MemberCount Integer True The number of active subscribers currently included in the segment.
Type String True The type of segment: saved, static, or fuzzy.
CreatedAt Datetime True The time and date the segment was created.
UpdatedAt Datetime True The time and date the segment was last updated.
Options_Match String False Match type of 'any' or 'all'.
Options_Conditions String False An array of segment conditions.
ListId [KEY] String False The ID for the list.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
EmailList String A comma-separated list of emails that you want to include in this ListSegment.

ListsWebhooks

Webhooks configured for the given list.

Columns
Name Type ReadOnly Description
Id [KEY] String True An identifier for the webhook across all of MailChimp.
Url String False the URL for this Webhook.
Events_Subscribe Boolean False
Events_Unsubscribe Boolean False
Events_Profile Boolean False
Events_Cleaned Boolean False
Events_Upemail Boolean False
Events_Campaign Boolean False
Sources_User Boolean False
Sources_Admin Boolean False
Sources_Api Boolean False
ListId [KEY] String False The ID for the list.

TemplateFolders

Folders for organizing templates

Columns
Name Type ReadOnly Description
Id [KEY] String True A string that uniquely identifieds this folder
Name String False The name of the folder
Count Integer True The number of templates in the folder

Templates

A list an account's available templates.

Table Specific Information

SELECT and DELETE are supported for Templates.

Columns
Name Type ReadOnly Description
Id [KEY] Integer True The individual ID for the template.
Type String True The type of template (user, base, or gallery).
Name String False The name of the template.
DragAndDrop Boolean True Whether or not the template uses the drag and drop editor.
Responsive Boolean True Whether or not the template contains media queries to make it responsive.
Category String True If available, the category the template is listed in.
DateCreated Datetime True The date and time the template was created.
CreatedBy String True The login name for template's creator.
Active Boolean False User templates are not 'deleted,' but rather marked as 'inactive.' Returns whether or not the template is still active.
FolderId [KEY] String False The ID of the folder the template is currently in.
Thumbnail String True If available, the URL for a thumbnail of the template.
ShareUrl String True The URL used for template sharing. For more information, see: http://kb.mailchimp.com/templates/basic-and-themes/how-to-share-a-template

Views

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

Mailchimp Connector Views

Name Description
AuthorizedApps A list of applications authorized to access the account.
AutomationEmails A summary of the emails in an automation workflow.
Automations A summary of the automations within an account.
CampaignOpenEmailDetails A list of members who opened the campaign email.
ConversationMessages Messages from a specific conversation. Conversation tracking is a feature available to paid accounts that lets you view replies to your campaigns from inside your MailChimp account.
Conversations A collection of this account's tracked conversations. Conversation tracking is a feature available to paid accounts that lets you view replies to your campaigns from inside your MailChimp account.
EcommerceStores A list of an account's ecommerce stores.
ListAbuse A collection of abuse complaints for a specific list. An abuse complaint occurs when your recipient clicks to 'report spam' in their email program.
ListActivity Up to the previous 180 days of daily detailed aggregated activity stats for a given list. Does not include AutoResponder or Automation activity.
ListClients Top email clients used, as measured by their user-agent string
ListGrowthHistory A month-by-month summary of a specific list's growth activity.
ListMemberActivity The last 50 member events for a list.
ListSignupForms Collection of List Signup Forms
ReportAbuse A list of abuse complaints for a specific list.
ReportAdvice A list of feedback based on a campaign's statistics.
ReportClickDetails A list of URLs and unique IDs included in HTML and plain-text versions of a campaign.
ReportClickDetailsMembers A collection of members who clicked on a specific link within a campaign.
ReportDomainPerformance Statistics for the top-performing email domains in a campaign.
ReportEmailActivity A list of member's subscriber activity in a specific campaign.
ReportLocations Top open locations for a specific campaign.
Reports A list of reports containing campaigns marked as Sent.
ReportSentTo A list of subscribers who were sent a specific campaign.
ReportUnsubscribes A list of members who have unsubscribed from a specific campaign.
SurveyResponses List of survey responses.
Surveys Get reports for surveys.

AuthorizedApps

A list of applications authorized to access the account.

Table Specific Information

SELECT is supported for AuthorizedApps.

Columns
Name Type Description
Id [KEY] String The ID for this application integration.
Name String The name of the application.
Description String The description for the application.
Users String An array of usernames of the users who have linked this app.

AutomationEmails

A summary of the emails in an automation workflow.

Table Specific Information

SELECT is supported for AutomationEmails.

Select
Select * from AutomationEmails
Columns
Name Type Description
Id [KEY] String A string that uniquely identifies the automation email.
WorkflowId [KEY] String A string that uniquely identifies an automation workflow.
Position Integer
Delay_Amount Integer The delay amount for an automation email.
Delay_Type String The type of delay for an automation email.
Delay_Direction String Whether the delay settings describe before or after the delay action of an automation email.
Delay_Action String The action that triggers the delay of an automation emails.
CreateTime Datetime The date and time the campaign was created.
StartTime Datetime The date and time the campaign was started.
ArchiveUrl String The link to the campaign's archive version.
Status String The current status of the campaign ('save', 'paused', 'sending').
EmailsSent Integer The total number of emails sent for this campaign.
SendTime Datetime The time and date a campaign was sent.
ContentType String How the campaign's content is put together ('template', 'drag_and_drop', 'html', 'url').
Recipients_ListId String The ID of the list.
Recipients_SegmentOpts String Segment options.
Settings_SubjectLine String The subject line for the campaign.
Settings_Title String The title of the campaign.
Settings_FromName String The 'from' name on the campaign (not an email address).
Settings_ReplyTo String The reply-to email address for the campaign.
Settings_Authenticate Boolean Whether or not the campaign was authenticated by MailChimp. Defaults to 'true'.
Settings_AutoFooter Boolean Automatically append MailChimp's default footer to the campaign.
Settings_InlineCss Boolean Automatically inline the CSS included with the campaign content.
Settings_AutoTweet Boolean Automatically tweet a link to the campaign archive page when the campaign is sent.
Settings_AutoFbPost String An array of Facebook page ids (integers) to auto-post to.
Settings_FbComments Boolean Allows Facebook comments on the campaign (also force-enables the Campaign Archive toolbar). Defaults to 'true'.
Settings_TemplateId Integer The ID for the template used in this campaign.
Settings_DragAndDrop Boolean Whether the campaign uses the drag-and-drop editor.
Tracking_Opens Boolean Whether to track opens. Defaults to 'true'.
Tracking_HtmlClicks Boolean Whether to track clicks in the HTML version of the campaign. Defaults to 'true'.
Tracking_TextClicks Boolean Whether to track clicks in the plain-text version of the campaign. Defaults to 'true'.
Tracking_GoalTracking Boolean Whether to enable Goal racking. For more information, see this Knowledge Base article: http://eepurl.com/GPMdH
Tracking_Ecomm360 Boolean Whether to enable eCommerce360 tracking.
Tracking_GoogleAnalytics String The custom slug for Google Analytics tracking (max of 50 bytes).
Tracking_Clicktale String The custom slug for ClickTale Analytics tracking (max of 50 bytes).
Tracking_Salesforce String Salesforce tracking options for a campaign. Must be using MailChimp's built-in Salesforce integration.
Tracking_Highrise String Highrise tracking options for a campaign. Must be using MailChimp's built-in Highrise integration.
Tracking_Capsule String Capsule tracking option sfor a campaign. Must be using MailChimp's built-in Capsule integration.
SocialCard_ImageUrl String The URL for the header image for the card.
SocialCard_Description String A short summary of the campaign to display.
SocialCard_Title String The title for the card. Typically the subject line of the campaign.
TriggerSettings_Runtime String The advanced scheduling options for an automation email.
ReportSummary String For sent campaigns, a summary of opens, clicks, and unsubscribes.

Automations

A summary of the automations within an account.

Columns
Name Type Description
Id [KEY] String A string that identifies this automation.
CreateTime Datetime The date and time the automation was created.
StartTime Datetime The date and time the automation was started.
Status String The current status of the automation ('save', 'paused', 'sending').
EmailsSent Integer The total number of emails sent for this automation.
Recipients_ListId String The ID of the list.
Recipients_ListName String List Name
Recipients_SegmentOpts String Segment options.
Settings_Title String The title of the automation.
Settings_FromName String The 'from' name on the automation (not an email address).
Settings_ReplyTo String The reply-to email address for the automation.
Settings_UseConversation Boolean Use MailChimp's Conversations feature to manage out of office replies.
Settings_ToName String The automation's custom 'to' name. Typically something like the first name merge var.
Settings_Authenticate Boolean Whether or not the automation is authenticated by MailChimp. Defaults to 'true'.
Settings_AutoFooter Boolean Automatically append MailChimp's default footer to the automation.
Settings_InlineCss Boolean Automatically inline the CSS included with the automation content.
Tracking_Opens Boolean Whether to track opens. Defaults to 'true'.
Tracking_HtmlClicks Boolean Whether to track clicks in the HTML version of the automation. Defaults to 'true'.
Tracking_TextClicks Boolean Whether to track clicks in the plain-text version of the automation. Defaults to 'true'.
Tracking_GoalTracking Boolean Whether to enable Goal tracking. For more information, see this Knowledge Base article: http://eepurl.com/GPMdH
Tracking_Ecomm360 Boolean Whether to enable eCommerce360 tracking.
Tracking_GoogleAnalytics String The custom slug for Google Analytics tracking (max of 50 bytes).
Tracking_Clicktale String The custom slug for ClickTale Analytics tracking (max of 50 bytes).
Tracking_Salesforce String Salesforce tracking options for an automation. Must be using MailChimp's built-in Salesforce integration.
Tracking_Highrise String Highrise tracking options for an automation. Must be using MailChimp's built-in Highrise integration.
Tracking_Capsule String Capsule tracking options for an automation. Must be using MailChimp's built-in Capsule integration.
TriggerSettings String A summary of an automation workflow's trigger settings.
ReportSummary String A summary of open and click activity for an automation workflow.

CampaignOpenEmailDetails

A list of members who opened the campaign email.

Table Specific Information

SELECT is supported for CampaignOpenEmailDetails.

Select

For example:

SELECT * FROM CampaignOpenEmailDetails
SELECT * FROM CampaignOpenEmailDetails WHERE CampaignId = '9f218dcf18'
Columns
Name Type Description
CampaignId [KEY] String The ID for this application integration.
ListId [KEY] String The name of the application.
ListIsActive Boolean The description for the application.
ContactStatus String An array of usernames of the users who have linked this app.
EmailId [KEY] String The description for the application.
EmailAddress String The description for the application.
MergeFields String The description for the application.
Vip Boolean The description for the application.
OpensCount Integer The description for the application.
Opens String The description for the application.

ConversationMessages

Messages from a specific conversation. Conversation tracking is a feature available to paid accounts that lets you view replies to your campaigns from inside your MailChimp account.

Table Specific Information

SELECT is supported for ConversationMessages.

Select
SELECT * from ConversationMessages
Columns
Name Type Description
Id [KEY] String A string that uniquely identifies this message
ConversationId [KEY] String A string that identifies this message's conversation
ListId [KEY] String The unique identifier of the list this conversation is associated with
FromLabel String A label representing the sender of this message
FromEmail String A label representing the email of the sender of this message
Subject String The subject of this message
Message String The plain-text content of the message
Read Boolean Whether or not this message has been marked as read
Timestamp Datetime Date the message was either sent or received

Conversations

A collection of this account's tracked conversations. Conversation tracking is a feature available to paid accounts that lets you view replies to your campaigns from inside your MailChimp account.

Columns
Name Type Description
Id [KEY] String A string that uniquely identifies this conversation
MessageCount Integer The total number of messages in this conversation
CampaignId [KEY] String The unique identifier of the campaign this conversation is associated with
ListId [KEY] String The unique identifier of the list this conversation is associated with
UnreadMessages Integer The number of unread messages in this conversation
FromLabel String A label representing the sender of this message
FromEmail String A label representing the email of the sender of this message
Subject String The subject of the message
LastMessage_FromLabel String A label representing the sender of this message
LastMessage_FromEmail String A label representing the email of the sender of this message
LastMessage_Subject String The subject of this message
LastMessage_Message String The plain-text content of the message
LastMessage_Read Boolean Whether or not this message has been marked as read
LastMessage_Timestamp Datetime Date the message was either sent or received

EcommerceStores

A list of an account's ecommerce stores.

Table Specific Information
Select
  • Id supports the = operator.

For example:

SELECT * FROM EcommerceStores WHERE  ID = '44'
Columns
Name Type Description
Id [KEY] String The unique identifier for the store.
ListId [KEY] String The unique identifier for the MailChimp list that's associated with the store. The list_id for a specific store can't change.
Name String The name of the store.
Platform String The ecommerce platform of the store.
Domain String The store domain.
EmailAddress String The email address for the store.
CurrencyCode String The three-letter ISO 4217 code for the currency that the store accepts.
MoneyFormat String The currency format for the store. For example: `$`, `�`, etc.
PrimaryLocale String The primary locale for the store. For example: `en`, `de`, etc.
Timezone String The timezone for the store.
Phone String The store phone number.
Address_Address1 String The store's mailing address.
Address_Address2 String An additional field for the store's mailing address.
Address_City String The city the store is located in.
Address_Province String The store's state name or normalized province.
Address_ProvinceCode String The two-letter code for the store's province or state.
Address_PostalCode String The store's postal or zip code.
Address_Country String The store's country.
Address_CountryCode String The two-letter code for to the store's country.
Address_Longitude Double The longitude of the store location.
Address_Latitude Double The latitude of the store location.
CreatedAt Datetime The date and time the store was created.
UpdatedAt Datetime The date and time the store was last updated.

ListAbuse

A collection of abuse complaints for a specific list. An abuse complaint occurs when your recipient clicks to 'report spam' in their email program.

Table Specific Information

SELECT is supported for ListAbuse.

Select
Select * from ListAbuse

Select * from ListAbuse where ListId='abc'
Columns
Name Type Description
Id [KEY] String The ID for the abuse report
CampaignId [KEY] String The campaign ID for the abuse report
ListId [KEY] String The list ID for the abuse report.
EmailId [KEY] String The MD5 hash of the list member's email address.
EmailAddress String Email address for a subscriber
Date String Date for the abuse report

ListActivity

Up to the previous 180 days of daily detailed aggregated activity stats for a given list. Does not include AutoResponder or Automation activity.

Table Specific Information

SELECT is supported for ListActivity.

Select
Select * from ListActivity

Select * from ListActivity where ListId='abc'
Columns
Name Type Description
ListId [KEY] String The ListId for the table.
Day [KEY] Date The date for the activity summary.
EmailsSent Integer The total number of emails sent on the date for the activity summary.
UniqueOpens Integer The number of unique opens.
RecipientClicks Integer The number of clicks.
HardBounce Integer The number of hard bounces.
SoftBounce Integer The number of soft bounces
Subs Integer The number of subscribes.
Unsubs Integer The number of unsubscribes.
OtherAdds Integer The number of subscribers who may have been added outside of the double opt-in process such as imports or API activity.
OtherRemoves Integer The number of subscribers who may have been removed outside of unsubscribing or reporting an email as spam. For example, deleted subscribers.

ListClients

Top email clients used, as measured by their user-agent string

Columns
Name Type Description
Client String The name of the email client.
Members Integer The number of subscribed members who used this email client.
ListId [KEY] String The unique ID for the list.

ListGrowthHistory

A month-by-month summary of a specific list's growth activity.

Table Specific Information

SELECT is supported for ListGrowthHistory.

Select
Select * from ListGrowthHistory

Select * from ListGrowthHistory where ListId='abc'
Columns
Name Type Description
ListId [KEY] String The list ID for the growth activity report.
Month [KEY] String The month that the growth history is describing.
Subscribed Integer Total subscribed members on the list at the end of the month.
Unsubscribed Integer Newly unsubscribed members on the list for a specific month.
Reconfirm Integer Newly reconfirmed members on the list for a specific month.
Cleaned Integer Newly cleaned (hard-bounced) members on the list for a specific month.
Pending Integer Pending members on the list for a specific month.
Deleted Integer Newly deleted members on the list for a specific month.
Transactional Integer Subscribers that have been sent transactional emails via Mandrill.

ListMemberActivity

The last 50 member events for a list.

Table Specific Information

SELECT is supported for ListMemberActivity.

Select
Select * from ListMemberActivity
Columns
Name Type Description
MemberId [KEY] String The MemberId of the member to get events for.
EmailId [KEY] String The EmailId for the table.
ListId [KEY] String The ListId for the table.
Action String The type of action recorded for the subscriber.
Timestamp [KEY] Datetime The date and time recorded for the action.
Url String For clicks, the URL the subscriber clicked on.
Type String The type of campaign that was sent.
CampaignId [KEY] String The web-based ID for the campaign.
Title String If set, the campaign's title.
ParentCampaign String The ID of the parent campaign.
ContactId String The ID of the contact.

ListSignupForms

Collection of List Signup Forms

Columns
Name Type Description
Header_ImageUrl String Header Image Url
Header_Text String Header Text
Header_ImageWidth String Image width
Header_ImageHeight String Image height
Header_ImageAlt String Image Alt
Header_ImageLink String Image Link
Header_ImageAlign String Image align
Header_ImageBorderWidth String Image border width
Header_ImageBorderStyle String Image border style
Header_ImageBorderColor String Image border color
Header_ImageTarget String Image target
Contents String Signup form body contents options
Styles String An array of objects, each representing each element of signup forms.
SignupFormUrl String Signup form URL
ListId [KEY] String A string that identifies this signup forms' list.

ReportAbuse

A list of abuse complaints for a specific list.

Table Specific Information

SELECT is supported for ReportAbuse.

Select
Select * from ReportAbuse

Select * from ReportAdvice where CampaignId='abc'
Columns
Name Type Description
Id [KEY] String The unique ID for the abuse report.
CampaignId [KEY] String The campaign ID for the abuse report
ListId [KEY] String The list ID for the abuse report.
EmailId [KEY] String The list-specific ID for the given email address
EmailAddress String Email address for a subscriber
Date String Date for the abuse report

ReportAdvice

A list of feedback based on a campaign's statistics.

Table Specific Information

SELECT is supported for ReportAdvice.

Select
Select * from ReportAdvice

Select * from ReportAdvice where CampaignId='abc'
Columns
Name Type Description
CampaignId [KEY] String The CampaignId for the table.
Type String The 'type' of message ('negative', 'positive', 'neutral').
Message String The advice message.

ReportClickDetails

A list of URLs and unique IDs included in HTML and plain-text versions of a campaign.

Table Specific Information

SELECT is supported for ReportClickDetails.

Select
Select * from ReportClickDetails

Select * from ReportClickDetails where CampaignId='abc'
Columns
Name Type Description
Id [KEY] String
Url String The URL for the link in the campaign.
TotalClicks Integer The number of total clicks for a given link.
ClickPercentage Double The percentage of total clicks a given link generated for a campaign.
UniqueClicks Integer Number of unique clicks for a given link.
UniqueClickPercentage Double The percentage of unique clicks a given link generated for a campaign.
LastClick Datetime The date and time for the last recorded click for a given link.
AbSplit_A String
AbSplit_B String
CampaignId [KEY] String The ID for the campaign.

ReportClickDetailsMembers

A collection of members who clicked on a specific link within a campaign.

Table Specific Information

SELECT is supported for ReportClickDetailsMembers.

Select
Select * from ReportClickDetailsMembers
Columns
Name Type Description
EmailId [KEY] String The list-specific ID for the given email address.
EmailAddress String Email address for a subscriber
Clicks Integer The total number of times the subscriber clicked on the link.
CampaignId [KEY] String The ID for the campaign.
UrlId [KEY] String The ID for the tracked URL in the campaign.
ListId [KEY] String The ID for the list.

ReportDomainPerformance

Statistics for the top-performing email domains in a campaign.

Table Specific Information

SELECT is supported for ReportDomainPerformance.

Select
Select * from ReportDomainPerformance

Select * from ReportDomainPerformance where CampaignId='abc'
Columns
Name Type Description
CampaignId [KEY] String The CampaignId for the table.
Domain String The name of the domain (gmail.com, hotmail.com, yahoo.com).
EmailsSent Integer The number of emails sent to that specific domain.
Bounces Integer The number of bounces at a domain.
Opens Integer The number of opens for a domain.
Clicks Integer The number of clicks for a domain.
Unsubs Integer The total number of unsubscribes for a domain.
Delivered Integer The number of successful deliveries for a domain.
EmailsPct Double The percentage of total emails that went to this domain.
BouncesPct Double The percentage of total bounces that came from this domain.
OpensPct Double The percentage of total opens that came from this domain.
ClicksPct Double The percentage of total clicks tht came from this domain.
UnsubsPct Double The percentage of total unsubscribes taht came from this domain.

ReportEmailActivity

A list of member's subscriber activity in a specific campaign.

Table Specific Information

SELECT is supported for ReportEmailActivity.

Select
Select * from ReportEmailActivity
Columns
Name Type Description
CampaignId [KEY] String The unique ID for the campaign.
ListId [KEY] String The unique ID for the list.
EmailId [KEY] String The list-specific ID for the given email address.
EmailAddress String Email address for a subscriber
Activity String An array of objects, each showing an interaction with the email.

ReportLocations

Top open locations for a specific campaign.

Columns
Name Type Description
CampaignId [KEY] String The CampaignId for the table.
Region String A more specific location area such as city or state.
Opens Integer The number of unique campaign opens for a given region.

Reports

A list of reports containing campaigns marked as Sent.

Columns
Name Type Description
Id [KEY] String A string that uniquely identifies this campaign.
CampaignTitle String The title of the campaign.
Type String The type of campaign (regular, plain-text, ab_split, rss, automation, variate, or auto).
EmailsSent Integer The total number of emails sent for this campaign.
AbuseReports Integer The number of abuse reports generated for this campaign.
Unsubscribed Integer The total number of unsubscribed members for this campaign.
SendTime Datetime The time and date a campaign was sent.
Bounces_HardBounces Integer The total number of hard bounced email addresses.
Bounces_SoftBounces Integer The total number of soft bounced email addresses.
Bounces_SyntaxErrors Integer The total number of addresses that were syntax-related bounces.
Forwards_ForwardsCount Integer
Forwards_ForwardsOpens Integer
Opens_OpensTotal Integer The total number of opens for a campaign.
Opens_UniqueOpens Integer The total number of unique subscribers who opened a campaign.
Opens_OpenRate Double The number of unique subscribers who opened divided by the total number of successful deliveries.
Opens_LastOpen Datetime The date and time of the last recorded open.
Clicks_ClicksTotal Integer The total number of clicks for the campaign.
Clicks_UniqueClicks Integer The total number of unique clicks for links across a campaign.
Clicks_UniqueSubscriberClicks Integer The total number of subscribers who clicked on a campaign.
Clicks_ClickRate Double The number of unique subscribers who clicked divided by the total number of successful deliveries.
Clicks_LastClick Datetime The date and time of the last recorded click for the campaign.
FacebookLikes_RecipientLikes Integer
FacebookLikes_UniqueLikes Integer
FacebookLikes_FacebookLikes Integer
IndustryStats_Type String
IndustryStats_OpenRate Double
IndustryStats_ClickRate Double
IndustryStats_BounceRate Double
IndustryStats_UnopenRate Double
IndustryStats_UnsubRate Double
IndustryStats_AbuseRate Double
ListStats_SubRate Double The average number of subscriptions per month for the list.
ListStats_UnsubRate Double The average number of unsubscriptions per month for the list.
ListStats_OpenRate Double The average open rate (a percentage represented as a number between 0 and 100) per campaign for the list.
ListStats_ClickRate Double The average click rate (a percentage represented as a number between 0 and 100) per campaign for the list.
AbSplit_A String
AbSplit_B String
Timewarp String An hourly breakdown of sends, opens, and clicks if a campaign is sent using timewarp.
Timeseries String An hourly breakdown of the performance of the campaign over the first 24 hours.
ShareReport_ShareUrl String The URL for the VIP report.
ShareReport_SharePassword String If password protected, the password for the VIP report.
DeliveryStatus String Updates on campaigns in the process of sending.

ReportSentTo

A list of subscribers who were sent a specific campaign.

Table Specific Information

SELECT is supported for ReportSentTo.

Select

A CampaignId is required to retrieve data from this table. If none is specified in the WHERE clause, the first one available for your account will be used.

Columns
Name Type Description
EmailId [KEY] String The list-specific ID for the given email address.
EmailAddress String Email address for a subscriber.
Status String The status of the member ('sent', 'hard' for hard bounce, or 'soft' for soft bounce).
OpenCount Integer The number of times a campaign was opened by this member.
LastOpen String The date and time of the last open for this member.
AbsplitGroup String For A/B Split Campaigns, the group the member was apart of ('a', 'b', or 'winner').
GmtOffset Integer For campaigns sent with timewarp, the time zone group the member is apart of.
CampaignId [KEY] String The ID for the campaign.
ListId [KEY] String The ID for the list.

ReportUnsubscribes

A list of members who have unsubscribed from a specific campaign.

Table Specific Information

SELECT is supported for ReportUnsubscribes.

Select
Select * from ReportUnsubscribes
Columns
Name Type Description
EmailId [KEY] String The list-specific ID for the given email address
EmailAddress String Email address for a subscriber
Timestamp Datetime The date and time the member opted-out.
Reason String If available, the reason listed by the member for unsubscribing.
CampaignId [KEY] String The ID for the campaign.
ListId [KEY] String The ID for the list.

SurveyResponses

List of survey responses.

Table Specific Information

SELECT is supported for SurveyResponses.

Select
Select * from SurveyResponses
Columns
Name Type Description
Id [KEY] String The ID for the survey response.
SubmittedAt Datetime The date and time when the survey response was submitted.
ContactEmailId String The MD5 hash of the lowercase version of the list member email address.
ContactId String The ID of this contact.
ContactStatus String The contact's current status.
ContactEmail String The contact's email address.
ContactFullName String The contact's full name.
ContactConsentsToOneToOneMessaging Boolean Indicates whether a contact consents to 1:1 messaging.
ContactAvatarUrl String URL for the contact's avatar or profile image.
IsNewContact Boolean If this contact was added to the Mailchimp audience via this survey.
SurveyId [KEY] String A string that uniquely identifies this survey.

Surveys

Get reports for surveys.

Table Specific Information

SELECT is supported for Surveys.

Columns
Name Type Description
Id [KEY] String A string that uniquely identifies this survey.
WebId Integer The ID used in the Mailchimp web application.
ListId String The ID of the list connected to this survey.
ListName String The name of the list connected to this survey.
Title String The title of the survey.
Url String The URL for the survey.
Status String The status of the Surney. Possible values: published or unpublished.
PublishedAt Datetime The date and time the survey was published.
CreatedAt Datetime The date and time the survey was created.
UpdatedAt Datetime The date and time the survey was last updated.
TotalResponses Integer The total number of responses to this survey.

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Mailchimp.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Mailchimp, along with an indication of whether the procedure succeeded or failed.

Mailchimp Connector Stored Procedures

Name Description
CampaignCancel Cancels a MailChimp Regular or Plain-Text campaign .
CampaignPause Pauses a MailChimp RSS campaign.
CampaignResume Resumes a MailChimp RSS campaign.
CampaignSchedule Schedules a MailChimp campaign. Either Timewarp or the batch properties can be used, not both.
CampaignSend Sends a MailChimp campaign.
CampaignTest Sends a test email for a MailChimp campaign.
CampaignUnschedule Unschedules a MailChimp campaign.
GetOAuthAccessToken Obtains the OAuth access token to be used for authentication with MailChimp.
GetOAuthAuthorizationURL Obtains the OAuth authorization URL used for authentication with MailChimp.

CampaignCancel

Cancels a MailChimp Regular or Plain-Text campaign .

Input
Name Type Required Description
CampaignID String True The ID of the campaign to cancel.
Result Set Columns
Name Type Description
Success String Whether the operation was successful.

CampaignPause

Pauses a MailChimp RSS campaign.

Input
Name Type Required Description
CampaignID String True The ID of the campaign to pause.
Result Set Columns
Name Type Description
Success String Whether the operation was successful.

CampaignResume

Resumes a MailChimp RSS campaign.

Input
Name Type Required Description
CampaignID String True The ID of the campaign to resume.
Result Set Columns
Name Type Description
Success String Whether the operation was successful.

CampaignSchedule

Schedules a MailChimp campaign. Either Timewarp or the batch properties can be used, not both.

Input
Name Type Required Description
CampaignID String True The ID of the campaign to schedule.
ScheduleTime String True The local date and time to schedule the campaign for delivery. Campaigns may only be scheduled to send on the quarter-hour (:00, :15, :30, :45).
Timewarp String False Boolean determining whether the campaign should use Timewarp when sending. The default value is false.
BatchCount String False The number of batches to send.
BatchDelay String False The delay, in minutes, between batches
Result Set Columns
Name Type Description
Success String Whether the operation was successful.

CampaignSend

Sends a MailChimp campaign.

Input
Name Type Required Description
CampaignID String True The ID of the campaign to send.
Result Set Columns
Name Type Description
Success String Whether the operation was successful.

CampaignTest

Sends a test email for a MailChimp campaign.

Input
Name Type Required Description
CampaignID String True The ID of the campaign to test.
TestEmails String True A comma-separated list of emails to send.
SendType String True The type of test email to send. Either html or plaintext The allowed values are html, plaintext. The default value is html.
Result Set Columns
Name Type Description
Success String Whether the test was successful.

CampaignUnschedule

Unschedules a MailChimp campaign.

Input
Name Type Required Description
CampaignID String True The ID of the campaign to unschedule.
Result Set Columns
Name Type Description
Success String Whether the operation was successful.

GetOAuthAccessToken

Obtains the OAuth access token to be used for authentication with MailChimp.

Input
Name Type Required Description
AuthMode String True The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
Verifier String False The verifier code returned by MailChimp after permission for the app to connect has been granted. WEB AuthMode only.
CallbackURL String False This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the APIs Console, including the HTTP or HTTPS schemes, capitalization, and trailing forward slash ('/').
State String False This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to MailChimp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.
Result Set Columns
Name Type Description
OAuthAccessToken String The authentication token returned from MailChimp. This can be used in subsequent calls to other operations for this particular service.
ExpiresIn String The remaining lifetime on the access token.
DataCenter String The datacenter for the user.

GetOAuthAuthorizationURL

Obtains the OAuth authorization URL used for authentication with MailChimp.

Input
Name Type Required Description
CallbackURL String False This field determines where the response is sent. The value of this parameter must exactly match one of the values registered in the APIs Console, including the HTTP or HTTPS schemes, case, and trailing forward slash ('/').
State String False This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the MailChimp authorization server and back. Possible uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.
Result Set Columns
Name Type Description
URL String The URL to complete user authentication.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Mailchimp:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Lists table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Lists'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the GetOAuthAccessToken stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='GetOAuthAccessToken' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Lists table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Lists'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:mailchimp:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

sys_information

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information
Columns
Name Type Description
Product String The name of the product.
Version String The version number of the product.
Datasource String The name of the datasource the product connects to.
NodeId String The unique identifier of the machine where the product is installed.
HelpURL String The URL to the product's help documentation.
License String The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.)
Location String The file path location where the product's library is stored.
Environment String The version of the environment or rumtine the product is currently running under.
DataSyncVersion String The tier of Sync required to use this connector.
DataSyncCategory String The category of Sync functionality (e.g., Source, Destination).

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Property Description
AuthScheme Whether to use APIKey Authentication or OAuth Authentication when connecting to MailChimp.
APIKey The API key used for accessing your MailChimp account.

Connection

Property Description
IncludeCustomFields Set whether to include custom fields that are added to the ListMembers view. This defaults to true.

OAuth

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
Datacenter The datacenter used for your MailChimp account.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property Description
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Mailchimp.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
AuthScheme Whether to use APIKey Authentication or OAuth Authentication when connecting to MailChimp.
APIKey The API key used for accessing your MailChimp account.

AuthScheme

Whether to use APIKey Authentication or OAuth Authentication when connecting to MailChimp.

Possible Values

OAuth, APIKey

Data Type

string

Default Value

APIKey

Remarks
  • OAuth: Set this to perform OAuth authentication.
  • APIKey: Set this to perform APIKey authentication.

APIKey

The API key used for accessing your MailChimp account.

Data Type

string

Default Value

""

Remarks

The API key used for accessing your MailChimp account. The API key can be found in MailChimp by going to Account -> Extras -> API Keys.

Connection

This section provides a complete list of connection properties you can configure.

Property Description
IncludeCustomFields Set whether to include custom fields that are added to the ListMembers view. This defaults to true.

IncludeCustomFields

Set whether to include custom fields that are added to the ListMembers view. This defaults to true.

Data Type

bool

Default Value

true

Remarks

Set whether to include custom fields that are added to the ListMembers view. This defaults to true.

OAuth

This section provides a complete list of OAuth properties you can configure.

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
Datacenter The datacenter used for your MailChimp account.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
CallbackURL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

OFF

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

OAuthAccessToken

The access token for connecting using OAuth.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

Datacenter

The datacenter used for your MailChimp account.

Possible Values

None, US1, US2, US3, US4, US5, US6, US7, US8, US9, US10, US11, US12, US13

Data Type

string

Default Value

None

Remarks

The datacenter used for your MailChimp account. This value can be found in the URL when logging into MailChimp in the browser. The datacenter is embedded in the APIKey and when using OAuth the connector obtains the datacenter automatically. However, you can set this property to save an extra request when you authenticate using OAuth.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.

Data Type

string

Default Value

%APPDATA%\MailChimp Data Provider\OAuthSettings.txt

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the connector saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.

Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

The default location is "%APPDATA%\MailChimp Data Provider\OAuthSettings.txt" with %APPDATA% set to the user's configuration directory. The default values are

  • Windows: "register://%DSN"
  • Unix: "%AppData%..."
  • Mac: "%AppData%..."

where DSN is the name of the current DSN used in the open connection.

The following table lists the value of %APPDATA% by OS:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

CallbackURL

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.

Data Type

string

Default Value

""

Remarks

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\MailChimp Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\MailChimp Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Mailchimp.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from Mailchimp.

Data Type

int

Default Value

1000

Remarks

The Pagesize property affects the maximum number of results to return per page from Mailchimp. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

  • Each root element defines the name of a view.
  • Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Lists WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json

Note that the specified path is not embedded in quotation marks.