Adding a Connection to Mailchimp

To add a connection to Mailchimp:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Mailchimp icon is not available, click the Add More icon to download and install the Mailchimp connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting 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

You must create a custom application that includes a client Id and client secret for authentication. See Creating a Custom OAuth Application for more information.

Before you connect, set the following variables:

• OAuthClientId: The client Id assigned when you registered your custom OAuth application.
• OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.

Click Connect to Mailchimp to open the OAuth endpoint in your default browser. Log in and grant permissions to the application.

The driver then completes the OAuth process as follows:

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values so that they persist across connections.

Creating 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".

This section details a selection of advanced features of the Mailchimp Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

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

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

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 CData Sync App models the Mailchimp API in Tables so that it can be easily queried and updated.

The Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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.

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

Mailchimp Connector for CData Sync Tables

Name	Description
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.
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.

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	Integer	True	If a reply, the id of the parent feedback item.
BlockId	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.

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

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.

A list of an ecommerce cart's lines.

Table Specific Information

Select

The Sync App 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 Sync App.

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

A list of an account's ecommerce carts.

Table Specific Information

Select

The Sync App 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 Sync App.

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

A list of an account's ecommerce customers.

Table Specific Information

Select

The Sync App 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 Sync App.

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.

A list of an ecommerce order's lines.

Table Specific Information

Select

The Sync App 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 Sync App.

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

A list of an account's ecommerce orders.

Table Specific Information

Select

The Sync App 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 Sync App.

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

A list of an account's ecommerce products.

Table Specific Information

Select

The Sync App 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 Sync App.

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.

A list of an ecommerce product's variants.

Table Specific Information

Select

The Sync App 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 Sync App.

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.

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

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.

A listing of this list's interest categories.

Table Specific Information

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

Select

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.

A list of this category's interests

Table Specific Information

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

Select

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.

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.

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.

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.

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

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.

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 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 ('[email protected]','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.

A list of available segments.

Table Specific Information

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

Select

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.

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.

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

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	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 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 for CData Sync Views

Name	Description
AuthorizedApps	A list of applications authorized to access the account.
AutomationEmailQueues	A summary of the queue for an email in an automation workflow.
AutomationEmails	A summary of the emails in an automation workflow.
Automations	A summary of the automations within an account.
AutomationsRemovedSubscribers	A summary of the subscribers removed from an automation workflow.
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.
ListMemberTags	Tags assigned to specific members.
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.

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.

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

Table Specific Information

SELECT is supported for AutomationEmailQueues.

Select

SELECT Id, WorkflowId, EmailAddress, NextSend FROM AutomationEmailQueues WHERE WorkflowId = 'abc123';

Columns

Name	Type	Description
Id [KEY]	String	The MD5 hash of the lowercase version of the list member's email address.
WorkflowId [KEY]	String	A string that uniquely identifies an automation workflow.
EmailId [KEY]	String	A string that uniquely identifies an email in an automation workflow.
ListId	String	A string that uniquely identifies a list.
EmailAddress	String	Email Address
NextSend	String	Next Send

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.

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.

A summary of the subscribers removed from an automation workflow.

Table Specific Information

SELECT is supported for AutomationsRemovedSubscribers.

Select

SELECT Id, WorkflowId, EmailAddress FROM AutomationsRemovedSubscribers WHERE WorkflowId = 'abc123';

Columns

Name	Type	Description
Id [KEY]	String	The MD5 hash of the lowercase version of the list member's email address.
WorkflowId [KEY]	String	A string that uniquely identifies an automation workflow.
ListId	String	A string that uniquely identifies a list.
EmailAddress	String	Email Address

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.

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

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

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.

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

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

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.

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

Table Specific Information

SELECT is supported for ListGrowthHistory.

Select

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.

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.

Tags assigned to specific members.

Table Specific Information

SELECT is supported for ListMemberTags.

Select

SELECT Id, Name, TimeAdded FROM ListMemberTags WHERE ListId = '12345';

Columns

Name	Type	Description
Id [KEY]	String	The unique ID of the tag.
Name	String	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	Date and time the tag was added to the member.
ListId	String	The ID of the list on which the member of this tag belongs to.
MemberId	String	The ID of the member this tag is assigned to.

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.

A list of abuse complaints for a specific list.

Table Specific Information

SELECT is supported for ReportAbuse.

Select

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

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

Table Specific Information

SELECT is supported for ReportAdvice.

Select

SELECT * FROM ReportAdvice WHERE CampaignId = 'abc'

Columns

Name	Type	Description
CampaignId	String	The CampaignId for the table.
Type	String	The 'type' of message ('negative', 'positive', 'neutral').
Message	String	The advice message.

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

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.

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

Table Specific Information

SELECT is supported for ReportDomainPerformance.

Select

SELECT * FROM ReportDomainPerformance WHERE CampaignId = 'abc'

Columns

Name	Type	Description
CampaignId	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.

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.

Top open locations for a specific campaign.

Columns

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

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.

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.

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.

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.

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.

The connection string 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 in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

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
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.
Datacenter	The datacenter used for your MailChimp account.

SSL

---

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

Firewall

---

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.

Proxy

---

Property	Description
ProxyAutoDetect	Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.
ProxyServer	The hostname or IP address of the proxy server that you want to route HTTP traffic through.
ProxyPort	The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.
ProxyAuthScheme	Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.
ProxyUser	The username of a user account registered with the proxy server specified in the ProxyServer connection property.
ProxyPassword	The password associated with the user specified in the ProxyUser connection property.
ProxySSLType	The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Logging

---

Property	Description
LogModules	Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Schema

---

Property	Description
Location	Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Tables	Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Views	Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .

Miscellaneous

---

Property	Description
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other	Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
Pagesize	Specifies the maximum number of results to return from Mailchimp, per page. This setting overrides the default page size set by the datasource, which is optimized for most use cases.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
UserDefinedViews	Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

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.

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

Remarks

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

The API key used for accessing your MailChimp account.

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.

This section provides a complete list of the Connection properties you can configure in the connection string for this provider.

---

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

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

Remarks

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

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.
Datacenter	The datacenter used for your MailChimp account.

Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.

Remarks

OAuthClientId is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.

Remarks

OAuthClientSecret is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

The datacenter used for your MailChimp account.

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 Sync App obtains the datacenter automatically. However, you can set this property to save an extra request when you authenticate using OAuth.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

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

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

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.

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

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

---

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.

Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Note: By default, the Sync App connects to the system proxy. To disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

The following table provides port number information for each of the supported protocols.

Protocol	Default Port	Description
TUNNEL	80	The port where the Sync App opens a connection to Mailchimp. Traffic flows back and forth via the proxy at this location.
SOCKS4	1080	The port where the Sync App opens a connection to Mailchimp. SOCKS 4 then passes theFirewallUser value to the proxy, which determines whether the connection request should be granted.
SOCKS5	1080	The port where the Sync App sends data to Mailchimp. If the SOCKS 5 proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Specifies the TCP port to be used for a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Identifies the user ID of the account authenticating to a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Specifies the password of the user account authenticating to a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

---

Property	Description
ProxyAutoDetect	Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.
ProxyServer	The hostname or IP address of the proxy server that you want to route HTTP traffic through.
ProxyPort	The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.
ProxyAuthScheme	Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.
ProxyUser	The username of a user account registered with the proxy server specified in the ProxyServer connection property.
ProxyPassword	The password associated with the user specified in the ProxyUser connection property.
ProxySSLType	The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.

Remarks

When this connection property is set to True, the Sync App checks your system proxy settings for existing proxy server configurations (no need to manually supply proxy server details).

This connection property takes precedence over other proxy settings. Set to False if you want to manually configure the Sync App to connect to a specific proxy server.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

The hostname or IP address of the proxy server that you want to route HTTP traffic through.

Remarks

The Sync App only routes HTTP traffic through the proxy server specified in this connection property when ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead routes HTTP traffic through the proxy server specified in your system proxy settings.

The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.

Remarks

The Sync App only routes HTTP traffic through the proxy server port specified in this connection property when ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead routes HTTP traffic through the proxy server port specified in your system proxy settings.

For other proxy types, see FirewallType.

Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.

Remarks

The authentication type can be one of the following:

• BASIC: The Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NTLM: The Sync App retrieves an NTLM token.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• NONE: Set this when the ProxyServer does not require authentication.

For all values other than "NONE", you must also set the ProxyUser and ProxyPassword connection properties.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

The username of a user account registered with the proxy server specified in the ProxyServer connection property.

Remarks

The ProxyUser and ProxyPassword connection properties are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

After selecting one of the available authentication types in ProxyAuthScheme, set this property as follows:

ProxyAuthScheme Value	Value to set for ProxyUser
BASIC	The user name of a user registered with the proxy server.
DIGEST	The user name of a user registered with the proxy server.
NEGOTIATE	The username of a Windows user who is a valid user in the domain or trusted domain that the proxy server is part of, in the format user@domain or domain\user.
NTLM	The username of a Windows user who is a valid user in the domain or trusted domain that the proxy server is part of, in the format user@domain or domain\user.
NONE	Do not set the ProxyPassword connection property.

The Sync App only uses this username if ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead uses the username specified in your system proxy settings.

The password associated with the user specified in the ProxyUser connection property.

Remarks

The ProxyUser and ProxyPassword connection properties are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

After selecting one of the available authentication types in ProxyAuthScheme, set this property as follows:

ProxyAuthScheme Value	Value to set for ProxyPassword
BASIC	The password associated with the proxy server user specified in ProxyUser.
DIGEST	The password associated with the proxy server user specified in ProxyUser.
NEGOTIATE	The password associated with the Windows user account specified in ProxyUser.
NTLM	The password associated with the Windows user account specified in ProxyUser.
NONE	Do not set the ProxyPassword connection property.

For SOCKS 5 authentication or tunneling, see FirewallType.

The Sync App only uses this password if ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead uses the password specified in your system proxy settings.

The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.

Remarks

This property determines when to use SSL for the connection to the HTTP proxy specified by ProxyServer. You can set this connection property to the following values :

AUTO	Default setting. If ProxyServer is set to an HTTPS URL, the Sync App uses the TUNNEL option. If ProxyServer is set to an HTTP URL, the component uses the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is made through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed. If you want to explicitly configure proxy exceptions for this connection, set ProxyAutoDetect to False.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

---

Property	Description
LogModules	Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Remarks

This property lets you customize the log file content by specifying the logging modules to include. Logging modules categorize logged information into distinct areas, such as query execution, metadata, or SSL communication. Each module is represented by a four-character code, with some requiring a trailing space for three-letter names.

For example, EXEC logs query execution, and INFO logs general provider messages. To include multiple modules, separate their names with semicolons as follows: INFO;EXEC;SSL.

The Verbosity connection property takes precedence over the module-based filtering specified by this property. Only log entries that meet the verbosity level and belong to the specified modules are logged. Leave this property blank to include all available modules in the log file.

For a complete list of available modules and detailed guidance on configuring logging, refer to the Advanced Logging section in Logging.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

---

Property	Description
Location	Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Tables	Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Views	Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .

Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.

Remarks

The Location property is only needed if you want to either customize definitions (for example, change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

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

Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Linux	~/.config

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .

Remarks

Listing all available tables from some databases can take extra time, thus degrading performance. Providing a list of tables in the connection string saves time and improves performance.

If there are lots of tables available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those tables. To do this, 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: If you are connecting to a data source with multiple schemas or catalogs, you must specify each table you want to view by its fully qualified name. This avoids ambiguity between tables that may exist in multiple catalogs or schemas.

Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .

Remarks

Listing all available views from some databases can take extra time, thus degrading performance. Providing a list of views in the connection string saves time and improves performance.

If there are lots of views available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those views. To do this, 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: If you are connecting to a data source with multiple schemas or catalogs, you must specify each view you want to examine by its fully qualified name. This avoids ambiguity between views that may exist in multiple catalogs or schemas.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

---

Property	Description
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other	Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
Pagesize	Specifies the maximum number of results to return from Mailchimp, per page. This setting overrides the default page size set by the datasource, which is optimized for most use cases.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
UserDefinedViews	Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Remarks

This property sets an upper limit on the number of rows the Sync App returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.

Remarks

This property allows advanced users to configure hidden properties for specialized scenarios. These settings are not required for normal use cases but can address unique requirements or provide additional functionality. Multiple properties can be defined in a semicolon-separated list.

Note: It is strongly recommended to set these properties only when advised by the support team to address specific scenarios or issues.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

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.