Marketo Period Cost Updates via API

If getting Marketo period costs data and calculating cost per lead is something you are interested in doing but have never done before, you might be worried about the prospect of having to manually update hundreds of Marketo programs with multiple costs for each month they were in existence. No need to fear the Marketo REST API is here!

This post will walk you through how to collect all your advertising costs in the correct format, map them to Marketo programs, and then import these program names and costs into a Python script to automatically update each program with all historical costs.

Then once the backdating of Marketo period costs is complete you can follow the Zapier Nested Looping using Webhooks & Python post to see how to automatically update Marketo period costs every month using Zapier and Google Sheets.

N.B. If it is your first time using the Marketo REST API or you need a quick refresher then check out the Marketo API Quick-Start Guide to see how to make your first Marketo REST API requests in Postman before transitioning to making requests in code or in the Zapier automation tool.

Marketo Period Cost Updates via API Walkthrough Video

Marketo Period Costs Introduction

Every program in Marketo has a “Period Cost” attribute, which allows you to input marketing costs (advertising, events, offers, etc) for each month the program is live and generating leads. Thus, with the number of leads generated by the program and how much it cost to generate these leads, a cost per lead can be calculated.

To get you started with Marketo period costs and what insight they can help derive here are 3 short Marketo product docs to get you started:

1. Using Period Costs in a Program
2. Understanding Period Costs
3. Understanding the Program Cost Analysis Area

Marketo Period Costs Google Sheet Setup

Mapping Ad Campaigns to Marketo Programs

To begin the program cost update journey you will need a way to map marketing spend to the Marketo program that was set up to track leads created from these marketing initiatives. In this example, advertising spend per campaign per month is imported into the Spend by Campaign tab of the “Marketo Period Costs Update” sheet along with the utm campaign parameter that was appended to all links from the ad that point to our site.

On the Marketo side, a corresponding program with tracking campaigns was set up to check which leads visit the site with the utm parameters matching that of the ad campaign, thereby allowing the first touch attribution parameters of these leads to be set (see the Marketo UTM Tracking & Automation post to see how this is done).

All our Marketo programs were set up so that the utm parameters the program is set up to track are included in the program description. Thus, once the program names and descriptions have been imported into the Campaign-Program Mapping tab, the utm campaign parameter can be split from the “Program Description” field.

N.B. A CSV of Marketo programs with their ids and descriptions can be obtained in bulk by using one of the program query requests from the Marketo REST API library and then doing some parsing to isolate only the program names, ids, and their descriptions. Since you already have the program id you should replace the program name in the pivot table with the program id so that you will not need to make a request to get the program id in the Python script. When I was tasked with bulk updating these Marketo period costs I was already given a list of program names and descriptions so I had to get the program ids using the REST API within the Python script (see below).

Then in the Spend by Campaign tab and in the “Matched Marketo Program” column a vlookup formula is used to take the utm campaign parameter, search for a match in the Campaign-Program Mapping tab, and then return the Marketo program name from the matching row.

=VLOOKUP("UTM Campaign",'Campaign-Program Mapping'!$A$2:$F,"Program Name" Column Number,false)

Creating a Program-Marketo Period Cost Pivot Table

Once the campaign costs have been imported for each month and matched with their corresponding Marketo program, a pivot table is then used to list out all the Marketo programs with their costs per month in the same row. The pivot table can be created easily by using Google sheet’s inbuilt pivot table function (see Pivot Table (GUI) ) or for the nerdy among us, below you can find a brief aside as to how you can build your own pivot table using just Google sheets formulas (see the output in Pivot Table (Formula) ).

Marketo Period Costs Bulk Update Python Script

Importing Marketo Period Costs Data and Getting Access Token

The first action in the “Marketo Period Costs Main” script is to read in the pivot table as a data frame using the Pandas library and then the code below is used to create a list where each index contains a series of (date, cost) pairs for each Marketo program.

iterrows = list( df.iterrows() )

Before looping through the programs in the pivot table, it is necessary to get an access token from Marketo and ensure that there are more than 60secs left on the token life so that there is enough time to update the Marketo period costs in a single iteration of the loop. This is achieved using nested while loops with the inner while loop checking that token life is greater than 60secs before every run and if this is not true the outer while loop will wait until the access token has expired and then get a new access token with 3600secs of life.

The getToken function is used to make a get request to the token endpoint, passing the client id and client secret, to return the API access token and the length of time that this token will be valid for.

Appending to the Marketo Period Costs List

Inside the inner while loop, each index of the iterrows list is brought into a for loop where the (date,cost) pairs for a Marketo program are assessed.

IF the date is the “Marketo Program” header (this will always be the case for the first (date,cost) pair of an iterrows index) then a call will be made to the getProgramByName function passing the program name to get the program’s information.

• IF the program was found successfully then the program id and its created date are parsed out from the response and the day of the created_at date is set to 1 for later comparison with the input date values from the pivot table.
• ELSE the program name was not found then the script will break from the for loop and the next iteration of the loop will proceed to assess the next Marketo program.

ELSE for all subsequent (date,cost) pairs date will be one of the date headers and if the cost for this header is not null and the Marketo program existed before this date or was created in the same month then append the (date,cost) pair to the costs list. If there was spend for a paid campaign in months before the corresponding Marketo program was set up e.g in June, then you do not want to add the program costs for these prior months because they will incorrectly drive up the cost per lead for the leads who became members of this program from June onwards. Once all the (date,cost) pairs have been collected a single API call will be made to upload them to the Marketo program.

Updating Marketo Period Costs

IF the costs list length is greater than 0 after iterating over all the (date,cost)pairs for a particular Marketo program i.e. it has been populated with at least one (date,cost) pair,  then the program id and costs list are passed to the updateProgram function, which makes a call to the Marketo REST API update program endpoint. 

• Additionally the costsDestructiveUpdate parameter is passed to the updateProgram function, which when set to TRUE will clear out any period costs that are stored in the program and replace them with the Marketo period costs in the costs list, which is desired since the costs in the (date,cost)pairs are the ad spend for the entirety of the months. Setting costsDestructiveUpdate equal to TRUE is also better for debugging and rerunning this code so after a lot of testing you do not have to delete a lot of costs from programs that were part of the test.
• If you would like to preserve the existing costs in the program then set costsDestructiveUpdate equal to FALSE then the incoming Marketo period costs in the costs list will be appended to the existing costs, and all the costs that now exist for a month will be summed and used to get the cost per lead for that month.

N.B. There is also the option to clear all costs from a program by setting costDestructiveUpdate equal to TRUE without passing a costs parameter. In order to account for the scenario where a cost parameter may or may not be passed to the updateProgram function the kwargs magic variable is used to pass keyworded arguments to the function. Thus, the costs and costDestructiveUpdate parameters can then be accessed inside the function and their values stored in the payload by referencing their respective keys via kwargs.

ELSE the costs list has not been appended with any (date,cost) pairs, meaning that the Marketo program was not found (and the break call was made inside the for loop) or there are no costs to update. This can happen either because there simply are no costs for that campaign or all costs for a particular campaign were before the Marketo program was created.

Finally, at the end of the inner while loop, a 0.2sec delay is implemented so that Marketo’s REST API rate limit of 100 calls in 20secs is not exceeded. Then the remaining time on the token is calculated and if this time is greater than 60secs and there are still more indices of iterrows to iterate through then the next iteration of this inner while loop will proceed.

If the remaining time is below 60secs, the script breaks out into the outer while loop and then waits for this remaining time to elapse before requesting a new access token and diving back into the inner while loop. This process will be repeated until all the rows from the pivot table have been iterated over and the associated Marketo period costs updated.

Checking Output Log of Marketo Period Costs Updates

Before entering the outer while loop and beginning program updates, a timestamped log file is created and subsequently updated inside the inner while loop to record information for each row of the pivot table and whether processes like finding and updating the program were successful. You can see a sample output file of this log script here.

I recommended that you check a few of the rows of this log once your script has finished successfully and make sure that the Marketo period costs updated correctly by comparing the (startDate,cost) pairs in the log to the (date,cost) pairs for the program in the pivot table.

What’s Next?

Now that you have gotten a taste for the Marketo REST API and have seen how it can save you time, take a look at these other posts to get more ideas for things you can automate:

• Marketo UTM Tracking & Automation: Streamline the process of creating UTM parameters and tracking these parameters in Marketo using Google Forms, Google Sheets, and the Marketo REST API.
• Marketo Dynamic Email Content via API: Learn how to create and populate emails from Google Sheet templates using the Marketo API.
• Marketo Merge Leads in Bulk using the API: Merge leads in bulk with Python code that will allow you to set custom rules for how you want to prioritize conflicting values for the same fields on different leads.

As always if you are enjoying this content and want to get an email when I launch a new post then hit that shiny, pink, subscribe button at the top of the page 🙂