Establishing a Connection

Create a connection to Marketo by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Marketo icon is not available, click the Add More icon to download and install the Marketo connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Both the REST and SOAP APIs are supported and can be chosen using the Schema property.

Connecting to the Marketo REST API

Before you can connect to the Marketo REST API, you will need to create a custom service.

Creating a Custom Service

To create a custom service, follow the procedure below:

Navigate to the admin area of your Marketo application.
Click Users & Roles in the Security section.
Select the Roles tab and click New Role to create a new Role.
Enter a Role Name and select the permissions for the Role. The Access API permissions are specific to the REST API.
Now that an API Role is created, select the Users tab and click Invite New User.
Enter the new user information and select the role that was just created with API access. The API Only option can be selected to denote the user as an API Only user.
Now that a new user has been created, a new service will need to be created. Click the LaunchPoint option (Admin -> Integration -> LaunchPoint).
Click New Service.
Select the Custom service type and enter a display name and description.
Select the user you created.

Obtaining OAuth Credentials

To obtain the OAuthClientId and OAuthClientSecret, navigate to the LaunchPoint option on the Admin area.

Click the View Details link for the desired service. A window containing the authentication credentials is displayed.

Authenticating to the REST API

Once you have created a custom service and obtained your OAuth credentials, set the following to connect to data:

Schema: Set this to REST.
OAuthClientId: The OAuth Client ID associated with your custom service.
OAuthClientSecret: The OAuth Client Secret associated with your custom service.
RESTEndpoint: The URL of the REST Web service endpoint. This can be found on your Marketo Admin area on the Integration -> Web Services option in the REST API section.
The Identity Endpoint will not be needed.

Connecting to the Marketo SOAP API

To connect to the SOAP API, you will need to provide valid Marketo credentials. Specify the following to connect to data:

Schema: Set this to SOAP.
UserId: The client access ID is found within your Marketo admin SOAP API panel under Integration.
EncryptionKey: The Marketo SOAP API Encryption Key. This key is generated on the Admin page of the Marketo website.
SOAPEndpoint: The URL of the SOAP Web service endpoint, provided by Marketo on the Admin page of the Marketo website.

Advanced Features

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

User Defined Views

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

SSL Configuration

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

Firewall and Proxy

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

Query Processing

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

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

SSL Configuration

Customizing the SSL Configuration

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

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

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

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

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

Other Proxies

Set the following properties:

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

SOAP Data Model

The CData Sync App models Marketo entities in relational Tables, Views, and Stored Procedures. API limitations and requirements are documented in the following sections; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Note: Bulk operations are not supported for the SOAP data model.

Tables

Tables describes the available tables.

Views

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

Tables

The Sync App models the data in Marketo into a list of tables that can be queried using standard SQL statements.

Generally, querying Marketo tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Marketo Connector for CData Sync Tables


Name	Description
Leads	Create, update, delete, and query Leads for a Marketo organization.
Programs	Query and update Programs for a Marketo organization.

Leads

Create, update, delete, and query Leads for a Marketo organization.

Table Specific Information

Select

All columns must be specified using the '=' operator. The Id and Email columns allow multiple values to be specified by using the OR logical operator. The OldestUpdatedAt column acts as a "since" criteria, and a range can be specified by adding the LatestUpdatedAt column, which specifies the "until" criteria.

Response time from the server can be improved by identifying only the rows and columns you want to retrieve.

SELECT Id, FirstName, LastName FROM Leads WHERE Email='[email protected]' OR Email='[email protected]'

Insert

To create a new Lead record, specify the information about the Lead to be entered into the database.

The following example demonstrates how to insert a new Lead:

INSERT INTO Leads (Email, FirstName, LastName) VALUES ('[email protected]', 'John', 'Mark')

Update

Any field that is not read-only can be updated.

UPDATE Leads SET MobilePhone='111-222-3333' WHERE Id='1'

Delete

Deleting a Lead is not directly supported by the Marketo API. However, a Lead can be deleted by creating a campaign, to be run via an API call, that triggers the deletion of a Lead as part of the campaign's flow. Details for how to create a campaign like this can be found here: http://developers.marketo.com/blog/delete-a-lead-with-the-marketo-api/

Once the campaign is created within Marketo, a delete request can be made by specifying the campaign with either the CampaignId or CampaignName column, along with the Id or Email column of the Leads to be deleted.

DELETE FROM Leads WHERE Id='1000195' AND CampaignId='1027'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique, Marketo-assigned identifier of the account.
Email	String	False	The lead's email address.
Salutation	String	False	The lead's salutation.
FirstName	String	False	The lead's first name.
MiddleName	String	False	The lead's middle name.
LastName	String	False	The lead's last name.
DateofBirth	Date	False	The lead's date of birth.
Title	String	False	The lead's job title.
Address	String	False	The lead's street address.
City	String	False	The lead's city.
State	String	False	The lead's state.
PostalCode	String	False	The lead's ZIP/postal code.
Country	String	False	The lead's country.
Website	String	False	The lead's website.
Phone	String	False	The lead's phone number.
MobilePhone	String	False	The lead's mobile phone number.
Fax	String	False	The lead's fax number.
Company	String	False	The name of the lead's company.
MainPhone	String	False	The phone number of the lead's company.
SICCode	String	False	The SIC (Standard Industrial Classification) code of the lead's company.
Site	String	False	The site of the lead's company.
BillingStreet	String	False	The billing street address of the lead's company.
BillingCity	String	False	The billing city of the lead's company.
BillingState	String	False	The billing state of the lead's company.
BillingPostalCode	String	False	The billing ZIP/postal code of the lead's company.
BillingCountry	String	False	The billing country of the lead's company.
NumberOfEmployees	Integer	False	The number of employees at the lead's company.
Industry	String	False	The industry of the lead's company.
AnnualRevenue	Decimal	False	The annual revenue generated at the lead's company.
LeadScore	Integer	False	The lead's score.
Rating	String	False	The lead's rating.
Unsubscribed	Boolean	False	Determines whether the lead is unsubscribed.
UnsubscribedReason	String	False	The reason why the lead has unsubscribed.
AnonymousIP	String	False	The IP address of the lead if it is anonymous.
Department	String	False	The lead's deparment.
DoNotCall	Boolean	False	Identifies whether the lead is on the 'Do Not Call' list.
DoNotCallReason	String	False	The reason why the lead is on the 'Do Not Call' list.
InferredCompany	String	False	The lead's inferred company.
InferredCountry	String	False	The lead's inferred country.
LeadRole	String	False	The lead's role.
LeadSource	String	False	The lead's source.
LeadStatus	String	False	The lead's current status.
ForeignSysPersonId	String	False	The lead's Id from a foreign system.
ForeignSysType	String	False	The foreign system type that the ForeignSysPersonId value came from.
MarketoSocialFacebookDisplayName	String	False	The lead's Facebook display name.
MarketoSocialFacebookId	String	False	The lead's Facebook Id.
MarketoSocialFacebookPhotoURL	String	False	The lead's Facebook photo URL.
MarketoSocialFacebookProfileURL	String	False	The lead's Facebook profile URL.
MarketoSocialFacebookReach	Integer	False	The lead's Facebook reach.
MarketoSocialFacebookReferredEnrollments	Integer	False	The lead's Facebook referred enrollments.
MarketoSocialFacebookReferredVisits	Integer	False	The lead's Facebook referred visits.
MarketoSocialGender	String	False	The lead's social gender.
MarketoSocialLastReferredEnrollment	Datetime	False	The lead's last social referred enrollment.
MarketoSocialLastReferredVisit	Datetime	False	The lead's last social referred visit.
MarketoSocialLinkedInDisplayName	String	False	The lead's LinkedIn display name.
MarketoSocialLinkedInId	String	False	The lead's LinkedIn Id.
MarketoSocialLinkedInPhotoURL	String	False	The lead's LinkedIn photo URL.
MarketoSocialLinkedInProfileURL	String	False	The lead's LinkedIn profile URL.
MarketoSocialLinkedInReach	Integer	False	The lead's LinkedIn reach.
MarketoSocialLinkedInReferredEnrollments	Integer	False	The lead's LinkedIn referred enrollments.
MarketoSocialLinkedInReferredVisits	Integer	False	The lead's LinkedIn referred visits.
MarketoSocialSyndicationId	String	False	The lead's social syndication Id.
MarketoSocialTotalReferredEnrollments	Integer	True	The lead's total social referred enrollments.
MarketoSocialTotalReferredVisits	Integer	True	The lead's total social referred visits.
MarketoSocialTwitterDisplayName	String	False	The lead's Twitter display name.
MarketoSocialTwitterId	String	False	The lead's Twitter Id.
MarketoSocialTwitterPhotoURL	String	False	The lead's Twitter photo URL.
MarketoSocialTwitterProfileURL	String	False	The lead's Twitter profile URL.
MarketoSocialTwitterReach	Integer	False	The lead's Twitter reach.
MarketoSocialTwitterReferredEnrollments	Integer	False	The lead's Twitter referred enrollments.
MarketoSocialTwitterReferredVisits	Integer	False	The lead's Twitter referred visits.
MarketoSocialYahooDisplayName	String	False	The lead's Yahoo display name.
MarketoSocialYahooPhotoURL	String	False	The lead's Yahoo photo URL.
MarketoSocialYahooProfileURL	String	False	The lead's Yahoo profile URL.
MarketoSocialYahooReach	Integer	False	The lead's Yahoo reach.
MarketoSocialYahooReferredEnrollments	Integer	False	The lead's Yahoo referred enrollments.
MarketoSocialYahooReferredVisits	Integer	False	The lead's Yahoo referred visits.
MicrosoftAddress2	String	False	The lead's Microsoft street line 2 address.
MicrosoftAddress3	String	False	The lead's Microsoft street line 3 address.
MicrosoftBillingAddress2	String	False	The lead's Microsoft billing street line 2 address.
MicrosoftBillingAddress3	String	False	The lead's Microsoft billing street line 3 address.
MicrosoftDoNotEmail	Boolean	False	Identifies whether the lead is on the Microsoft 'Do Not Email' list.
MicrosoftDoNotFax	Boolean	False	Identifies whether the lead is on the Microsoft 'Do Not Fax' list.
MicrosoftDoNotSendMarketingMaterial	Boolean	False	Identifies whether the lead is on the Microsoft 'Do Not Send Marketing Material' list.
MicrosoftHomePhone	String	False	The lead's Microsoft home phone.
MicrosoftPreferredMethodofContact	String	False	The lead's Microsoft preferred method of contact.
MicrosoftTopic	String	False	The lead's Microsoft topic.
SAP_CRM_ABCClassification	String	False	The lead's SAP ABC classification.
SAP_CRM_AccountID	String	False	The lead's SAP account Id.
SAP_CRM_Account_CreatedOn	Datetime	False	The lead's SAP account creation date.
SAP_CRM_Account_ExternalID	String	False	The lead's SAP account external Id.
SAP_CRM_Account_Fax	String	False	The lead's SAP account fax number.
SAP_CRM_Account_HouseNumber	String	False	The lead's SAP account house number.
SAP_CRM_Account_IsDeleted	Boolean	False	Identifies if the lead's SAP account is deleted.
SAP_CRM_Account_Status	String	False	The lead's SAP account status.
SAP_CRM_BestReachedBy	String	False	The lead's SAP best method to be reached.
SAP_CRM_ContactID	String	False	The lead's SAP contact Id.
SAP_CRM_Contact_Name	String	False	The lead's SAP contact name.
SAP_CRM_DepartmentFromBusinessCard	String	False	The lead's SAP department retrieved from business card.
SAP_CRM_EmployeeID	String	False	The lead's SAP employee Id.
SAP_CRM_ExternalSystem	String	False	The lead's SAP external system.
SAP_CRM_Function	String	False	The lead's SAP function.
SAP_CRM_Lead_HouseNumber	String	False	The lead's SAP lead house number.
SAP_CRM_Name	String	False	The lead's SAP name.
SAP_CRM_Person_Type	String	False	The lead's SAP person type.
SAP_CRM_PrimaryContact	String	False	The lead's SAP primary contact.
SAP_CRM_Qualification	String	False	The lead's SAP qualification.

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
OldestUpdatedAt	Datetime	Used when performing a GET. Returns all leads updated since the specified time.
LatestUpdatedAt	Datetime	Used when performing a GET. Returns all leads updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.
CampaignSource	String	Used when performing a DELETE and is used to specify the source of the campaign. Valid values are MKTOWS or SALES. If not specified, MKTOWS will be used by default.
CampaignId	Integer	Used when performing a DELETE and is used to specify the Id of the campaign that contains the 'Delete Lead' trigger. Note CampaignId and Campaign name cannot both be specified.
CampaignName	String	Used when performing a DELETE and is used to specify the name of the campaign that contains the 'Delete Lead' trigger. Note CampaignId and Campaign name cannot both be specified.

Programs

Query and update Programs for a Marketo organization.

Table Specific Information

Select

Marketo allows the following columns to be used in the WHERE clause of a SELECT query: Id, Name, CreatedAt, UpdatedAt, CRMId, TagType, TagValue, WorkspaceName, WorkspaceId, and IncludeArchive. The Id, Name, and CRMId columns allow multiple values to be specified by using the OR logical operator. The CreatedAt and UpdateAt filters can be specified twice to create a date range.

SELECT * FROM Programs WHERE CreatedAt > '08/01/2014' AND CreatedAt <= '08/31/2014'

Update

Any field that is not read-only can be updated.

Note that the Tag*** and Cost*** columns only take a single value. Therefore, they do not take a comma-separated list when performing an update.

UPDATE Programs SET TagType='Program Owner', TagValue='Admin', CostMonth='11/2014', CostAmount='30' WHERE Id='1002'

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique, Marketo-assigned identifier of the program.
Name	String	True	The name of the program.
Description	String	True	The description of the program.
WorkspaceId	String	True	The Id of the workspace where the program is located.
WorkspaceName	String	True	The name of the workspace where the program is located.
TreePath	String	True	The folder structure tree path describing the location of the program.
IsArchived	String	True	Specifies whether the program is archived.
TagType#	String	False	A comma-separated list of tag types associated with the program. Each TagType has a value associated with it which is returned via the TagValue column.
TagValue#	String	False	A comma-separated list of tag values. Each value corresponds to the type listed within the TagTypes column.
CostMonth#	String	False	A comma-separated list of period cost months for the program.
CostAmount#	String	False	A comma-separated list of period cost amounts for the program.
CostId#	String	False	A comma-separated list of period cost Ids for the program.
CostNote#	String	False	A comma-separated list of period cost notes for the program.

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
CreatedAt	Datetime	Used when performing a GET. Filters programs by the date they were created.
UpdatedAt	Datetime	Used when performing a GET. Filters programs by the date they were updated.
CRMId	String	Used when performing a GET. The CRM Id associated with the program. (This value could refer to the Id of the Salesforce campaign connected to the program.)
IncludeArchive	Boolean	Used when performing a GET. When set to 'True', will return archived programs.

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Marketo Connector for CData Sync Views


Name	Description
Activities	Query Activities for a Marketo organization.
Activities_AddToList	Query Add To List Activities for a Marketo organization.
Activities_AddToSFDCCampaign	Query Add To SFDC Campaign Activities for a Marketo organization.
Activities_ChangeDataValue	Query Change Data Value Activities for a Marketo organization.
Activities_ChangeOwner	Query Change Owner Activities for a Marketo organization.
Activities_ChangeRevenueStage	Query Change Revenue Stage Activities for a Marketo organization.
Activities_ChangeScore	Query Change Score Activities for a Marketo organization.
Activities_ChangeStatusInProgression	Query Change Status In Progression Activities for a Marketo organization.
Activities_ChangeStatusInSFDCCampaign	Query Change Status In SFDC Campaign Activities for a Marketo organization.
Activities_ClickEmail	Query Click Email Activities for a Marketo organization.
Activities_ClickLink	Query Click Link Activities for a Marketo organization.
Activities_ConvertLead	Query Convert Lead Activities for a Marketo organization.
Activities_CreateTask	Query Create Task Activities for a Marketo organization.
Activities_EmailBouncedSoft	Query Email Bounced Soft Activities for a Marketo organization.
Activities_EmailDelivered	Query Email Delivered Activities for a Marketo organization.
Activities_InterestingMoments	Query Interesting Moments Activities for a Marketo organization.
Activities_MergeLeads	Query Merge Leads Activities for a Marketo organization.
Activities_NewLeads	Query New Lead Activities for a Marketo organization.
Activities_OpenEmail	Query Open Email Activities for a Marketo organization.
Activities_RemoveFromFlow	Query Remove from Flow Activities for a Marketo organization.
Activities_RemoveFromList	Query Remove from List Activities for a Marketo organization.
Activities_RemoveFromSFDCCampaign	Query Remove from SFDC Campaign Activities for a Marketo organization.
Activities_SendAlert	Query Send Alert Activities for a Marketo organization.
Activities_SendEmail	Query Send Email Activities for a Marketo organization.
Activities_SFDCActivity	Query SFDC Activity Activities for a Marketo organization.
Activities_SFDCMergeLeads	Query SFDC Merge Leads Activities for a Marketo organization.
Activities_VisitWebpage	Query Visit Webpage Activities for a Marketo organization.
Campaigns	Query Campaigns for a Marketo organization.
Channels	Query Channels for a Marketo organization.
Opportunities	Query Opportunities for a Marketo organization.
OpportunityPersonRoles	Query Opportunity Person Roles for a Marketo organization.
Tags	Query Tags for a Marketo organization.

Activities

Query Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, ActivityCreatedAt. ActivityNameFilter or ExcludeActivities can be specified as an additional column in the WHERE clause. A list of available Activity Types can be found here: http://developers.marketo.com/activity-types/. The LeadId, Email, ActivityNameFilter, and ExcludeActivities columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator. Otherwise, an exception will be thrown.

SELECT * FROM Activities WHERE LeadId='1' OR LeadId='2'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
ActivityType	String	The type of activity.
MktgAssetName	String	The name of the marketing asset tied to the activity.
Campaign	String	The campaign the activity is associated with.
ActivityAttributes#	String	A comma-separated list of name-value pairs (name=value).
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.
ActivityNameFilter	String	Used when performing a GET. The name of the specific activities to retrieve.
ExcludeActivities	String	Used when performing a GET. The name of the specific activities to exclude.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_AddToList

Query Add To List Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_AddToList WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
ListId	Integer	The Id of the list that the lead was added to.
ListName	String	The name of the list that the lead was added to.
Source	String	The source by which the lead was added to the list.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_AddToSFDCCampaign

Query Add To SFDC Campaign Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_AddToSFDCCampaign WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
CampaignId	Integer	The Id of the SFDC campaign that the lead was added to.
Campaign	String	The campaign the activity is associated with.
Status	String	The status of the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ChangeDataValue

Query Change Data Value Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_ChangeDataValue WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Source	String	The source by which the data value was changed (i.e., Web service API, SFDC, etc).
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
AttributeName	String	The name of the attribute whose data value was changed.
NewValue	String	The new and current data value.
OldValue	String	The old and previous data value.
Reason	String	The reason why the data value was changed.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ChangeOwner

Query Change Owner Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_ChangeOwner WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Owner	String	The name of the new and current owner of the lead.
OwnerId	String	The Id of the new and current owner of the lead.
OldOwner	String	The name of the previous owner of the lead.
OldOwnerId	String	The Id of the previous owner of the lead.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ChangeRevenueStage

Query Change Revenue Stage Activities for a Marketo organization.

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
ModelId	String	The id of the revenue model.
ModelVersion	String	The version of the revenue model.
NewStageId	String	The new Id of the stage that the lead is currently in.
NewStage	String	The new name of the stage that the lead is currently in.
OldStageId	String	The Id of the stage that the lead was previously in.
OldStage	String	The name of the stage that the lead was previously in.
Reason	String	The reason why the data value was changed.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ChangeScore

Query Change Score Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_ChangeScore WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
ScoreName	String	The name of the score whose data value was changed.
ChangeValue	String	The net change in the score value. For example, +5 or -5.
NewValue	String	The new and current score value.
OldValue	String	The old and previous score value.
Urgency	String	How much the score has changed recently. A higher urgency represents a score that has increased a lot lately, showing customer interest. This value is derived from score value (NewValue).
Reason	String	The reason why the data value was changed.
Priority	String	The priority rank used to decide which lead should be contacted first. Priority has two components: Urgency and Relative Score. The higher the priority, the higher likelihood that the lead will respond positively to a contact.
RelativeScore	String	Contains a measure of how a lead's score compares to other lead scores. This value is derived from score value (NewValue).
RelativeUrgency	String	Contains a measure of how a lead's urgency compares to other lead urgencies.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ChangeStatusInProgression

Query Change Status In Progression Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_ChangeStatusInProgression WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
NewStatus	String	The new and current status.
NewStatusId	String	The Id of the NewStatus value.
OldStatus	String	The old and previous status.
OldStatusId	String	The Id of the OldStatus value.
Program	String	The name of the program where the status change occurred.
ProgramId	Integer	The Id of the Program.
Success	Boolean	Identifies whether the status represents a success.
AcquiredBy	String	Identifies whether the lead was acquired by this activity.
Reason	String	The reason why the status was changed.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ChangeStatusInSFDCCampaign

Query Change Status In SFDC Campaign Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_ChangeStatusInSFDCCampaign WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
CampaignId	Integer	The Id of the SFDC campaign that the lead's status was changed.
Campaign	String	The campaign the activity is associated with.
NewStatus	String	The new and current status.
OldStatus	String	The old and previous status.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ClickEmail

Query Click Email Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_ClickEmail WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
Link	String	The URL of the link that was clicked.
MailingId	String	The Id of the email that the link was contained in.
VariationId	String	The Id of the email variation.
UserAgent	String	The Web browser user agent information obtained when the lead clicked the email link.
IsMobileDevice	String	Identifies whether the device used to click the link was a mobile device.
Platform	String	The operating system platform used when the link was opened.
Device	String	The type of device used when the link was opened.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ClickLink

Query Click Link Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_ClickLink WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
WebpageId	String	The Id of the Web page that contained the link that was clicked.
LinkId	String	The Id of the link that was clicked.
QueryParameters	String	The query parameters contained within the link.
ClientIPAddress	String	The IP address of the client that clicked the link.
MessageId	String	The Id of the message where the link was clicked.
UserAgent	String	The Web browser user agent information obtained when the lead clicked the link.
ReferrerURL	String	The URL of the referrer used to identify where the link click originated from.
CreatedAt	String	The date and time the link click activity was created.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_ConvertLead

Query Convert Lead Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_ConvertLead WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
AssignTo	String	The owner that the lead was assigned to.
SFDCAccountId	String	The Id of the lead's SFDC account.
SFDCType	String	The lead's SFDC type.
SFDCOpportunityId	String	The Id of the lead's SFDC opportunity.
SFDCLeadId	String	The lead's SFDC lead Id.
SFDCContactId	String	The lead's SFDC contact Id.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_CreateTask

Query Create Task Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_CreateTask WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
Subject	String	The subject of the task.
Operator	String	The task operator.
DataType	String	The task data type.
DueIn	String	The number of days until the task is due.
Owner	String	The owner that the task was assigned to.
Comments	String	The comments for the task.
Priority	String	The priority of the task.
Status	String	The current status of the task.
Notify	String	Identifies whether a notification should be sent.
RemindIn	String	The number of days that a reminder should be sent.
SFDCTaskId	String	The SFDC Id of the task.
OwnerPersonId	String	The Id of the Owner of the task.
DueDate	String	The date the task is due.
ReminderTimestamp	String	The timestamp identifying when the reminder will be sent.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_EmailBouncedSoft

Query Email Bounced Soft Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_EmailBouncedSoft WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
MailingId	String	The Id of the email message that was sent.
Campaign	String	The campaign the activity is associated with.
Email	String	The email address of the intended recipient.
Details	String	The details about why the email bounced.
VariantId	String	The Id of the email variant.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
AssetName	String	The marketing asset name associated with the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_EmailDelivered

Query Email Delivered Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_EmailDelivered WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
MailingId	String	The Id of the email message that was sent.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_InterestingMoments

Query Interesting Moments Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_InterestingMoments WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
Type	String	The type of moment that occurred such as Web, Email, Milestone, etc.
Description	String	The description about the moment.
Operator	String	The interesting moment operator.
DataType	String	The interesting moment datatype.
Source	String	The source by which the interesting moment occurred.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
Date	String	The date the interesting moment occurred.
TriggeringActivityLogID	String	The Id of the activity that triggered the interesting moment.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_MergeLeads

Query Merge Leads Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_MergeLeads WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
LeadName	String	The name of the lead that was merged.
MasterUpdated	Boolean	Identifies whether the master lead was updated.
MergeIds	String	The lead Ids that were merged.
MergeFields	String	The fields that were merged.
MergedInSales	String	Identifies whether the lead was merged in sales.
MergeSource	String	The source by which the merge was performed.
ActivityDateTime	Datetime	The date and time the activity was performed.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_NewLeads

Query New Lead Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_NewLeads WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
MktgAssetName	String	The name of the lead.
SourceType	String	The source type that created the new lead, such as Salesforce.com.
LeadSource	String	The source where the new lead was created.
SourceInfo	String	Information about the source of the new lead creation.
SFDCType	String	The SFDC type for this lead.
CreatedDate	String	The date the new lead was created.
ActivityDateTime	Datetime	The date and time the activity was performed.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_OpenEmail

Query Open Email Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_OpenEmail WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the email open activity is associated with.
MailingId	String	The Id of the email that was opened.
VariationId	String	The Id of the email variation.
UserAgent	String	The Web browser user agent information obtained when the lead clicked the email link.
IsMobileDevice	String	Identifies whether the device used to click the link was a mobile device.
Platform	String	The operating system platform used when the link was opened.
Device	String	The type of device used when the link was opened.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_RemoveFromFlow

Query Remove from Flow Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_RemoveFromFlow WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
RemoveFromCampaigns	String	The campaign Id that the lead was removed from.
DataType	String	The campaign flow datatype.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_RemoveFromList

Query Remove from List Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_RemoveFromList WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
ListId	Integer	The Id of the list the lead was removed from.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_RemoveFromSFDCCampaign

Query Remove from SFDC Campaign Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_RemoveFromSFDCCampaign WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
CampaignId	Integer	The Id of the campaign that the lead was removed from.
Campaign	String	The campaign the activity is associated with.
Status	String	The current status of the SFDC campaign.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_SendAlert

Query Send Alert Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_SendAlert WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
CampaignRunId	String	The Id of the campaign that the alert was run for.
Campaign	String	The campaign the email send activity is associated with.
MailingId	String	The Id of the email alert sent.
SendToOwner	String	Identifies which owner received the alert. For example Lead, Account, or None.
SendToList	String	Additional recipients of the alert.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_SendEmail

Query Send Email Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_SendEmail WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
CampaignRunId	String	The Id of the campaign the email was sent for.
Campaign	String	The campaign the activity is associated with.
MailingId	String	The Id of the email sent.
StepId	String	The Id of the current step in the flow.
ChoiceNumber	String	The choice number of the current step that triggered the activity.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_SFDCActivity

Query SFDC Activity Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_SFDCActivity WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Subject	String	The subject of the SFDC activity.
Description	String	The description of the SFDC activity.
OwnerID	String	The Id of the SFDC activity owner.
ActivityOwner	String	The owner of the SFDC activity.
Status	String	The current status of the SFDC activity.
Priority	String	The priority of the activity.
IsTask	String	Identifies whether the activity is a task.
DueDate	String	The date the SFDC activity is due.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_SFDCMergeLeads

Query SFDC Merge Leads Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_SFDCMergeLeads WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
LeadName	String	The name of the SFDC lead that was merged.
Merged	String	The fields that were merged for the SFDC lead.
WinningValues	String	The winning values of the merged SFDC lead.
ActivityDateTime	Datetime	The date and time the activity was performed.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer other:filterable=	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Activities_VisitWebpage

Query Visit Webpage Activities for a Marketo organization.

Table Specific Information

Select

Marketo allows one (and only one) of the following columns to be used in the WHERE clause of a SELECT query: LeadId, Email, StaticListId, StaticListName, OldestCreatedAt, or ActivityCreatedAt. The LeadId and Email columns allow multiple values to be specified by the OR logical operator. All columns must be specified using the '=' operator; otherwise, an exception will be thrown.

SELECT * FROM Activities_VisitWebpage WHERE Email='[email protected]' OR Email='[email protected]'

Columns


Name	Type	Description
ActivityId [KEY]	Integer	The unique Id of the activity.
LeadId	Integer	The unique Id of the lead associated with the activity.
Campaign	String	The campaign the activity is associated with.
WebpageId	String	The Id of the Web page that was visited.
WebpageURL	String	The URL of the Web page that was visited.
LinkId	String	The Id of the link that was clicked.
QueryParameters	String	The query parameters contained within the URL.
ClientIPAddress	String	The IP address of the client that clicked the link.
MessageId	String	The Id of the message where the link was clicked.
UserAgent	String	The Web browser user agent information obtained when the Web page was visited.
ReferrerURL	String	The URL of the referrer used to identify where the link click originated from.
CreatedAt	String	The date and time the Web page activity was created.
ActivityDateTime	Datetime	The date and time the activity was performed.
AssetName	String	The marketing asset name associated with the activity.
Email	String	Used when performing a GET. Filters activities using the email address of the lead associated with the activity.

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
OldestCreatedAt	Datetime	Used when performing a GET. Returns all activities updated since the specified time.
ActivityCreatedAt	Datetime	Used when performing a GET. Returns all activities updated up until the specified time.
StaticListId	Integer	Used when performing a GET. The Id of the static list to retrieve.
StaticListName	String	Used when performing a GET. The name of the static list to retrieve.

Campaigns

Query Campaigns for a Marketo organization.

Table Specific Information

Select

Marketo allows the Source and Name columns to be used in the WHERE clause of a SELECT query. Use the Source column to identify the campaigns that were created within Marketo or by Sales. The Name column allows the use of the '=' or 'LIKE' operator. The '=' operator denotes an exact match; the 'LIKE' operator denotes a partial match of the name.

SELECT * FROM Campaigns WHERE Name LIKE 'test'

Columns


Name	Type	Description
Id [KEY]	Integer	The unique Id of the campaign.
Name	String	The name of the campaign.
Description	String	The description of the campaign.
Source	String	The source of the campaign. Valid values are MKTOWS and SALES.

Channels

Query Channels for a Marketo organization.

Table Specific Information

Select

Marketo allows the TagValue column to be used in the WHERE clause of a SELECT query. The TagValue column allows multiple values to be specified by using the OR logical operator. Note that only the '=' operator is supported.

SELECT * FROM Channels WHERE TagValue='Email Blast' OR TagValue='Blog' OR TagValue='Webinar'

Columns


Name	Type	Description
TagValue [KEY]	String	The tag value of the channel. For example, Webinar, Blog, or Tradeshow.
Status [KEY]	String	The progression status of the channel.
Step	Integer	The step number of each progression status used to sequence the order of the channel.
Success	Boolean	Specifies whether the progression status results in a success.
CheckInStatus	String	The check-in status for a roadshow event. Valid values are None, Registered, Attended.
WebinarBehavior	String	The behavior performed during a webinar event.

Opportunities

Query Opportunities for a Marketo organization.

Table Specific Information

Select

Marketo allows the following columns to be used in the WHERE clause of a SELECT query: Id, Name, CreatedAt, UpdatedAt, Type, and Stage. The CreatedAt and UpdatedAt filters can be specified twice to create a date range.

SELECT * FROM Opportunities WHERE Stage='Commercial'

Columns


Name	Type	Description
Id [KEY]	Integer	The unique, Marketo-assigned identifier of the opportunity.
Name	String	The name of the opportunity.
Description	String	The description of the opportunity.
CompanyId	String	The Id of the company assigned to the opportunity.
Quantity	Double	Number of items included in this opportunity. Used in quantity-based forecasting.
Amount	Decimal	The estimated total sale amount from the opportunity.
ExpectedRevenue	Decimal	The expected revenue to result from the opportunity.
Stage	String	The stage that the opportunity process is currently in.
LeadSource	String	The source of the opportunity, such as Advertisement or Trade Show.
ExternalCreatedDate	Datetime	The local created date of the opportunity.
LastActivityDate	Date	The date of the last activity performed with this opportunity.
IsWon	Boolean	Determines whether the opportunity was won.
NextStep	String	A description of the next task in closing the opportunity.
IsClosed	Boolean	Determines whether the opportunity is closed.
CloseDate	Date	The date when the opportunity is expected to close.
Fiscal	String	If fiscal years are not enabled, the name of the fiscal quarter or period in which the opportunity CloseDate falls. Value should be in YYYY Q format, for example, '2006 1' for first quarter of 2006.
FiscalQuarter	Integer	The fiscal quarter the opportunity took place. Valid values are 1, 2, 3, or 4.
FiscalYear	Integer	The fiscal year the opportunity took place.
ForecastCategoryName	String	The name of the forecast category.
Probability	Integer	The percentage of estimated confidence in closing the opportunity.

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
CreatedAt	Datetime	Used when performing a GET. Filters opportunities by the date they were created.
UpdatedAt	Datetime	Used when performing a GET. Filters opportunities by the date they were updated.
Type	String	Used when performing a GET. Filters opportunities by the opportunity type.

OpportunityPersonRoles

Query Opportunity Person Roles for a Marketo organization.

Table Specific Information

Select

Marketo allows the following columns to be used in the WHERE clause of a SELECT query: Id, CreatedAt, UpdatedAt, OpportunityId, and Role. The CreatedAt and UpdatedAt filters can be specified twice to create a date range.

SELECT * FROM OpportunityPersonRoles WHERE Role='Business User'

Columns


Name	Type	Description
Id [KEY]	Integer	The unique, Marketo-assigned identifier of the opportunity person role.
OpportunityId	Integer	The Id of the opportunity associated with the person specified via PersonId.
PersonId	String	The Id of the person associated with the opportunity specified via OpportunityId.
Role	String	The role of the contact person in regards to the opportunity.
IsPrimary	Boolean	Determines whether the person, specified via PersonId, is the primary contact for this opportunity.
ExternalCreatedDate	Datetime	The local created data of the OpportunityPersonRole association.

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
CreatedAt	Datetime	Used when performing a GET. Filters opportunities by the date they were created.
UpdatedAt	Datetime	Used when performing a GET. Filters opportunities by the date they were updated.

REST Data Model

The CData Sync App models Marketo entities in relational Tables, Views, and Stored Procedures. API limitations and requirements are documented in the following sections; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Note: Bulk operations are supported for all tables in the REST data model.

Tables

Tables describes the available tables. The Leads, CustomObjects, and Custom Activity tables are dynamic tables. The data model illustrates a sample of what your Marketo data model might look like. The actual data model will be obtained dynamically based on your user credentials and Marketo account.

Views

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

Stored Procedures

Stored Procedures are function-like interfaces to Marketo. They can be used to search, update, and modify information in Marketo.

Tables

The Sync App models the data in Marketo into a list of tables that can be queried using standard SQL statements.

Generally, querying Marketo tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Marketo Connector for CData Sync Tables


Name	Description
CustomActivities	Query Custom Activities for a Marketo organization.
CustomObjects	Create, update, delete, and query custom objects for a Marketo organization.
Emails	Query Emails for a Marketo organization.
EmailTemplates	Query, update and delete EmailTemplates for a Marketo organization.
Folders	Create, update, delete, and query Folders for a Marketo organization.
Forms	Create, update, delete and query Forms for a Marketo organization.
LandingPages	Create, update, delete and query Landing Pages for a Marketo organization.
LandingPageTemplates	Create, update, delete and query LandingPageTemplates for a Marketo organization.
Leads	Create, update, delete, and query Leads for a Marketo organization.
ListStaticMemberShip	Create, delete and query query static list members for a Marketo organization.
NamedAccounts	Query Named Accounts for a Marketo organization.
Opportunities	Query Opportunities for a Marketo organization.
OpportunityRoles	Query Opportunity Roles for a Marketo organization.
ProgramMembers	Query Program members for a program.
Programs	Query Programs for a Marketo organization.
SalesPersons	Query Sales Persons for a Marketo organization.
SmartCampaigns	Create, update, delete, and query SmartCampaigns for a Marketo organization.
SmartLists	Query and delete SmartLists for a Marketo organization.
Snippets	Create, update, delete and query Snippets for a Marketo organization.
StaticLists	Create, update, delete and query Static Lists for a Marketo organization.
Tokens	Create, update, delete, and query Tokens for a Marketo organization.

CustomActivities

Query Custom Activities for a Marketo organization.

Table Specific Information

Each custom activity contained within your Marketo organization will be returned as it's own table. Each table name will be prefixed with 'Activity_' followed by the name of your custom activity.

Select

Custom activities can be retrieved by performing a SELECT query on the custom activity table.

SELECT * FROM Activity_MyCustomActivity

Insert

Custom activities can be added by performing an INSERT. To create a new custom activity record, specify the information about the custom activity to be entered into the database.

The following properties are required when creating a custom activity record: ActivityDate, LeadId, and PrimaryAttributeValue (note this column name changes based on the name you designated for the primary field of the activity).

The following example demonstrates how to insert a new custom activity called MyCustomActivity:

INSERT INTO Activity_MyCustomActivity (ActivityDate, PrimaryFieldValue, LeadId, MyInt, Email) VALUES ('11/08/2016', '123', '1', 12345, '[email protected]')

Update

UPDATEs are not supported on custom activities.

Delete

DELETEs are not supported on custom activities.

Columns


Name	Type	ReadOnly	Filterable	Description
ActivityId [KEY]	Integer	True		The unique Id of the activity.
LeadId	Integer	True	True	The unique Id of the lead associated with the activity.
ActivityDate	Datetime	True	True	The date and time the lead was added to the list. Can be used as a filter to specify the starting date and time to retrieve all activities on or after the specified date.
PrimaryAttribute	String	True	True	The primary field Id
PrimaryAttributeValue	String	True		The primary field value.

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
ListId	Integer	A List Id used to retrieve actvities for all leads contained within the specified list.

CustomObjects

Create, update, delete, and query custom objects for a Marketo organization.

Table Specific Information

Each custom object contained within your Marketo organization will be returned as it's own table. Each table name will be prefixed with 'CustomObject_' followed by the name of your custom object.

Select

The Marketo REST API requires that a filter be specified to retrieve custom objects. The filter must contain at least one column that is the Key, a dedupeField, or a column that is searchable. Only the '=' operator is supported on these filter types. Other filters can be specified in addition to this but at least one of the previously mentioned filters must be specified. All filterable columns allow multiple values to be specified by using the IN operator or the OR logical operator.

Response time from the server can be improved by identifying only the rows and columns you want to retrieve.

SELECT Make, Model, Year, Color FROM CustomObject_MyCustomBikeObject WHERE VIN='12345'

Insert

To create a new custom object record, specify the information about the custom object to be entered into the database.

The following example demonstrates how to insert a new custom object called MyCustomBikeObject:

INSERT INTO MyCustomBikeObject (VIN, Make, Model, Color) VALUES ('99999', 'Yamaha', 'FZ-09', 'Blue')

Update

Any field that is not read-only can be updated.

UPDATE MyCustomBikeObject SET Year=2016 WHERE VIN='99999'

Delete

Delete is used to remove custom objects from Marketo. To perform a delete, the table key or a dedupeField column must be specified.

DELETE FROM MyCustomBikeObject WHERE VIN='99999'

Columns


Name	Type	ReadOnly	Filterable	Description
MarketoGUID [KEY]	String	True		The unique, Marketo-assigned identifier of the custom object.
CreatedAt	Datetime	False		The datetime the custom object was created.
UpdatedAt	Datetime	False		The datetime the custom object was updated.

Emails

Query Emails for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of emails from the target instance, filterable by name.

SELECT * FROM Emails

SELECT * FROM Emails WHERE Name = 'CRUD Test'

Retrieve the email record for the given target Id.

SELECT * FROM Emails WHERE Id = 1192

Insert

To create a new Email, specify at least the Name, Template, FolderId and FolderType column.

INSERT INTO Emails (Name, Template, FolderId, FolderType) Values ('My Email', '1078', 2307, 'Folder')

Update

Any field that is not read-only can be updated.

UPDATE Emails SET Description='Testing Update' WHERE Id = 1192

Delete

To delete a Email you must specify the ID field.

DELETE FROM Emails WHERE Id=1192

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	The unique, Marketo-assigned identifier of the email.
Name	String	False	True	The name of the email.
Description	String	False		The description of the email.
Subject	String	False		The email subject.
FromName	String	False		The from name.
FromEmail	String	False		The from email address.
ReplyEmail	String	False		The reply email address.
FolderId	Integer	False		The Id of the folder where the email is located
FolderType	String	False		The type of the folder where the email is located.
FolderName	String	False		The name folder where the email is located.
Operational	Boolean	False		Identifies whether the email is operational.
TextOnly	Boolean	False		Identifies whether the email is text only.
PublishToMSI	Boolean	False		Identifies whether the email is published.
WebView	Boolean	False		Identifies whether the email is web view.
Status	String	False		The status of the email.
Version	Integer	False		The version of the email.
AutoCopyToText	Boolean	False		Identifies whether the email is auto copied to text.
Template	Integer	False		The template associated with the email.
Workspace	String	False		The name of the workspace where the email is located.
CreatedAt	Datetime	True		The date and time the email was created.
UpdatedAt	Datetime	True		The date and time the email was last updated.
PreHeader	String	False		The preheader text for the email.

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
EarliestUpdatedAt	Datetime	Exclude emails prior to this date. Must be valid ISO-8601 string.
LatestUpdatedAt	Datetime	Exclude emails after this date. Must be valid ISO-8601 string.

EmailTemplates

Query, update and delete EmailTemplates for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of email templates from the target instance, filterable by name and status.

SELECT * FROM EmailTemplates

SELECT * FROM EmailTemplates WHERE Name = 'CRUD Test'

Retrieve the email record for the given target Id.

SELECT * FROM EmailTemplates WHERE Id = 1192

Update

Any field that is not read-only can be updated.

UPDATE EmailTemplates SET Name = 'Update Test' Description = 'Testing Update' WHERE Id = 1192

Delete

To delete a Email you must specify the ID field.

DELETE FROM EmailTemplates WHERE Id = 1192

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	Id of the asset.
Name	String	False	True	Name of the asset.
CreatedAt	Datetime	True		Datetime the asset was created.
Description	String	False		Description of the asset.
FolderId	Integer	True		The Id of the folder
FolderType	String	True		The Type of folder The allowed values are Folder, Program.
FolderName	String	True		The Name of folder
Status	String	True	True	Status filter for draft or approved versions
UpdatedAt	Datetime	True		Datetime the asset was most recently updated
Url	String	True		Url of the asset in the Marketo UI
Version	Integer	True		The Template version type The allowed values are 1, 2.
Workspace	String	True		Name of the workspace
Content	String	True		HTML content for template. Multipart file.

Folders

Create, update, delete, and query Folders for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve all folders within two levels of the folder hierarchy.

SELECT * FROM Folders

Retrieve all folders under a specific root folder.

SELECT * FROM Folders WHERE RootFolderId=38 AND MaxDepth=5

Insert

To create a new Folder, specify at least the Name, ParentId and ParentType column.

INSERT INTO Folders(Name,ParentId,ParentType) Values('New_Folder_Marketo_CData_Driver', 38, 'Folder')

Update

Any field that is not read-only can be updated.

Update Folders Set Description='Updated Folder', IsArchive=true WHERE Id=1996

Delete

To delete a folder you must specify the ID field. Deletions can be made against single folders if they are empty, meaning that they contain no assets or subfolders. If a folder is of type Program, or has the isSystem field set to true, it cannot be deleted.

DELETE FROM Folders WHERE Id=1996

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	The unique, Marketo-assigned identifier of the folder.
Name	String	False	True	The name of the folder.
Description	String	False		The description of the folder.
Type	String	True		The type of the folder.
CreatedAt	Datetime	True		The date and time the folder was created.
UpdatedAt	Datetime	True		The date and time the folder was last updated.
ParentId	Integer	False		The Id of the parent folder.
ParentType	String	False		The type of the parent folder.
Path	String	True		The path of a folder shows its hierarchy in the folder tree, similar to a Unix-style path.
WorkSpace	String	True	True	The name of the smart campaign workspace.
URL	String	True		The explicit URL of the asset in the designated instance.
IsSystem	Boolean	True		Whether or not the folder is a system folder.
IsArchive	Boolean	False		Whether or not the folder is archived.
AccessZoneId	Integer	True		The access zone id

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
RootFolderId	Integer	The parent folder ID under which the query will be performed.
RootFolderType	String	The parent folder type under which the query will be performed. The allowed values are Folder, Program. The default value is Folder.
MaxDepth	Integer	Maximum folder depth to traverse. The default value is 2.

Forms

Create, update, delete and query Forms for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of accessible forms from the target instance.

SELECT * FROM Forms

Retrieve the form for the given Id.

SELECT * FROM Forms WHERE Id = '1214'

Insert

To create a new form, specify at least the Name, FolderId and FolderType column.

INSERT INTO Forms (Name, Description, FolderId, FolderType) Values ('My Snippet','Test Snippet insert', 1089, 'Program')

Update

Any field that is not read-only can be updated.

UPDATE Forms SET Description='Testing Update', Name='Test Update' WHERE Id = '1214'

Delete

To delete a Snippet you must specify the ID field.

DELETE FROM Forms WHERE Id = '1214'

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	Id of the asset.
Name	String	False	True	Name of the asset.
ButtonLabel	String	False		Label text of the button.
ButtonLocation	Integer	False		Location in pixels of the button relative to the left of the form.
CreatedAt	Datetime	True		Datetime the asset was created.
Description	String	False		Description of the asset.
FolderId	Integer	False	True	Id of the folder.
FolderType	String	False	True	Type of folder.
FontFamily	String	False		font-family property for the form.
FontSize	String	False		font-size property of the form.
KnownVisitorTemplate	String	False		Template of the known visitor behavior for the form.
KnownVisitorType	String	False		Type of the known visitor behavior for the form.
LabelPosition	String	False		Default positioning of labels.
Language	String	False		Language of the form.
Locale	String	False		Locale of the form.
ProgressiveProfiling	Boolean	False		Whether progressive profiling is enabled for the form.
Status	String	False	True	Status filter for draft or approved versions. The allowed values are approved, draft.
Theme	String	False		CSS theme for the form to use.
UpdatedAt	Datetime	True		Datetime the asset was most recently updated.
Url	String	False		Url of the asset in the Marketo UI.
WaitingLabel	String	False		Waiting text of the button.

LandingPages

Create, update, delete and query Landing Pages for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of accessible landing pages from the target instance, filterable by Status and folder.

SELECT * FROM LandingPages

SELECT * FROM LandingPages WHERE Status = 'draft'

SELECT * FROM LandingPages WHERE FolderId = 1184 AND FolderType = 'Program'

Retrieve the landing page record for the given name or a target Id.

SELECT * FROM LandingPages WHERE Id = 1234

SELECT * FROM LandingPages WHERE Name = 'Agenda'

Insert

To create a new LandingPage, specify at least the Name, FolderId, FolderType and Template column.

INSERT INTO LandingPages (CustomHeadHTML,Description,FacebookOgTags,FolderId,FolderType,Keywords,MobileEnabled,Name,FormPrefill,Robots,Template,Title,URL,Workspace) VALUES ('<!DOCTYPE html>\n<html>\n<body>\n<h1>My First Heading</h1>\n<p>My first paragraph.</p>\n</body></html>','Testing  Insert operation','',1184,'Program','',false,'Test Insert',false,'index, nofollow',1,'Insert Operation','http://na-ab23.marketo.com/lp/119-IEY-862/LPtest_08.html','CRH')

Update

Any field that is not read-only can be updated.

UPDATE LandingPages SET CustomHeadHTML = '<!DOCTYPE html>\n<html>\n<body>\n<h1>My First Heading</h1>\n<p>My first paragraph.</p>\n</body></html>', Description='Testing Update', FacebookOgTags='', Keywords='', MobileEnabled=false, Name='Test Update', Robots='index, nofollow',Title='Update Operation', URL='http://na-ab23.marketo.com/lp/119-IEY-862/LPtest_08.html' WHERE Id = 1103

Delete

To delete a LandingPage you must specify the ID field.

DELETE FROM LandingPages WHERE Id = 1996

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	Id of the asset.
Name	String	False	True	Name of the asset.
ComputedUrl	String	True		Computed Url of the asset.
CreatedAt	Datetime	True		Datetime the asset was created.
CustomHeadHTML	String	False		Any custom HTML to embed in the tag of the page.
Description	String	False		Description of the asset.
FacebookOgTags	String	False		Any OpenGraph meta tags to apply to the page.
FolderId	Integer	False	True	Id of the folder.
FolderType	String	False	True	Type of folder. The allowed values are Folder, Program.
FolderName	String	False	False	Name of folder.
FormPrefill	Boolean	False		Boolean to toggle whether forms embedded in the page will prefill. Default false. The default value is false.
Keywords	String	False		Keywords
MobileEnabled	Boolean	False		Whether the page has mobile viewing enabled. Free-form pages only. Default false. The default value is false.
Robots	String	False		Robots directives to apply to the pages meta tags
Status	String	True	True	Status filter for draft or approved versions. The allowed values are approved, draft.
Template	Integer	False		Id of the template used.
Title	String	False		Title element of the landing page.
UpdatedAt	Datetime	True		Datetime the asset was most recently updated.
URL	String	False		Url of the asset in the Marketo UI. You have to send the URL path of the page while creating or updating.
Workspace	String	False		Name of the workspace.

LandingPageTemplates

Create, update, delete and query LandingPageTemplates for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of accessible landing page templates from the target instance, filterable by Name, Status and folder.

SELECT * FROM LandingPageTemplates

SELECT * FROM LandingPageTemplates WHERE Status='draft'

SELECT * FROM LandingPageTemplates WHERE FolderId=1184 AND FolderType='Program'

Retrieve the landing page template record for the given name or a target Id.

SELECT * FROM LandingPageTemplates WHERE Id=1234

SELECT * FROM LandingPageTemplates WHERE Name='Agenda'

Insert

To create a new LandingPageTemplate, specify at least the Name, FolderId, FolderType and TemplateType column.

INSERT INTO LandingPageTemplates (Description, EnableMunchkin, FolderId, FolderType, Name, TemplateType) VALUES ('Testing Insert', true, 19, 'Folder', 'Test Insert 1', 'guided')

Update

Any field that is not read-only can be updated.

UPDATE LandingPageTemplates SET Description='Testing Update', EnableMunchkin=false, Name='Test Update' WHERE Id = 1312

Delete

To delete a LandingPageTemplate you must specify the ID field.

DELETE FROM LandingPageTemplates WHERE Id = 1312

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	Id of the asset.
Name	String	False	True	Name of the asset.
CreatedAt	Datetime	True		Datetime the asset was created.
Description	String	False		Description of the asset.
EnableMunchkin	Boolean	False		Whether to enable munchkin on the derived pages. Defaults to true. The default value is true.
FolderId	Integer	False	True	Id of the folder.
FolderType	String	False	True	Type of folder. The allowed values are Folder, Program.
FolderName	String	False		Name of folder.
Status	String	True	True	Status filter for draft or approved versions. The allowed values are draft, approved.
TemplateType	String	False		Type of template to create 'guided' or 'freeForm' The allowed values are guided, freeForm. The default value is freeForm.
UpdatedAt	Datetime	True		Datetime the asset was most recently updated.
Url	String	True		Url of the asset in the Marketo UI.
Workspace	String	True		Name of the workspace.

Leads

Create, update, delete, and query Leads for a Marketo organization.

Table Specific Information

Select

All columns must be specified using the '=' operator. All filterable columns allow multiple values to be specified by using the IN operator or the OR logical operator.

Response time from the server can be improved by identifying only the rows and columns you want to retrieve.

SELECT Id, FirstName, LastName FROM Leads WHERE Id IN (1, 2, 5, 10)

If a filter is not specified, the Activities_NewLead table will be queried to retrieve a list of Lead Ids. Once the Lead Ids have been compiled, the Leads table will be queried using the compiled list of Lead Ids. The 'CreatedAt' column can be used as a filter to specify the created datetime range of Leads to retrieve. When the '>' or '>=' operator is specified, the datetime value will be submitted in the Activities_NewLead table request. Note that this requires additional API calls and will at least double the amount of API calls made as one Activities_NewLead request must be made for each Leads request made. The maximum batch size per request for the REST API is 300, so to get a rough estimate of the number of API calls required, the following formula can be used: (Total Number of Leads / 300) * 2

The 'UpdatedAt' column can also be used as a filter to specify the updated datetime range of Leads to retrieve. When specified, the Activities_LeadChanges table will be queried to retrieve a list of Lead Ids. Once the Lead Ids have been compiled, the Leads table will be queried using the compiled list of Lead Ids. When the '>' or '>=' operator is specified, the datetime value will be submitted in the Activities_LeadChanges table request. Note that this requires additional API calls and will at least double the amount of API calls made as one Activities_LeadChanges request must be made for each Leads request made. In most cases, the total API calls will more than double as individual values for a Lead are returned as a record). Thus to get a full list of Lead Ids, multiple Activities_LeadChanges requests may need to be made. The maximum batch size per request for the REST API is 300, so to get a minimum estimate of the number of API calls required, the following formula can be used: (Total Number of Leads / 300) * 2

Alternatively, the SOAP API can be used to to retrieve a list of Lead Ids by setting "UseSOAPForLeadIds=True" in the 'Other' property. When specified the SOAP API will be used (provided the SOAP connection details are specified) to compile a list of Lead Ids which will then be used as a filter for the REST API. Note that the SOAP API is significantly slower than the REST API, although this hybrid approach is faster than using just the SOAP API by itself. The maximum batch size per request for the SOAP API is 1000, so to get a minimum estimate of the number of API calls required, the following formula can be used: (Total Number of Leads / 1000) + (Total Number of Leads / 300)

To bypass utilizing multiple API calls and for the best performance, create a static list of Leads within Marketo and then specify the ListId to retrieve them.

Insert

To create a new Lead record, specify the information about the Lead to be entered into the database.

The following example demonstrates how to insert a new Lead:

INSERT INTO Leads (Email, FirstName, LastName) VALUES ('[email protected]', 'John', 'Mark')

Update

Any field that is not read-only can be updated. Updates can be performed using an external key which is any 'Filterable' column, such as Email.

The following example demonstrates how to update using the 'Id' column as the lookup field.

UPDATE Leads SET MobilePhone='111-222-3333' WHERE Id = 1

The following example demonstrates how to update using the 'Email' column as the lookup field.

UPDATE Leads SET MobilePhone='111-222-3333' WHERE Email = '[email protected]'

Additionally you can use a custom field as the lookup field. In such a case, you will need to set the 'LookupField' column as well to the name of the custom field you are using. The following example demonstrates how to update use a custom field called 'MyCustomField' as the lookup field.

UPDATE Leads SET MobilePhone='111-222-3333' WHERE LookupField = 'MyCustomField' AND MyCustomField = 'my_value'

Delete

Delete is used to remove leads from Marketo. To perform a delete, the lead Id field is required.

DELETE FROM Leads WHERE Id = 1

GetDeleted

GetDeleted is used to get deleted leads from Marketo. To get deleted leads, you need to specify the UpdatedAt filter. If not specified, it will fetch data for last 14 days.

GetDeleted FROM Leads

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	The unique, Marketo-assigned identifier of the account.
Email	String	False	True	The lead's email address.
Salutation	String	False		The lead's salutation.
FirstName	String	False		The lead's first name.
MiddleName	String	False		The lead's middle name.
LastName	String	False		The lead's last name.
DateOfBirth	Date	False		The lead's date of birth.
Title	String	False		The lead's job title.
Address	String	False		The lead's street address.
City	String	False		The lead's city.
State	String	False		The lead's state.
PostalCode	String	False		The lead's ZIP/postal code.
Country	String	False		The lead's country.
Website	String	False		The lead's website.
Phone	String	False		The lead's phone number.
MobilePhone	String	False		The lead's mobile phone number.
Fax	String	False		The lead's fax number.
Company	String	False		The name of the lead's company.
MainPhone	String	False		The phone number of the lead's company.
SICCode	String	False		The SIC (Standard Industrial Classification) code of the lead's company.
Site	String	False		The site of the lead's company.
BillingStreet	String	False		The billing street address of the lead's company.
BillingCity	String	False		The billing city of the lead's company.
BillingState	String	False		The billing state of the lead's company.
BillingPostalCode	String	False		The billing ZIP/postal code of the lead's company.
BillingCountry	String	False		The billing country of the lead's company.
NumberOfEmployees	Integer	False		The number of employees at the lead's company.
Industry	String	False		The industry of the lead's company.
Department	String	False		The lead's deparment.
AnnualRevenue	Double	False		The annual revenue generated at the lead's company.
AnonymousIP	String	False		The IP address of the lead if it is anonymous.
Unsubscribed	Boolean	False		Determines whether the lead is unsubscribed.
UnsubscribedReason	String	False		The reason why the lead has unsubscribed.
EmailInvalid	Boolean	False		Identifies whether the lead's email address is invalid.
EmailInvalidCause	String	False		The reason why the lead's email address is invalid.
DoNotCall	Boolean	False		Identifies whether the lead is on the 'Do Not Call' list.
DoNotCallReason	String	False		The reason why the lead is on the 'Do Not Call' list.
PersonType	String	False		The type of person the current record is, such as a contact.
IsAnonymous	Boolean	True		Identifies whether the lead is anonymous or not.
IsLead	Boolean	False		Identifies whether the person is a lead or not.
LeadRole	String	False		The lead's role.
LeadSource	String	False		The lead's source.
LeadStatus	String	False		The lead's current status.
LeadScore	Integer	False		The lead's score.
Rating	String	False		The lead's rating.
Urgency	Double	True		The lead's urgency.
Priority	Integer	True		The lead's priority.
RelativeScore	Integer	True		The lead's relative score.
OriginalSourceType	String	True		The original source type where the lead originated from.
OriginalSourceInfo	String	True		Information about the original source of the lead.
RegistrationSourceType	String	False		The original source type where the lead originated from.
RegistrationSourceInfo	String	False		Information about the original source of the lead.
CreatedAt	Datetime	True	True	The date the lead was created.
UpdatedAt	Datetime	True	True	The date the lead was last updated.
Cookies	String	False	True	The cookies associated with the lead.
AcquisitionProgramId	String	False		The Id of the program in which the lead was acquired.
Gender	String	False		The lead's social gender.
TotalReferredVisits	Integer	True		The lead's total social referred visits.
TotalReferredEnrollments	Integer	True		The lead's total social referred enrollments.
LastReferredEnrollment	Datetime	False		The lead's last social referred enrollment.
LastReferredVisit	Datetime	False		The lead's last social referred visit.
SyndicationId	String	False		The lead's social syndication Id.
FacebookDisplayName	String	False		The lead's Facebook display name.
FacebookId	String	False	True	The lead's Facebook Id.
FacebookPhotoURL	String	False		The lead's Facebook photo URL.
FacebookProfileURL	String	False		The lead's Facebook profile URL.
FacebookReach	Integer	False		The lead's Facebook reach.
FacebookReferredEnrollments	Integer	False		The lead's Facebook referred enrollments.
FacebookReferredVisits	Integer	False		The lead's Facebook referred visits.
LinkedInDisplayName	String	False	True	The lead's LinkedIn display name.
LinkedInId	String	False		The lead's LinkedIn Id.
LinkedInPhotoURL	String	False		The lead's LinkedIn photo URL.
LinkedInProfileURL	String	False		The lead's LinkedIn profile URL.
LinkedInReach	Integer	False		The lead's LinkedIn reach.
LinkedInReferredEnrollments	Integer	False		The lead's LinkedIn referred enrollments.
LinkedInReferredVisits	Integer	False		The lead's LinkedIn referred visits.
TwitterDisplayName	String	False		The lead's Twitter display name.
TwitterId	String	False		The lead's Twitter Id.
TwitterPhotoURL	String	False		The lead's Twitter photo URL.
TwitterProfileURL	String	False		The lead's Twitter profile URL.
TwitterReach	Integer	False		The lead's Twitter reach.
TwitterReferredEnrollments	Integer	False		The lead's Twitter referred enrollments.
TwitterReferredVisits	Integer	False		The lead's Twitter referred visits.
ListId	Integer	True	True	A List Id used to retrieve all leads contained within the specified list. This is a filter only field made available to enhance filtering capabilities.
ProgramId	Integer	True	True	A Program Id used to retrieve all leads associated with the specified program. This is a filter only field made available to enhance filtering capabilities.
LookupField	String	True	True	Used to specify the field used to find duplicate leads. Only used when performing an INSERT or UPDATE. Available values are: id (default), cookie, email, twitterId, facebookId, linkedInId, sfdcAccountId, sfdcContactId, sfdcLeadId, sfdcLeadOwnerId, and custom fields. This is a filter only field made available to enhance filtering capabilities.

ListStaticMemberShip

Create, delete and query query static list members for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of Static MemberShip from the target instance, filterable by Id and ListId.

SELECT * FROM ListStaticMemberShip WHERE ListId = 1014

SELECT * FROM ListStaticMemberShip WHERE ListId = 1014 AND id = 1016

Insert

To create a new Static MemberShip, specify at least the ListId and Id column.

INSERT INTO ListStaticMemberShip (ListId,Id) VALUES (1014,1014)

Delete

To delete a ListStaticMemberShip you must specify the ID and ListId field.

DELETE FROM ListStaticMemberShip WHERE Listid = 1014 AND Id = 1014

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	Id of static MemberShip.
FirstName	String	True		FirtName of the member
LastName	String	True		LastName of the member
Email	String	True		Email
CreatedAt	Datetime	True		The date and time the membership was created.
UpdatedAt	Datetime	True		The date and time the membership was last updated.
ListId	Integer	True	True	ListId.

NamedAccounts

Query Named Accounts for a Marketo organization.

Table Specific Information

Select

A filter must be specified when retrieving named accounts. Valid filters are any searchable columns which include MarketoGUID, Name, Industry, State, City, etc.

SELECT * FROM NamedAccounts WHERE Name='MyAccount'

Insert

To create a new NamedAccount record, specify the information about the named account to be entered into the database.

The following example demonstrates how to insert a new NamedAccount:

INSERT INTO NamedAccounts (Name, City, Country, Industry) VALUES ('MyAccount', 'MyCity', 'USA', 'Tech')

Update

Any field that is not read-only can be updated.

UPDATE NamedAccounts SET NumberOfEmployees=100, State='NC', AnnualRevenue='10000000.00' WHERE Name='MyAccount'

Delete

Delete is used to remove named accounts from Marketo. To perform a delete, either the MarketoGUID field or the Name field is required.

DELETE FROM NamedAccounts WHERE Name=='MyAccount'

Columns


Name	Type	ReadOnly	Filterable	Description
MarketoGUID [KEY]	String	True	True	The unique, Marketo-assigned identifier of the named account.
Name	String	False	True	The name of the account.
AccountOwnerId	Integer	False	True	The owner id of the account.
AnnualRevenue	Double	False	True	The annual revenue for the account.
City	String	False	True	The city for the account.
Country	String	False	True	The country for the account.
DomainName	String	False	True	The name of the domain for the account.
Industry	String	False	True	The industry for the account.
LogoURL	String	False	True	The URL to the logo for the account.
MembershipCount	Integer	True	True	The number of members for the account.
NumberOfEmployees	Integer	False	True	The number of employees for the account.
OpptyAmount	Double	True	True	The total amount of opportunities for the account.
OpptyCount	Integer	True	True	The total number of opportunities for the account.
SICCode	String	False	True	The SIC Code for the account.
State	String	False	True	The state for the account.
CreatedAt	Datetime	True		The date and time the named account was created.
UpdatedAt	Datetime	True		The date and time the named account was last updated.

Opportunities

Query Opportunities for a Marketo organization.

Table Specific Information

Note: This table is only available for Marketo subscriptions which do not have a native CRM sync enabled. If sync is enabled, an error will be returned when attempting to query the table stating that the API is disabled.

Select

A filter must be specified when retrieving opportunities. Valid filters are any searchable columns which include MarketoGUID, ExternalOpportunityId, ExternalCompanyId, or ExternalSalesPersonId.

SELECT * FROM Opportunities WHERE ExternalOpportunityId='CDATA1'

Insert

To create a new Opportunity record, specify the information about the Opportunity to be entered into the database.

The following example demonstrates how to insert a new Opportunity:

INSERT INTO Opportunities (ExternalOpportunityId, Description, ExternalCompanyId, Name) VALUES ('CDATA1', 'CData Software Inc Opportunity', 'CDATA', 'CData')

Update

Any field that is not read-only can be updated.

UPDATE Opportunities SET IsWon=true, FiscalYear=2016, Amount='1000.00' WHERE ExternalOpportunityId='Opportunity1'

Delete

Delete is used to remove opportunities from Marketo. To perform a delete, either the Id field or the ExternalOpportunityId field is required.

DELETE FROM Opportunities WHERE ExternalOpportunityId=='Opportunity1'

Columns


Name	Type	ReadOnly	Filterable	Description
MarketoGUID [KEY]	String	True	True	The unique, Marketo-assigned identifier of the opportunity.
ExternalOpportunityId	String	False	True	The external Id of the opportunity.
Amount	Double	False		The amount of the opportunity.
CloseDate	Datetime	False		The date and time the opportunity was closed.
Description	String	False		The description of the opportunity.
ExpectedRevenue	Double	False		The expected revenue of the opportunity.
ExternalCompanyId	String	False	True	The external company Id of the opportunity.
ExternalCreatedDate	Datetime	False		The external date and time the opportunity was created.
ExternalSalesPersonId	String	False	True	The external sales person Id of the opportunity.
Fiscal	String	False		The fiscal of the opportunity.
FiscalQuarter	String	False		The fiscal quarter of the opportunity.
FiscalYear	String	False		The fiscal year of the opportunity.
ForecastCategoryName	String	False		The forecast category name of the opportunity.
IsClosed	Boolean	False		Specifies whether the opportunity is closed.
IsWon	Boolean	False		Specifies whether the opportunity was won.
LastActivityDate	Datetime	False		The date and time the last activity occurred on the opportunity.
LeadSource	String	False		The lead source of the opportunity.
Name	String	False		The name of the opportunity.
NextStep	String	False		The next step of the opportunity.
Probability	Integer	False		The probability of the opportunity.
Quantity	Double	False		The quantity of the opportunity.
Stage	Double	False		The stage of the opportunity.
Type	Double	False		The type of the opportunity.
CreatedAt	Datetime	True		The date and time the opportunity was created.
UpdatedAt	Datetime	True		The date and time the opportunity was last updated.

OpportunityRoles

Query Opportunity Roles for a Marketo organization.

Table Specific Information

Note: This table is only available for Marketo subscriptions which do not have a native CRM sync enabled. If sync is enabled, an error will be returned when attempting to query the table stating that the API is disabled.

Select

A filter must be specified when retrieving opportunities. Valid filters are any searchable columns which include MarketoGUID and a combination of ExternalOpportunityId and LeadId.

SELECT * FROM OpportunityRoles WHERE ExternalOpportunityId='Opportunity1' AND LeadId='1'

Insert

To create a new Opportunity Role record, specify the information about the Opportunity Role to be entered into the database.

The following example demonstrates how to insert a new Opportunity Role:

INSERT INTO OpportunityRoles (ExternalOpportunityId, LeadId, IsPrimary, Role) VALUES ('CDATA1', '1', false, 'MyRole')

Update

Any field that is not read-only can be updated.

UPDATE OpportunityRoles SET IsPrimary=true WHERE MarketoGUID='c674bda8-6e94-40cf-a853-98833b85b7cb'

Delete

Delete is used to remove Opportunity Roles from Marketo. To perform a delete, the MarketoGUID field is required.

DELETE FROM OpportunityRoles WHERE MarketoGUID=='c674bda8-6e94-40cf-a853-98833b85b7cb'

Columns


Name	Type	ReadOnly	Filterable	Description
MarketoGUID [KEY]	String	True	True	The unique, Marketo-assigned identifier of the opportunity role.
ExternalOpportunityId	String	False	True	The external Id of the opportunity.
ExternalCreatedDate	Datetime	False		The external date and time the opportunity role was created.
IsPrimary	Boolean	False		Specifies whether the opportunity role is the primary role on the opportunity.
LeadId	Integer	False	True	The lead Id associated with the opportunity role.
Role	String	False		The role associated with the opportunity.
CreatedAt	Datetime	True		The date and time the opportunity role was created.
UpdatedAt	Datetime	True		The date and time the opportunity role was last updated.

ProgramMembers

Query Program members for a program.

Table Specific Information

Select

Retrieve all Program Members for the specific ProgramId.

Note:

When UseBulkAPI=true and ProgramId is not specified, it will fetch the first ProgramId. We can only use =, IN operator with ProgramID.
When UseBulkAPI=false and ProgramId is not specified, it will fetch records for all the ProgramId. We can specify the =, IN, >, <, <=, >= operator with ProgramID.

SELECT * FROM ProgramMembers WHERE ProgramId = 1102
SELECT * FROM ProgramMembers WHERE ProgramId = '1044' AND LeadId IN ('1789','1789','1790','1791','1792')
SELECT * FROM ProgramMembers WHERE ProgramId = '1001' AND LeadId = '4'
SELECT * FROM ProgramMembers WHERE ProgramId IN (1102, 1103, 1104)
SELECT * FROM ProgramMembers WHERE LeadId IN ('1789','1790','1791','1792') AND reachedSuccess IN (false,true)
SELECT * FROM ProgramMembers WHERE reachedSuccess IN (false,true) AND LeadId = '1789'

Insert

To create a new Program Member, specify at least the LeadId, StatusName and ProgramId column.This operation is only supported when UseBulkApi=false.

INSERT INTO ProgramMembers (LeadId,StatusName,ProgramId) VALUES (4,'member','1001')

Update

Fields WebinarURL and RegistrationCode can be updated. To update any Program Member, specify at least the LeadId, ProgramId column.This operation is only supported when UseBulkApi=false.

UPDATE ProgramMembers SET WebinarURL = 'www.testURL.com',RegistrationCode = 'dcff5f12-a7c7-11eb-bcbc-0242ac130001' WHERE LeadId = '4' AND ProgramId = '1001'

Delete

To delete a Program member you must specify the LeadId and ProgramId field.This operation is only supported when UseBulkApi=false.

DELETE FROM ProgramMembers WHERE LeadId = '4' AND ProgramId = '1001'

Columns


Name	Type	ReadOnly	Filterable	Description
Id	Integer	True		The Id of the Program Member
AcquiredBy	Boolean	False		Indicates this program was responsible for creating the lead record
AttendanceLikelihood	Integer	False		The likelihood of the attendance at the individual level
LeadId [KEY]	Integer	False	True	The Id of the Lead
MemberDate	Datetime	False		The date of the membership
NurtureProgramId	String	True		The Id of the Nurture Program
Program	String	False		The Program with which the member is associated
ProgramId [KEY]	Integer	False	True	The Id of the Program
ProgramTypeId	String	True		The Id of the Program Type
RegistrationCode	String	False		The registration code
RegistrationLikelihood	String	False		The registration likelihood
Status	String	False		The Status of the Program Member
StatusId	String	True		The Id of the Status of the Program Member
StatusReason	String	False		The Reason of the Status of the Program Member
StatusName	String	False	True	The name of the Status of the Program Member
ReachedSuccess	Boolean	False	True	Indicates when a person reaches the status that achieves that goal
CreatedAt	Datetime	False		The created date of the membership
IsExhausted	Boolean	False		Indicates whether the membership is exhausted or not
MemberShipDate	Datetime	False		The date of the membership
NurtureCadence	String	False		The Nurture Program
ReachedSuccessDate	Datetime	False		The date when a person reaches the status that achieves that goal
UpdatedAt	Datetime	False		The updated date of the membership
Success	Boolean	False		Indicates when a person reaches the status that achieves that goal
Track	String	False		The track of the Program Member Activity
TrackName	String	False		The name of the track
WaitlistPriority	Integer	False		Indicates the program member waitlist priority
WebinarURL	String	False		The Webinar URL

Programs

Query Programs for a Marketo organization.

Table Specific Information

Select

Note: Tag and Cost columns are not returned when browsing all Programs (such as performing a SELECT * query). These columns are only returned when filtering by a specific Program Id or Name.

Tag and Cost Columns are not returned in this case.

SELECT * FROM Programs

Tag and Cost Columns are returned in this case.

SELECT * FROM Programs WHERE Id='1001'

Insert

To create a new Program record, specify the information about the Program to be entered into the database.

The following example demonstrates how to insert a new Program:

INSERT INTO Programs (Name, FolderId, FolderType, Type, Description, Channel, TagTypes, TagValues, CostStartDates, Costs, CostNotes) VALUES ('InsertEvent', '35', 'Folder', 'Default', 'Test Insert Description', 'Email Blast', 'Program Owner', 'Admin', '01/01/2015,02/02/2015', '100,200', 'January,February')

Update

Any field that is not read-only can be updated.

UPDATE Programs SET Name='UpdatedProgram', Description='Updated Description' WHERE Id='1'

Delete

Delete is used to remove programs from Marketo. To perform a delete, the program Id field is required.

DELETE FROM Programs WHERE Id='1'

Note: FolderId and FolderName can be included when inserting a new record, but they cannot be updated in existing records (read-only once a record exists).

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	The unique, Marketo-assigned identifier of the program.
Name	String	False	True	The name of the program.
Description	String	False		The description of the program.
Type	String	False		The program type.
Channel	String	False		The channel the program is associated with.
Workspace	String	False		The name of the workspace where the program is located.
Url	String	True		The URL reference to the program.
Status	String	False		The status of the program.
FolderType	String	False		The folder type that the program is contained in.
FolderId	Integer	False		The folder id that the program is contained in.
FolderName	String	False		The name of the folder the program is contained in.
TagTypes#	String	False	True	A comma-separated list of tag types associated with the program. Each TagType has a value associated with it which is returned via the TagValue column.
TagValues#	String	False	True	A comma-separated list of tag values. Each value corresponds to the type listed within the TagTypes column.
CostStartDates#	String	False		A comma-separated list of cost start dates. Each value corresponds to the costs and notes listed within the Costs and CostNotes columns.
Costs#	String	False		A comma-separated list of costs (integer values). Each value corresponds to the start dates and notes listed within the CostStartDates and CostNotes columns.
CostNotes#	String	False		A comma-separated list of cost notes. Each value corresponds to the costs and start dates listed within the Costs and CostStartDates columns.
CreatedAt	Datetime	True		The date and time the program was created.
UpdatedAt	Datetime	True		The date and time the program was last updated.

SalesPersons

Query Sales Persons for a Marketo organization.

Table Specific Information

Note: This table is only available for Marketo subscriptions which do not have a native CRM sync enabled. If sync is enabled, an error will be returned when attempting to query the table stating that the API is disabled.

Select

A filter must be specified when retrieving companies. Valid filters are any searchable columns which include Id, ExternalSalesPersonId, or Email.

SELECT * FROM SalesPersons WHERE ExternalSalesPersonId='[email protected]'

Insert

To create a new SalesPerson record, specify the information about the sales person to be entered into the database.

The following example demonstrates how to insert a new Opportunity:

INSERT INTO SalesPersons (ExternalSalesPersonId, Email, FirstName, LastName) VALUES ('[email protected]', '[email protected]', 'Sales', 'Person')

Update

Any field that is not read-only can be updated.

UPDATE SalesPersons SET Phone='919-928-5214', Title='Technical Sales', Email='[email protected]' WHERE ExternalSalesPersonId='[email protected]'

Delete

Delete is used to remove a sales person from Marketo. To perform a delete, either the Id field or the ExternalSalesPersonId field is required.

DELETE FROM SalesPersons WHERE ExternalSalesPersonId=='[email protected]'

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	The unique, Marketo-assigned identifier of the sales person.
ExternalSalesPersonId	String	False	True	The external Id of the sales person.
Email	String	False	True	The email address of the sales person.
Fax	String	False		The fax number of the sales person.
FirstName	String	False		The first name of the sales person.
LastName	String	False		The last name of the sales person.
MobilePhone	String	False		The mobile phone number of the sales person.
Phone	String	False		The phone number of the sales person.
Title	String	False		The sales person's title.
CreatedAt	Datetime	True		The date and time the sales person was created.
UpdatedAt	Datetime	True		The date and time the sales person was last updated.

SmartCampaigns

Create, update, delete, and query SmartCampaigns for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve all smart campaigns

SELECT * FROM SmartCampaigns

Retrieve a specific smart campaign

SELECT * FROM SmartCampaigns WHERE Id=2046

Insert

To create a new smart campaign, specify at least the Name, FolderId and FolderType column.

INSERT INTO SmartCampaigns(Name,FolderId,FolderType) VALUES('NewSmartCampaign','1357','Folder')

Update

Only the Name and Description columns can be updated.

Update SmartCampaigns Set Name='UpdatedSmartCampaignName', Description='CData Campaign' WHERE Id=2047

Delete

To delete a smart campaign you must specify the ID field.

DELETE FROM SmartCampaigns WHERE Id=2047

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	The Id of the smart campaign.
Name	String	False		The name of the smart campaign.
ComputedUrl	String	False		The Computed Url of the Smart Campaign
Description	String	False		The description of the smart campaign.
Type	String	True		The type of the the smart campaign. Batch: has at least one filter and no triggers. Trigger: has at least one trigger. Default: has no smart list rules.
SmartListId	Integer	True		The Id of the smart campaign's child smart list.
FlowId	Integer	True		The Id of the smart campaign's child flow.
CreatedAt	Datetime	True		The date and time the smart campaign was created.
UpdatedAt	Datetime	True		The date and time the smart campaign was last updated.
WorkSpace	String	True		The name of the workspace where the folder is located.
Status	String	True		The status of the smart campaign. The allowed values are Inactive, Single Run, Invalid, Recurring Run, Active, Requested, Never Run.
IsSystem	Boolean	True		Whether smart campaign is system managed.
IsActive	Boolean	True		Whether smart campaign is active.
IsRequestable	Boolean	True		Whether smart campaign is requestable (is active and contains 'Campaign is Requested' trigger with Source of 'Web Service API').
IsCommunicationLimitEnabled	Boolean	True		Whether smart campaign communication limit is enabled (i.e. block non-operational emails).
MaxMembers	Integer	True		The smart campaign membership limit.
QualificationRuleType	String	True		The type of qualification rule. The allowed values are once, any, interval.
QualificationRuleInterval	Integer	True		The interval of qualification rule. Only set when qualificationRuleType is 'interval'
QualificationRuleUnit	String	True		The unit of measure of qualification rule. Only set when qualificationRuleType is 'interval' = ['hour', 'day', 'week', 'month']
RecurrenceStartAt	Datetime	True		The datetime of the first scheduled campaign to run. Required if setting recurrence. Not required to create a smart campaign that has no recurrence.
RecurrenceEndAt	Datetime	True		The datetime after which no further runs will be automatically scheduled.
RecurrenceIntervalType	String	True		The recurrence interval. Not required to create a smart campaign that has no recurrence = ['Daily', 'Weekly', 'Monthly'].
RecurrenceInterval	Integer	True		The number of interval units between recurrences.
RecurrenceWeekDayOnly	Boolean	True		Only run smart campaign on weekdays. May only be set if intervalType is 'Daily'.
RecurrenceWeekDayMask	String	True		String array of empty or one or more of 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'. May only be set if intervalType is 'Weekly'.
RecurrenceDayOfMonth	Integer	True		The day of the month to recur. Permissible range 1-31. May only be set if intervalType is 'Monthly' and dayOfWeek and weekOfMonth are unset.
RecurrenceDayOfWeek	String	True		The day of the week to recur. May only be set if dayOfMonth is not set, and weekOfMonth is set = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'].
RecurrenceWeekOfMonth	Integer	True		The week of the month to recur. Permissible range 1-4. May only be set if dayOfMonth is not set, and dayOfWeek is set.
FolderId	Integer	False		The Id of the folder.
FolderType	String	False		The type of folder. The allowed values are Folder, Program.

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
EarliestUpdatedAt	Datetime	Exclude smart campaigns prior to this date.
LatestUpdatedAt	Datetime	Exclude smart campaigns after this date.
Folder	String	JSON representation of parent folder, with members 'id', and 'type' which may be 'Folder' or 'Program'.

SmartLists

Query and delete SmartLists for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve all smart lists

SELECT * FROM SmartLists

Retrieve a specific smart list

SELECT * FROM SmartLists WHERE Id=1142

Delete

To delete a smart list you must specify the ID field.

DELETE FROM SmartLists WHERE Id=1142

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	The Id of the smart list.
Name	String	True	True	The name of the smart list.
CreatedAt	Datetime	True		The date and time the smart list was created.
Description	String	True		The description of the Smart list
UpdatedAt	Datetime	True		The date and time the smart list was last updated.
WorkSpace	String	True		The name of the workspace where the smart list is located.
Url	String	True		The url of the smart list.
FolderId	Integer	False		The Id of the folder.
FolderType	String	False		The type of folder. The allowed values are Folder, Program.

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
EarliestUpdatedAt	Datetime	Exclude smart list prior to this date.
LatestUpdatedAt	Datetime	Exclude smart list after this date.
Folder	String	JSON representation of parent folder, with members 'id', and 'type' which may be 'Folder' or 'Program'.
SmartCampaignId	Integer	The Id of the smart campaign
ProgramId	Integer	The Id of the Program

Snippets

Create, update, delete and query Snippets for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of accessible snippets from the target instance, filterable by Status.

SELECT * FROM Snippets

SELECT * FROM Snippets WHERE Status='draft'

Retrieve the snippet record for the given target Id.

SELECT * FROM Snippets WHERE Id=1234

Insert

To create a new Snippet, specify at least the Name, FolderId and FolderType column.

INSERT INTO Snippets (Name,Description,FolderId,FolderType) Values ('My Snippet','Test Snippet insert',31,'Folder')

Update

Any field that is not read-only can be updated.

UPDATE Snippets SET Description='Testing Update', IsArchive='No', Name='Test Update' WHERE Id=8

Delete

To delete a Snippet you must specify the ID field.

DELETE FROM Snippets WHERE Id=1934

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	Id of the asset.
Name	String	False		Name of the asset.
CreatedAt	Datetime	True		Datetime the asset was created.
Description	String	False		Description of the asset.
FolderId	Integer	False		Id of the folder.
FolderType	String	False		Type of folder. The allowed values are Folder, Program.
FolderName	String	False		Name of folder.
Status	String	True	True	Status filter for draft or approved versions.
UpdatedAt	Datetime	True		Datetime the asset was most recently updated.
Url	String	True		Url of the asset in the Marketo UI.
Workspace	String	True		Name of the workspace.
IsArchive	String	False		Archival status of the snippet

StaticLists

Create, update, delete and query Static Lists for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of Static List from the target instance, filterable by name.

SELECT * FROM StaticLists

SELECT * FROM StaticLists WHERE Name = 'test0319'

Retrieve the Static List record for the given target Id.

SELECT * FROM StaticLists WHERE Id = 1192

Retrieve the Static List record for the given folder.

SELECT * FROM StaticLists WHERE Folder = '{id:12,type:Folder}'

Insert

To create a new Static List, specify at least the FolderId,FolderType,Name and FolderName column.

INSERT INTO StaticLists(FolderId,FolderType,Name ,FolderName) VALUES(12,'folder','testvs' ,'ManualList')

Update

Any field that is not read-only can be updated.

UPDATE StaticLists SET Name = 'testupdate' WHERE Id = 1058

Delete

To delete a Email you must specify the ID field.

DELETE FROM StaticLists  WHERE Id = 1058

Columns


Name	Type	ReadOnly	Filterable	Description
Id [KEY]	Integer	True	True	Id of the static list.
Name	String	False	True	Name of the static list.
CreatedAt	Datetime	True		Datetime the static list was created.
UpdatedAt	Datetime	True		Datetime the static list was most recently updated.
FolderId	Integer	False		Id of the folder.
FolderType	String	False		Type of folder.
FolderName	String	False		Type of folder.
ComputedUrl	String	False		Computed urls of static list.
Workspace	String	False		Workspace of static 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
Folder	String	The folder parameter can be used to specify the parent folder under which the query will be performed

Tokens

Create, update, delete, and query Tokens for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve Tokens under a specific ParentResourceType.

SELECT * FROM Tokens WHERE ParentResourceId = 1121 AND ParentResourceType = 'program'

Insert

To create a new query Tokens, specify ParentResourceId, ParentResourceType, Name, Type and Value fields.

 
INSERT INTO Tokens (ParentResourceId, ParentResourceType, Name, Type, Value) VALUES (1111,'program','testname','text','testvalue')

Update

To Update a Token you must specify the ParentResourceId, ParentResourceType, Name, Type and Value fields.

UPDATE Tokens SET value = 'testvalue', Name = 'testname', Type = 'text' WHERE ParentResourceId = 1 AND ParentResourceType = 'folder'

Delete

To Delete a Token you must specify the ParentResourceId, ParentResourceType, Name and Type fields.

DELETE FROM Tokens WHERE ParentResourceId = 1 AND ParentResourceType = 'program' AND Name = 'testname' AND Type = 'text'

Columns


Name	Type	ReadOnly	Filterable	Description
ParentResourceId	Integer	True	True	The Id of the Folder or Program.
ParentResourceType	String	True	True	The type of the token. It could be either Folder or Program. The allowed values are folder, program. The default value is folder.
Name	String	False		The name of the Token.
Type	String	False		The data type of the Token. The supported values are date, number, rich text, score, sfdc campaign and text The allowed values are date, number, rich text, score, sfdc campaign, text.
Value	String	False		The value of the Token.
ComputedURL	String	False		The Computed URL of the Token.

Views

Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

Marketo Connector for CData Sync Views


Name	Description
Activities	Returns a list of activities from after a datetime given by the nextPageToken parameter.
ActivityBulkExports	Returns a list of activity export jobs that were created in the past 7 days.
ActivityTypes	Get activity types for a Marketo Organization
ActivityTypesAttributes	Get activity types attributes for a Marketo Organization
Campaigns	Query Campaigns for a Marketo organization.
ChannelProgressionStatuses	Query ProgressionStatuses of Channels for a Marketo organization.
Channels	Query Channels for a Marketo organization.
DailyErrorStatistics	Gets a list of users and the count of each error type they have encountered in the current day
DailyUsageStatistics	Gets a list of users and the number of calls they have consumed in the current day
EmailCCFields	Query Emails CC Fields for a Marketo organization.
Files	Query Files for a Marketo organization.
LandingPageContentSection	Get section of a landing page content for a Marketo organization.
LandingPageTemplateContent	Query the LandingPageTemplateContent for a Marketo organization.
LeadBulkExports	Returns a list of lead export jobs that were created in the past 7 days.
LeadChanges	Returns a list of Data Value Changes and New Lead activities after a given datetime.
LeadChangesAttributes	Returns a list of Data Value Changes and New Lead activities after a given datetime.
LeadChangesFields	Returns a list of Data Value Changes and New Lead activities after a given datetime.
LeadLists	Query static list membership for one lead.
LeadPartitions	Query Lead Partitions for a Marketo organization.
LeadPrograms	Query program membership for one lead.
Lists	Query Lists for a Marketo organization.
PreviewEmail	View a preview of an email.
ProgramMembersBulkExports	Returns a list of program members export jobs that were created in the past 7 days.
Segmentations	Query segmentations for a Marketo organization.
Segments	Query segments for a Marketo organization.
SmartListRuleFilters	Query SmartLists rule filters
SnippetContent	Query the content of the specific snippet for a Marketo Organization
Tags	Query Tags for a Marketo organization.
ThankYouList	Query Thank you list for the forms
WeeklyErrorStatistics	Gets a list of users and the count of each error type they have encountered in the past 7 days
WeeklyUsageStatistics	Gets a list of users and the number of calls they have consumed in a week

Activities

Returns a list of activities from after a datetime given by the nextPageToken parameter.

Table Specific Information

SELECT

The Sync App uses the Marketo API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the Sync App.

ActivityDate supports the '<,>,>=,<='
ActivityTypeId supports the '=,IN'.
LeadId supports the '=,IN'.
ListId supports the '='

For example, the following query is processed server-side:

Select * from Activities where activitydate > '2022-09-3' and activitydate < '2022-09-5'

Select * from Activities where activitytypeid = 11

Select * from Activities where activitytypeid IN (11,12) and leadid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)

Select * from Activities where ActivityTypeId='1' and  LeadId='123'

Columns


Name	Type	Filterable	Description
Id [KEY]	String	True	Unique id of the activity.
ActivityDate	Datetime	True	Datetime of the activity.
ActivityTypeId	Integer	True	Id of the activity type.
LeadId	Integer	True	Id of the lead associated to the activity.
MarketoGUID	String		Unique id of the activity (128 character string).
PrimaryAttributeValue	String		Value of the primary attribute.
PrimaryAttributeValueId	Integer		Id of the primary attribute field.

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
ListId	String	Id of a static list. If set, will only return activities of members of this static list.

ActivityBulkExports

Returns a list of activity export jobs that were created in the past 7 days.

Columns


Name	Type	Filterable	Description
ExportId [KEY]	String		Unique id of the export job.
ErrorMessage	String		The error message in case of failed status.
CreatedAt	Datetime		The date when the export request was created.
FileSize	Long		The size of file in bytes. This column will have a value only when status is 'Completed'.
FinishedAt	Datetime		The finish time of export job. This column will have a value only when status is 'Completed' or 'Failed'.
Format	String		The format of the file.
NumberOfRecords	Integer		The number of records in the export file. This column will have a value only when the status is 'Completed'.
QueuedAt	String		The queue time of the export job. This column will have a value only when 'Queued' status is reached.
StartedAt	String		The start time of the export job. This column will have a value only when 'Processing' status is reached.
Status	String		The status of the export.

ActivityTypes

Get activity types for a Marketo Organization

Table Specific Information

Select

Note: To specify all filterable columns, you must use the '=' operator.

To retrieve a list of activity types for the target instance:

SELECT * FROM ActivityTypes

Columns


Name	Type	Filterable	Description
Id [KEY]	Integer		The unique, Marketo-assigned identifier of the Activity Types
Name	String		The name of the Activity Types
Description	String		The description of the Activity Types
PrimaryAttributeName	String		The name of the primary attribute
PrimaryAttributeDatatype	String		The data type of the primary attribute

ActivityTypesAttributes

Get activity types attributes for a Marketo Organization

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of activity types attributes for the target instance

SELECT * FROM ActivityTypesAttributes

Columns


Name	Type	Filterable	Description
ActivityTypeId	Integer		The unique, Marketo-assigned identifier of the Activity Types.
ActivityTypeName	String		The name of the Activity Types.
AttributeName	String		The name of the primary attribute
AttributeDataType	String		The description of the Activity Types.

Campaigns

Query Campaigns for a Marketo organization.

Columns


Name	Type	Filterable	Description
Id [KEY]	Integer	True	The unique, Marketo-assigned identifier of the campaign.
Name	String	True	The name of the campaign.
Description	String		The description of the campaign.
Type	String		The campaign type.
ProgramId	Integer		The Id of the program associated with the campaign.
ProgramName	String		The name of the program associated with the campaign.
WorkspaceName	String		The name of the workspace associated with the campaign.
CreatedAt	Datetime		The date and time the campaign was created.
UpdatedAt	Datetime		The date and time the campaign was last updated.
Active	Boolean		Identifies whether the campaign is active.

ChannelProgressionStatuses

Query ProgressionStatuses of Channels for a Marketo organization.

Columns


Name	Type	Filterable	Description
ChannelName	String	True	The name of the channel.
Name	String		Name of the status.
Description	String		Description of the program status.
Hidden	Boolean		Whether the status has been hidden.
Step	Integer		Step number of the status.
Success	Boolean		Whether this status is a success step for program members.

Channels

Query Channels for a Marketo organization.

Columns


Name	Type	Filterable	Description
Id [KEY]	Integer		The unique, Marketo-assigned identifier of the channel.
Name	String	True	The name of the channel.
Description	String		The description of the channel.
ApplicableProgramType	String		The type of program that the channel is used for.
CreatedAt	Datetime		The date and time the channel was created.
UpdatedAt	Datetime		The date and time the channel was last updated.

DailyErrorStatistics

Gets a list of users and the count of each error type they have encountered in the current day

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of users and the count of each error type they have encountered in the current day

SELECT * FROM DailyErrorStatistics

Columns


Name	Type	Filterable	Description
Date	Date		The date when the user encountered error
Total	Integer		The total count of the errors
ErrorCode	String		The error code
ErrorCount	Integer		The error count for the particular error code

DailyUsageStatistics

Gets a list of users and the number of calls they have consumed in the current day

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of users and the number of calls they have consumed in the current day

SELECT * FROM DailyUsageStatistics

Columns


Name	Type	Filterable	Description
Date	Date		The date when the API Calls made
Total	Integer		The total count of the API Calls
UserId	String		The ID of the user
APICount	Integer		The individual count for the user

EmailCCFields

Query Emails CC Fields for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of email cc fields.

SELECT * FROM EmailsCFields

Columns


Name	Type	Filterable	Description
AttributeId	String		The attribute identifier
ObjectName	String		Object Name; Lead or Company
DisplayName	String		The display name
ApiName	String		The API name

Files

Query Files for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of Files for the target instance

SELECT * FROM Files

Retrieve the File for the given Id.

SELECT * FROM Files WHERE Id = '2012'

Columns


Name	Type	Filterable	Description
Id [KEY]	Integer	True	Id of the file.
Name	String	True	Name of the file.
CreatedAt	Datetime		Datetime when the file was created
Description	String		Description of the file
FolderId	Integer	True	Id of the folder
FolderName	String		The Name of the folder
FolderType	String	True	The Type of folder The allowed values are Folder, Program.
MimeType	String		MIME type of the file
Size	Integer		Size of the file in bytes
UpdatedAt	Datetime		Datetime when the file was most recently updated
Url	String		Publically accessible URL of the file
FileName	String		The filename for the file to insert.
File	String		Multipart file. Content of the file
InsertOnly	Boolean		Whether the calls hould fail if there is already an existing file with the same name

LandingPageContentSection

Get section of a landing page content for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of landing page content section for a given landing Page Id.

SELECT * FROM LandingPageContentSection WHERE LandingPageId = 1193

Columns


Name	Type	Filterable	Description
Id [KEY]	String		Id of the content section, may be a string or an int.
Content	String		Content of the section. Expected values vary based on type. Image: An image URL. RichText: HTML Content.
ContentType	String		Content of the section. Expected values vary based on type. Image: An image URL. RichText: HTML Content.
ContentUrl	String		Content of the section. Expected values vary based on type. Image: An image URL. RichText: HTML Content.
FollowupType	String		Follow-up behavior of a form. Only available for form-type content sections. Defaults to form defined behavior. The allowed values are url, lp, formDefined.
FollowupValue	String		Where to follow-up on form submission. When followupType is lp, accepts the integer id of a landing page. For url, it accepts a url string.
FormattingOptionsZIndex	Integer		The zindex of the content
FormattingOptionsLeft	String		The left margin of the content
FormattingOptionsTop	String		The top margin of the content
Index	Integer		Index of the content section. Index orients the elements from lowest to highest.
Type	String		Type of content section. The allowed values are Image, SocialButton, Form, DynamicContent, Rectangle, Snippet, RichText, HTML, Video, Poll, ReferralOffer, Sweepstakes.

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
LandingPageId	Integer	Id of the LandingPage.
Status	String	Status filter for draft or approved versions. The allowed values are draft, approved.

LandingPageTemplateContent

Query the LandingPageTemplateContent for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of landing page template content for a given landing Page template Id.

SELECT * FROM LandingPageTemplateContent WHERE LandingPageTemplateId = 1

Columns


Name	Type	Filterable	Description
Id	Integer	True	Unique integer id of the template.
Content	String		HTML content of the landing page template.
EnableMunchkin	Boolean		Whether to enable munchkin on the derived pages. Defaults to true.
Status	String	True	Status filter for draft or approved versions The allowed values are approved, draft.
TemplateType	String		Type of template to create. Defaults to freeForm. The allowed values are guided, freeForm.

LeadBulkExports

Returns a list of lead export jobs that were created in the past 7 days.

Columns


Name	Type	Filterable	Description
ExportId [KEY]	String		Unique id of the export job.
ErrorMessage	String		The error message in case of failed status.
CreatedAt	Datetime		The date when the export request was created.
FileSize	Integer		The size of file in bytes. This column will have a value only when status is 'Completed'.
FinishedAt	Datetime		The finish time of export job. This column will have a value only when status is 'Completed' or 'Failed'.
Format	String		The format of the file.
NumberOfRecords	Integer		The number of records in the export file. This will have a value only when the status is 'Completed'.
QueuedAt	String		The queue time of the export job. This column will have a value only when 'Queued' status is reached.
StartedAt	String		The start time of the export job. This column will have a value only when 'Processing' status is reached.
Status	String		The status of the export.

LeadChanges

Returns a list of Data Value Changes and New Lead activities after a given datetime.

Columns


Name	Type	Filterable	Description
Id	Integer		Integer id of the activity
LeadId	Integer		Id of the lead associated to the activity
ActivityDate	Datetime		Datetime of the activity.
ActivityTypeId	Integer		Id of the activity type.
CampaignId	Integer		Id of the Campaign.
MarketoGUID	String		Unique id of the activity (128 character string).

LeadChangesAttributes

Returns a list of Data Value Changes and New Lead activities after a given datetime.

Columns


Name	Type	Filterable	Description
Id	Integer		Integer id of the activity
AttributeAPIName	String		API Name of the attribute
AttributeName	String		Name of the attribute
AttributeValue	String		Value of the attribute

LeadChangesFields

Returns a list of Data Value Changes and New Lead activities after a given datetime.

Columns


Name	Type	Filterable	Description
Id	Integer		Integer id of the activity
LeadChangeFieldId	Integer		Unique integer id of the change record
LeadChangeFieldName	String		Name of the field which was changed
LeadChangeFieldNewValue	String		New value after the change
LeadChangeFieldOldValue	String		Old value before the change

LeadLists

Query static list membership for one lead.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of lists for the specific lead id.

SELECT * FROM LeadLists WHERE LeadId = 1021579

Columns


Name	Type	Filterable	Description
ListId	Integer		The Id of the Program Member
CreatedAt	Datetime		Indicates this program was responsible for creating the lead record
UpdatedAt	Datetime		The likelihood of the attendance at the individual level

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

LeadPartitions

Query Lead Partitions for a Marketo organization.

Columns


Name	Type	Filterable	Description
Id [KEY]	Integer		The unique, Marketo-assigned identifier of the lead partition.
Name	String	True	The name of the partition.
Description	String		The description of the partition.

LeadPrograms

Query program membership for one lead.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of programs for the specific lead id.

SELECT * FROM LeadPrograms WHERE LeadId = 1021579

Columns


Name	Type	Filterable	Description
Id	Integer		Unique integer id of a program record.
ProgressionStatus	String		Program status of the lead in the parent program.
ProgressionStatusType	String		Program status Type of the lead in the parent program.
IsExhausted	Boolean		Whether the lead is currently exhausted in the stream, if applicable.
AcquiredBy	Boolean		Whether the lead was acquired by the parent program.
ReachedSuccess	Boolean		Whether the lead is in a success-status in the parent program.
MembershipDate	Datetime		Date the lead first became a member of the program.
UpdatedAt	Datetime		Datetime when the program was most recently updated.

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
LeadId	Integer	The Marketo lead id

Lists

Query Lists for a Marketo organization.

Columns


Name	Type	Filterable	Description
Id [KEY]	Integer	True	The unique, Marketo-assigned identifier of the list.
Name	String	True	The name of the list.
Description	String		The description of the list.
ProgramName	String		The name of the program associated with the list.
WorkspaceName	String		The name of the workspace associated with the list.
CreatedAt	Datetime		The date and time the list was created.
UpdatedAt	Datetime		The date and time the list was last updated.

PreviewEmail

View a preview of an email.

Columns


Name	Type	Filterable	Description
Id [KEY]	Integer	True	The id of the email asset you wish to preview.
Status	String	True	Accepts the values 'draft' or 'approved' which will default to the approved version, if approved, draft if unapproved.
Content	String		The content of the email.

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
Type	String	Accepts 'Text' or 'HTML' and defaults to HTML.
LeadId	Integer	Accepts the integer id of a lead. When set, previews the email as though it were received by the designated lead

ProgramMembersBulkExports

Returns a list of program members export jobs that were created in the past 7 days.

Columns


Name	Type	Filterable	Description
ExportId [KEY]	String		Unique id of the export job.
ErrorMessage	String		The error message in case of failed status.
CreatedAt	Datetime		The date when the export request was created.
FileSize	Integer		The size of file in bytes. Thiscolumn will have a value only when status is 'Completed'.
FinishedAt	Datetime		The finish time of export job. This column will have a value only when status is 'Completed' or 'Failed'.
Format	String		The format of the file.
NumberOfRecords	Integer		The number of records in the export file. This column will have a value only when the status is 'Completed'.
QueuedAt	String		The queue time of the export job. This column will have a value only when 'Queued' status is reached.
StartedAt	String		The start time of the export job. This column will have a value only when 'Processing' status is reached.
Status	String		The status of the export.

Segmentations

Query segmentations for a Marketo organization.

Columns


Name	Type	Filterable	Description
Id	Integer		Id of the asset.
Name	String		Name of the asset.
CreatedAt	Datetime		Datetime the asset was created.
Description	String		Description of the asset.
FolderId	Integer		Id of the folder.
FolderType	String		Type of folder. The allowed values are Folder, Program.
Status	String	True	Status filter for draft or approved versions. The allowed values are approved, draft.
UpdatedAt	Datetime		Datetime the asset was most recently updated.
Url	String		Url of the asset in the Marketo UI.
Workspace	String		Name of the workspace.

Segments

Query segments for a Marketo organization.

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list segments for the given segmentation id.

SELECT * FROM Segments WHERE SegmentationId = 1012

Columns


Name	Type	Filterable	Description
Id	Integer		Id of the asset.
Name	String		Name of the asset.
CreatedAt	Datetime		Datetime the asset was created.
Description	String		Description of the asset.
SegmentationId	Integer	True	Id of the Segmentation.
Status	String	True	Status filter for draft or approved versions. The allowed values are approved, draft.
UpdatedAt	Datetime		Datetime the asset was most recently updated.
Url	String		Url of the asset in the Marketo UI.

SmartListRuleFilters

Query SmartLists rule filters

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator. Atleast one of SmartListId, SmartCampaignId or ProgramId is required to get the Rule Filters.

Retrieve rule filters for Smart Lists by SmartListId

SELECT * FROM SmartListRuleFilters WHERE SmartListId = 1143

SELECT * FROM SmartListRuleFilters WHERE SmartListId IN (SELECT Id FROM SmartLists)

Retrieve rule filters for Smart Lists by SmartCampaignId

SELECT * FROM SmartListRuleFilters WHERE SmartCampaignId = 1682

SELECT * FROM SmartListRuleFilters WHERE SmartCampaignId IN (SELECT Id FROM SmartCampaigns)

Retrieve rule filters for Smart Lists by ProgramId

SELECT * FROM SmartListRuleFilters WHERE ProgramId = 1089

SELECT * FROM SmartListRuleFilters WHERE ProgramId IN (SELECT Id FROM SmartCampaigns)

Columns


Name	Type	Filterable	Description
Id [KEY]	Integer		The Id of the smart list rule filter.
Name	String		The name of the smart list rule filter.
Operator	String		The operator used in the filter.
RuleType	String		The type of the rule.
RuleTypeId	Integer		The Id of the rule type.
Conditions	String		The Rule filter conditions.
FilterMatchType	String		The rule filter match type
FilterCustomRuleLogic	String		The rule filter custom logic

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
SmartCampaignId	Integer	The Id of the smart campaign
ProgramId	Integer	The Id of the Program
SmartListId	Integer	The Smart List Id

SnippetContent

Query the content of the specific snippet for a Marketo Organization

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieve a list of snippetcontent for a given snippetId.

SELECT * FROM SnippetContent WHERE SnippetId = 3

Columns


Name	Type	Filterable	Description
Type	String		Type of the content
Content	String		The content of the snippet

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
SnippetId	Integer	The Id of the Snippet

ThankYouList

Query Thank you list for the forms

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of Thank you pages for the forms.

SELECT * FROM ThankYouList

Columns


Name	Type	Filterable	Description
FormId [KEY]	Integer	True	Id of the asset.
FollowupType	String	True	Name of the asset.
FollowupValue	String		Label text of the button.
Default	Boolean		Location in pixels of the button relative to the left of the form.

WeeklyErrorStatistics

Gets a list of users and the count of each error type they have encountered in the past 7 days

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of users and the count of each error type they have encountered in the past 7 days

SELECT * FROM WeeklyErrorStatistics

Columns


Name	Type	Filterable	Description
Date	Date		The date when the user encountered error
Total	Integer		The total count of the errors
ErrorCode	String		The error code
ErrorCount	Integer		The error count for the particular error code

WeeklyUsageStatistics

Gets a list of users and the number of calls they have consumed in a week

Table Specific Information

Select

Note: All filterable columns must be specified using the '=' operator.

Retrieves a list of users and the number of calls they have consumed in the paast 7 days

SELECT * FROM WeeklyUsageStatistics

Columns


Name	Type	Filterable	Description
Date	Date		The date when the API Calls made
Total	Integer		The total count of the API Calls
UserId	String		The ID of the user
APICount	Integer		The individual count for the user

Connection String Options

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

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

REST

Property	Description
RESTEndpoint	The Marketo REST API Endpoint.

SOAP

Property	Description
UserId	The Marketo SOAP API User Id.
EncryptionKey	The Marketo SOAP API Encryption Key.
SOAPEndpoint	The Marketo SOAP API Endpoint.

OAuth

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.

SSL

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

Firewall

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

Proxy

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Logging

Property	Description
LogModules	Core modules to be included in the log file.

Schema

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

Miscellaneous

Property	Description
JobPollingInterval	Specifies the polling interval (in seconds) when checking the status of a bulk API job.
MaxRows	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	These hidden properties are used only in specific use cases.
Pagesize	The maximum number of results to return per page from Marketo.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UseBulkAPI	Specifies whether to use the Marketo Bulk API.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

REST

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

Property	Description
RESTEndpoint	The Marketo REST API Endpoint.

RESTEndpoint

The Marketo REST API Endpoint.

Remarks

The URL of the REST Web service endpoint is provided by Marketo on the Admin page of the Marketo website.

SOAP

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

Property	Description
UserId	The Marketo SOAP API User Id.
EncryptionKey	The Marketo SOAP API Encryption Key.
SOAPEndpoint	The Marketo SOAP API Endpoint.

UserId

The Marketo SOAP API User Id.

Remarks

The User Id is provided by Marketo and is used to authenticate to the Marketo SOAP Web service.

EncryptionKey

The Marketo SOAP API Encryption Key.

Remarks

The EncryptionKey is generated on the Admin page of the Marketo website and is used to authenticate to the Marketo SOAP Web service.

SOAPEndpoint

The Marketo SOAP API Endpoint.

Remarks

The URL of the SOAP Web service endpoint is provided by Marketo on the Admin page of the Marketo website.

OAuth

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

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.

OAuthClientId

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

Remarks

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

OAuthClientSecret

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

Remarks

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

SSL

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

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

SSLServerCert

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

Remarks

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

This property can take the following forms:


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

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

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

Firewall

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

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

FirewallType

The protocol used by a proxy-based firewall.

Remarks

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


Type	Default Port	Description
TUNNEL	80	When this is set, the Sync App opens a connection to Marketo and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the Sync App 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 Sync App 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

The name or IP address of a proxy-based firewall.

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 Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

The TCP port for a proxy-based firewall.

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

The user name to use to authenticate with a proxy-based firewall.

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

A password used to authenticate to a proxy-based firewall.

Remarks

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

Proxy

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

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

ProxyAutoDetect

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Remarks

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

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

ProxyServer

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App 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 Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

The TCP port the ProxyServer proxy is running on.

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

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

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

Note that the Sync App 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 Sync App performs HTTP BASIC authentication.
DIGEST: The Sync App performs HTTP DIGEST authentication.
NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The Sync App 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

A user name to be used to authenticate to the ProxyServer proxy.

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

A password to be used to authenticate to the ProxyServer proxy.

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 Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

The SSL type to use when connecting to the ProxyServer proxy.

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

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

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

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

Logging

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

Property	Description
LogModules	Core modules to be included in the log file.

LogModules

Core modules to be included in the log file.

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.

Schema

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

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

Location

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

Remarks

The path to a directory which contains the schema files for the Sync App (.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.

Note: Given that this Sync App supports multiple schemas, the structure for Marketo custom schema files is as follows:

Each schema is given a folder corresponding to that schema name.
These schema folders are contained in a parent folder.
The parent folder should be set as the Location, not an individual schema's folder.

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

Schema

The type of schema to use.

Remarks

The schemas available are REST (to use Marketo's REST API) and SOAP (to use Marketo's SOAP API).

BrowsableSchemas

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

Remarks

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

Tables

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

Remarks

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

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

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

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

Views

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

Remarks

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

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

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

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

Miscellaneous

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

Property	Description
JobPollingInterval	Specifies the polling interval (in seconds) when checking the status of a bulk API job.
MaxRows	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	These hidden properties are used only in specific use cases.
Pagesize	The maximum number of results to return per page from Marketo.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UseBulkAPI	Specifies whether to use the Marketo Bulk API.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

JobPollingInterval

Specifies the polling interval (in seconds) when checking the status of a bulk API job.

Remarks

This property is used to specify the polling interval (in seconds) to identify when a bulk API job has completed. The Sync App will wait JobPollingInterval seconds between calls to check a bulk API job status. Once the job is identified as 'Completed', the Sync App will download and parse the generated file returning the results of the specified query.

This property can be set to 0 to just create and enqueue a job in which case the Job Id will be returned in the result set. The job status can then be checked using stored procedures.

Note: This property is only applicable when UseBulkAPI is set to True. See the UseBulkAPI page for more information about using the Bulk API.

MaxRows

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.

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

These hidden properties are used only in specific use cases.

Remarks

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

Specify multiple properties in a semicolon-separated list.

Integration and Formatting


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

Pagesize

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

Remarks

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

PseudoColumns

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

Remarks

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

Timeout

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

Remarks

If the Timeout property is set to 0, operations do not time out: They run until they complete successfully or encounter an error condition.

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

UseBulkAPI

Specifies whether to use the Marketo Bulk API.

Remarks

When set to 'True', the Marketo Bulk API will be used to extract or import data, where applicable. The Bulk API in an interface that allows you to retrieve or import large sets of data using delimited (CSV, TSV, or SSV) files. Currently the only tables that support the Bulk API are: Leads (extract and import) and Activities (extract). For any tables that do not support the Bulk API, this property will be ignored.

The Bulk API causes all the data to be retrieved in a single request and requires the data to be accumulated on the server side prior to sending. Therefore requesting a large amount of data using the Bulk API may be advantageous over using the REST API and you may see performance improvements. Additionally the Bulk API requires less API requests to be made (which helps preserve your API calls and staying within the API restrictions enforced by Marketo).

To use the Bulk API to extract records, a job must be created and enqueued. Once enqueued, Marketo will begin processing the job to retrieve the requested data and generate the delimited file. The status of the job can be polled to determine the current status and whether the file is available to be downloaded. Once the status shows that the job is complete and the file is ready, the data can then be downloaded.

When UseBulkAPI is set to True and JobPollingInterval is set to a value greater than 0, the Sync App will perform all the previous mentioned steps for you when executing a SELECT query on a Leads or Activities table. This will create and enqueue a job with the specified columns and filters. Note that a filter is required when exporting bulk data. For the Activities tables, an ActivityDate range must be specified. For the Leads table, a CreatedAt or UpdatedAt range may be specified or a Static or Smart list. The Sync App will poll the job status to identify when the job has completed, waiting JobPollingInterval seconds in between calls. Once the job is complete, the Sync App will download the delimited file that was created, parse it, and return the results for the specified query.

Note that job status calls count against your API call limit and thus it is suggested to space out your status requests based on the amount of data you are requesting. The job status polling interval is configurable via JobPollingInterval. Marketo will only update the status every 60 seconds and thus it is suggested that your polling interval be larger than 60 seconds. When expecting large datasets, it may be best to increase the polling interval to a value greater than 5 minutes to minimize API calls. It is possible that it may take a while for the job to be processed and thus it may seem like the query is exhibiting a hanging behavior when it is actually just waiting for the job to complete.

In the case that you want to issue your own job status polling requests, you can set JobPollingInterval to 0. This will just create and enqueue the job for you when you execute a SELECT query on a Leads or Activities table, returning the JobId in the result set.

Once a job has been enqueued, the status of the job can be polled by calling the GetExportJobStatus stored procedure.

The JobStatus value will be 'Complete' signaling that the job has finished processing and is ready to be downloaded. To finish executing your initial SELECT query, add the JobId filter to the WHERE clause of the initial SELECT statement. This query will download the file for the specified JobId and parse the result set.

Logic/Code Example (JobPollingInterval = 0):

SELECT JobId, Company, FirstName AS fn, LastName AS ln FROM Leads WHERE CreatedAt>='10/01/2017' AND CreatedAt<'10/31/2017'
# Retrieve the JobId value from the ResultSet (e.g. c4ebf745-b0e3-4bb8-bfc9-bd8472a28d35). Only one row is returned and JobId will be the only relevant value returned.

loop(desired time interval) {
  EXEC GetExportJobStatus @JobId='c4ebf745-b0e3-4bb8-bfc9-bd8472a28d35', @Type='Leads'
  if (JobStatus == 'Completed') break;
}

SELECT Company, FirstName AS fn, LastName AS ln FROM Leads WHERE CreatedAt>='10/01/2016' AND CreatedAt<'10/31/2016' AND JobId='c4ebf745-b0e3-4bb8-bfc9-bd8472a28d35'

Note: this property is only applicable when using the REST API.

UserDefinedViews

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

Remarks

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

Marketo Connector for CData Sync

Marketo Connector for CData Sync

Overview

Establishing a Connection

Connecting to the Marketo REST API

Creating a Custom Service

Obtaining OAuth Credentials

Authenticating to the REST API

Connecting to the Marketo SOAP API

Advanced Features

User Defined Views

SSL Configuration

Firewall and Proxy

Query Processing

Logging

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

SOAP Data Model

Tables

Views

Tables

Marketo Connector for CData Sync Tables

Leads

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

Programs

Table Specific Information

Select

Update

Columns

Pseudo-Columns

Views

Marketo Connector for CData Sync Views

Activities

Table Specific Information

Select

Columns

Pseudo-Columns

Activities_AddToList

Table Specific Information

Select

Columns

Pseudo-Columns

Activities_AddToSFDCCampaign

Table Specific Information

Select

Columns

Pseudo-Columns

Activities_ChangeDataValue

Table Specific Information

Select

Columns

Pseudo-Columns

Activities_ChangeOwner

Table Specific Information

Select

Columns

Pseudo-Columns

Activities_ChangeRevenueStage

Columns

Pseudo-Columns

Activities_ChangeScore

Table Specific Information

Select

Columns

Pseudo-Columns

Activities_ChangeStatusInProgression

Table Specific Information

Select

Columns

Pseudo-Columns