Denodo Templates for Marketo - Quick Use Guide

Last modified on: 29 Jun 2022

You can translate the document:

Overview

Marketo is SaaS based marketing automation software built to help organizations automate and measure marketing engagement, tasks and workflows.

You can configure Denodo to retrieve data from Marketo by creating JSON data sources and base views in Denodo using the Marketo REST API.

As Marketo manages a very large amount of data and views, it may be hard work to add all the required data sources and base views in Denodo. In order to make this process easier and faster, we distribute a template: a set of predefined VQL and properties files. You just have to configure a few parameters in the properties file and import these files into your Denodo server to get access to Marketo.

The Marketo templates

The marketo templates are divided in two different script files:

marketo_main_templates_denodo.vql
marketo_bulk_templates_denodo.vql

The first one creates the Marketo database and provides access to the main functionalities of Marketo. The second, which depends on the main one, provides access to the bulk extract Marketo interface.

Please note: importing these scripts will create a database called marketo in your Virtual DataPort installation, and will drop any existing database previously existing with that exact name.

Marketo main templates

The Marketo main template: the VQL and properties files, configures access for the following functionalities of Marketo:

Leads

Activities

Campaigns

Custom Objects

Named Accounts

Lists

Static Lists

Smart Lists

Programs

Tag Type

Channel

Segments

Importing artifacts

Marketo does not offer a mechanism for refreshing OAuth tokens as a part of their API. Instead, once a token is expired the user has to request a new one. The Denodo Templates for Marketo worked around this by means of a set of Stored Procedures. Because of this, before importing the VQL into Denodo, you have to add the marketo-api-storedprocedures-8.0-{version}.jar extension included in the lib folder of the distribution using the Extension management option of the VDP Administration Tool:

Extension management option in VDP

Before importing the VQL in Denodo, you have to set the following parameters defined in the marketo_templates_denodo.properties file:

client_id: the client id provided by your Marketo instance

client_secret: the client secret provided by your Marketo instance

scope: the Marketo user with API access permissions

Marketo instance: your Marketo instance

Configuration properties

Once you have set the properties file, you only have to import both the properties and the marketo_main_templates_denodo.vql file using the Import option of the VDP Administration Tool:

Import option in VDP

Import wizard in VDP

Refresh

After the importation finishes, refresh the Server Explorer by selecting File > Refresh and you will be able to see the data sources and views for accessing to Marketo:

Important

The set of Stored Procedures included are used by final views and are in charge of the task of asking for new access tokens when the older ones expire. So, ONLY the FINAL VIEWS are meant to be directly used.

Activity

Activity data sources and views

activity: Returns a list of activities of type given by the activity_type_id parameter from after a datetime given by the since_date_time parameter.

Smart Campaign

Smart Campaign data sources and views

campaign: Returns a list of smart campaign records.

Custom Object

Custom Object data sources and views

custom_object: Returns a list of custom objects records based on filter and set of values.

Named Account

Named Account data sources and views

named_account: Retrieves named account records from your Marketo instance based on the submitted filter.
named_account_list: Retrieves a list of named account list records based on the filter type and values given.

Lead

Lead data sources and views

lead: Returns a list of up to 300 leads based on a list of values in a particular field.

List

List data sources and views

list: Returns a set of static list records.

Smart List

Smart List data sources and views

smartlists: Retrieves a list of user created Smart List records.

Static List

Static List data sources and views

staticlist: Retrieves a list of Static List records.

Programs

Program data sources and views

program: Retrieves the list of accessible programs from your Marketo instance.

Tag Type

Tag Type data sources and views

tag_type: Retrieves a list of available tag types.

Channel

Channel data sources and views

channel: Retrieves all channels.

Segment

Segment data sources and views

segment: Retrieves a list of accessible segmentations for your Marketo instance.

Query

Now, you can execute queries over the Marketo views that have been created:

Marketo Lead query

Marketo Lead results

Marketo Activity query

Marketo Activity query results

Marketo bulk extract templates

Marketo provides interfaces for retrieval of large sets of person and person related data, called Bulk Extract. The Marketo bulk extract templates configures the access for the following entities:

Lead

Activity

Program

Bulk extract is performed by creating a job, defining the set of data to retrieve, enqueuing the job, waiting for the job to complete writing a file, and then retrieving the file over HTTP. These jobs are executed asynchronously, and can be polled to retrieve the status of the export.

Import VQL

The marketo_bulk_templates_denodo.vql adds the bulk extract functionality to the Marketo templates. Before importing it in Denodo, the marketo_main_templates_denodo.vql script has to be imported.

In order to import the marketo_bulk_templates_denodo.vql, you have to follow the same steps explained for importing the marketo_main_templates_denodo.vql.

Activity

Marketo bulk extract Activity data sources and views

create_export_activity_job: Creates a job for exporting marketo activities. The parameter startat defines the time when the job starts and the endat parameter, the time when the job finishes.
enqueue_export_activity_job: Enqueue export job. This will place export job in queue, and will start the job when computing resources become available. The job must be in “Created” state.
export_activity_file: Returns the file content of an export job. The export job must be in "Completed" state.
export_activity_job: Returns a list of export jobs that were created in the past 7 days.
get_status_export_activity_job: Returns status of an export job. Job status is available for 30 days after Completed or Failed status was reached.
cancel_export_activity_job: Cancels the export job.

Lead

Marketo bulk extract Lead data sources and views

create_export_lead_job: Creates a job for exporting marketo leads. The parameter startat defines the time when the job starts and the endat parameter, the time when the job finishes.
enqueue_export_lead_job: Enqueue export job. This will place export job in queue, and will start the job when computing resources become available. The job must be in “Created” state.
export_lead_file: Returns the file content of an export job. The export job must be in "Completed" state.
get_export_lead_jobs: Returns a list of export jobs that were created in the past 7 days.
get_status_export_lead_job: Returns status of an export job. Job status is available for 30 days after Completed or Failed status was reached.
cancel_export_lead_job: Cancels the export job.

Program

Marketo bulk extract Program data sources and views

create_export_program_job: Creates a job for exporting marketo programs. The parameter startat defines the time when the job starts and the endat parameter, the time when the job finishes.
enqueue_export_program_job: Enqueue export job. This will place export job in queue, and will start the job when computing resources become available. The job must be in “Created” state.
export_program_file: Returns the file content of an export job. The export job must be in "Completed" state.
get_export_program_jobs: Returns a list of export jobs that were created in the past 7 days.
get_status_export_program_job: Returns status of an export job. Job status is available for 30 days after Completed or Failed status was reached.
cancel_export_program_job: Cancels the export job.