Marketo Bulk API Quick-Start Guide

Ever made a big MOOPs in Marketo where a field value was incorrectly changed to the wrong value and you want to change it back to the original? Well the Marketo Bulk API, Postman, and Google Sheets can help you fix it!

Before we get started here take a look at the Marketo API Quick Start Guide to see how to set up your Marketo environment and variables in Postman and how to make your first Marketo API GET & POST requests. While you’re here also fill out the form to receive a copy of the Marketo API Postman Collection that I created, which contains 100+ pre-configured Marketo API requests to get you started.

While the guide linked above contains everything you need to get started before tackling the Marketo Bulk API if you want to get hands-on exposure to the Marketo API and see how it can be used for automating processes then take a look at the Marketo REST API Crash Course 🙂

Additionally Lesson 5 of the course “An Introduction to the Bulk Extract API” will walk you through making the API requests mentioned in this blog post using Postman so you can fully understand these requests before embedding them in an automation workflow.

Marketo Bulk API Walkthrough Video

Marketo Bulk API Introduction

Have you ever wondered how business intelligence tools like Domo, PowerBI, and Tableau pull in the plethora of lead and activity data from your Marketo instance? You guessed it! The Marketo Bulk API and in particular the Marketo Bulk Extract API.

The Marketo Bulk API is a powerful means to import or export large amounts of data into or from Marketo for leads, activities, custom objects, and program members. In this post, we will be focusing on the Marketo bulk extract API, however, if you are interested in the bulk import API then still tune in because the setup and job flow for the bulk import API are very similar and all the Postman steps below will still be very useful to you.

The Marketo Bulk API works with a queue system to extract information from Marketo, where new jobs are added to the end of a queue and will be completed after the jobs already in the queue are completed i.e. first in – first completed.

Every Marketo Bulk API flow can be broken down as follows:

1. Create a job
2. Put the job in the queue
3. Query the job status periodically until the status is marked as complete
4. Extract the job data

Marketo Bulk API Activity Extract Job Flow

We will be using the all-too-familiar example of needing to revert a Marketo field back to a previous value for a large number of leads because it was accidentally changed (I know we’ve all been there!). I’m sure if you have ever manually had to go through the Marketo logs to see what the previous value was for each lead in a list and manually update it, you will be bouncing up and down with excitement once you see how the Marketo bulk extract API can make this a breeze!

The TLDR summary of the scenario described in the YouTube video above is that the “Behavior Score – 7 Day History” field in our instance was incorrectly changed for a lot of people by the “Trim BS7DH” webhook.

N.B. If you want to avoid fields being overwritten incorrectly by webhook responses then check out the Marketo Webhook Guide with Examples post to see how you can use intermediary fields to store the webhook response attributes and only overwrite desired fields if these response attributes have acceptable values.

Therefore we want to create a Marketo bulk API job to extract the activity data for the “Change Data Value” activity for the “Behavior Score – 7 Day History” field in the time window that we know the campaign ran.

N.B. If you haven’t done so already go to the Marketo API Quick Start Guide and fill out the form to get your own copy of the Marketo API Postman Collection so that you can make the requests below right away without any setup needed.

Finding Activity IDs

To find the activity id for the “Change Data Value” activity, which we will need when creating the job, we need to make a GET request to the Activity Types endpoint and search the response returned to Postman.

Finding Field IDs

To find the field id for the “Behavior Score – 7 Day History” field, which we will need when creating the job, we need to make a GET request to the Describe Lead endpoint and search the response returned to Postman.

Creating a Job using the Marketo Bulk API

When creating a bulk activity extract job using the Create Job endpoint, the body of the request needs to contain the starting time point and ending time point for the time window you are interested in (the maximum span is 31 days, see the “Marketo Bulk API Limits” section below) along with the activity ids that you are interested in and if applicable you can filter even further by specifying field ids.

If a smart campaign was responsible for changing the field you want to revert to an original value then you can narrow down the time range using the “Results” tab of the smart campaign and using a view filter to only select the activity of interest (see the YouTube video above for a visual walkthrough of this).

N.B. The timestamps that you use for defining the time window must be in UTC time.

The response to this “create job” request then contains the job id in the “exportId” field. Since we will be using this job id in all subsequent Marketo bulk API requests we can create a new “job_id” variable in our Marketo Postman environment and automatically populate this variable by placing the code below in the “Tests” tab of the request.

var jsonData = pm.response.json();
pm.environment.set("job_id", jsonData.result[0].exportId);

Queueing a Job using the Marketo Bulk API

Once the job has been created the next step is to put the job in the queue (see job and queue limits in the “Marketo Bulk API Limits” section below) using the Enqueue Job endpoint, which uses the job_id environment variable we populated using the test code above.

Canceling a Job using the Marketo Bulk API

If for some reason you made a mistake and need to cancel a job then you can use the Cancel Job endpoint to do so.

Querying Job Status using the Marketo Bulk API

Next, you need to intermittently query the status of the job until the “status” field in the response says “completed”. Notice that the returned response also includes the file size in bytes of the data contained within the job.

Extracting Job Data using the Marketo Bulk API

Finally, you are ready to extract the job data and save the response to a CSV file that can be imported into Google Sheets so that the previous values for the field of interest can be extracted using Google Sheets formulas.

Extracting Previous Values Using Google Sheets

When you import the job data into Google Sheets you will end up with a tab similar to the Bulk Extract Data tab in the “Marketo Bulk API Extract Change Data Value” workbook.

Next, the “New Value” and “Old Value” values can be obtained by parsing the “attributes” column using the regexextract function as shown below.

New Value  = REGEXEXTRACT(attributes_value,"""New Value"":""(.*)"",""Old Value""")

Old Value = REGEXEXTRACT(attributes_value,"""Old Value"":""(.*)"",""Source")

Then in the example that I mention in the YouTube video, I wanted to identify all the people who had their “Behavior Score – 7 Day History” field incorrectly truncated by getting the length of the “Old Value” rows and filtering on those less than 5000 characters in length.

Once the affected people had been identified, the vlookup function was then used to pull in their email address because when importing lists into Marketo you cannot use the Marketo ID as the identifier, you need to use the email address instead.

As the “attributes” column shows, the “Reason” for the change data value action, in this case, was marked as “Webhook Updated Lead : Trim BS7DH” so in Marketo we can create a Smart List using this reason and the activity date to obtain a 2 column list of people affected, containing each person’s Marketo ID and Email Address. We can then import this information into the ID-Email Mapping tab and use them in our vlookup formula.

Then we can copy the “Email Address” and “Old Value” columns for the filtered rows into the Export tab, export this list, and then finally import this list into Marketo to revert these people’s “Behavior Score – 7 Day History” fields back to their original value.

Marketo Bulk Lead Export API

Exporting leads using the Marketo Bulk API follows the same flow as outlined in the “Marketo Bulk API Activity Extract Job Flow” section and as shown in the walkthrough video except that different endpoints are used:

1. Create a job using the /bulk/v1/leads/export/create.json endpoint
2. Queue the job using the /bulk/v1/leads/export/{exportId}/enqueue.json endpoint
3. Poll the job status using the /bulk/v1/leads/export/{exportId}/status.json endpoint until the returned status is marked as “Completed”
4. Get the leads data using the /bulk/v1/leads/export/{exportId}/file.json endpoint
5. If necessary you can cancel the job using the /bulk/v1/leads/export/{exportId}/cancel.json endpoint so that it does not use up your bulk extract quota unnecessarily

As shown in the docs linked above, when creating the job you can use date ranges (createdAt or updatedAt) or the names/ids of static or smart lists to select the leads you want to export. Then you must specify the REST API names of the fields you want to be included in the lead export. Optionally you can choose to rename the column headers and specify a different output file format.

What’s Next?

Now that you are well warmed up with using the Marketo bulk API take a look at how you can use the Marketo API to automate and streamline different workflows:

• Bulk Merge Marketo Duplicates according to your own custom rules
• Streamline your email creation process using Google Sheets templates and Zapier
• Automate your UTM creation and tracking using Google Forms, Google Sheets, and Zapier

And don’t forget if you want a 4hr crash course in how to use the Marketo API then check out the Marketo REST API Crash Course.

Marketo Bulk API Limits

Bulk Extract

• You are only allowed to have 10 jobs in the queue at once. Note that this queue is shared between the leads, activities, custom objects, and program member bulk APIs.
• Only 2 jobs can be in the “Processing” state at any one time
• The total amount of data that you can export from Marketo is limited to 500MB per day unless you have purchased a higher data limit. This 500MB limit resets daily at 12:00AM CST.
• The data extracted from a job will only be retained for 10 days
• When using the “createdAt” or “updatedAt” values to define a time window for your request the maximum width of this interval is 31 days

Bulk Import

• You are only allowed to have 10 jobs in the queue at once
• Only 2 jobs can be in the “Processing” state at any one time
• You are limited to importing a maximum file size of 10MB

Marketo Bulk API FAQs

Do I need special authentication to access the Marketo Bulk API?

No, you can use the same authentication that you normally use for the Marketo API to get an access token to use in subsequent Marketo Bulk API requests. You can then store this access token in your Marketo environment as a variable in Postman to make it easy to use in all other requests.

It is worth noting that only the API user who created the job can make requests related to that job e.g. querying status, retrieving data, etc.

How big are the files from the job?

The size of the job data will depend on the parameters you specify when creating the job e.g. the time frame or the number of activities or leads being exported. Once the “Query Job Status” request is complete it will show you the file size of the job in bytes so you then know before extracting the data how big the file will be.

Is there a maximum file size for job size?

No, there is no limit to the size of the job created when using the bulk extract API. The only constraint is that you are restricted to exporting a maximum of 500MB per day unless you purchased an additional quota. When using the bulk import API you are limited to importing 10MB at a time.

How can I tell how much of my daily 500MB extraction quota is left?

In order to tell how much of your Marketo bulk API extract quota you have used, you will need to make 4 requests to each of the Lead, Activity, Custom Object, and Program Member endpoints below to get a list of all jobs created within the last 7 days. Each of these jobs returned will contain a “filesize” attribute in bytes.

Start by filtering on jobs that have a “Completed” status and a “finishedAt” value with today’s date, remembering that these “finishedAt” values are in UTC time, and then sum all of these values for the current day to see how much of your 500MB quota is left.

• Get Lead Jobs within the past 7 days
• Get Activity Jobs within the past 7 days
• Get Program Member Jobs within the past 7 days
• Get Custom Object Jobs within the past 7 days

Can the Marketo Bulk API pull data from all of the workspaces in my instance?

Yes, the Marketo Bulk API pulls data from all of your workspaces and it is not possible to limit the scope of your request to a particular workspace.