Banging your head against the wall because of the 500 iteration limit on the “Looping by Zapier” action? Well make like Freddie Mercury and break free by using webhooks and Python in Zapier to achieve nested looping so that you can do as many iterations as you need!
On a related note are you interested in learning more about APIs? APIs are the foundational building blocks that power modern software ecosystems like Zapier and the Demystify the API Crash Course will explain the fundamentals and give you a hands-on introduction to making your first API requests. Check out the course using the button below 🙂
In the “Nested Looping using Webhooks to Get Google Sheets Rows” section of the Zapier Loop Array with Nesting post I outline how webhooks can be used on either end of your zap to form an outer loop that will house a nested inner loop. Whereas that post used the “Looping by Zapier” action for the nested inner loop this post will show you how you can use the “Code by Zapier” action and a loop in Python as the nested inner loop.
One possible advantage of using the “Code by Zapier” action for the inner loop is that you are no longer limited to 500 iterations as you would be using the “Looping by Zapier” action. However, there is a 10-sec execution limit placed on all Zapier actions so if your code takes too long to execute then the action, and possibly the rest of your Zap, will fail.
Therefore the key is to do as many iterations in the nested Python/JavaScript loop as possible in less than 10 seconds before exiting to the outer loop to reset for the next round of nested loop iterations.
Calculating Customer Acquisition Cost
In this post, we will use the “Marketo Program Period Cost Tracking” sheet as an example of where we need to use Zapier nested looping to loop through a list of items and carry out an action for each item.
In a typical marketing team, a sheet like the “Marketo Program Period Cost Tracking” sheet will be updated at the start of every month to map all the ad campaign costs from their various marketing channels from the previous month to the corresponding Marketo program that tracked the leads generated from this campaign.
Therefore, the objective of the Zapier nested looping automation workflow is to update each of the Marketo programs listed in this sheet with their respective costs so that the cost of acquiring a lead can be calculated.
Importing Ad Campaign Costs from Google Sheets into Marketo
Now that we have the programs and costs collected nicely in Google Sheets the first task that needs to be done is importing this information into Zapier.
As explained in theZapier Google Sheets Quick-Start Guide the Google Sheets app in Zapier is limited to pulling in a maximum of 20 rows of data in a single action so if you want to import more rows than this then you need to get creative!
Using a Google Script to package the data up so that it can be imported into Zapier
Assigning this script to a button so that it can be triggered to alert Zapier that the data is ready
Using a Zap to listen out for this alert and then use the “Lookup Spreadsheet Row” action to pull in the data
Taking a quick peek at the “Marketo Program Period Cost Submissions” tab, we can see that there is a column for Period Costs and another for Marketo Programs where both columns contain a string variable. These string variables are created by the “concatenate.js” Google Script which joins every value in the “Marketo Program” and “Period Cost” columns of the “Marketo Program Period Costs July 2020” tab together using the * character.
This Google Script can be assigned to a button (seeTriggering a Google Script using a Button) so that anytime the button is clicked these strings will be created and then stored in a new row in the “Marketo Program Period Cost Submissions” tab along with a timestamp and the beginning of a log which will be populated by the Zapier nested looping zap with the status of each Marketo program period cost update.
Zapier Nested Looping using Webhooks & Python
Achieving nested looping in Zapier is done by book-ending the zap with the “Catch Hook” trigger event at the start and the “POST” webhook action event at the end to form an outer loop, which contains the nested Python loop. When you come to the “Update Marketo Program Period Costs” in Step 3 below, you might be wondering why there is a need to have nested looping in this zap at all. Why could we not just iterate through all the programs at once in the Python loop and remove the webhooks forming the outer loop?
Again the reason for this is that Zapier has a 10-sec timeout on all its tasks so if you tried to update all the Marketo program period costs at once then the task might timeout and the zap will fail. Therefore for improved scalability as your number of digital advertising campaigns increases, it is better to break the programs into smaller groups and then update the programs within each group in successive iterations of the outer loop.
From my own experimentation, I found that updating 20 programs at a time in the “Update Marketo Program Period Costs” action lead to an execution time well under the 10-sec timeout.
The “Custom Webhook URL” under the “Set up trigger” section is the destination URL that is used in the Google Script function in the previous section to send the timestamp and index to start this zap.
The index value retrieved by this webhook denotes the point at which the Python code in step 3 will start/continue parsing through the two parallel lists of Marketo programs and period costs
Step 2. Get Marketo Program Period Costs
The timestamp from Step 1 is used as a lookup field to get the Marketo programs and period costs from the row that was submitted to trigger the zap
Step 3. Update Marketo Program Period Costs [Python Loop]
The “update-marketo-program-period-costs.py” Python script is used in the “Code by Zapier” action to iterate through the Marketo programs and update their period costs using the Marketo REST API.
The “Marketo Programs”, “Period Costs”, and “Log” cells from the Google sheet are pulled in as input data along with the “Index” and “Timestamp” from the webhook in Step 1
N.B. If it is your first time using the Marketo REST API or you need a quick refresher then check out theMarketo API Quick-Start Guideto 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.
Step 4. Update Zapier Nested Looping Log
The “Log” column in the Google Sheets row is updated with the responses from the Marketo program period cost update REST API calls made in Step 3
Make sure to only include necessary information in the log because Google Sheets has a 50,000 character limit for single cells. If you try and write to a cell and exceed this threshold then this step will fail with an error message “There was an error writing to your Google sheet”.
After the zap has updated all the Marketo program period costs, I recommend pasting the log into a JSON formatting tool so that you can see the data in a more presentable format
Step 5. Only continue if more period costs to update
A filter is used with the “finished” boolean value set in Step 3 so that the Zap will only progress to Step 6 if there are more Marketo programs that still need their period costs updated.
Step 6. Send Webhook
If the zap passes the filter in step 5 then a webhook is used to send the index at which the next run of the Python loop will need to start from along with the timestamp needed to lookup the correct row in the Google sheet.
The destination URL of this webhook is that of the “Catch Hook” trigger event in Step 1 of the zap so when Step 6 runs it will trigger the whole zap to run again. This zap will run until all Marketo program period costs have been updated, at which point it will finally finish at Step 5 when the “finished” boolean is True.
Zapier Nested Looping Follow Ups
This is just one example of how you can use Zapier nested looping to do tasks in bulk. If you want to see how to use the native “Looping by Zapier” action as the nested loop then take a look at theZapier Loop Array with Nesting post. Also if you want an overview of all the looping capabilities of Zapier then take a look at theZapier For Each Loop Quick-Start Guide.
For those Marketo heads among us, now you can use this Zapier nested looping zap to update your Marketo program period costs at the start of every month so that you can determine your customer acquisition cost for the previous month. But now that your CMO knows you’re an attribution rockstar, what if they ask you for the customer acquisition cost for all the months before you had your Marketo program period cost updating in order?
Never fear! The Marketo Period Costs Bulk Update Via API post will guide you through how to use the Marketo REST API to take your historical ad campaign costs and update the period costs of the associated Marketo programs in bulk.
P.S. Check out the video below to see how everything is stitched together, how the nested looping zap is setup, and what the output log of successful Marketo program period cost updates looks like.
P.P.S.There is no commentary on this video so no there is not an issue with the sound haha Go easy this was my very first YouTube video! I may redo it at some point in the future with commentary so if you want to see that leave a comment below
About The Author — Tyron Pretorius
Greetings fellow MO Pros! As a previous mechanical engineer turned marketing operations lead, I now fancy myself as a full-stack marketer capable of designing websites, writing SEO-optimized content, and doing all the tracking and automation on the backend.
That being said my bread and butter is marketing operations and I love to get down and dirty with the Marketo API, Python, Zapier, and Google Scripts.
Created with the assistance of DALL·E Introduction and Background In 2018, Edward Unthank introduced the four pillars of marketing operations, setting the stage for the management of effective marketing technology (Martech) functions. These pillars have since been the bedrock for many successful careers in marketing operations. My journey to redefine
Introduction When it comes to account-based marketing (ABM), your marketing team might have invested in intent data, built tailored content, and spent time targeting accounts. But is the juice worth the squeeze? You could say marketing operations teams are there in part to squeeze every last marketing dollar in order
Introduction: Blending Human and Technical Perspectives in Martech Caption: Future gardens of martech will blend human and AI perspectives As a co-host of the Humans of Martech podcast, I’ve had the privilege of interviewing some of the brightest minds in marketing technology. This unique experience has provided a blend of