Zapier Nested Looping with Code by Zapier

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

Zapier Nested Looping with Python Video

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 Zapier

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

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 with Code by Zapier

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.

N.B. If you want a simpler example of how to use webhooks for looping in Zapier, one that does not involve nested looping, then check out the Marketo Program Cloning Via API post.

Step 1. Catch Webhook to Start Zapier Nested Looping

• 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

(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]

• 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

• 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. Decide Whether to Continue Zapier Nested Looping

• 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 to Restart Zapier Nested Looping

• 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 “Bulk Updating Marketo Program Period Costs” 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.