This page provides a guide to Establishing a Connection to Mailchimp in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Mailchimp

Establishing a Connection shows how to authenticate to Mailchimp and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Mailchimp through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Mailchimp by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

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

Customizing the SSL Configuration

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

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

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

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

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

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 Cloud 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 Cloud 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 Cloud models the data in Mailchimp as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Mailchimp Tables

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

Table Specific Information

SELECT and INSERT are supported for AutomationEmailQueues.

Select

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

Insert

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

INSERT INTO AutomationEmailQueues (WorkflowId, EmailId, EmailAddress) VALUES ('myWorkflowId', 'myEmailId', 'myEmailAddress')

Columns

A summary of the subscribers removed from an automation workflow.

Table Specific Information

SELECT and INSERT are supported for AutomationsRemovedSubscribers.

Select

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

Insert

The WorkflowId, and EmailAddress are required for INSERTs.

Insert Into AutomationsRemovedSubscribers (WorkflowId, EmailAddress) Values('3456df3456','[email protected]'); 

Columns

A summary of the comment feedback for a specific campaign.

Table Specific Information

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

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.

Insert

The CampaignId and Message are required for INSERTs.

INSERT INTO CampaignFeedback (CampaignId, Message) VALUES ('myCampaignId', 'myMessage')

Columns

Folders for organizing campaigns

Columns

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

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 and INSERT are supported for ConversationMessages.

Select

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

Insert

ConversationId, FromEmail, and Read are required for INSERTs.

INSERT INTO ConversationMessages (ConversationId, FromEmail, Read) VALUES ('myConversationId', 'myFromEmail', 'true')

Columns

A list of an ecommerce cart's lines.

Table Specific Information

Select

The Cloud will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The StoreId and CartId are required to make a request and the rest of the filter is executed client-side within the Cloud.

The StoreId can be retrieved by selecting the ECommerceStores view.

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

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

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

Columns

A list of an account's ecommerce carts.

Table Specific Information

Select

The Cloud will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The StoreId is required to make a request and the rest of the filter is executed client-side within the Cloud.

The StoreId can be retrieved by selecting the ECommerceStores view.

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

SELECT * FROM EcommerceCarts WHERE StoreId = 'Test_Store123'

SELECT * FROM EcommerceCarts WHERE StoreId = 'Test_Store123' and Id = '44'

Columns

A list of an account's ecommerce customers.

Table Specific Information

Select

The Cloud will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The StoreId is required to make a request and the rest of the filter is executed client-side within the Cloud.

The StoreId can be retrieved by selecting the ECommerceStores view.

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

SELECT * FROM EcommerceCustomers WHERE StoreId = 'Test_Store123'

SELECT * FROM EcommerceCustomers WHERE StoreId = 'Test_Store123' and Id = '44'

Columns

A list of an ecommerce order's lines.

Table Specific Information

Select

The Cloud will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The StoreId and OrderId are required to make a request and the rest of the filter is executed client-side within the Cloud.

The StoreId can be retrieved by selecting the ECommerceStores view.

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

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

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

Columns

A list of an account's ecommerce orders.

Table Specific Information

Select

The Cloud will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The StoreId is required to make a request and the rest of the filter is executed client-side within the Cloud.

The StoreId can be retrieved by selecting the ECommerceStores view.

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

SELECT * FROM EcommerceOrders WHERE StoreId = 'Test_Store123'

SELECT * FROM EcommerceOrders WHERE StoreId = 'Test_Store123' and Id = '44'

Columns

A list of an account's ecommerce products.

Table Specific Information

Select

The Cloud will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The StoreId is required to make a request and the rest of the filter is executed client-side within the Cloud.

The StoreId can be retrieved by selecting the ECommerceStores view.

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

SELECT * FROM EcommerceProducts WHERE StoreId = 'Test_Store123'

SELECT * FROM EcommerceProducts WHERE StoreId = 'Test_Store123' and Id = '44'

Columns

A list of an ecommerce product's variants.

Table Specific Information

Select

The Cloud will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The StoreId is required to make a request and the rest of the filter is executed client-side within the Cloud.

The StoreId can be retrieved by selecting the ECommerceStores view.

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

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

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

Columns

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

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

A listing of this list's interest categories.

Table Specific Information

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

Select

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

Insert

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

INSERT INTO ListInterestCategories (Name, Type, ListID) VALUES ('myNewListInterestCategory', 'myType', 'myListID')

Columns

A list of this category's interests

Table Specific Information

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

Select

A ListId and CategoryId are required to retrieve data from this table. If none are specified in the WHERE clause, the first ones available for your account will be used.

Insert

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

INSERT INTO ListInterests (Name, CategoryId, ListID) VALUES ('myNewListInterest', 'myCategory', 'myListID')

Columns

Events information for a specific list.

Table Specific Information

SELECT and INSERT are supported for ListMemberEvents.

Select

A ListId and EmailId are required to retrieve data from this table. If none are specified in the WHERE clause, the first ones available for your account will be used.

Insert

Name column is required when INSERTing.

Columns

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

A ListId and EmailId are required to retrieve data from this table. If none are specified in the WHERE clause, the first ones available for your account will be used.

Insert

No fields are are required when INSERTing.

Columns

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

Tags assigned to a certain member/members.

Table Specific Information

SELECT and INSERT are supported for ListMemberNotes.

Select

A ListId and MemberId are required to retrieve data from this table. If none are specified in the WHERE clause, the first ones available for your account will be used.

Insert

Name, ListId and MemberId are required when Inserting.

INSERT INTO ListMemberTags (Name, ListId, MemberId) VALUES ('test', '434gfsd4', 'esdfsd4563vfghxgsf45645sdfgdfg')

Columns

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

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

Insert

The Name and ListID are required for INSERTs.

INSERT INTO ListMergeFields (Name, ListID) VALUES ('myNewListMergeField', 'myListID')

Columns

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

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

Table Specific Information

SELECT, INSERT, and DELETE are supported for ListSegmentMembers.

Select

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

Insert

The Name and ListID are required for INSERTs.

INSERT into ListSegmentMembers (EmailAddress,ListId,SegmentId) values ('[email protected]','44a64c46cb','7032720')

Columns

A list of available segments.

Table Specific Information

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

Select

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

Insert

The Name and ListID are required for INSERTs.

INSERT INTO ListSegments (Name, ListID) VALUES ('myNewListSegment', 'myListID')

Columns

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.

Webhooks configured for the given list.

Columns

Folders for organizing templates

Columns

A list an account's available templates.

Table Specific Information

SELECT and DELETE are supported for Templates.

Columns

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.

CData Cloud - Mailchimp Views

A list of applications authorized to access the account.

Table Specific Information

SELECT is supported for AuthorizedApps.

Columns

A summary of the emails in an automation workflow.

Table Specific Information

SELECT is supported for AutomationEmails.

Select

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

A summary of the automations within an account.

Columns

A list of members who opened the campaign email.

Table Specific Information

SELECT is supported for CampaignOpenEmailDetails.

Select

A CampaignId is required to retrieve data from this table. For example:

    SELECT * FROM CampaignOpenEmailDetails WHERE CampaignId = '9f218dcf18'

Columns

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

A list of an account's ecommerce stores.

Table Specific Information

Select

• Id supports the '=' operator.

SELECT * FROM EcommerceStores WHERE  Id = '44'

Columns

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

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

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

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

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

Columns

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

Table Specific Information

SELECT is supported for ListGrowthHistory.

Select

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

The last 50 member events for a list.

Table Specific Information

SELECT is supported for ListMemberActivity.

Select

Select * from ListMemberActivity

Columns

Collection of List Signup Forms

Columns

A list of abuse complaints for a specific list.

Table Specific Information

SELECT is supported for ReportAbuse.

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

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

Table Specific Information

SELECT is supported for ReportAdvice.

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

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

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

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

Table Specific Information

SELECT is supported for ReportClickDetailsMembers.

Select

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

Columns

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

Table Specific Information

SELECT is supported for ReportDomainPerformance.

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

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

Table Specific Information

SELECT is supported for ReportEmailActivity.

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

Top open locations for a specific campaign.

Columns

A list of reports containing campaigns marked as Sent.

Columns

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

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

Table Specific Information

SELECT is supported for ReportUnsubscribes.

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

List of survey responses.

Table Specific Information

SELECT is supported for SurveyResponses.

Select

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

Get reports for surveys.

Table Specific Information

SELECT is supported for Surveys.

Columns

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

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

CData Cloud - Mailchimp Stored Procedures

Cancels a MailChimp Regular or Plain-Text campaign .

Input

Result Set Columns

Pauses a MailChimp RSS campaign.

Input

Result Set Columns

Resumes a MailChimp RSS campaign.

Input

Result Set Columns

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

Input

Result Set Columns

Sends a MailChimp campaign.

Input

Result Set Columns

Sends a test email for a MailChimp campaign.

Input

Result Set Columns

Unschedules a MailChimp campaign.

Input

Result Set Columns

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

Schema Tables

The following tables return database metadata for Mailchimp:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

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

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

Query Information Tables

The following table returns query statistics for data modification queries:

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

Lists the available databases.

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

SELECT * FROM sys_catalogs

Columns

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Describes the columns of the available tables and views.

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

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Lists' 

Columns

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Describes stored procedure parameters.

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

SELECT * FROM sys_procedureparameters WHERE ProcedureName='GetOAuthAccessToken' AND Direction=1 OR Direction=2

Columns

Describes the primary and foreign keys.

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

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Lists' 
          

Columns

Describes the foreign keys.

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

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns