Skip to Content

Marketo Connection Details

Introduction

Connector Version

This documentation is based on version 21.0.8662 of the connector.

Get Started

Marketo Version Support

The connector enables SQL92 access to the entities available through version 1 of the REST API and version 2.6 of the SOAP API.

Establish a Connection

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

Connect to the Marketo REST API

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

Create a Custom Service

To create a custom service, follow the procedure below:

  1. Navigate to the admin area of your Marketo application.
  2. Click Users & Roles in the Security section.
  3. Select the Roles tab and click New Role to create a new Role.
  4. Enter a Role Name and select the permissions for the Role. The Access API permissions are specific to the REST API.
  5. Now that an API Role is created, select the Users tab and click Invite New User.
  6. 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.
  7. Now that a new user has been created, a new service will need to be created. Click the LaunchPoint option (Admin -> Integration -> LaunchPoint).
  8. Click New Service.
  9. Select the Custom service type and enter a display name and description.
  10. Select the user you created.
Obtain 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.

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

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

Important Notes

Configuration Files and Their Paths

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

Advanced Features

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

User Defined Views

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

SSL Configuration

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

Proxy

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

User Defined Views

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

There are two ways to create user defined views:

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

Define Views Using a Configuration File

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

{
    "MyView": {
        "query": "SELECT * FROM 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"

Schema for User Defined Views

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

Work with User Defined Views

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

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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

Resulting in the effective query to the source:

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

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

SSL Configuration

Customize the SSL Configuration

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

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

SOAP Data Model

The Marketo connector 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 connector 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 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='test@lead1.com' OR Email='test@lead4.com'
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 ('john@mark.com', '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. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Marketo Connector 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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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.

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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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='test@server.com' OR Email='testlead@server.com'
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.

Tags

Query Tags for a Marketo organization.

Table Specific Information
Select

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

SELECT * FROM Tags WHERE Type='TestTag' AND Value='Value1' OR Value='Value2'
Columns
Name Type Description
Type [KEY] String The tag type.
Value [KEY] String The tag value.

REST Data Model

The Marketo connector 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 connector 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 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, 'insert@test.com')
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 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__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 ('john@mark.com', '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 = 'john@mark.com'

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

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', ' Software Inc Opportunity', '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
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='sales@cdata.com'
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 ('sales@cdata.com', 'sales@cdata.com', 'Sales', 'Person')
Update

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

UPDATE SalesPersons SET Phone='919-928-5214', Title='Technical Sales', Email='sales@cdata.com' WHERE ExternalSalesPersonId='sales@cdata.com'
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=='sales@cdata.com'
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=' 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. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

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

Columns
Name Type Filterable Description
Id [KEY] Integer True Unique ID of the activity.
ActivityDate String 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 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.

ActivityTypes

Get activity types for a Marketo Organization

Table Specific Information
Select

Note

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

Retrieves 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

Tags

Query Tags for a Marketo organization.

Columns
Name Type Filterable Description
TagType [KEY] String True The name/type of the tag.
ApplicableProgramTypes String The types of program that the tag is used for.
Required Boolean The date and time the channel was created.
AllowableValues String The date and time the channel was last updated.

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

Stored Procedures

Stored procedures are available to complement the data available from the REST Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Marketo Connector Stored Procedures

Name Description
AddLandingPageContentSection Adds a content section to the target landing page.
AddLeadsToList Adds leads to a list in Marketo.
AssociateLead Associates a known Marketo lead record to a munchkin cookie and its associated web activity history
CancelExportJob Cancels an export job.
CreateEmailTemplate Creates a new email template.
CreateExportJob Create export job for search criteria defined via filter parameter. Returns the 'JobId' which is passed as a parameter in subsequent calls to Bulk Export Activities. Use EnqueueExportJob to queue the export job for processing. Use GetExportJobStatus to retrieve status of export job.
CreateFile Creates a new file from the included payload.
DeleteUpdatePageContentSection Deletes the section of the landing page content in Marketo.
EnqueueExportJob Places an export job in queue and starts the job when computing resources become available.
GetExportJobFile Returns the file content of an export job. The export job must be in 'Completed' state. Use GetExportJobStatus to get the status of an export job.
GetExportJobStatus Returns the status of an export job. Job status is available for 30 days after Completed or Failed status was reached.
GetImportCustomObjectsFailures Downloads and saves the import custom objects failures file from Marketo.
GetImportCustomObjectsStatus Gets the status of an import custom objects from file operation for Marketo.
GetImportCustomObjectsWarnings Downloads and saves the import custom objects warnings file from Marketo.
GetImportLeadsFailures Downloads and saves the import leads failures file from Marketo.
GetImportLeadsStatus Gets the status of an import leads from file operation for Marketo.
GetImportLeadsWarnings Downloads and saves the import leads warnings file from Marketo.
GetImportProgramMembersFailures Downloads and saves the import program members failures file from Marketo.
GetImportProgramMembersStatus Gets the status of an import program members from file operation for Marketo.
GetImportProgramMembersWarnings Downloads and saves the import program members warnings file from Marketo.
GetOAuthAccessToken Gets an authentication token from Marketo.
ImportCustomObjectsFromFile Imports custom objects from a file into Marketo.
ImportLeadsFromFile Imports leads from a file into Marketo.
ImportProgramMembersFromFile Imports program members from a file into Marketo.
ListMembersOfList Determines whether leads are contained within a list in Marketo.
MergeLeads Merges two or more known lead records into a single lead record.
RefreshOAuthAccessToken Gets an authentication token from Marketo.
RemoveLeadsFromList Removes leads from a list in Marketo.
ScheduleCampaign Remotely schedules a batch campaign to run at a given time.
UpdateEmailContent Updates the content of an email
UpdateEmailFullContent Updates the content of an email
UpdateEmailTemplateContent Updates the content of the given email template.
UpdateFile Replaces the current content of the file with the included payload
UpdateLandingPageContentSection Add or update the section of the landing page content in Marketo.
UpdateLandingPageTemplateContent Updates the content for the target landing page template.
UpdateLeadProgramStatus Changes the program status of a list of leads in a target program. Only existing members of the program may have their status changed with this API.

AddLandingPageContentSection

Adds a content section to the target landing page.

EXECUTE Example:

EXECUTE AddLandingPageContentSection LandingPageId = '1003', ContentId = '1' Type = 'HTML'
Input
Name Type Required Description
LandingPageId String True ID of the landing page.
ContentId String True ID of the content section. Also the HTML ID of the section.
Type String True Type of content section. The allowed values are Image, Form, Rectangle, Snippet, RichText, HTML.
BackgroundColor String False background-color property of the HTML section.
BorderColor String False Border-color property of the HTML section.
BorderStyle String False Border-style property of the HTML section.
BorderWidth String False Border-width property of the HTML section.
Height String False Height property of the HTML section.
HideDesktop String False Hide the section when displayed on a desktop browser.
HideMobile String False Hide the section when displayed on a mobile browser. Default false.
ImageOpenNewWindow String False Image open new window.
Index String False Index of the content section. Determines the order of the section in the landing page.
Left String False Left property of the HTML section.
LinkUrl String False URL parameter of a link type section.
Opacity String False Opacity property of the HTML section.
Top String False Top property of the HTML section.
Value String False Type of content section.
Width String False Width property of the HTML section.
ZIndex String False Z-index property of the HTML section.
Result Set Columns
Name Type Description
Id String ID of the content section.

AddLeadsToList

Adds leads to a list in Marketo.

EXECUTE Example:

EXECUTE AddLeadsToList ListId='1001', LeadId='10,13,20'
Input
Name Type Required Description
ListId String True The ID of the list to add leads to.
LeadId String True A comma-separated list of lead Ids to be added to the list specified by ListId.
Result Set Columns
Name Type Description
LeadId String The LeadId that was attempted to be added to the list.
Status String The status of the lead being added to the list. Applicable values: added and skipped.
ReasonCode String The reason code pertaining to why a lead was skipped (status will return skipped).
ReasonMessage String The reason message pertaining to why a lead was skipped (status will return skipped).

AssociateLead

Associates a known Marketo lead record to a munchkin cookie and its associated web activity history

EXECUTE Example:

EXECUTE AssociateLead LeadId = 1021579, Cookie = 'id:119-IEY-862%26token:_mch-amazonaws.com-1541561364144-71837'
Input
Name Type Required Description
LeadId Integer True ID of the Lead to associate
Cookie String True The cookie value to associate
Result Set Columns
Name Type Description
Status String The status of the lead being associated

CancelExportJob

Cancels an export job.

Input
Name Type Required Description
JobId String True The ID of the export job.
Type String True The object type of the BulkExtract. Available values are : Activities, Leads, ProgramMembers The allowed values are Activities, Leads, ProgramMembers.
Result Set Columns
Name Type Description
JobId String The ID of the export job.
JobStatus String The status of the export process.
Format String The format of the export job.
CreatedAt String The date when the export job was created.
StartedAt String The date when the export job was started.
QueuedAt String The date when the export job was queued.

CreateEmailTemplate

Creates a new email template.

EXECUTE Example:

EXECUTE CreateEmailTemplate Name = 'Marketo.html', File = 'C:\Users\Dell\Desktop\Test_Logs\Marketo.html', Description = 'Test Create Email Template', FolderId = 45, FolderType = 'Folder'
Input
Name Type Required Description
File String False Multipart File. HTML content for template.
FolderId Integer False The ID of the folder
FolderType String False The type of the folder
Name String False The name of the Email Template. Must be unique under the parent folder.
Description String False The description of the email template
Result Set Columns
Name Type Description
Id String The ID of the created email template

CreateExportJob

Create export job for search criteria defined via filter parameter. Returns the 'JobId' which is passed as a parameter in subsequent calls to Bulk Export Activities. Use EnqueueExportJob to queue the export job for processing. Use GetExportJobStatus to retrieve status of export job.

Input
Name Type Required Description
Fields String False A comma separated list of Marketo fields.
Type String True The object type of the BulkExtract. Available values are : Activities, Leads, ProgramMembers. The allowed values are Activities, Leads, ProgramMembers.
Format String False Format of file as given in the request. Available values are: CSV, TSV, SSV The allowed values are CSV, TSV, SSV. The default value is CSV.
\*ColumnHeaderName String False The header column name for the field. For example : If you want to set the header name of two fields called 'FirstName' and 'LastName', you just need to set the value of 'FirstNameColumnHeaderName' and 'LastNameColumnHeaderName'.
CreatedAtStartAt String False The start date of the Marketo Object created date
CreatedAtEndAt String False The end date of the Marketo Object created date
UpdatedAtStartAt String False The start date of the Marketo Object updated date
UpdatedAtEndAt String False The end date of the Marketo Object updated date
ActivityTypeIds String False List of activity type ids to filter on. Available only when the Type is set to 'Activities'.
StaticListName String False The name of a static list you want to use as a filter. Available only when the Type is set to 'Leads'
StaticListId String False The ID of a static list you want to use as a filter. Available only when the Type is set to 'Leads'
SmartListName String False The name of a smart list you want to use as a filter. Available only when the Type is set to 'Leads'
SmartListId String False The ID of a smart list you want to use as a filter. Available only when the Type is set to 'Leads'
ProgramId String False The ID of the program you want to use as a filter. Available only when the Type is set to 'ProgramMembers'
Result Set Columns
Name Type Description
JobId String The ID of the export job.
Format String The format of the export job.
CreatedAt String The date when the export job was created.
JobStatus String The status of the export process.

CreateFile

Creates a new file from the included payload.

EXECUTE Example:

EXECUTE CreateFile File = 'C:\Users\Dell\Desktop\Test_Logs\Marketo.html', Description = 'Test Create File', Name = 'john@abc.com', FolderId = 1032, FolderType = 'folder'
Input
Name Type Required Description
File String True Multipart File. Content of the file.
FolderId Integer True The ID of the folder
FolderType String True The type of the folder
InsertOnly Boolean False Indicates whether the call should fail if there is already an existing file with the same name
Name String True The name of the file
Description String False The description of the asset
Result Set Columns
Name Type Description
Id String The ID of the created file

DeleteUpdatePageContentSection

Deletes the section of the landing page content in Marketo.

Input
Name Type Required Description
LandingPageId String True ID of the landing page.
ContentId String True ID of the content section. Also the HTML ID of the section.
Result Set Columns
Name Type Description
Id String ID of the landing page.

EnqueueExportJob

Places an export job in queue and starts the job when computing resources become available.

Input
Name Type Required Description
JobId String True The ID of the export job.
Type String True The object type of the BulkExtract. Available values are : Activities, Leads, ProgramMembers. The allowed values are Activities, Leads, ProgramMembers.
Result Set Columns
Name Type Description
JobId String The ID of the export job.
JobStatus String The status of the export process.
Format String The format of the export job.
CreatedAt String The date when the export job was created.
QueuedAt String The date when the export job was queued.

GetExportJobFile

Returns the file content of an export job. The export job must be in 'Completed' state. Use GetExportJobStatus to get the status of an export job.

Input
Name Type Required Description
JobId String True The ID of the export job.
Type String True The type of the BulkExtract. Available values are : Activities, Leads, ProgramMembers The allowed values are Activities, Leads, ProgramMembers.
File String False The file path where you want to store the file.
Result Set Columns
Name Type Description
ResponseData String The content of the file. Returns data only if File is not specified.

GetExportJobStatus

Returns the status of an export job. Job status is available for 30 days after Completed or Failed status was reached.

Input
Name Type Required Description
JobId String True The ID of the export job.
Type String True The object type of the BulkExtract. Available values are : Activities, Leads, ProgramMembers. The allowed values are Activities, Leads, ProgramMembers.
Result Set Columns
Name Type Description
JobId String The ID of the export job.
Format String The format of the export job.
CreatedAt String The date when the export job was created.
JobStatus String The status of the export process. Applicable values: Created, Queued, Processing, Cancelled, Completed, Failed.
QueuedAt String The date when the export job was queued.
StartedAt String The date when the export job was started.
FinishedAt String The date when the export job was finished.
NumberOfRecords String The number of records contained within the generated file.
FileSize String The size of the generated file.

GetImportCustomObjectsFailures

Downloads and saves the import custom objects failures file from Marketo.

Input
Name Type Required Description
BatchId String True The batch ID of the import custom objects operation to retrieve the failures file for.
TableName String True The name of the custom object table. Ex: CustomObject_test
FilePath String False The file and path where the downloaded failures file will be saved, i.e. 'C:\temp\myfile.csv'. Note the file contains CSV (comma separated value) data.
Result Set Columns
Name Type Description
FileData String The raw file data. This is only populated if the 'File' input was not set to write the data to a file. Note the file contains CSV (comma separated value) data.

GetImportCustomObjectsStatus

Gets the status of an import custom objects from file operation for Marketo.

Input
Name Type Required Description
BatchId String True The batch ID returned from an import custom objects from file operation.
TableName String True The name of the custom object table. Ex: CustomObject_test
Result Set Columns
Name Type Description
Status String The status of the import process.
NumOfObjectsProcessed String The number of custom objects processed.
NumOfRowsFailed String The number of rows that failed.
NumOfRowsWithWarning String The number of rows that contain a warning.
ImportTime String The elapsed time of the import process.
Message String The message about the import operation.

GetImportCustomObjectsWarnings

Downloads and saves the import custom objects warnings file from Marketo.

Input
Name Type Required Description
BatchId String True The batch ID of the import custom objects operation to retrieve the warnings file for.
TableName String True The name of the custom object table. Ex: CustomObject_test
FilePath String False The file and path where the downloaded warnings file will be saved, i.e. 'C:\temp\myfile.csv'. Note the file contains CSV (comma separated value) data.
Result Set Columns
Name Type Description
FileData String The raw file data. This is only populated if the 'File' input was not set to write the data to a file. Note the file contains CSV (comma separated value) data.

GetImportLeadsFailures

Downloads and saves the import leads failures file from Marketo.

Input
Name Type Required Description
BatchId String True The batch ID of the import leads operation to retrieve the failures file for.
FilePath String False The file and path where the downloaded failures file will be saved, i.e. 'C:\temp\myfile.csv'. Note the file contains CSV (comma separated value) data.
Result Set Columns
Name Type Description
FileData String The raw file data. This is only populated if the 'File' input was not set to write the data to a file. Note the file contains CSV (comma separated value) data.

GetImportLeadsStatus

Gets the status of an import leads from file operation for Marketo.

Input
Name Type Required Description
BatchId String True The batch ID returned from an import leads from file operation.
Result Set Columns
Name Type Description
BatchId String The ID of the import leads batch.
Status String The status of the import process.
NumOfLeadsProcessed String The number of leads processed.
NumOfRowsFailed String The number of rows that failed.
NumOfRowsWithWarning String The number of rows that contain a warning.
Message String The message about the import operation.

GetImportLeadsWarnings

Downloads and saves the import leads warnings file from Marketo.

Input
Name Type Required Description
BatchId String True The batch ID of the import leads operation to retrieve the warnings file for.
FilePath String False The file and path where the downloaded warnings file will be saved, i.e. 'C:\temp\myfile.csv'. Note the file contains CSV (comma separated value) data.
Result Set Columns
Name Type Description
FileData String The raw file data. This is only populated if the 'File' input was not set to write the data to a file. Note the file contains CSV (comma separated value) data.

GetImportProgramMembersFailures

Downloads and saves the import program members failures file from Marketo.

Input
Name Type Required Description
BatchId String True The batch ID of the import program members operation to retrieve the failures file for.
FilePath String False The file and path where the downloaded failures file will be saved, i.e. 'C:\temp\myfile.csv'. Note the file contains CSV (comma separated value) data.
Result Set Columns
Name Type Description
FileData String The raw file data. This is only populated if the 'File' input was not set to write the data to a file. Note the file contains CSV (comma separated value) data.

GetImportProgramMembersStatus

Gets the status of an import program members from file operation for Marketo.

Input
Name Type Required Description
BatchId String True The batch ID returned from an import program members from file operation.
Result Set Columns
Name Type Description
BatchId String The ID of the import leads batch.
Status String The status of the import process.
NumOfLeadsProcessed String The number of leads processed.
NumOfRowsFailed String The number of rows that failed.
NumOfRowsWithWarning String The number of rows that contain a warning.
Message String The message about the import operation.

GetImportProgramMembersWarnings

Downloads and saves the import program members warnings file from Marketo.

Input
Name Type Required Description
BatchId String True The batch ID of the import program members operation to retrieve the warnings file for.
FilePath String False The file and path where the downloaded failures file will be saved, i.e. 'C:\temp\myfile.csv'. Note the file contains CSV (comma separated value) data.
Result Set Columns
Name Type Description
FileData String The raw file data. This is only populated if the 'File' input was not set to write the data to a file. Note the file contains CSV (comma separated value) data.

GetOAuthAccessToken

Gets an authentication token from Marketo.

Result Set Columns
Name Type Description
OAuthAccessToken String The access token used for communication with Marketo.
ExpiresIn String The remaining lifetime on the access token.

ImportCustomObjectsFromFile

Imports custom objects from a file into Marketo.

Input
Name Type Required Description
File String True The path to the file containing custom objects to be imported into Marketo, i.e. 'C:\temp\custom_object_import.csv'.
TableName String True The name of the custom object table. Ex: CustomObject_test
Format String False The format of the file that will be used to import custom objects into Marketo. The allowed values are csv, tsv, ssv.
Result Set Columns
Name Type Description
BatchId String The ID of the import custom objects batch.
Status String The status of the import process.

ImportLeadsFromFile

Imports leads from a file into Marketo.

Stored Procedure Specific Information

The 'File' parameter must be a path to a file formatted as specified in the 'Format' parameter.

The first row in the file will be the columns, which are the Marketo API field names and map directly to the column names for the Leads table. The remaining rows will be the Leads data you want to import into Marketo.

The maximum file size is 10 MB and all the processing is performed on the Marketo back-end which creates faster performance and less work for the local machine. GetImportLeadsFailures, GetImportLeadsStatus, and GetImportLeadsWarnings are related stored procedures that provide information on the import process.

Input
Name Type Required Description
File String True The path to the file containing leads to be imported into Marketo, i.e. 'C:\temp\myleads.csv'.
Format String True The format of the file that will be used to import leads into Marketo. Applicable values are: 'csv' (comma separated value), 'tsv' (tab separated value), and 'ssv' (semi colon separated value).
LookupField String False Used to specify the field used to find duplicate leads. Available values are: id, cookie, email, twitterId, facebookId, linkedInId, sfdcAccountId, sfdcContactId, sfdcLeadId, sfdcLeadOwnerId.
ListId String False The ID of the list to import the leads into.
PartitionName String False The name of the partition to import the leads into.
Result Set Columns
Name Type Description
BatchId String The ID of the import leads batch.
Status String The status of the import process.

ImportProgramMembersFromFile

Imports program members from a file into Marketo.

Stored Procedure Specific Information

The 'File' parameter must be a path to a file formatted as specified in the 'Format' parameter.

The first row in the file will be the columns, which are the Marketo API field names and map directly to the column names for the Leads table. The remaining rows will be the Program Members data you want to import into Marketo.

The maximum file size is 10 MB and all the processing is performed on the Marketo back-end which creates faster performance and less work for the local machine. GetImportProgramMembersFailures, GetImportProgramMembersStatus, and GetImportProgramMembersWarnings are related stored procedures that provide information on the import process.

Execute ImportProgramMembersFromFile ProgramId='1089', ProgramMemberStatus='Member', File='C:\Dev\tests\v20\ProviderMarketo\importprogrammembers.csv'
Input
Name Type Required Description
File String True The path to the file containing program members to be imported into Marketo, i.e. 'C:\temp\importprogrammembers.csv'.
ProgramId String True The ID of the program
ProgramMemberStatus String True Program member status for members being added.
Format String False The format of the file that will be used to import program members into Marketo. The allowed values are csv, tsv, ssv.
Result Set Columns
Name Type Description
BatchId String The ID of the import program members batch.
ImportId String The ID of the import job.
Status String The status of the import process.

ListMembersOfList

Determines whether leads are contained within a list in Marketo.

EXECUTE Example:

EXECUTE ListMembersOfList ListId='1001', LeadId='10,13,20'
Input
Name Type Required Description
ListId String True The ID of the list to check for leads.
LeadId String True A comma-separated list of lead Ids to be checked if they are in the list specified by ListId.
Result Set Columns
Name Type Description
LeadId String The LeadId that was checked if it is in the list.
Status String The status of the lead and whether it is in the list. Applicable values: memberof, notmemberof, and skipped.
ReasonCode String The reason code pertaining to why a lead was skipped (status will return skipped).
ReasonMessage String The reason message pertaining to why a lead was skipped (status will return skipped).

MergeLeads

Merges two or more known lead records into a single lead record.

EXECUTE Example:

EXECUTE MergeLeads WinningLeadId = 1021579, LoosingLeadId = 1021580

EXECUTE MergeLeads WinningLeadId = 1021579, LoosingLeadId = 1021632,1021630
Input
Name Type Required Description
WinningLeadId String True The ID of the winning lead record
LoosingLeadId String True A comma separated list of loosing lead ids
Result Set Columns
Name Type Description
Status String The status of the merge lead

RefreshOAuthAccessToken

Gets an authentication token from Marketo.

Result Set Columns
Name Type Description
OAuthAccessToken String The access token used for communication with Marketo.
ExpiresIn String The remaining lifetime on the access token.

RemoveLeadsFromList

Removes leads from a list in Marketo.

EXECUTE Example:

EXECUTE RemoveLeadsFromList ListId='1001', LeadId='10,13,20'
Input
Name Type Required Description
ListId String True The ID of the list to remove leads from.
LeadId String True A comma-separated list of lead Ids to be removed from the list specified by ListId.
Result Set Columns
Name Type Description
LeadId String The LeadId that was attempted to be removed from the list.
Status String The status of the lead being removed from the list. Applicable values: removed and skipped.
ReasonCode String The reason code pertaining to why a lead was skipped (status will return skipped).
ReasonMessage String The reason message pertaining to why a lead was skipped (status will return skipped).

ScheduleCampaign

Remotely schedules a batch campaign to run at a given time.

EXECUTE Example:

INSERT into TokenAggregate#Temp (TokenName, TokenValue) VALUES ('test', 'testvalue_cdata')
INSERT into TokenAggregate#Temp (TokenName, TokenValue) VALUES ('test1', 'testvalue1')

EXECUTE ScheduleCampaign TokenAggregate = 'TokenAggregate#Temp', CampaignId = 1019, CloneToProgramName = 'Program 1'
Input
Name Type Required Description
CampaignId String True ID of the batch campaign to schedule.
CloneToProgramName String False Name of the resulting program.
RunAt String False Datetime to run the campaign at. If unset, the campaign will be run five minutes after the call is made.
TokenName String False Name of the token. Should be formatted as '{{my.name}}'
TokenValue String False Value of the token
TokenAggregate String False List of my tokens to replace during the run of the target campaign. The tokens must be available in a parent program or folder to be replaced during the run
Result Set Columns
Name Type Description
Success String Whether the request succeeded ,

UpdateEmailContent

Updates the content of an email

EXECUTE Example:

EXECUTE UpdateEmailContent EmailId = '1187', FromName = 'John', FromEmail = 'john@abc.com', Subject = 'test', ReplyTo = 'steve@abc.com'
Input
Name Type Required Description
EmailId String True The ID of the email.
FromEmail String False From-address of the Email
FromName String False From-name of the Email
ReplyTo String False Reply-To address of the Email
Subject String False Subject Line of the Email
Result Set Columns
Name Type Description
Id String The ID of the updated email content

UpdateEmailFullContent

Updates the content of an email

EXECUTE Example:

EXECUTE UpdateEmailFullContent EmailId = '1187', File = 'C:\Users\Dell\Desktop\Test_Logs\Marketo.html'
Input
Name Type Required Description
EmailId String True The ID of the email.
File String False From-address of the Email
Result Set Columns
Name Type Description
Id String The status of the lead being added to the list. Applicable values: added and skipped.

UpdateEmailTemplateContent

Updates the content of the given email template.

EXECUTE Example:

EXECUTE UpdateEmailTemplateContent File = 'C:\Users\Dell\Desktop\Test_Logs\Marketo.html', EmailTemplateId = '1'
Input
Name Type Required Description
EmailTemplateId Integer True The ID of the email template.
File String True Content for the email template. Multipart file.
Result Set Columns
Name Type Description
Id String The status of the lead being added to the list. Applicable values: added and skipped.

UpdateFile

Replaces the current content of the file with the included payload

EXECUTE Example:

EXECUTE UpdateFile File = 'C:\Users\Dell\Desktop\Test_Logs\Marketo.html', FileId = '43996'
Input
Name Type Required Description
File String False Multipart File. Content of the file.
FileId Integer False The ID of the folder
Result Set Columns
Name Type Description
Id String The ID of the created file

UpdateLandingPageContentSection

Add or update the section of the landing page content in Marketo.

EXECUTE Example:

EXECUTE UpdateLandingPageContentSection LandingPageId = '1003', ContentId = '1009', Type = 'HTML', Value = '<html><body><h3>Test Update</h3></body></html>'
Input
Name Type Required Description
LandingPageId String True ID of the landing page.
ContentId String True ID of the content section. Also the HTML ID of the section.
Type String True Type of content section. The allowed values are Image, Form, Rectangle, Snippet, RichText, HTML.
BackgroundColor String False background-color property of the HTML section.
BorderColor String False Border-color property of the HTML section.
BorderStyle String False Border-style property of the HTML section.
BorderWidth String False Border-width property of the HTML section.
Height String False Height property of the HTML section.
HideDesktop String False Hide the section when displayed on a desktop browser.
HideMobile String False Hide the section when displayed on a mobile browser. Default false.
ImageOpenNewWindow String False Image open new window.
Index String False Index of the content section. Determines the order of the section in the landing page.
Left String False Left property of the HTML section.
LinkUrl String False URL parameter of a link type section.
Opacity String False Opacity property of the HTML section.
Top String False Top property of the HTML section.
Value String False Type of content section.
Width String False Width property of the HTML section.
ZIndex String False Z-index property of the HTML section.
Result Set Columns
Name Type Description
Id String ID of the landing page.

UpdateLandingPageTemplateContent

Updates the content for the target landing page template.

EXECUTE Example:

EXECUTE UpdateLandingPageTemplateContent File = 'C:\Users\Dell\Desktop\Test_Logs\Marketo.html', LandingPageTemplateId = '2'
Input
Name Type Required Description
LandingPageTemplateId Integer True ID of the landing page template.
File String True Multipart File
Result Set Columns
Name Type Description
Id String ID of the asset.

UpdateLeadProgramStatus

Changes the program status of a list of leads in a target program. Only existing members of the program may have their status changed with this API.

EXECUTE Example:

EXECUTE UpdateLeadProgramStatus ProgramId = '1091', LeadIds = '1021579,1021686', Status = 'Not in program'
Input
Name Type Required Description
ProgramId String True ID of the batch campaign to schedule.
LeadIds String True A comma separated list of lead ids for input.
Status String True Program status of the record.
Result Set Columns
Name Type Description
Success String Whether the request succeeded

System Tables

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

Schema Tables

The following tables return database metadata for Marketo:

Data Source Tables

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

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

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations:

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

sys_catalogs

Lists the available databases.

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

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

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

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Leads'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

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

SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

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

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Leads'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

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

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_indexes

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

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

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:marketo:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Collaborative Query Processing

When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. The connector offloads as much of the SELECT statement processing as possible to the server and then processes the rest of the query in memory.

Discover the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. The following result set indicates the SELECT functionality that the connector can offload to the data source or process client side. Your data source may support additional SQL syntax. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the REST Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Advanced Configurations Properties

The advanced configurations 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. Click the links for further details.

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
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
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.
OAuthAccessToken The access token for connecting using OAuth.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL

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

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.

Authentication

Property Description
AuthScheme The type of authentication to use when connecting to Marketo.

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.

REST

This section provides a complete list of the REST properties you can configure.

Property Description
RESTEndpoint The Marketo REST API Endpoint.

RESTEndpoint

The Marketo REST API Endpoint.

Data Type

string

Default Value

""

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.

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.

Data Type

string

Default Value

""

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.

Data Type

string

Default Value

""

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.

Data Type

string

Default Value

""

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 OAuth properties you can configure.

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
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.
OAuthAccessToken The access token for connecting using OAuth.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

GETANDREFRESH

Remarks

The following options are available:

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

OAuthClientId

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

Data Type

string

Default Value

""

Remarks

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

OAuthClientSecret

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

Data Type

string

Default Value

""

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.

OAuthAccessToken

The access token for connecting using OAuth.

Data Type

string

Default Value

""

Remarks

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

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

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

This section provides a complete list of SSL properties you can configure.

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.

Data Type

string

Default Value

""

Remarks

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

This property can take the following forms:

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

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

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

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

Schema

This section provides a complete list of schema properties you can configure.

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.

Data Type

string

Default Value

%APPDATA%\Marketo Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.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 connector 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%\Marketo Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

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

Schema

The type of schema to use.

Possible Values

REST, SOAP

Data Type

string

Default Value

REST

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.

Data Type

string

Default Value

""

Remarks

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

Tables

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

Data Type

string

Default Value

""

Remarks

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

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.

Data Type

string

Default Value

""

Remarks

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

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.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
AuthScheme The type of authentication to use when connecting to Marketo.

AuthScheme

The type of authentication to use when connecting to Marketo.

Possible Values

Auto, OAuth, HMAC

Data Type

string

Default Value

Auto

Remarks
  • Auto: Lets the driver decide automatically based on the other connection properties you have set.
  • OAuth: Set to this to perform OAuth authentication.
  • HMAC: Set this to perform HMAC authentication.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

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.

JobPollingInterval

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

Data Type

int

Default Value

120

Remarks

This property is used to specify the polling interval (in seconds) to identify when a bulk API job has completed. The connector will wait JobPollingInterval seconds between calls to check a bulk API job status. Once the job is identified as 'Completed', the connector 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.

Data Type

int

Default Value

-1

Remarks

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

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

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

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
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.

Data Type

int

Default Value

1000

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.

Data Type

string

Default Value

""

Remarks

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

Timeout

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

Data Type

string

Default Value

300

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 connector throws an exception.

UseBulkAPI

Specifies whether to use the Marketo Bulk API.

Data Type

bool

Default Value

false

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 connector 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 connector will poll the job status to identify when the job has completed, waiting JobPollingInterval seconds in between calls. Once the job is complete, the connector 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.