Connection String OptionsBack To Top

AuthScheme
APIKey
IncludeCustomFields
InitiateOAuth
OAuthClientId
OAuthClientSecret
OAuthAccessToken
Datacenter
OAuthSettingsLocation
CallbackURL
OAuthVerifier
OAuthExpiresIn
OAuthTokenTimestamp
SSLServerCert
FirewallType
FirewallServer
FirewallPort
FirewallUser
FirewallPassword
ProxyAutoDetect
ProxyServer
ProxyPort
ProxyAuthScheme
ProxyUser
ProxyPassword
ProxySSLType
ProxyExceptions
Logfile
Verbosity
LogModules
MaxLogFileSize
MaxLogFileCount
Location
BrowsableSchemas
Tables
Views
AutoCache
CacheDriver
CacheConnection
CacheLocation
CacheTolerance
Offline
CacheMetadata
BatchSize
ConnectionLifeTime
ConnectOnOpen
MaxRows
Other
Pagesize
PoolIdleTimeout
PoolMaxSize
PoolMinSize
PoolWaitTime
PseudoColumns
Readonly
RTK
SupportEnhancedSQL
Timeout
UseConnectionPooling

AuthScheme

Data Type

string

Default Value

"APIKey"

Remarks

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

APIKey

Data Type

string

Default Value

Remarks

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

IncludeCustomFields

Data Type

bool

Default Value

true

Remarks

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

InitiateOAuth

Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

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

OAuthClientId

Data Type

string

Default Value

Remarks

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

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

OAuthAccessToken

Data Type

string

Default Value

Remarks

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

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

Datacenter

Data Type

string

Default Value

"None"

Remarks

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

OAuthSettingsLocation

Data Type

string

Default Value

"%APPDATA%\\CData\\MailChimp Data Provider\\OAuthSettings.txt"

Remarks

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

Alternatively to specifying a file path, memory storage can be used instead. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (ex: memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike with the file based storage, you must manually store the credentials when closing the connection with memory storage to be able to set them in the connection when the process is started again. The OAuth property values can be retrieved with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties should be read from the last connection to be closed.

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


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

CallbackURL

Data Type

string

Default Value

Remarks

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

OAuthVerifier

Data Type

string

Default Value

Remarks

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

Authentication on Headless Machines

See Establishing a Connection to obtain the OAuthVerifier value.

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

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

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

OAuthExpiresIn

Data Type

string

Default Value

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

Data Type

string

Default Value

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSLServerCert

Data Type

string

Default Value

Remarks

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

This property can take the following forms:


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

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

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

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

FirewallType

Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy. Note that by default, the driver connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.


Type	Default Port	Description
TUNNEL	80	When this is set, the driver opens a connection to MailChimp and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your 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.

FirewallServer

Data Type

string

Default Value

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the driver uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

Data Type

int

Default Value

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

FirewallUser

Data Type

string

Default Value

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

FirewallPassword

Data Type

string

Default Value

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

ProxyAutoDetect

Data Type

bool

Default Value

false

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

NOTE: When this property is set to True, the proxy used is determined as follows:

A search from the JVM properties (http.proxy, https.proxy, socksProxy, etc.) is performed.
In the case that the JVM properties don't exist, a search from java.home/lib/net.properties is performed.
In the case that java.net.useSystemProxies is set to True, a search from the SystemProxy is performed.
In Windows only, an attempt is made to retrieve these properties from the Internet Options in the registry.

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

ProxyServer

Data Type

string

Default Value

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

Data Type

int

Default Value

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

ProxyAuthScheme

Data Type

string

Default Value

"BASIC"

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

BASIC: The driver performs HTTP BASIC authentication.
DIGEST: The driver performs HTTP DIGEST authentication.
NEGOTIATE: The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The driver does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

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

ProxyUser

Data Type

string

Default Value

Remarks

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

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain

domain\user

ProxyPassword

Data Type

string

Default Value

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the driver uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

Data Type

string

Default Value

"AUTO"

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:


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

ProxyExceptions

Data Type

string

Default Value

Remarks

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

Note that the driver uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Logfile

Data Type

string

Default Value

Remarks

Once this property is set, the driver will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.

Connection strings and version information are also logged, though connection properties containing sensitive information are masked automatically.

If a relative filepath is supplied, the location of the log file will be resolved based on the path found in the Location connection property.

For more control over what is written to the log file, you can adjust the Verbosity property.

Log contents are categorized into several modules. You can show/hide individual modules using the LogModules property.

To edit the maximum size of a single logfile before a new one is created, see MaxLogFileSize.

If you would like to place a cap on the number of logfiles generated, use MaxLogFileCount.

Java Logging

Java logging is also supported. To enable Java logging, set Logfile to:

Logfile=JAVALOG://myloggername

As in the above sample, JAVALOG:// is a required prefix to use Java logging, and you will substitute your own Logger.

The supplied Logger's getLogger method is then called, using the supplied value to create the Logger instance. If a logging instance already exists, it will reference the existing instance.

When Java logging is enabled, the Verbosity will now correspond to specific logging levels.

Verbosity

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

LogModules

Data Type

string

Default Value

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

MaxLogFileSize

Data Type

string

Default Value

"100MB"

Remarks

When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.

Adjust the maximum number of logfiles generated with MaxLogFileCount.

MaxLogFileCount

Data Type

int

Default Value

-1

Remarks

When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.

The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

Adjust the maximum size of the logfiles generated with MaxLogFileSize.

Location

Data Type

string

Default Value

"%APPDATA%\\CData\\MailChimp Data Provider\\Schema"

Remarks

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

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


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

BrowsableSchemas

Data Type

string

Default Value

Remarks

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

Tables

Data Type

string

Default Value

Remarks

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

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

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

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

Views

Data Type

string

Default Value

Remarks

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

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

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

AutoCache

Data Type

bool

Default Value

false

Remarks

When AutoCache = true, the driver automatically maintains a cache of your table's data in the database of your choice.

Setting the Caching Database

When AutoCache = true, the driver caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:

CacheLocation: Specifies the path to the file store.
CacheDriver and CacheConnection: Specifies a driver to a database and the connection string.

CacheDriver

Data Type

string

Default Value

Remarks

You can cache to any database for which you have a JDBC driver, including CData JDBC drivers.

The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you want to use to cache data.

Note that you must also add the CacheDriver JAR file to the classpath.

The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.

Derby and Java DB

The driver simplifies Derby configuration. Java DB is the Oracle distribution of Derby. The JAR file is shipped in the JDK. You can find the JAR file, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath:

jdbc:mailchimp:CacheLocation='c:/Temp/cachedir';APIKey=myAPIKey;

To customize the Derby JDBC URL, use CacheDriver and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:

jdbc:mailchimp:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';APIKey=myAPIKey;

SQLite

The following is a JDBC URL for the SQLite JDBC driver:

jdbc:mailchimp:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';APIKey=myAPIKey;

MySQL

The following is a JDBC URL for the included CData JDBC Driver for MySQL:

  jdbc:mailchimp:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';APIKey=myAPIKey;

SQL Server

The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:

jdbc:mailchimp:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';APIKey=myAPIKey;

Oracle

The following is a JDBC URL for the Oracle Thin Client:

jdbc:mailchimp:Cache Driver=oracle.jdbc.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';APIKey=myAPIKey;

NOTE: If using a version of Oracle older than 9i, the cache driver will instead be oracle.jdbc.driver.OracleDriver .

PostgreSQL

The following JDBC URL uses the official PostgreSQL JDBC driver:

jdbc:mailchimp:CacheDriver=cdata.jdbc.postgresql.PostgreSQLDriver;CacheConnection='jdbc:postgresql:User=postgres;Password=admin;Database=postgres;Server=localhost;Port=5432;';APIKey=myAPIKey;

CacheConnection

Data Type

string

Default Value

Remarks

The cache database is determined based on the CacheDriver and CacheConnection properties. Both properties are required to use the cache database. Examples of common cache database settings can be found below. For more information on setting the caching database's driver, refer to CacheDriver.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. Consult the documentation for the specific JDBC driver for more information on the available properties. Make sure to include the JDBC driver in your application's classpath.

Derby and Java DB

The driver simplifies caching to Derby, only requiring you to set the CacheLocation property to make a basic connection.

Alternatively, you can configure the connection to Derby manually using CacheDriver and CacheConnection. The following is the Derby JDBC URL syntax:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]

For example, to cache to an in-memory database, use the following:

jdbc:derby:memory

SQLite

To cache to SQLite, you can use the SQLite JDBC driver. The following is the syntax of the JDBC URL:

jdbc:sqlite:dataSource

Data Source: The path to an SQLite database file. Or, use a value of :memory to cache in memory.

MySQL

The installation includes the CData JDBC Driver for MySQL. The following is an example JDBC URL:

jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache

The following are typical connection properties:

Server: The IP address or domain name of the server you want to connect to.
Port: The port that the server is running on.
User: The user name provided for authentication to the database.
Password: The password provided for authentication to the database.
Database: The name of the database.

SQL Server

The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:

jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]

For example:

jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true

The following are typical SQL Server connection properties:

Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
Port: The port SQL Server is running on.
Database: The name of the SQL Server database.
Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.
To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.
User Id: The user name provided for authentication with SQL Server. This property is only needed if you are not using integrated security.
Password: The password provided for authentication with SQL Server. This property is only needed if you are not using integrated security.

Oracle

The following is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:

jdbc:oracle:thin:[userId/password]@[//]host[[:port][:sid]]

For example:

jdbc:oracle:thin:scott/tiger@myhost:1521:orcl

The following are typical connection properties:

Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).
Password: The password provided for authentication with the Oracle database.
User Id: The user Id provided for authentication with the Oracle database.

PostgreSQL

The following is the JDBC URL syntax for the official PostgreSQL JDBC driver:

jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]

For example, the following connection string connects to a database on the default host (localhost) and port (5432):

jdbc:postgresql:postgres

The following are typical connection properties:

Host: The address of the server hosting the PostgreSQL database.
Port: The port used to connect to the server hosting the PostgreSQL database.
Database: The name of the database.
User name: The user Id provided for authentication with the PostgreSQL database. You can specify this in the JDBC URL with the "user" parameter.
Password: The password provided for authentication with the PostgreSQL database.

CacheLocation

Data Type

string

Default Value

"%APPDATA%\\CData\\MailChimp Data Provider"

Remarks

The CacheLocation is a simple, file-based cache. The driver uses Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR file to the classpath. The JAR file, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.

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


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

CacheTolerance

Data Type

int

Default Value

600

Remarks

The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The driver checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Offline

Data Type

bool

Default Value

false

Remarks

When Offline = true, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.

CacheMetadata

Data Type

bool

Default Value

false

Remarks

As you execute queries with this property set, table metadata in the MailChimp catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.

When to Use CacheMetadata

The driver automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.

When Not to Use CacheMetadata

When you are working with volatile metadata: Metadata for a table is only retrieved the first time the connection to the table is made. To pick up new, changed, or deleted columns, you would need to delete and rebuild the metadata cache. Therefore, it is best to rely on the in-memory caching for cases where metadata changes often.
When you are caching to a database: CacheMetadata can only be used with CacheLocation. If you are caching to another database with the CacheDriver and CacheConnection properties, use AutoCache to cache implicitly. Or, use CACHE Statements to cache explicitly.

BatchSize

Data Type

int

Default Value

Remarks

When BatchSize is set to a value greater than 0, the batch operation will split the entire batch into separate batches of size BatchSize. The split batches will then be submitted to the server individually. This is useful when the server has limitations on the size of the request that can be submitted.

Setting BatchSize to 0 will submit the entire batch as specified.

ConnectionLifeTime

Data Type

int

Default Value

Remarks

The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed. The default is 0 which indicates there is no limit to the connection lifetime.

ConnectOnOpen

Data Type

bool

Default Value

false

Remarks

When set to true, a connection will be made to MailChimp when the connection is opened. This property enables the Test Connection feature available in various database tools.

This feature acts as a NOOP command as it is used to verify a connection can be made to MailChimp and nothing from this initial connection is maintained.

Setting this property to false may provide performance improvements (depending upon the number of times a connection is opened).

MaxRows

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Other

Data Type

string

Default Value

Remarks

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

Specify multiple properties in a semicolon-separated list.

Caching Configuration


CachePartial=True	Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True	Passes the specified query to the cache database instead of using the SQL parser of the driver.

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.

Pagesize

Data Type

int

Default Value

1000

Remarks

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

PoolIdleTimeout

Data Type

int

Default Value

Remarks

The allowed idle time a connection can remain in the pool until the connection is closed. The default is 60 seconds.

PoolMaxSize

Data Type

int

Default Value

100

Remarks

The maximum connections in the pool. The default is 100. To disable this property, set the property value to 0 or less.

PoolMinSize

Data Type

int

Default Value

Remarks

The minimum number of connections in the pool. The default is 1.

PoolWaitTime

Data Type

int

Default Value

Remarks

The max seconds to wait for a connection to become available. If a new connection request is waiting for an available connection and exceeds this time, an error is thrown. By default, new requests wait forever for an available connection.

PseudoColumns

Data Type

string

Default Value

Remarks

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

Readonly

Data Type

bool

Default Value

false

Remarks

If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.

RTK

Data Type

string

Default Value

Remarks

The RTK property may be used to license a build. See the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.

SupportEnhancedSQL

Data Type

bool

Default Value

true

Remarks

When SupportEnhancedSQL = true, the driver offloads as much of the SELECT statement processing as possible to MailChimp and then processes the rest of the query in memory. In this way, the driver can execute unsupported predicates, joins, and aggregation.

When SupportEnhancedSQL = false, the driver limits SQL execution to what is supported by the MailChimp API.

Execution of Predicates

The driver determines which of the clauses are supported by the data source and then pushes them to the source to get the smallest superset of rows that would satisfy the query. It then filters the rest of the rows locally. The filter operation is streamed, which enables the driver to filter effectively for even very large datasets.

Execution of Joins

The driver uses various techniques to join in memory. The driver trades off memory utilization against the requirement of reading the same table more than once.

Execution of Aggregates

The driver retrieves all rows necessary to process the aggregation in memory.

Timeout

Data Type

int

Default Value

Remarks

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

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

UseConnectionPooling

Data Type

bool

Default Value

false

Remarks

This property enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.

TablesBack To Top

AuthorizedApps
AutomationEmailQueues
AutomationsRemovedSubscribers
CampaignFeedback
CampaignFolders
Campaigns
ConversationMessages
EcommerceCartLines
EcommerceCarts
EcommerceCustomers
EcommerceOrderLines
EcommerceOrders
EcommerceProducts
EcommerceProductVariants
FileManagerFiles
FileManagerFolders
ListInterestCategories
ListInterests
ListMemberEvents
ListMemberNotes
ListMembers
ListMemberTags
ListMergeFields
Lists
ListSegmentMembers
ListSegments
ListsTwitterLeadGenCards
ListsWebhooks
TemplateFolders
Templates

AuthorizedApps

A list of applications authorized to access the account.

Table Specific Information

SELECT and INSERT are supported for AuthorizedApps.

Insert

There are no required fields when INSERTing.

Columns


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

AutomationEmailQueues

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


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

AutomationsRemovedSubscribers

A summary of the subscribers removed from an automation workflow.

Columns


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

CampaignFeedback

A summary of the comment feedback for a specific campaign.

Table Specific Information

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

Select

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


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

CampaignFolders

Folders for organizing campaigns

Columns


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

Campaigns

A summary of the campaigns within an account.

Table Specific Information

SELECT, UPDATE and DELETE are supported for Campaigns.

Update

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



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



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

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	A string that uniquely identifies this campaign.
Type	String	False	The type of campaign (regular, plaintext, absplit, or rss).
CreateTime	Datetime	True	The date and time the campaign was created.
ArchiveUrl	String	True	The link to the campaign's archive version.
LongArchiveUrl	String	True	The original link to the campaign's archive version.
Status	String	True	The current status of the campaign ('save', 'paused', 'schedule', 'sending', 'sent').
EmailsSent	Integer	True	The total number of emails sent for this campaign.
SendTime	Datetime	True	The time and date a campaign was sent.
ContentType	String	True	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.

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

The ConversationId, FromEmail, and Read are required for INSERTs.

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

Columns


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

EcommerceCartLines

A list of an ecommerce cart's lines.

Columns


Name	Type	ReadOnly	Description
StoreId [KEY]	String	True	The StoreId for the table.
CartId [KEY]	String	True	The CartId for the table.
Id [KEY]	String	False	A unique identifier for the cart line item.
ProductId [KEY]	String	False	A unique identifier for the product associated with the cart line item.
ProductTitle	String	True	The name of the product for the cart line item.
ProductVariantId [KEY]	String	False	A unique identifier for the product variant associated with the cart line item.
ProductVariantTitle	String	True	The name of the product variant for the cart line item.
Quantity	Integer	False	The quantity of a cart line item.
Price	Decimal	False	The price of a cart line item.

EcommerceCarts

A list of an account's ecommerce carts.

Columns


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

EcommerceCustomers

A list of an account's ecommerce customers.

Columns


Name	Type	ReadOnly	Description
StoreId [KEY]	String	True	The StoreId for the table.
Id [KEY]	String	True	A unique identifier for the customer.
EmailAddress	String	True	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	False	The customer's total order count.
TotalSpent	Decimal	False	The total amount the customer has spent.
Address_Address1	String	False	The mailing address of the customer.
Address_Address2	String	False	An additional field for the customer's mailing address.
Address_City	String	False	The city the customer is located in.
Address_Province	String	False	The customer's state name or normalized province.
Address_ProvinceCode	String	False	The two-letter code for the customer's province or state.
Address_PostalCode	String	False	The customer's postal or zip code.
Address_Country	String	False	The customer's country.
Address_CountryCode	String	False	The two-letter code for the customer's country.
CreatedAt	Datetime	True	The date and time the customer was created.
UpdatedAt	Datetime	True	The date and time the customer was last updated.

EcommerceOrderLines

A list of an ecommerce order's lines.

Columns


Name	Type	ReadOnly	Description
StoreId [KEY]	String	True	The StoreId for the table.
OrderId [KEY]	String	True	The OrderId for the table.
Id [KEY]	String	False	A unique identifier for the order line item.
ProductId [KEY]	String	False	A unique identifier for the product associated with the order line item.
ProductTitle	String	True	The name of the product for the order line item.
ProductVariantId [KEY]	String	False	A unique identifier for the product variant associated with the order line item.
ProductVariantTitle	String	True	The name of the product variant for the order line item.
Quantity	Integer	False	The quantity of an order line item.
Price	Decimal	False	The price of an ecommerce order line item.

EcommerceOrders

A list of an account's ecommerce orders.

Columns


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

EcommerceProducts

A list of an account's ecommerce products.

Columns


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

EcommerceProductVariants

A list of an ecommerce product's variants.

Columns


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

FileManagerFiles

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

Table Specific Information

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

Select

A FolderId 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, FolderId, and FileData are required for INSERTs.

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The unique id given to the file.
FolderId [KEY]	Integer	False	The id of the folder.
Type	String	True	The type of file in the gallery: Image or file.
Name	String	False	The name of the file.
FullSizeUrl	String	True	The url of the full-size file.
ThumbnailUrl	String	True	The url of the thumbnail preview.
Size	Integer	True	The size of the file in bytes.
CreatedAt	Datetime	True	The date and time a file was added to the gallery.
CreatedBy	String	True	The username of the profile that uploaded the file.
Width	Integer	True	The width of the image.
Height	Integer	True	The height of an image.
FileData	String	False	When adding a new file, the base64-encoded file.

FileManagerFolders

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

Table Specific Information

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

Insert

The Name is required for INSERTs.

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The unique id given to the folder.
Name	String	False	The name of the folder.
FileCount	Integer	True	The number of files within the folder.
CreatedAt	Datetime	True	The date and time a file was added to the gallery.
CreatedBy	String	True	The username of the profile that created the folder.

ListInterestCategories

A listing of this list's interest categories.

Table Specific Information

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

Select

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


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

ListInterests

A list of this category's interests

Table Specific Information

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

Select

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


Name	Type	ReadOnly	Description
CategoryId [KEY]	String	True	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	False	The number of subscribers associated with this interest.
DisplayOrder	Integer	False	Order in which the interests display.

ListMemberEvents

Events information for a specific list.

Table Specific Information

SELECT and INSERT are supported for ListMemberEvents.

Select

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


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	True	The unique id for the list.
MemberId [KEY]	String	True	The MD5 hash of the list member's email address.

ListMemberNotes

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

Table Specific Information

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

Select

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


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	True	The unique id for the list.
MemberId [KEY]	String	True	The MD5 hash of the list member's email address.

ListMembers

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

Table Specific Information

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

Select

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 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	False	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	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	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	False	The note's ID.
LastNote_CreatedAt	String	False	The date the note was created.
LastNote_CreatedBy	String	False	The author of the note.
LastNote_Note	String	False	The content of the note.
ListId [KEY]	String	False	The id for the list.
TagsAggregate	String	True	Tags of the member, displayed as an aggregate.

ListMemberTags

Tags assigned to a certain member/members.

Table Specific Information

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


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

ListMergeFields

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

Table Specific Information

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

Select

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


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	True	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	True	A string that identifies this merge field collections' list.

Lists

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

Table Specific Information

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

Insert

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

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

Columns


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

ListSegmentMembers

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The MD5 hash of the list member's email address.
EmailAddress	String	True	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').
Status	String	False	Subscriber's current status ('subscribed', 'unsubscribed', 'cleaned', or 'pending').
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	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	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	False	The note's ID.
LastNote_CreatedAt	String	False	The date the note was created.
LastNote_CreatedBy	String	False	The author of the note.
LastNote_Note	String	False	The content of the note.
ListId [KEY]	String	True	The id for the list.
SegmentId [KEY]	String	True	The id for the segment.

ListSegments

A list of available segments.

Table Specific Information

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

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

ListsTwitterLeadGenCards

Twitter Lead Generation Cards for given List

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	Card unique ID
Name	String	True	Twitter Lead Generation Card Name
Title	String	True	Twitter Lead Generation Card Title
CtaText	String	True	Phrase to display on Call to Action button
PrivacyPolicyUrl	String	True	Privacy Policy URL
CustomKeyName	String	True	Custom key name to store twitter subscriber's display name
ImageUrl	String	True	Header image url, size must be 800x200px
TwitterCardId [KEY]	String	True	Twitter Lead Generation Card ID
TwitterAccountId [KEY]	String	True	Twitter Account ID
ListId [KEY]	String	True	List ID
PreviewUrl	String	True	Twitter Lead Generation Card Preview Url

ListsWebhooks

Webhooks configured for the given list.

Columns


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

TemplateFolders

Folders for organizing templates

Columns


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

Templates

A list an account's available templates.

Table Specific Information

SELECT and DELTE are supported for Templates.

Columns


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

ViewsBack To Top

AutomationEmails
Automations
CampaignOpenEmailDetails
Conversations
EcommerceStores
ListAbuse
ListActivity
ListClients
ListGrowthHistory
ListMemberActivity
ListSignupForms
ReportAbuse
ReportAdvice
ReportClickDetails
ReportClickDetailsMembers
ReportDomainPerformance
ReportEmailActivity
ReportLocations
Reports
ReportSentTo
ReportUnsubscribes

AutomationEmails

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


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

Automations

A summary of the automations within an account.

Columns


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

CampaignOpenEmailDetails

A list of members who opened the campaign email.

Table Specific Information

SELECT is supported for CampaignOpenEmailDetails.

Select

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


    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.

Conversations

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

Columns


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

EcommerceStores

A list of an account's ecommerce stores.

Columns


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

ListAbuse

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

Table Specific Information

SELECT is supported for ListAbuse.

Select

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


Name	Type	Description
Id [KEY]	String	The id for the abuse report
CampaignId [KEY]	String	The campaign id for the abuse report
ListId [KEY]	String	The list id for the abuse report.
EmailId [KEY]	String	The MD5 hash of the list member's email address.
EmailAddress	String	Email address for a subscriber
Date	String	Date for the abuse report

ListActivity

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

Table Specific Information

SELECT is supported for ListActivity.

Select

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


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

ListClients

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

Columns


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

ListGrowthHistory

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

Table Specific Information

SELECT is supported for ListGrowthHistory.

Select

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


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

ListMemberActivity

The last 50 member events for a list.

Table Specific Information

SELECT is supported for ListMemberActivity.

Select

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


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

ListSignupForms

Collection of List Signup Forms

Columns


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

ReportAbuse

A list of abuse complaints for a specific list.

Table Specific Information

SELECT is supported for ReportAbuse.

Select

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
Id [KEY]	String	The unique id for the abuse report.
CampaignId [KEY]	String	The campaign id for the abuse report
ListId [KEY]	String	The list id for the abuse report.
EmailId [KEY]	String	The list-specific ID for the given email address
EmailAddress	String	Email address for a subscriber
Date	String	Date for the abuse report

ReportAdvice

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

Table Specific Information

SELECT is supported for ReportAdvice.

Select

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
CampaignId [KEY]	String	The CampaignId for the table.
Type	String	The 'type' of message ('negative', 'positive', 'neutral').
Message	String	The advice message.

ReportClickDetails

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

Table Specific Information

SELECT is supported for ReportClickDetails.

Select

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
Id [KEY]	String
Url	String	The URL for the link in the campaign.
TotalClicks	Integer	The number of total clicks for a given link.
ClickPercentage	Double	The percentage of total clicks a given link generated for a campaign.
UniqueClicks	Integer	Number of unique clicks for a given link.
UniqueClickPercentage	Double	The percentage of unique clicks a given link generated for a campaign.
LastClick	Datetime	The date and time for the last recorded click for a given link.
AbSplit_A	String
AbSplit_B	String
CampaignId [KEY]	String	The id for the campaign.

ReportClickDetailsMembers

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

Table Specific Information

SELECT is supported for ReportClickDetailsMembers.

Select

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


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

ReportDomainPerformance

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

Table Specific Information

SELECT is supported for ReportDomainPerformance.

Select

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
CampaignId [KEY]	String	The CampaignId for the table.
Domain	String	The name of the domain (gmail.com, hotmail.com, yahoo.com).
EmailsSent	Integer	The number of emails sent to that specific domain.
Bounces	Integer	The number of bounces at a domain.
Opens	Integer	The number of opens for a domain.
Clicks	Integer	The number of clicks for a domain.
Unsubs	Integer	The total number of unsubscribes for a domain.
Delivered	Integer	The number of successful deliveries for a domain.
EmailsPct	Double	The percentage of total emails that went to this domain.
BouncesPct	Double	The percentage of total bounces that came from this domain.
OpensPct	Double	The percentage of total opens that came from this domain.
ClicksPct	Double	The percentage of total clicks tht came from this domain.
UnsubsPct	Double	The percentage of total unsubscribes taht came from this domain.

ReportEmailActivity

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

Table Specific Information

SELECT is supported for ReportEmailActivity.

Select

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
CampaignId [KEY]	String	The unique id for the campaign.
ListId [KEY]	String	The unique id for the list.
EmailId [KEY]	String	The list-specific ID for the given email address.
EmailAddress	String	Email address for a subscriber
Activity	String	An array of objects, each showing an interaction with the email.

ReportLocations

Top open locations for a specific campaign.

Columns


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

Reports

A list of reports containing campaigns marked as Sent.

Columns


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

ReportSentTo

A list of subscribers who were sent a specific campaign.

Table Specific Information

SELECT is supported for ReportSentTo.

Select

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

Columns


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

ReportUnsubscribes

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

Table Specific Information

SELECT is supported for ReportUnsubscribes.

Select

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

Matillion ETL Data Model for MailchimpVersion - 21.0.8137.0

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Authentication on Headless Machines

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Matillion ETL Data Model for Mailchimp
Version - 21.0.8137.0