App Connectors: Marketo

👤 This documentation is intended for Site Administrators and/or Database Administrators.

Connectors are Periscope Data’s built-in mechanisms for customers to connect to and ingest from popular data sources.

Note: Connectors are supported for customers with Data Engine on your Warehouse or Warehouse infrastructure. Site administrators can contact their Account Manager if interested in enabling Connectors.

<div>
<UL>
<LI><a href="#PreparingaConnector">Preparing to add a Connector</a></LI>
<LI><a href="#AddingaConnector">Adding a Connector</a></LI>
<LI><a href="#SetUpIngestion">Set Up Ingestion</a></LI>
<UL>
<LI><a href="#SetupDatabasePermissions">Setup Database Permissions</a></LI>
<LI><a href="#HowToSetUpIngestion">How To Set Up Ingestion</a></LI>
<LI><a href="#SuspendIngestionforaConnector">Suspend Ingestion for a Connector</a></LI>
</UL>
<LI><a href="#DeleteaConnector">Delete a Connector</a></LI>
<LI><a href="#SupportedConnectors">Supported Connectors</a></LI>
<LI><a href="#MarketoTableSchemas">Marketo Table Schemas</a></LI>
</UL>
</div>
<HR>

<a name="PreparingaConnector"></a>

Preparing a Connector

To set up Marketo in Periscope, please make sure to have the following:

  • Admin permissions in Marketo. Marketo Admin permissions are required to complete portions of the setup process.

Prior to set up,  monitor the Marketo API call usage if other applications are also connected to the Marketo account. Ingestion may be impacted if numerous applications are using the API.

Step 1: Create an API-Only User Role in Marketo
  1. Sign into the Marketo account.
  2. Click the 'Admin' option.
  3. Under Admin, open the Security menu.
  4. Click 'Users & Roles'.
  5. Click the Roles tab.
  6. Click 'New Role'.
  7. In the Create New Role window, do the following:
  • Role Name - Enter a name for the role. If it’s specific to Periscope, make the name specific - something like “Periscope API Role.”
  • Description - Enter a description.
  • Permissions - Click the checkbox next to the 'Access API' option.

      8. Click 'Create'.

 

Step 2: Create a Periscope Marketo API User

Creating a Periscope-specific user ensures that Periscope is easily distinguishable in any logs or audits.

  1. Click the 'Admin' option.
  2. Under Admin, open the Security menu.
  3. Click 'Users & Roles'.
  4. In the Users tab, click 'Invite New User'.
  5. In the INFO section, enter an email address as well as first and last name.
  6. Click 'Next'.
  7. In the PERMISSIONS section, click the checkboxes next to the API User Role created and the API Only option.
  8. Click 'Next'.
  9. In the MESSAGE section, click the 'Send' button to create the user.
Step 3: Create an API Custom Service in Marketo

To generate the API credentials needed to connect Periscope to Marketo, an API Custom Service must first be created. It is necessary to associate this with the Periscope API user.

  1. In Admin, open the Integration menu.
  2. Click 'LaunchPoint'.
  3. Click 'New' and then 'New Service'.
  4. In the New Service window, do the following:
  • Display Name - Enter “Periscope”.
  • Service - Select 'Custom' from the dropdown.
  • Description - Enter a description.
  • API Only User - Select the Periscope user that was created.

        5. Click 'Create'.

        6. After the service is created, it will display in the Installed Services grid. Click the 'View Details' link to display the API credentials.

        7. Copy the Client ID and Secret into a text file.

 

Step 4: Whitelist Periscope's IP Addresses in Marketo

Completing this step is required only if IP Restriction is enabled in Marketo. Check to see if this setting is enabled by clicking Admin > Web Services and looking in the IP Restrictions section. If this setting isn’t enabled, skip to the next step.

  1. In the Integration menu, click 'Web Services'.
  2. In the IP Restrictions section, click the 'Edit' button.
  3. In the Allowed Addresses field, paste one of the IP addresses listed below and then click Add.
  • 52.23.137.21/32
  • 52.204.223.208/32
  • 52.204.228.32/32
  • 52.204.230.227/32

        4. Repeat step 4 until all the Periscope IP addresses are added.

        5. Click the 'Save' button.

Leave the Web Services page open - this will be used in the next step.

Step 5: Retrieve Marketo REST API Base URLs
  1. On the Web Services page, scroll down to the REST API section.
  2. In this section, find the Endpoint and Identity fields.
  3. Copy these URLs into the text file with the Client ID and Client Secret

<a name="AddingaConnector"></a>

<a href="#top">Back to top</a>

Adding a Connector

To add a Connector, administrators can first click Settings menu in the bottom left hand corner.

Then, click the App Connections option:

In the top right corner, click the green Connect Source button.


From the Data Source Type dropdown, select the 'Marketo'' option:

Enter the display name for the Connector in the Display Name section of the Connect Source menu, then enter the additional requested information in spaces provided and click 'Add'.


The process of establishing a new connection will take a few minutes as all of the tables that are associated with that source are being discovered. During this time a spinning Periscope Data icon will appear.


Available tables from the connected data source will appear as they become available.

In the event that the connection was unsuccessful, a Connection failed screen will appear. Please verify that the information entered for the Marketo connector is correct.

<a name="SetUpIngestion"></a>

<a href="#top">Back to top</a>

Setup Ingestion

There are no specific table permissions that need to be set for Marketo. However, the following are specific to the Marketo Connector:

Daily REST API call limits

By default, all Marketo accounts have a maximum number of 50,000 daily account calls. Periscope’s Marketo integration is designed to use up to 40,000 of these calls per day.  The API call limit can be adjusted when configuring the integration.

When the Max Daily API Calls limit has been reached, Periscope will be unable to ingest any Marketo data until more API quota is available. If the 50,000 total call limit isn’t enough, contact Marketo support to inquire about raising the limit.

Activities and Leads ingestion

To efficiently ingest activity and lead data, Periscope’s Marketo integration uses the Bulk API to extract data.

Leads ingestion and Marketo Corona

To incrementally ingest leads data, Marketo requires the authorizing account to have a feature called Corona. Corona allows Periscope to use an updatedAt query parameter to extract only new and updated data from the leads endpoint. Without Corona, each sync of the Leads table requires a full export which can lead to lower data freshness. Please contact Marketo to request Corona support be added to the account for the best experience.

Bulk API Limits

Part of the extraction process using the Bulk API involves writing and downloading a file of the extracted data. Periscope then pushes the data from this file into the destination.

Marketo currently limits the amount of data pulled on a daily basis to 500MB. Exceeding the limit will pause ingestion until midnight CT, when it will be possible to resume.

Note: This applies to the activity_[activity_types] and leads tables and is a separate quota from the REST API call limits mentioned previously

<a href="#top">Back to top</a>

<a name="SetUpDatabasePermissions"></a>

Setup Database Permissions

In order to ingest data into Periscope, certain database permissions will need to be updated the first time a connector is added.

Periscope Warehouse:

Periscope will create a new database user and grant the necessary permissions to load data into the warehouse from the connected Sources. Periscope will also grant the Read User the necessary schema permissions in order to query the sources' data from within Periscope.

Data Engine on Your Warehouse:

In order to ingest data into the Redshift or Snowflake warehouse using Periscope App Connectors, Periscope requires a database user that can perform write operations on the database. Create permissions are required to create the necessary database objects to load and store the data. Read permissions on system tables are required to validate the existence and structure of existing database objects.

Please grant the following privileges to the database user that will be used for the App Connectors function:

Snowflake:

<body>
<blockquote>
<br>GRANT CREATE ON WAREHOUSE warehouse_name TO periscope_ingest;
<br>GRANT CREATE ON DATABASE database_name TO periscope_ingest;
</blockquote>
</body>

Redshift:

<body>
<blockquote>
<br>GRANT CREATE ON DATABASE database_name TO periscope_ingest;
<br>GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO periscope_ingest;
<br>GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO periscope_ingest;
</blockquote>
</body>

The above permissions can be added to the existing Warehouse Admin created for Data Engine, but Periscope recommends providing an additional user to perform the App Connectors function. This will ensure that one service account is not responsible for too many actions.

To query the data imported into the warehouse using the Connectors feature, it is also necessary to grant query permissions on the newly-created schema and its tables to the read user. The name of the schema will be the Display Name of the connected Source. After the first replication job has been completed, run the following commands to grant permissions to query the schema from within Periscope:

Snowflake:

<body>
<blockquote>
<br>GRANT USAGE ON SCHEMA database_name.schema_name TO ROLE periscope_read;
</blockquote>
</body>

Redshift:

<body>
<blockquote>
<br>GRANT USAGE ON SCHEMA schema_name TO periscope_read;
<br>GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO periscope_read;
</blockquote>
</body>

Note: The following IPs will need to be whitelisted for access to the destination database server:

  • 52.23.137.21/32
  • 52.204.223.208/32
  • 52.204.228.32/32
  • 52.204.230.227/32

<a name="HowToSetUpIngestion"></a>

<a href="#top">Back to top</a>

How to Setup Ingestion

Tables listed for the connector are available for ingestion into the warehouse. To select a table for ingestion, click on the box to the left of the table’s name.

Once a table has been selected, a list of columns that are available for ingestion will appear “Columns Selected for Ingestion”.

To select specific columns for ingestion, click on the box to the left of the column’s name.

Once the desired tables and columns have been selected, click Save in the bottom right. To select an Update Interval, use the dropdown menu under Update Interval. Select the desired frequency for which the data should be replicated into the warehouse.

Use the Anchor Time dropdown menu to select an Anchor Time. This is the time that the ingestion job will start. Be sure to allow enough time for loading the data when setting this time. If an ingestion job is not complete before the next scheduled interval, the interval will be skipped.


The Fetch Records Newer Than field indicates the date from which the ingestion of the data into the warehouse should begin.  Select the fetch records date by clicking into the Fetch Records Newer Than box.  

Note: The fetch records date will default to one year in the past if a date is not manually selected. To select an Update Interval, use the dropdown menu under Update Interval. Select the desired frequency for which the data should be ingested into the warehouse. 

‍Note: The selected Update Interval and Fetch Records Newer Than date will apply for all tables and columns within a Connector that have been selected for ingestion and ingestion. Tables and columns within a given Connector cannot have differing Update Intervals and/or Fetch Records Newer Than dates.

Save the Update Interval and Fetch Records Newer Than data by clicking 'Save' in the lower right corner of the section. It will take a few minutes for the data to be ingested into the warehouse. After a few minutes, navigate to the Database Connections tab from the Settings menu.

Select the appropriate warehouse from the dropdown menu and click on the refresh icon next to the schema for the warehouse.

Once the schema has been refreshed, the source name that was entered when the Connector was set up will appear in the schema browser and data will now be queryable.

<a name="SuspendIngestionforaConnector"></a>

<a href="#top">Back to top</a>

Suspend Ingestion for a Connector

In the event that ingestion for a Connector should be suspended, please select the Connector from the dropdown menu within the Ingestion page and toggle on Suspend Ingest from the Update Interval menu. Lastly, please click 'Save' within this menu to save the changes made.

Note: It may be necessary to scroll within the menu to see this option.

<a name="DeleteaConnector"></a>

<a href="#top">Back to top</a>

Delete a Connector

To delete a Connector, navigate to the App Connections page from the Settings menu and select the name of the Connector to be deleted from the dropdown menu.

Click 'Delete' in the bottom left:

In the Delete Source window, follow the instructions provided by typing the name of the Connector to be deleted in the field provided.

Once the name of the Connector has been entered, the Delete button will become available. Click the 'Delete' button to delete the Connector. Note: This action cannot be undone.

<a name="SupportedConnectors"></a>

<a href="#top">Back to top</a>

Supported Connectors

Periscope Data currently supports the below Connectors:

The below list details future Connectors:

  • Google Sheets
  • Google Analytics

<a name="MarketoTableSchemas"></a>

<a href="#top">Back to top</a>

Marketo Table Schemas

Primary keys noted in bold.

<details>
<summary><b><big>activities_[activity_type]</big></b></summary>
<body>
<table style="border-collapse:collapse;">
<tr>
<th style="width:200px;text-align:left">Column</th>
<th style="width:200px;text-align:left">Data-Type</th>
<th style="width:200px;text-align:left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><b>marketoguide</b></td>
<td>STRING</td>
<td>The ID of the activity.</td>
</tr>
<tr>
<td>activityDate</td>
<td>DATE-TIME</td>
<td>The date of the activity.</td>
</tr>
<tr>
<td>leadId</td>
<td>INTEGER</td>
<td>The ID of the lead associated with the activity.</td>
</tr>
<tr>
<td>activityTypeId</td>
<td>INTEGER</td>
<td>The ID of the activity type.</td>
</tr>
<tr>
<td>primary_attribute_value_id</td>
<td>INTEGER</td>
<td>The ID of the activity’s primary attribute.</td>
</tr>
<tr>
<td>primary_attribute_name</td>
<td>STRING</td>
<td>The name of the activity’s primary attribute.</td>
</tr>
<tr>
<td>primary_attribute_value</td>
<td>STRING</td>
<td>The value of the activity’s primary attribute.</td>
</tr>
</table>
</body>
</details>

For every activity type defined in the Marketo account, there is an activities_[activity_type] table in the “tables” tab in the app connectors workflow. For example: activities_click_email contains lead activity data for the click_email activity type.

These tables will contain the fields listed below, along with any fields specific to that activity type.

<details>
<summary><b><big>Activity_types</big></b></summary>
<body>
<table style="border-collapse:collapse;">
<tr>
<th style="width:200px;text-align:left">Column</th>
<th style="width:200px;text-align:left">Data-Type</th>
<th style="width:200px;text-align:left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><b>id</b></td>
<td>INTEGER</td>
<td>The activity type ID.</td>
</tr>
<tr>
<td>name</td>
<td>STRING</td>
<td>The name of the activity type.<br>Ex:Change Nurture Track</td>
</tr>
<tr>
<td>description</td>
<td>STRING</td>
<td>The description of the activity type.</td>
</tr>
<tr>
<td>primaryAttribute</td>
<td>OBJECT</td>
<td>Primary attributes of the activity type.</td>
</tr>
<tr>
<td>attributes</td>
<td>ARRAY</td>
<td>Secondary attributes of the activity type.</td>
</tr>
</table>
</body>
</details>

The activity_types table contains metadata about the activity types - form fill, web page visit, lead creation, and so on - available in Marketo.

<details>
<summary><b><big>Campaigns</big></b></summary>
<body>
<table style="border-collapse:collapse;">
<tr>
<th style="width:200px;text-align:left">Column</th>
<th style="width:200px;text-align:left">Data-Type</th>
<th style="width:200px;text-align:left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><b>id</b></td>
<td>INTEGER</td>
<td>The ID of the campaign.</td>
</tr>
<tr>
<td>updatedAT</td>
<td>DATE-TIME</td>
<td>The time the campaign was most recently updated.</td>
</tr>
<tr>
<td>createdAt</td>
<td>DATE-TIME</td>
<td>The time the campaign was created.</td>
</tr>
<tr>
<td>active</td>
<td>BOOLEAN</td>
<td>Indicates if the campaign is active. Applicable only to trigger campaigns.</td>
</tr>
<tr>
<td>description</td>
<td>STRING</td>
<td>The description of the Smart campaign.</td>
</tr>
<tr>
<td>name</td>
<td>STRING</td>
<td>The name of the Smart campaign.</td>
</tr>
<tr>
<td>programId</td>
<td>INTEGER</td>
<td>The ID of the parent program.</td>
</tr>
<tr>
<td>programName</td>
<td>STRING</td>
<td>The name of the parent program.</td>
</tr>
<tr>
<td>type</td>
<td>STRING</td>
<td>The type of Smart campaign.<br>Possible values arebatchortrigger.</td>
</tr>
<tr>
<td>workspaceName</td>
<td>STRING</td>
<td>The name of the parent workspace.</td>
</tr>
</table>
</body>
</details>

The campaigns table contains info about the campaigns in the Marketo account.

<details>
<summary><b><big>Leads</big></b></summary>
<body>
<table style="border-collapse:collapse;">
<tr>
<th style="width:200px;text-align:left">Column</th>
<th style="width:200px;text-align:left">Data-Type</th>
<th style="width:200px;text-align:left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><b>id</b></td>
<td>INTEGER</td>
<td>The ID of the lead.</td>
</tr>
<tr>
<td>acquiredBy</td>
<td>BOOLEAN</td>
<td>Indicates if the lead was acquired by the parent program.</td>
</tr>
<tr>
<td>isExhausted</td>
<td>BOOLEAN</td>
<td>Indicates if the lead is currently exhausted in the stream</td>
</tr>
<tr>
<td>membershipDate</td>
<td>DATE</td>
<td>The date the lead first became a member of the program.</td>
</tr>
<tr>
<td>nurtureCadence</td>
<td>STRING</td>
<td>Cadence of the parent stream</td>
</tr>
<tr>
<td>progressionStatus</td>
<td>STRING</td>
<td>The program status of the lead in the parent program.</td>
</tr>
<tr>
<td>reachedSuccess</td>
<td>BOOLEAN</td>
<td>Indicates if the lead is in a success-status in the parent program.</td>
</tr>
<tr>
<td>reachedSuccessDate</td>
<td>STRING</td>
<td>The date the lead reached success in the parent program.</td>
</tr>
<tr>
<td>stream</td>
<td>STRING</td>
<td>The stream that the lead is a member of, if the parent program is an engagement program.</td>
</tr>
</table>
</body>
</details>

The leads table contains info about Marketo leads.

Marketo Corona and ingestion  Method for Leads

Periscope ingests leads from Marketo using the Bulk API. The ingestion method for this table will vary depending on the Marketo account setup:

If Corona is enabled, this table will use Incremental ingestion  based on an updatedAt timestamp included in the API query.
If Corona isn’t enabled, this table will use Full Table ingestion.

<details>
<summary><b><big>Lists</big></b></summary>
<body>
<table style="border-collapse:collapse;">
<tr>
<th style="width:200px;text-align:left">Column</th>
<th style="width:200px;text-align:left">Data-Type</th>
<th style="width:200px;text-align:left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><b>id</b></td>
<td>INTEGER</td>
<td>The ID of the list.</td>
</tr>
<tr>
<td>updatedAt</td>
<td>DATE-TIME</td>
<td>The datetime when the list was most recently updated.</td>
</tr>
<tr>
<td>name</td>
<td>STRING</td>
<td>The name of the list.</td>
</tr>
<tr>
<td>description</td>
<td>STRING</td>
<td>The description of the list.</td>
</tr>
<tr>
<td>programName</td>
<td>STRING</td>
<td>The name of the program associated with the list.</td>
</tr>
<tr>
<td>workspaceName</td>
<td>STRING</td>
<td>The name of the parent workspace, if applicable.</td>
</tr>
<tr>
<td>createdAt</td>
<td>DATE-TIME</td>
<td>The datetime the list was created.</td>
</tr>
</table>
</body>
</details>

The lists table contains info about the static lists in the Marketo account.

Note: Due to some of the limitations in Marketo API, only static lists will be ingested.

<details>
<summary><b><big>Programs</big></b></summary>
<body>
<table style="border-collapse:collapse;">
<tr>
<th style="width:200px;text-align:left">Column</th>
<th style="width:200px;text-align:left">Data-Type</th>
<th style="width:200px;text-align:left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><b>id</b></td>
<td>INTEGER</td>
<td>The ID of the program.</td>
</tr>
<tr>
<td>updatedAt</td>
<td>DATE-TIME</td>
<td>The time the asset was most recently updated.</td>
</tr>
<tr>
<td>createdAt</td>
<td>DATE-TIME</td>
<td>The time the asset was created.</td>
</tr>
<tr>
<td>description</td>
<td>STRING</td>
<td>The description of the asset.</td>
</tr>
<tr>
<td>name</td>
<td>STRING</td>
<td>The name of the asset.</td>
</tr>
<tr>
<td>url</td>
<td>STRING</td>
<td>The URL of the asset in the Marketo UI.</td>
</tr>
<tr>
<td>type</td>
<td>STRING</td>
<td>The type of program.</td>
</tr>
<tr>
<td>channel</td>
<td>STRING</td>
<td>The channel of the program.</td>
</tr>
<tr>
<td>status</td>
<td>STRING</td>
<td>The status of the program. Only applicable to email and engagement programs.</td>
</tr>
<tr>
<td>workspace</td>
<td>STRING</td>
<td>The name of the workspace.</td>
</tr>
<tr>
<td>folder</td>
<td>OBJECT</td>
<td>Details about the asset’s parent folder.</td>
</tr>
</table>
</body>
</details>

The programs table contains info about the Marketo programs. Programs can be parents to most types of assets in Marketo and allow for tracking membership and success of leads.

<a href="#top">Back to top</a>


Our support team is ready to help