Updating Period Costs using the API & Zapier

[Tyron_Pretorius]

Zapier looping flow diagram

Calculating Customer Acquisition Cost

In this post, we will use the “Cost Tracking” sheet as an example of where we need to use Zapier to loop through a list of items and carry out an action for each item.

Ad campaign costs mapped to their corresponding program

In a typical marketing team, a sheet like the “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 program that tracked the leads generated from this campaign.

Therefore, the objective of the Zapier automation workflow is to update each of the 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

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 (DM me for the link) 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 (DM me for the link) 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

Taking a quick peek at the “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 “Program” and “Cost” columns of the “Costs July 2020” tab together using the * character.

This Google Script can be assigned to a button (see the Triggering a Google Script using a Button post (DM me for the link)) so that anytime the button is clicked these strings will be created and then stored in a new row in the “Cost Submissions” tab along with a timestamp and the beginning of a log which will be populated by the zap with the status of each cost update.

Looping using Webhooks & Python

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 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 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 Step 3 led to an execution time well under the 10-sec timeout.

Zap overview

Step 1. “Catch Webhook” Trigger

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 programs and costs

Step 2. Get Costs

Retrieving costs from Google Sheets using a lookup value

• The timestamp from Step 1 is used as a lookup field to get the programs and costs from the row that was submitted to trigger the zap

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

Step 3. Update Costs [Python Loop]

Inputs used in the Python code

• This Python script is used in the “Code by Zapier” action to iterate through the programs and update their costs using the API.
• The “Programs”, “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 API or you need a quick refresher then check out the Quick-Start Guide to the API to see how to make your first 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 🙂

Step 4. Update Looping Log

Update the log with the responses from the update costs requests

• The “Log” column in the Google Sheets row is updated with the responses from the 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 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 costs to update

Filter to allow progression only if there are more 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 programs that still need their costs updated.

Step 6. Send Webhook

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 costs have been updated, at which point it will finally finish at Step 5 when the “finished” boolean is True.

Need to Backdate Costs?

Now you can use this zap to update your 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 rock star, what if they ask you for the customer acquisition cost for all the months before you had your cost updating in order?

Never fear! The Bulk Updating Costs Via API post will guide you through how to use the API to take your historical ad campaign costs and update the costs of the associated programs in bulk.

P.S. Check out the video below to see how everything is stitched together, how the zap is setup, and what the output log of successful 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 🙂

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.