If getting 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 programs with multiple costs for each month they were in existence. No need to fear the API is here!
This post will walk you through how to collect all your advertising costs in the correct format, map them to 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 costs is complete you can follow the Updating Costs using the API & Zapier post to see how to automatically update costs every month using Zapier and Google Sheets.
N.B. If it is your first time using the API or you need a quick refresher then check out the Quick-Start Guide to the API to see how to make your first API requests in Postman before transitioning to making requests in code or in the Zapier automation tool.
Also if you are looking for a course for learning the Marketo API then DM me 🙂
Introduction
Every program 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 costs and what insight they can help derive here are 3 short product docs to get you started:
Google Sheet Setup
Mapping Ad Campaigns to Programs
To begin the program cost update journey you will need a way to map marketing spend to the 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 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 UTM Tracking & Automation post to see how this is done).
All our 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 programs with their ids and descriptions can be obtained in bulk by using one of the program query requests from the 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 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 program name from the matching row.
=VLOOKUP("UTM Campaign",'Campaign-Program Mapping'!$A$2:$F,"Program Name" Column Number,false)
Creating a Program-Cost Pivot Table
Once the campaign costs have been imported for each month and matched with their corresponding program, a pivot table is then used to list out all the 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) ).
All the programs are brought into the left-hand column using a filter applied to the unique function to remove #N/A values (in the scenario where a program was not found for the ad campaign).
=filter(UNIQUE('Spend by Campaign'!E2:E),NOT (ISNA(UNIQUE('Spend by Campaign'!E2:E))) )
The program name is then concatenated with each of the date headers in succession and a vlookup formula for each concatenation is used to search for a match in the “Helper” column of the Spend by Campaign tab and then return the cost from the matching row. If the vlookup formula returns an #N/A value then the cell is left blank.
=if(ISNA(VLOOKUP("Marketo Program" & " " & "Date",'Spend by Campaign'!$B:$E,"Cost" Column Number,false)),,VLOOKUP("Marketo Program" & " " & "Date",'Spend by Campaign'!$B:$E,"Cost" Column Number,false))
Once this formula has been applied to every Marketo Program – Date combination then the data is ready for export as a CSV and processing by the Python script.
Bulk Updating Python Script
Importing Costs Data and Getting Access Token
The first action in the 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 program.
iterrows = list( df.iterrows() )
Before looping through the programs in the pivot table, it is necessary to get an access token and ensure that there are more than 60secs left on the token life so that there is enough time to update the 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 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 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_atdate 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 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 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 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 program.
Updating Costs
IF the costs list length is greater than 0 after iterating over all the (date,cost)pairs for a particular 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 API update program endpoint.
- Additionally the
costsDestructiveUpdateparameter is passed to the updateProgram function, which when set toTRUEwill clear out any costs that are stored in the program and replace them with the costs in thecostslist, which is desired since the costs in the(date,cost)pairs are the ad spend for the entirety of the months. SettingcostsDestructiveUpdateequal toTRUEis 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
costsDestructiveUpdateequal toFALSEthen the incoming 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 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 program was created.
Finally, at the end of the inner while loop, a 0.2sec delay is implemented so that the 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 costs updated.
Checking Output Log of 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 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?
Once the backdating of costs is complete you can follow the Updating Costs using the API & Zapier post to see how to automatically update costs every month using Zapier and Google Sheets.
Now that you have gotten a taste for the 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:
- UTM Tracking & Automation: Streamline the process of creating UTM parameters and tracking these parameters in Marketo using Google Forms, Google Sheets, and the API.
- Email Process Automation Using the API & Zapier: Learn how to create and populate emails from Google Sheet templates using the API.
- Merging People 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.
The content in this blog has been reviewed by the Community Manager to ensure that it is following Marketing Nation Community guidelines. If you have concerns or questions, please reach out to @Jon_Chen or comment down below.
.png)