Skip to Content

Marketo Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8843 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

Connect to Marketo

Marketo supports two APIs: REST and SOAP. To specify the API to be used, configure the

Schema property to either REST or SOAP.

REST API

The REST API uses OAuth to authentication to validate the user's access. To set up access to

Marketo via the REST API:

  1. Create a custom service.
  2. Obtain the appropriate OAuth credentials.
  3. Configure the connection variables.
Create a Custom Service

This procedure creates a new role with REST API privileges, assigns that role to an existing or new user, and creates a new service. It must be executed by a user with Admin privileges.

  1. Navigate to the Marketo application's Admin area.
  2. Navigate to the Security section.
  3. Click Users & Roles.
  4. Create a new role for API Access:

    1. Select the Roles tab.

    2. Click New Role.

    3. Specify a Role Name and select Role permissions. Specify Access API permissions that

      are specific to the REST API. 5. Assign or create a new User to execute API Access: 1. Select the Users tab. 2. To assign a new user the role you just created, click Invite New User. 3. Enter the new user information, and assign them the role you just created with REST API access. To denote the user as an API Only user, select the API Only option. 6. Create the new service: 1. Navigate to Admin > Integration and click the LaunchPoint option. 2. Click New Service. 3. Specify the Service Type of Custom. 4. Enter a display name and description for the service. 5. Assign the user you just created, to the service.

Marketo creates a new REST API service designed for connecting and authenticating to Marketo. It also generates the OAuth-based authentication credentials required for validating user access to the REST API.

Obtain OAuth Credentials

To obtain and record the new service's OAuth credentials, return to the Marketo Admin area and navigate to the LaunchPoint option.

Click the View Details link for the service you just created. Marketo displays a window that shows the authentication credentials. These credentials provide the values for OAuthClientId and OAuthClientSecret.

Set Connection Variables

After you have set the following connection variables, you are ready to connect via the REST API:

  • Schema: 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, as found in the Admin area.

    Navigate to the REST API Integration > Web Services section to find this URL. Leave Identity Endpoint blank.

SOAP API

After you have set the following connection variables, you are ready to connect via the SOAP API:

  • Schema: SOAP.

  • UserId: The client access ID, found in the Integration section of the Marketo Admin SOAP API panel.

  • EncryptionKey: The Marketo SOAP API Encryption Key, generated on the Admin page of the

    Marketo website.

  • SOAPEndpoint: The URL of the SOAP Web service endpoint, provided 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 Jitterbit 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.

Query Processing

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

See Query Processing for more information.

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"

Define Views Using DDL Statements

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

Create a View

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

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

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

Alter a View

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

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

The view is then updated in the JSON configuration file.

Drop a View

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

DROP LOCAL VIEW [MyViewName]

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

Schema for User Defined Views

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

Work with User Defined Views

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

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

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

Resulting in the effective query to the source:

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

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

SSL Configuration

Customize the SSL Configuration

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

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

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

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 similar to tables in the way that data is represented; however, views are read-only.

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

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

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 Create, update, delete, and query members for program in Marketo.
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, 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 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 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 can specify the ID or Name field.

DELETE FROM Emails WHERE Id = 1192
DELETE FROM Emails WHERE Name in ('Test1', 'Test2')
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 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 can specify the ID or Name field.

DELETE FROM EmailTemplates WHERE Id = 1192
DELETE FROM EmailTemplates WHERE Name in ('Test1', 'Test2')
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 can specify the ID or Name 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
DELETE FROM Folders WHERE Name in ('Marketo1','Marketo2')
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 can specify the ID or Name field.

DELETE FROM Forms WHERE Id = '1214'
DELETE FROM Forms WHERE Name in ('Test1', 'Test2')
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 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 can specify the ID or Name field.

DELETE FROM LandingPages WHERE Id = 1996
DELETE FROM LandingPages WHERE Name in ('Test1', 'Test2')
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 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 can specify the ID or Name field.

DELETE FROM LandingPageTemplates WHERE Id = 1312
DELETE FROM LandingPageTemplates WHERE Name in ('Test1', 'Test2')
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. If a column is filterable, you specify multiple values by use of the IN operator or the OR logical operator.

To optimize response time from the server, identify only the rows and columns you want to retrieve.

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

You will get the best performance from this query if you can confine your query to a list of known Leads within Marketo. To do this, create a static list of Leads within Marketo, and then specify the ListId to retrieve them.

If no filter is specified, the Activities_NewLead table is queried to retrieve a list of Lead Ids. After the Lead Ids are compiled, they are used to query the Leads table. Since one Activities_Newlead request must be made for each Leads request made, when no filter is specified it effectively doubles the number of API calls made per lead. This affects query performance.

For example:

  • To limit the returned Leads to those created during a specific time period, filter on the 'CreatedAt' column. When you use the '>' or '>=' operator, the

    datetime value is included in the Activities_NewLead table request. Since the maximum batch size per request for the REST API is 300, you can get a rough estimate of the number of API calls this query will require using the formula:

    (Total Number of Leads / 300) * 2
    
  • To limit the returned Leads to those updated at a particular datetime, filter on the 'UpdatedAt' column. When you use the '>' or '>=' operator, the datetime value is included in the Activities_LeadChanges table request. Since the maximum batch size per request for the REST API is 300, you can get a rough estimate of the number of API calls this query will require using the formula:

    (Total Number of Leads / 300) * 2
    

You can also use the SOAP API to retrieve a list of Lead Ids by setting "UseSOAPForLeadIds=True" in the 'Other' property. When UseSOAPForLeadIDs is True and the SOAP connection details are specified, the SOAP API compiles a list of Lead Ids which will then be used as a filter for the REST API.

This hybrid approach is faster than using the SOAP API by itself, because the SOAP API is significantly slower than the REST API. Since the maximum batch size per request for the SOAP API is 1000, you can get a rough estimate of the number of API calls this query will require using the formula:

(Total Number of Leads / 1000) + (Total Number of Leads / 300)
Insert

To create a new Lead record, specify the Lead's first name, last name, email address, and company name for entry into the database.

For example, to insert a new lead for someone named John Mangel with an email address of john@abc.com, enter:

INSERT INTO Leads (Email, FirstName, LastName) VALUES ('john@abc.com', 'John', 'Mangel')

To insert multiple leads at once via a #TEMP table, first create the #TEMP table, and then insert that table into your Leads table.

The following example creates a #TEMP table with three new Leads, and then inserts that #TEMP table into the Leads table:

INSERT INTO Leads#TEMP (FirstName, LastName, Email, Company) VALUES ('John', 'Mangel', 'john@abc.com', 'ABC')
INSERT INTO Leads#TEMP (FirstName, LastName, Email, Company) VALUES ('Steve', 'Puth', 'steve@abc.com', 'ABC')
INSERT INTO Leads#TEMP (FirstName, LastName, Email, Company) VALUES ('Andrew', 'Stack', 'andy@abc', 'ABC')

INSERT INTO Leads (FirstName, LastName, Email, Company) SELECT FirstName, LastName, Email, Company FROM Leads#TEMP

To import multiple leads from a CSV file, ensure UseBulkAPI is set to True, then enter a command similar to the following:

INSERT INTO Leads (CSVFile) VALUES ('C:\\Upload\\ImportLeads.csv')
Update

You can update any field in the Leads table that is not read-only. Updates are performed using any 'Filterable' column, such as Email, as a lookup field (external key). (To identify all the ReadOnly and Filterable columns in the Leads table, see "Columns", below.)

For example:

  • To update the Leads table to assign 111-222-3333 as the MobilePhone entry to all rows where Id=1 (Id is the lookup field):

    UPDATE Leads SET MobilePhone = '111-222-3333' WHERE ID = 1
    
  • To update the Leads table to assign 111-222-3333 as the MobilePhone entry to all rows where Email=john@abc.com (Email is the lookup field):

    UPDATE Leads SET MobilePhone = '111-222-3333' WHERE Email = 'john@abc.com'
    

You can also use a custom field as the lookup field. To do this, you must clearly identify that the custom field is being used in this manner, by first defining the LookupField as the custom field. For example, to assign 111-222-3333 as the MobilePhone entry to all rows where MyCustomField=my value (MyCustomField is the lookup field):

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

To remove a lead from the Leads table, you must identify the lead by its Marketo Id. For example, to delete the lead whose Marketo Id=1, enter:

DELETE FROM Leads WHERE ID = 1
GetDeleted

To retrieve a list of all leads that have been deleted from the Leads Table in the past 14 days, use the GetDeleted query.

GetDeleted FROM Leads

To retrieve a list of all leads that have been deleted since the table's last update, use the GetDeleted query with the UpdatedAt filter.

GetDeleted FROM Leads UpdatedAt='date'
Columns
Name Type ReadOnly Filterable Description
Id [KEY] Int False True This is a generated column, no description is available.
Company String False This is a generated column, no description is available.
Site String False This is a generated column, no description is available.
BillingStreet String False This is a generated column, no description is available.
BillingCity String False This is a generated column, no description is available.
BillingState String False This is a generated column, no description is available.
BillingCountry String False This is a generated column, no description is available.
BillingPostalCode String False This is a generated column, no description is available.
Website String False This is a generated column, no description is available.
MainPhone String False This is a generated column, no description is available.
AnnualRevenue Decimal False This is a generated column, no description is available.
NumberOfEmployees Int False This is a generated column, no description is available.
Industry String False This is a generated column, no description is available.
SicCode String False This is a generated column, no description is available.
MktoCompanyNotes String False This is a generated column, no description is available.
ExternalCompanyId String False This is a generated column, no description is available.
MktoName String True This is a generated column, no description is available.
PersonType String False This is a generated column, no description is available.
MktoIsPartner Bool False This is a generated column, no description is available.
IsLead Bool False This is a generated column, no description is available.
MktoIsCustomer Bool False This is a generated column, no description is available.
IsAnonymous Bool False This is a generated column, no description is available.
Salutation String False This is a generated column, no description is available.
FirstName String False This is a generated column, no description is available.
MiddleName String False This is a generated column, no description is available.
LastName String False This is a generated column, no description is available.
Email String False True This is a generated column, no description is available.
Phone String False This is a generated column, no description is available.
MobilePhone String False This is a generated column, no description is available.
Fax String False This is a generated column, no description is available.
Title String False This is a generated column, no description is available.
ContactCompany Int True This is a generated column, no description is available.
DateOfBirth Date False This is a generated column, no description is available.
Address String False This is a generated column, no description is available.
City String False This is a generated column, no description is available.
State String False This is a generated column, no description is available.
Country String False This is a generated column, no description is available.
PostalCode String False This is a generated column, no description is available.
PersonTimeZone String True This is a generated column, no description is available.
OriginalSourceType String True This is a generated column, no description is available.
OriginalSourceInfo String True This is a generated column, no description is available.
RegistrationSourceType String False This is a generated column, no description is available.
RegistrationSourceInfo String False This is a generated column, no description is available.
OriginalSearchEngine String True This is a generated column, no description is available.
OriginalSearchPhrase String True This is a generated column, no description is available.
OriginalReferrer String True This is a generated column, no description is available.
EmailInvalid Bool False This is a generated column, no description is available.
EmailInvalidCause String False This is a generated column, no description is available.
Unsubscribed Bool False This is a generated column, no description is available.
UnsubscribedReason String False This is a generated column, no description is available.
DoNotCall Bool False This is a generated column, no description is available.
MktoDoNotCallCause String False This is a generated column, no description is available.
DoNotCallReason String False This is a generated column, no description is available.
MarketingSuspended Bool False This is a generated column, no description is available.
MarketingSuspendedCause String False This is a generated column, no description is available.
BlackListed Bool False This is a generated column, no description is available.
BlackListedCause String False This is a generated column, no description is available.
MktoPersonNotes String False This is a generated column, no description is available.
AnonymousIP String False This is a generated column, no description is available.
InferredCompany String True This is a generated column, no description is available.
InferredCountry String True This is a generated column, no description is available.
InferredCity String True This is a generated column, no description is available.
InferredStateRegion String True This is a generated column, no description is available.
InferredPostalCode String True This is a generated column, no description is available.
InferredMetropolitanArea String True This is a generated column, no description is available.
InferredPhoneAreaCode String True This is a generated column, no description is available.
EmailSuspended Bool False This is a generated column, no description is available.
EmailSuspendedCause String False This is a generated column, no description is available.
EmailSuspendedAt Datetime False This is a generated column, no description is available.
Department String False This is a generated column, no description is available.
CreatedAt Datetime True True This is a generated column, no description is available.
UpdatedAt Datetime True True This is a generated column, no description is available.
Cookies String False True This is a generated column, no description is available.
ExternalSalesPersonId String False This is a generated column, no description is available.
LeadPerson Int True This is a generated column, no description is available.
LeadRole String False This is a generated column, no description is available.
LeadSource String False This is a generated column, no description is available.
LeadStatus String False This is a generated column, no description is available.
LeadScore Int False This is a generated column, no description is available.
Urgency Double False This is a generated column, no description is available.
Priority Int False This is a generated column, no description is available.
RelativeScore Int False This is a generated column, no description is available.
RelativeUrgency Int False This is a generated column, no description is available.
Rating String False This is a generated column, no description is available.
PersonPrimaryLeadInterest Int True This is a generated column, no description is available.
LeadPartitionId Int False This is a generated column, no description is available.
LeadRevenueCycleModelId Int False This is a generated column, no description is available.
LeadRevenueStageId Int False This is a generated column, no description is available.
AcquisitionProgramId Int False This is a generated column, no description is available.
MktoAcquisitionDate Datetime False This is a generated column, no description is available.
TestKpQA String False This is a generated column, no description is available.
TestCustomfieldEmail String False This is a generated column, no description is available.
Ecids String True This is a generated column, no description is available.
TestFieldText1 String False This is a generated column, no description is available.
Test1 Bool False This is a generated column, no description is available.
Cstmfdtest1 String False This is a generated column, no description is available.
Cstmfdtest2 String False This is a generated column, no description is available.
Test String False This is a generated column, no description is available.
Test98 String False This is a generated column, no description is available.
LookupField String False True This is filter only column that will not contain data.
ListId Int False True This is filter only column that will not contain data.
ProgramId Int False True This is filter only column that will not contain data.
PartitionName String False True This is filter only column that will not contain data.
MembershipAcquiredBy String True False This is a generated column, that is only available when filtering by ProgramId.
MembershipIsExhausted Bool True False This is a generated column, that is only available when filtering by ProgramId.
MembershipMembershipDate Datetime True False This is a generated column, that is only available when filtering by ProgramId.
MembershipNurtureCadence String True False This is a generated column, that is only available when filtering by ProgramId.
MembershipProgressionStatus String True False This is a generated column, that is only available when filtering by ProgramId.
MembershipReachedSuccess Bool True False This is a generated column, that is only available when filtering by ProgramId.
MembershipReachedSuccessDate Datetime True False This is a generated column, that is only available when filtering by ProgramId.
MembershipStream String True False This is a generated column, that is only available when filtering by ProgramId.
MembershipUpdatedAt Datetime True False This is a generated column, that is only available when filtering by ProgramId.

ListStaticMemberShip

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

Table Specific Information
Select

Note

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

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

SELECT * FROM ListStaticMemberShip WHERE ListId = 1014

SELECT * FROM ListStaticMemberShip WHERE ListId = 1014 AND ID = 1016
Insert

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

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

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

DELETE FROM ListStaticMemberShip WHERE Listid = 1014 AND ID = 1014
Columns
Name Type ReadOnly Filterable Description
Id [KEY] Integer True True Id of static MemberShip.
FirstName String True FirtName of the member
LastName String True LastName of the member
Email String True Email
CreatedAt Datetime True The date and time the membership was created.
UpdatedAt Datetime True The date and time the membership was last updated.
ListId Integer True True ListId.

NamedAccounts

Query Named Accounts for a Marketo organization.

Table Specific Information
Select

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

SELECT * FROM NamedAccounts WHERE Name = 'MyAccount'
Insert

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

The following example demonstrates how to insert a new NamedAccount:

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

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

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

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

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

Opportunities

Query Opportunities for a Marketo organization.

Table Specific Information

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

Select

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

SELECT * FROM Opportunities WHERE ExternalOpportunityId = 'CDATA1'
Insert

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

The following example demonstrates how to insert a new Opportunity:

INSERT INTO Opportunities (ExternalOpportunityId, Description, ExternalCompanyId, Name) VALUES ('CDATA1', ' 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

Create, update, delete, and query members for program in Marketo.

Table Specific Information
Select

Retrieve all Program Members for the specific ProgramId.

Note:

  • When UseBulkAPI=true and ProgramId is not specified, it will fetch the first ProgramId. We can only use =, IN operator with ProgramID.
  • When UseBulkAPI=false and ProgramId is not specified, it will fetch records for all the ProgramId. We can specify the =, IN, >, <, <=, >= operator with ProgramID.
SELECT * FROM ProgramMembers WHERE ProgramId = 1102
SELECT * FROM ProgramMembers WHERE ProgramId = '1044' AND LeadId IN ('1789', '1789', '1790', '1791', '1792')
SELECT * FROM ProgramMembers WHERE ProgramId = '1001' AND LeadId = '4'
SELECT * FROM ProgramMembers WHERE ProgramId IN (1102, 1103, 1104)
SELECT * FROM ProgramMembers WHERE LeadId IN ('1789', '1790', '1791', '1792') AND reachedSuccess IN (false, true)
SELECT * FROM ProgramMembers WHERE reachedSuccess IN (false, true) AND LeadId = '1789'
Insert

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

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

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

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

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

DELETE FROM ProgramMembers WHERE LeadId = '4' AND ProgramId = '1001'
Columns
Name Type ReadOnly Filterable Description
Id Int True This is a generated column, no description is available.
AttendanceLikelihood Int True This is a generated column, no description is available.
CreatedAt Datetime True This is a generated column, no description is available.
IsExhausted Bool True This is a generated column, no description is available.
LeadId [KEY] Int False True This is a generated column, no description is available.
MembershipDate Datetime True This is a generated column, no description is available.
NurtureCadence String True This is a generated column, no description is available.
Program String True This is a generated column, no description is available.
ProgramId [KEY] Int False True This is a generated column, no description is available.
ReachedSuccess Bool True True This is a generated column, no description is available.
ReachedSuccessDate Datetime True This is a generated column, no description is available.
RegistrationLikelihood Int True This is a generated column, no description is available.
StatusName String True True This is a generated column, no description is available.
TrackName String True This is a generated column, no description is available.
UpdatedAt Datetime True True This is a generated column, no description is available.
WaitlistPriority Int True This is a generated column, no description is available.
AcquiredBy Bool False This is a generated column, no description is available.
FlowStep Int False True This is a generated column, no description is available.
RegistrationCode String False This is a generated column, no description is available.
ReiNewCustomField String False True This is a generated column, no description is available.
StatusReason String False This is a generated column, no description is available.
TestCustomObjFd String False True This is a generated column, no description is available.
UTMSource String False True This is a generated column, no description is available.
WebinarUrl String False This is a generated column, no description is available.

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, you can specify ID or Name field.

DELETE FROM Programs WHERE Id = '1'
DELETE FROM Programs WHERE Name in ('Test1', 'Test2')

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 can specify the ID or Name field.

DELETE FROM SmartLists WHERE Id = 1142
DELETE FROM SmartLists WHERE Name in ('Test1', 'Test2')
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 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 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 can specify the ID or Name field.

DELETE FROM StaticLists  WHERE Id = 1058
DELETE FROM StaticLists  WHERE Name in ('Test1', 'Test2')
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, 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')
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 True The name of the Token.
Type String False True 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 True The value of the Token.
ComputedURL String False The Computed URL of the Token.

Views

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

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

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.

Table Specific Information
SELECT

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

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

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

SELECT * FROM Activities WHERE activitydate > '2022-09-3' AND activitydate < '2022-09-5'

SELECT * FROM Activities WHERE activitytypeid = 11

SELECT * FROM Activities WHERE activitytypeid IN (11, 12) AND leadid IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)

SELECT * FROM Activities WHERE ActivityTypeId = '1' AND LeadId = '123'
Columns
Name Type Filterable Description
Id [KEY] String True Unique ID of the activity.
ActivityDate Datetime True Datetime of the activity.
ActivityTypeId Integer True Id of the activity type.
LeadId Integer True Id of the lead associated to the activity.
MarketoGUID String Unique ID of the activity (128 character string).
PrimaryAttributeValue String Value of the primary attribute.
PrimaryAttributeValueId Integer Id of the primary attribute field.
Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
ListId String Id of a static list. If set, will only return activities of members of this static list.

ActivityBulkExports

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

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

ActivityTypes

Get activity types for a Marketo Organization

Table Specific Information
Select

Note

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

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

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

ActivityTypesAttributes

Get activity types attributes for a Marketo Organization

Table Specific Information
Select

Note

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

Retrieves a list of activity types attributes for the target instance

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

Campaigns

Query Campaigns for a Marketo organization.

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

ChannelProgressionStatuses

Query ProgressionStatuses of Channels for a Marketo organization.

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

Channels

Query Channels for a Marketo organization.

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 Channels

SELECT * FROM Channels WHERE Name in ('Test1', 'Test2')
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
SmartListId Integer True The ID of the Smart List
SmartListName String The Name of the Smart List
CreatedAt Datetime The date and time the smart list was created.
UpdatedAt Datetime The date and time the smart list was last updated.
WorkSpace String The name of the workspace where the smart list is located.
Url String The URL of the smart 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
SmartCampaignId Integer The ID of the smart campaign
ProgramId Integer The ID of the Program

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 function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Marketo.

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

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

DeleteLandingPageContentSection

Deletes the section of the landing page content in Marketo.

EXECUTE Example:

EXECUTE DeleteLandingPageContentSection LandingPageId = '1003', ContentId = '1'
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.
Encoding String False The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.
Result Set Columns
Name Type Description
ResponseData String The content of the file. Returns data only if File and FileStream 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.
Encoding String False The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.
Result Set Columns
Name Type Description
FileData String The file data. This is only populated if the 'FilePath' and 'FileStream' 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.
Encoding String False The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.
Result Set Columns
Name Type Description
FileData String The file data. This is only populated if the 'FilePath' and 'FileStream' 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.
Encoding String False The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.
Result Set Columns
Name Type Description
FileData String The file data. This is only populated if the 'FilePath' and 'FileStream' 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.
Encoding String False The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.
Result Set Columns
Name Type Description
FileData String The file data. This is only populated if the 'FilePath' and 'FileStream' 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.
Encoding String False The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.
Result Set Columns
Name Type Description
FileData String The file data. This is only populated if the 'FilePath' and 'FileStream' 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.
Encoding String False The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.
Result Set Columns
Name Type Description
FileData String The file data. This is only populated if the 'FilePath' and 'FileStream' 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 False 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 False 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 False 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.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

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_primarykeys

Describes the primary keys.

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

SELECT * FROM sys_primarykeys
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.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary 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.

Discover the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. 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.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
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.

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 when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Marketo.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UseBulkAPI Specifies whether to use the Marketo Bulk API.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

REST

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

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.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
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.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.

Data Type

string

Default Value

%APPDATA%\Marketo Data Provider\OAuthSettings.txt

Remarks

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

Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

The default location is "%APPDATA%\Marketo Data Provider\OAuthSettings.txt" with %APPDATA% set to the user's configuration directory. The default values are

  • Windows: "register://%DSN"
  • Unix: "%AppData%..."
  • Mac: "%AppData%..."

where DSN is the name of the current DSN used in the open connection.

The following table lists the value of %APPDATA% by OS:

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

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.

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 when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from Marketo.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UseBulkAPI Specifies whether to use the Marketo Bulk API.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

JobPollingInterval

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

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 when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

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.

UserDefinedViews

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

Data Type

string

Default Value

""

Remarks

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

Note that the specified path is not embedded in quotation marks.