Using the new REST API Marketo Connector in Alteryx

“Why would I need to use a Marketo connector in the first place? The reports, called Smart Lists I can whip together in Marketo in a matter of seconds should be good enough? They handle large volumes of transactions pretty well, don’t they?”

Some businesses find Marketo very helpful to grow. Others use Marketo to Microsoft Dynamics CRM integration to help boost their business practices. And a few businesses won’t find Marketo helpful at all. It’s hard to know where you stand until you give it a go so I’ll respond to those objections that lists have a very limited purpose for business. They are more of an operational nature. They won’t serve well the need of tracking trends over time, which requires more altitude. Trend views should display aggregates, which do require transaction level data underneath. Hence the need to extract those transactions from Marketo, cleanse and assemble in Alteryx, and serve in aggregates in Tableau for analysis.

Should you still be not sold on the necessity to extract Marketo transaction to a data warehouse, just try to build a report in Marketo showing counts of unique activities, instead of a count of leads who performed the said activities. I haven’t found a way yet… Let’s take a simple example: it’s planning season and you need to set new targets, and obviously you need to establish a baseline from past performance. You might get asked something as simple as:
how many web forms were filled last year?
Marketo will reduce this inquiry to:
How many unique (to the best of Marketo’s knowledge) leads have filled a form last year?
It gets worse if you consider the issue of leads with multiple activities, which is not a far fetched scenario: a lead might visit your website more than once, at least I hope so, for the sake of your business… Let’s illustrate with Lead XYZ who filled out a form in February, one in March and one in June. Well, a Marketo report against the Lead database will give you different counts according to the time slice you select: 1 form filled for the Year, 2 if you break it down by Quarter (1 for Q1, 1 for Q2) and 3 if you break it down by Month!
To avoid those issues, you need to extract activities at the transaction level, and THEN tie them to their lead record, outside of Marketo…

With the newly released Alteryx V10, comes an eagerly expected new version of the Marketo Connector. The now deprecated earlier version of the connector was based on Marketo’s SOAP API. In the context of Marketing analytics, that connector worked, but too slowly to handle data volume, as a SOAP API is designed for a more transactional purpose, imposing rate limits of 100 API calls in a 20 second window. Any live Marketo instance’s daily volume of transaction will easily break those limits, which is why api testing is also vital in these kinds of circumstances.

The version of the connector delivered with Alteryx V10 is based on Marketo’s REST API, widely more adequate for manipulating volumes of transactions. Here is a handy guide to help your Marketo Admin generate connection credentials you will need, to use the connector.

Now Marketo’s REST API is not perfect, yet. The most glaring limitation is that it does not feature (yet?) a DateTo field to select transactions. This creates head scratchers for anyone who needs to refresh data sets in their data warehouse without reloading the whole history. Especially since Marketo is restricting contractually the number of API calls you can make against their API, and will charge you accordingly. I will hopefully contribute to alleviate that issue demonstrating how it can be done with an Alteryx workflow or an ETL.

The approach consists in creating first a baseline with the required history depth, and then a delta mechanism to append new transactions to the baseline, and therefore extract activity transactions only once. This is also a good exercise to understand hands on how the Marketo API connector works.

Step 1: Baseline Workflow

This workflow will build the baseline covering the history needed for your report. Let’s say your report tracks monthly number of forms filled in Marketo, and you build it in October, then you will need to extract all the activity transactions from January 1 to October:
Here is how the first Marketo connector, called Marketo Input, is configured to get the activities:

Note that each activity comprises of 5 rows (another way to increase the # of API calls to be invoiced?), and only 1 is relevant to the analysis, so we filter right away to retain only the Referrer URL and discard the other 4 lines… We would not want to run 5 times the next Marketo connector when once is enough…
Then I placed a Select Tool to rename the id field into FormID, less vague:
I then have a Formula tool, which creates a time stamp of when the workflow was last run, and therefore when the data was pulled, called Last Load Time, using the simple DateTimeStart() function.
Here is the second Marketo Connector configuration, called Marketo Append, which appends lead attributes to the activities which were pulled:

Note that we use the leadID field provided by the first Connector to feed the Append Connector, and then select which of the available attributes you need using the Output field drop down, which can be very long…
Then all it does is to write the baseline to a local database, of Alteryx type (yxdb) for convenience, but you can pick any type really…

Step 2: Delta Workflow

The goal of that workflow, admittedly more complex, will be to append the new transactions that occurred since you built your baseline, or later on, new transactions since you last ran your Delta, without re-extracting the transactions you already have pulled.

You can get started by saving your Baseline Workflow As a Delta workflow, since we will re-purpose quite a few object used for the baseline. There are 2 branches in the workflow, one for Baseline and one for New Transactions.
The Baseline is a simple dump which will unite with the new transactions through a Union tool.
The new Transactions start with a read from the same baseline DB, but will only select the Last Load Time field, sort it and pick the first 1 using the Sample tool, to obtain a unique DateTime indicating when the last pull was done, which we will feed to the Marketo Input Connector through a Macro:
The Macro is using the original connector you used in the Baseline workflow and will be controlled through a Control Parameter tool and an Action tool, which you need to configure exactly that way:
I need to stress that the first line must be highlighted for the date to pass through to the connector. The additional Actions are optional but let you use different credentials for the Connector should you need. Once you save that Macro as a Alteryx Macro (yxmc), you can insert it in your Delta workflow, and then proceed the Output as we did in the Baseline, namely filter the 4 unwanted row types:
stamping the load time for these new transactions and Union them with the baseline.
Then we put a Block Until Done tool so that the whole DB gets processed, before we drop it and rewrite it inclusive of all the new transactions. We can then proceed to export it to a TDE file for Tableau.
And you are done, you can schedule that job to run every day, or every hour, to update your data set swiftly, without running out of API calls! You will surface powerful new insights which were not visible within Marketo…

You will need to build your baseline database to get started, with all the transactions history needed. Here too, you will most likely run into the limits of the daily REST API calls allowed by Marketo, based on what you pay for in your contract with them. And here to there is a way to circumvent the issue! You can use the deprecated version of the Alteryx connector which came with V9, based on the SOAP API, which is not subject to those API calls restrictions. It is slow but will get you there, and you only need to run it once, for your initial load. It only takes one click to get to use the deprecated version of the Marketo connector:

1. Go to the Connectors tab in Alteryx V10 and right click in the tool bar, and select the only option:
   
2. Next to the V10 version of the REST API Marketo connector will appear the Deprecated V9 based on SOAP API: