Zapier Nested Looping using Webhooks & Python

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!

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 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.

Ad campaign costs mapped to their corresponding Marketo program
Ad campaign costs mapped to their corresponding Marketo program

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 the Zapier 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!

In this example we will follow the steps outlined in the Zapier Google Sheets Lookup Value post by

  • 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
"Cost Submissions" tab containing the information which will be retrieved by Zapier
“Marketo Program Period Cost Submissions” tab containing the information which will be retrieved by Zapier

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 (see Triggering 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

Zapier nested looping using webhooks and Python
Nested looping in Zapier using webhooks and 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.

Zapier nested looping setup
Nested looping zap overview

Step 1. Zapier Nested Looping “Catch Webhook” Trigger

Zapier nested looping trigger to catch the webhook sent from the Google script
Zapier trigger to catch the webhook sent from the Google script
  • 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

Retrieving Marketo program period costs from Google Sheets using a lookup value
Retrieving Marketo program period costs from Google Sheets using a lookup value
  • 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

(See the Zapier Google Sheets Lookup Value post for more detail on how this lookup works)

Step 3. Update Marketo Program Period Costs [Python Loop]

Inputs used in the "Update Marketo Program Period Costs" Python code
Inputs used in the “Update Marketo Program Period Costs” Python code
  • 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 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.

Step 4. Update Zapier Nested Looping Log

Update the Zapier nested looping log with the responses from the update Marketo program period costs requests
Update the log with the responses from the update Marketo program period costs requests
  • 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

Filter to allow progression only if there are more Marketo program period costs to update
Filter to allow progression only if there are more Marketo program 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

Sending a webhook to start the next iteration of the outer loop
Sending a webhook to start the next iteration of the outer loop
  • 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 the Zapier Loop Array with Nesting post. Also if you want an overview of all the looping capabilities of Zapier then take a look at the Zapier 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
Tyron Pretorius

Greetings fellow MOPs 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.

Related free workshops

Google Sheets data being accessed in a Zap action in Zapier

How to use Zapier with Google Sheets

Integrating Google Sheets with Zapier will allow you to unlock a whole new array of automation possibilities for the data in your spreadsheets. This post will show you how to use Zapier with Google Sheets by walking through the 3 different options for importing data into Zapier using the Google Sheets app.

Become a member
Timestamp being used as the Google Sheets lookup value in Zapier

Zapier Google Sheets Lookup Value

When trying to pull a specific row from your Google Sheet into Zapier you can use a lookup value dynamically populated with a variable from a previous step. This is useful but the real power of using the Google Sheets lookup value in Zapier comes when you need to import large amounts of data into your zap.

Become a member
Button in Google Sheets to trigger a Google Script to send multiple rows to Zapier

Zapier Import Multiple Rows from Google Sheets

Anyone who has worked with Google Sheets in Zapier is likely to have run into the issue of needing to import more than the 20-row maximum imposed when using the Google Sheets app. This post will show you how to overcome this limitation and import multiple rows from Google Sheets into Zapier using some JavaScript and webhooks.

Become a member