Mailchimp Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8895 of the connector.

Get Started¶

Mailchimp Version Support

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

Establish a Connection¶

Connect to Mailchimp¶

Mailchimp supports the following authentication methods:

• APIKey
• OAuth

API Key¶

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

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

Once you have the value of the API Key:

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

OAuth¶

Desktop Applications¶

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

Get and refresh the OAuth access token:

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

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

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

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

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

Create a Custom OAuth Application¶

Create a Custom OAuth Application¶

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

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

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

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

1. Log into your Mailchimp account.

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

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

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

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

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

Important Notes¶

Configuration Files and Their Paths¶

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

Advanced Features¶

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

User Defined Views

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

SSL Configuration

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

Proxy

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

Query Processing

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

See Query Processing for more information.

User Defined Views¶

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

There are two ways to create user defined views:

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

Define Views Using a Configuration File¶

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

Define Views Using DDL Statements¶

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

Create a View¶

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

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

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

Alter a View¶

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

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

The view is then updated in the JSON configuration file.

Drop a View¶

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

DROP LOCAL VIEW [MyViewName]

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

Schema for User Defined Views¶

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

Work with User Defined Views¶

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

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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

Resulting in the effective query to the source:

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

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

SSL Configuration¶

Customize the SSL Configuration¶

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

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

Data Model¶

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

Tables

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

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

Dynamic tables¶

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

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

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

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

Views

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

Dynamic views¶

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

Stored Procedures

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

in the data source.

Tables¶

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

Mailchimp Connector Tables¶

AutomationEmailQueues¶

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

Table Specific Information¶

SELECT and INSERT are supported for AutomationEmailQueues.

Select¶

Select * from AutomationEmailQueues

Insert¶

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

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

Columns¶

AutomationsRemovedSubscribers¶

A summary of the subscribers removed from an automation workflow.

Table Specific Information¶

SELECT and INSERT are supported for AutomationsRemovedSubscribers.

Select¶

Select * from AutomationsRemovedSubscribers

Insert¶

The WorkflowId, and EmailAddress are required for INSERTs.

Insert Into AutomationsRemovedSubscribers (WorkflowId, EmailAddress) Values('3456df3456','test1@mail.com');

Columns¶

CampaignFeedback¶

A summary of the comment feedback for a specific campaign.

Table Specific Information¶

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

Select¶

Select * from CampaignFeedback

Insert¶

The CampaignId and Message are required for INSERTs.

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

Columns¶

CampaignFolders¶

Folders for organizing campaigns

Columns¶

Campaigns¶

A summary of the campaigns within an account.

Table Specific Information¶

SELECT, UPDATE and DELETE are supported for Campaigns.

Update¶

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

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


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

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

Columns¶

EcommerceCartLines¶

A list of an ecommerce cart's lines.

Table Specific Information¶

Select¶

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

The StoreId can be retrieved by selecting the ECommerceStores view.

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

For example:

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

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

Delete¶

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

DELETE FROM EcommerceCartLines WHERE StoreId = 'Test_Store123' and CartId = '44' and ID = '88'

Columns¶

EcommerceCarts¶

A list of an account's ecommerce carts.

Table Specific Information¶

Select¶

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

The StoreId can be retrieved by selecting the ECommerceStores view.

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

For example:

SELECT * FROM EcommerceCarts WHERE StoreId = 'Test_Store123'

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

Columns¶

EcommerceCustomers¶

A list of an account's ecommerce customers.

Table Specific Information¶

Select¶

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

The StoreId can be retrieved by selecting the ECommerceStores view.

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

For example:

SELECT * FROM EcommerceCustomers WHERE StoreId = 'Test_Store123'

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

Columns¶

EcommerceOrderLines¶

A list of an ecommerce order's lines.

Table Specific Information¶

Select¶

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

The StoreId can be retrieved by selecting the ECommerceStores view.

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

For example:

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

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

Delete¶

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

DELETE FROM EcommerceOrderLines WHERE StoreId = 'Test_Store123' and OrderId = '44' and ID = '88'

Columns¶

EcommerceOrders¶

A list of an account's ecommerce orders.

Table Specific Information¶

Select¶

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

The StoreId can be retrieved by selecting the ECommerceStores view.

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

For example:

SELECT * FROM EcommerceOrders WHERE StoreId = 'Test_Store123'

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

Columns¶

EcommerceProducts¶

A list of an account's ecommerce products.

Table Specific Information¶

Select¶

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

The StoreId can be retrieved by selecting the ECommerceStores view.

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

For example:

SELECT * FROM EcommerceProducts WHERE StoreId = 'Test_Store123'

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

Columns¶

EcommerceProductVariants¶

A list of an ecommerce product's variants.

Table Specific Information¶

Select¶

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

The StoreId can be retrieved by selecting the ECommerceStores view.

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

For example:

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

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

Delete¶

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

DELETE FROM EcommerceProductVariants WHERE StoreId = 'Test_Store123' and ProductId = '44' and ID = '88'

Columns¶

FileManagerFiles¶

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

Table Specific Information¶

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

Select¶

SELECT * FROM FileManagerFiles

Insert¶

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

INSERT INTO FileManagerFiles (Name, FolderID, FileData) VALUES ('myNewFolder', 'myFolderID', 'myBase64EncodedFileData')

Columns¶

FileManagerFolders¶

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

Table Specific Information¶

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

Insert¶

The Name is required for INSERTs.

INSERT INTO FileManagerFolders (Name) VALUES ('myNewFolder'')

Columns¶

ListInterestCategories¶

A listing of this list's interest categories.

Table Specific Information¶

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

Select¶

Select * from ListInterestCategories

Select * from ListInterestCategories where ListId='abc'

Insert¶

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

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

Columns¶

ListInterests¶

A list of this category's interests

Table Specific Information¶

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

Select¶

Select * from ListInterests

Select * from ListInterests where ListId='abc'

Insert¶

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

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

Columns¶

ListMemberEvents¶

Events information for a specific list.

Table Specific Information¶

SELECT and INSERT are supported for ListMemberEvents.

Select¶

Select * from ListMemberEvents

Insert¶

Name column is required when INSERTing.

Columns¶

ListMemberNotes¶

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

Table Specific Information¶

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

Select¶

Select * from ListMemberNotes

Insert¶

No fields are are required when INSERTing.

Columns¶

ListMembers¶

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

Table Specific Information¶

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

Select¶

Select * from ListMembers

Insert¶

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

INSERT INTO ListMembers (ListId, EmailAddress, Status) VALUES ('myListId', 'myEmailAddress', 'subscribed')

Columns¶

ListMemberTags¶

Tags assigned to a certain member/members.

Table Specific Information¶

SELECT and INSERT are supported for ListMemberTags.

Select¶

Select * from ListMemberTags

Insert¶

Name, ListId and MemberId are required when Inserting.

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

Columns¶

ListMergeFields¶

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

Table Specific Information¶

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

Select¶

Select * from ListMergeFields

Select * from ListMergeFields where ListId='abc'

Insert¶

The Name and ListID are required for INSERTs.

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

Columns¶

Lists¶

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

Table Specific Information¶

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

Insert¶

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

INSERT INTO Lists (Name, PermissionReminder, EmailTypeOption, Contact_Company, Contact_Address1, Contact_City, Contact_State, Contact_Zip, Contact_Country, CampaignDefaults_FromName, CampaignDefaults_FromEmail, CampaignDefaults_Subject, CampaignDefaults_Language) VALUES ('myName', 'myPermissionReminder', 'true', 'myCompany', 'myAddress', 'myCity', 'myState', 'myZip', 'myCountry', 'myFromName', 'myFromEmail', 'myDefaultSubject', 'myDefaultLanguage')

Columns¶

ListSegmentMembers¶

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

Table Specific Information¶

SELECT, INSERT, and DELETE are supported for ListSegmentMembers.

Select¶

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

Select * from ListSegmentMembers where SegmentId = '2623'

Insert¶

The Name and ListID are required for INSERTs.

INSERT into ListSegmentMembers (EmailAddress,ListId,SegmentId) values ('abc@gmail.com','44a64c46cb','7032720')

Columns¶

ListSegments¶

A list of available segments.

Table Specific Information¶

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

Select¶

Select * from ListSegments

Select * from ListSegments where ListId = '5152'

Insert¶

The Name and ListID are required for INSERTs.

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

Columns¶

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.

ListsWebhooks¶

Webhooks configured for the given list.

Columns¶

TemplateFolders¶

Folders for organizing templates

Columns¶

Templates¶

A list an account's available templates.

Table Specific Information¶

SELECT and DELETE are supported for Templates.

Columns¶

Views¶

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

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

Mailchimp Connector Views¶

AuthorizedApps¶

A list of applications authorized to access the account.

Table Specific Information¶

SELECT is supported for AuthorizedApps.

Columns¶

AutomationEmails¶

A summary of the emails in an automation workflow.

Table Specific Information¶

SELECT is supported for AutomationEmails.

Select¶

Select * from AutomationEmails

Columns¶

Automations¶

A summary of the automations within an account.

Columns¶

CampaignOpenEmailDetails¶

A list of members who opened the campaign email.

Table Specific Information¶

SELECT is supported for CampaignOpenEmailDetails.

Select¶

For example:

SELECT * FROM CampaignOpenEmailDetails
SELECT * FROM CampaignOpenEmailDetails WHERE CampaignId = '9f218dcf18'

Columns¶

ConversationMessages¶

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

Table Specific Information¶

SELECT is supported for ConversationMessages.

Select¶

SELECT * from ConversationMessages

Columns¶

Conversations¶

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

Columns¶

EcommerceStores¶

A list of an account's ecommerce stores.

Table Specific Information¶

Select¶

• Id supports the = operator.

For example:

SELECT * FROM EcommerceStores WHERE  ID = '44'

Columns¶

ListAbuse¶

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

Table Specific Information¶

SELECT is supported for ListAbuse.

Select¶

Select * from ListAbuse

Select * from ListAbuse where ListId='abc'

Columns¶

ListActivity¶

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

Table Specific Information¶

SELECT is supported for ListActivity.

Select¶

Select * from ListActivity

Select * from ListActivity where ListId='abc'

Columns¶

ListClients¶

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

Columns¶

ListGrowthHistory¶

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

Table Specific Information¶

SELECT is supported for ListGrowthHistory.

Select¶

Select * from ListGrowthHistory

Select * from ListGrowthHistory where ListId='abc'

Columns¶

ListMemberActivity¶

The last 50 member events for a list.

Table Specific Information¶

SELECT is supported for ListMemberActivity.

Select¶

Select * from ListMemberActivity

Columns¶

ListSignupForms¶

Collection of List Signup Forms

Columns¶

ReportAbuse¶

A list of abuse complaints for a specific list.

Table Specific Information¶

SELECT is supported for ReportAbuse.

Select¶

Select * from ReportAbuse

Select * from ReportAdvice where CampaignId='abc'

Columns¶

ReportAdvice¶

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

Table Specific Information¶

SELECT is supported for ReportAdvice.

Select¶

Select * from ReportAdvice

Select * from ReportAdvice where CampaignId='abc'

Columns¶

ReportClickDetails¶

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

Table Specific Information¶

SELECT is supported for ReportClickDetails.

Select¶

Select * from ReportClickDetails

Select * from ReportClickDetails where CampaignId='abc'

Columns¶

ReportClickDetailsMembers¶

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

Table Specific Information¶

SELECT is supported for ReportClickDetailsMembers.

Select¶

Select * from ReportClickDetailsMembers

Columns¶

ReportDomainPerformance¶

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

Table Specific Information¶

SELECT is supported for ReportDomainPerformance.

Select¶

Select * from ReportDomainPerformance

Select * from ReportDomainPerformance where CampaignId='abc'

Columns¶

ReportEmailActivity¶

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

Table Specific Information¶

SELECT is supported for ReportEmailActivity.

Select¶

Select * from ReportEmailActivity

Columns¶

ReportLocations¶

Top open locations for a specific campaign.

Columns¶

Reports¶

A list of reports containing campaigns marked as Sent.

Columns¶

ReportSentTo¶

A list of subscribers who were sent a specific campaign.

Table Specific Information¶

SELECT is supported for ReportSentTo.

Select¶

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

Columns¶

ReportUnsubscribes¶

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

Table Specific Information¶

SELECT is supported for ReportUnsubscribes.

Select¶

Select * from ReportUnsubscribes

Columns¶

SurveyResponses¶

List of survey responses.

Table Specific Information¶

SELECT is supported for SurveyResponses.

Select¶

Select * from SurveyResponses

Columns¶

Surveys¶

Get reports for surveys.

Table Specific Information¶

SELECT is supported for Surveys.

Columns¶

Stored Procedures¶

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

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

Mailchimp Connector Stored Procedures¶

CampaignCancel¶

Cancels a MailChimp Regular or Plain-Text campaign .

Input¶

Result Set Columns¶

CampaignPause¶

Pauses a MailChimp RSS campaign.

Input¶

Result Set Columns¶

CampaignResume¶

Resumes a MailChimp RSS campaign.

Input¶

Result Set Columns¶

CampaignSchedule¶

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

Input¶

Result Set Columns¶

CampaignSend¶

Sends a MailChimp campaign.

Input¶

Result Set Columns¶

CampaignTest¶

Sends a test email for a MailChimp campaign.

Input¶

Result Set Columns¶

CampaignUnschedule¶

Unschedules a MailChimp campaign.

Input¶

Result Set Columns¶

GetOAuthAccessToken¶

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

Input¶

Result Set Columns¶

GetOAuthAuthorizationURL¶

Obtains the OAuth authorization URL used for authentication with MailChimp.

Input¶

Result Set Columns¶

System Tables¶

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

Schema Tables¶

The following tables return database metadata for Mailchimp:

• 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 connector can offload to the data source.

Query Information Tables¶

The following table returns query statistics for data modification queries:

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

sys_catalogs¶

Lists the available databases.

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

SELECT * FROM sys_catalogs

Columns¶

sys_schemas¶

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns¶

sys_tables¶

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns¶

sys_tablecolumns¶

Describes the columns of the available tables and views.

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

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

Columns¶

sys_procedures¶

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns¶

sys_procedureparameters¶

Describes stored procedure parameters.

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

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

Columns¶

sys_keycolumns¶

Describes the primary and foreign keys.

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

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

Columns¶

sys_foreignkeys¶

Describes the foreign keys.

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

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns¶

sys_primarykeys¶

Describes the primary keys.

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

SELECT * FROM sys_primarykeys

Columns¶

sys_indexes¶

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

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

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns¶