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.

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

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

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.

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

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.

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

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

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.

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

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.

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

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.

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

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.

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

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.

Activities_ChangeRevenueStage

Query Change Revenue Stage Activities for a Marketo organization.

Columns

Pseudo-Columns

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

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

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.

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

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.

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

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.

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

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.

Activities_ClickLink

Query Click Link Activities for a Marketo organization.

Table Specific Information

Select

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

SELECT * FROM Activities_ClickLink WHERE Email = 'test@server.com' OR Email = 'testlead@server.com'

Columns

Pseudo-Columns

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

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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

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

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.

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

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.

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

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.

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

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

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.

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

Emails

Query Emails for a Marketo organization.

Table Specific Information

Select

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

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.

EmailTemplates

Query, update and delete EmailTemplates for a Marketo organization.

Table Specific Information

Select

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

Folders

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

Table Specific Information

Select

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

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.

Forms

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

Table Specific Information

Select

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

LandingPages

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

Table Specific Information

Select

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

LandingPageTemplates

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

Table Specific Information

Select

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

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

ListStaticMemberShip

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

Table Specific Information

Select