Automating UTM Programs using Zapier

Tyron_Pretorius · ‎04-09-2022

Program details

Take the first step towards becoming an attribution master by learning how to set up UTM programs and smart campaigns to match website visitors to particular ad campaigns, set first and latest touch attribution fields, and track downstream conversions to signup or contact sales forms.

This post will focus on tracking UTM parameters but if you want to learn how to track organic referral, social, and search visits with your campaigns so they DON’T STEAL ATTRIBUTION FROM YOUR PAID CAMPAIGNS then take a look at the Anonymous People & Attribution post.

As a bonus for the eager beavers among us, I will also show you how you can streamline the process from a marketing team member requesting UTM parameters all the way to setting up tracking for these parameters in Marketo.

Tracking UTMs

UTM Program

Program Tree

Within the UTM program there are 4 different smart campaigns responsible for tracking visits or conversions and moving a person to the appropriate status in the program. Moving people to these program statuses is important because this allows us to report on cost per lead and cost per success.

The “UTM Campaign” and “Conversion Tracking Campaigns” sections below detail the setup of the 01 Visit, 02 Contacted, and 02 Converted smart campaigns. The setup of the 03 Anonymous smart campaign is detailed in the Anonymous People & Attribution post.

The first step when creating a UTM program is to create a “Default” program type and set the “Channel” to your paid advertising or attribution channel. In this example the “PPC” channel is being used which has the “Visited” status to track anyone who visits our website from an ad campaign and the “Converted” or “Contacted” status to track anyone who signs up or contacts sales straight from the ad campaign.

Advertising channel statuses

The 01 Visit campaign, uses local program tokens within its flow steps to update attribution fields to these token values. Since these token values can be populated using the API (see the “Preparing UTM Smart Campaigns” section below) this means that the values the attribution fields will be set to can indirectly be changed by the API.

Program Local Tokens

If you want to see possible values that the lead_source, lead_source_detail, source, medium, and campaign tokens can have then check out the Key tab of the “UTM Builder” sheet. To see how these tokens are used in the “Change Data Value” flow steps then read on!

UTM Campaigns

UTM tracking is achieved by using the “Visits Web Page” trigger along with the “Querystring” constraint to ensure that only web page visits with the desired UTM parameters matching our ad campaign are tracked.

Campaign Trigger

Then the flow kicks off to:

Change the person’s program status to “Visited”
Set the person’s Latest Touch field to the UTM querystring using the {{my.utm}} token. This Latest Touch field will be used in the conversion campaigns below to see if this ad campaign was the last thing the person interacted with before signing up or contacting sales.
If the person’s Person Source field is empty then update the Person Source and Lead Source Detail fields to be the {{my.lead_source}} and {{my.lead_source_detail}} tokens respectively. These are a person’s first touch attribution fields that can be used for attribution reporting later on.

Updating the program status, latest touch, and first touch attribution fields

4. Update the utm_source, utm_medium, and utm_campaign fields to each of the corresponding UTM parameters. These 3 fields are supplemental to the Person Source and Lead Source Detail fields to provide more information, the utm_campaign field especially, about where a lead came from. The utm_source and utm_medium fields can be removed, if desired, since their information is already contained in the Person Source and Lead Source Detail fields.

Storing the parameters in their respective fields

5. If the person’s Acquisition Program is empty then set the Acquisition Program to the parent program of this smart campaign and the Acquisition Date to the {{system.dateTime}} token. Setting these 2 fields is the most important requirement for doing program performance and attribution reporting.

Setting the Acquisition Program and Date

Conversion Tracking Campaigns

Remember that “Lastest Touch” field we set in the UTM campaign flow above? Well whenever we get a person who signs up or contacts sales with this “Lastest Touch” field equal to the UTM querystring of our ad campaign then we know this ad campaign was the last thing they interacted with before carrying out these actions.

This is achieved by including the Lastest Touch = UTM Querystring as a filter along with the triggers and other filters mentioned below.

A Contact Sales Conversion occurs whenever anyone fills out one of the contact sales forms and the Reason for Contact is a Sales Inquiry because in my company’s case we did not want to mark support or legal requests as conversions.
A Signup Conversion occurs whenever a person’s online portal id i.e. mc user id, gets populated (for people who already exist) or someone is created from the Web Service API with their mc user id already populated

Tracking contact sales conversions from the ad campaign

Tracking sign-up conversions from the ad campaign

Then we will set this person’s status in the UTM program to either Converted or Contacted depending on which of the above smart campaigns they passed through.

Changing program status to reflect contact sales conversion

Changing program status to reflect sign-up conversion

Automating UTM Programs

Now that you understand how the UTM program and campaigns are set up, I will show you how you can automate the creation of these programs and campaigns using the API and Zapier.

To do this we will use:

A Google Form that will send UTM requests from marketing team members to a Google Sheet triggering a Zap in Zapier to run
This Zap will then take the inputs to the Google Form and create the desired UTM parameters using formulas within another UTM Builder Google Sheet
The Zap will then create a UTM program and smart campaigns specific to the newly created UTM parameters
Finally, a Jira ticket will be created for Marketing Operations to check that the UTM program and campaigns were created correctly and then activate the smart campaigns

All the Python code used within the “Code by Zapier” actions can be be found in this directory in Github.

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 🙂

Collecting UTM Requests from Google Forms

The first step in the whole process is to create a Google Form that will allow a marketing team member to fill out their ad campaign details so that we can automatically create the necessary UTM parameters later on in the UTM Builder sheet. The important thing here is to not require any text entry and only allow the marketer to select from existing options so that you will always know the options available in your form and can thus configure your UTM Builder Google Sheet to create the corresponding UTM parameters for these inputs.

marketo_utm_tracking_form

Every new submission to the UTM Request Form gets logged in the UTM Request Form Responses sheet, which in turn triggers the Zap to run in Zapier.

Request Form Responses

Creating UTM Querystring using Google Sheets Formulas

Zapier actions to create a querystring from the request form submission

Once the Zap has been triggered, the next step is to take the row ID of this latest submission and use that to search for the row currently filled with “Next Row #” in the UTM Builder sheet.

Looking up the next row in the sheet

Originally, this zap was set up so that this step searched for the next empty row in the “UTM Builder” sheet, however, when multiple form submissions were made in quick succession it would turn out that the same row in the “UTM Builder” sheet was being written to/overwritten for each of the submissions.

To get around this race condition, the Campaign Name column in the “UTM Builder” sheet is populated with Next Row # where the row numbers being used correspond to the next available empty rows in the “UTM Form Responses” Google Sheet. This way each successive UTM form submission will be in a a different row in the “UTM Form Responses” sheet and thus will lookup a different row in the “UTM Builder” sheet.

marketo_utm_tracking_utm_builder

Once the correct row in the “UTM Builder” sheet is found, the Channel and Sub-Channel values are populated from the form submission, at which point the formulas in the Google Sheet take over to create the UTM parameters and querystring automatically.

Putting ad campaign information into the sheet

Putting ad destination into the "UTM Builder" sheet

Creating a UTM Program

Now that the UTM querystring for the ad campaign has been created we want to create the UTM program and campaigns.

Creating the UTM program

The next 5 “Code by Zapier” steps use Python code to make requests to the API to:

Get the access token that is needed to make subsequent requests to the API (get_marketo_token.py)
Get the yearly paid campaigns folder for the current year or create it if it does not already exist (get_year_folder.py)
Get the monthly paid campaigns folder for the current month or create it if it does not already exist (get_month_folder.py)
Get the most recently created previous program for the sub-channel of the ad campaign we want to track or if a previous program cannot be found then default to using a template program (get_latest_program.py)
Clone the previous program for this sub-channel, creating it within the current monthly paid campaigns folder (clone_program.py)

UTM folder hierarchy

Preparing UTM Smart Campaigns

If Step 8 to clone the latest UTM program for a particular sub-channel is successful then the program ID of the newly created program will be returned. However, I often found that although Marketo was able to successfully create the new program it did not return the successful response and program ID to Zapier within the 10-sec timeout limit imposed on every Zapier action. Therefore, Step 8 would fail along with all the subsequent steps in the zap, which depend on the returned program ID.

In order to remove the dependency on Step 8, Step 9 (get_new_program.py) was added for redundancy and gets the program ID of the newly created program by looking up the name of the program we know was created in Step 8. Now all subsequent steps will use the program ID returned from Step 9 so if Step 8 is ever marked as failed the rest of the zap can continue.

Updating the UTM program and smart campaigns

The next 3 “Code by Zapier” actions:

Take the channel, sub-channel, and utm parameters for the ad campaign from Step 3 and use these to populate the utm, lead_source, lead_source_detail,source, medium, and campaign local program tokens (update_program_tokens.py).
Get the ids of the 4 smart campaigns nested under the UTM program (get_smart_campaigns.py).
Update the description of these smart campaigns to be the UTM parameters querystring so that it is easy for us as the marketing operations person to copy this value and use it within the smart campaign (see the “Manual Input Required from Marketing Operations” section below)(update_smart_campaigns.py).

Example of campaign description containing querytring

Notifying the Marketing Operations Team

Once the UTM program and campaigns are ready, we then need to create a ticket for the marketing operations team so that they can review the program and campaigns and do the small bit of manual work outlined below.

Creating a ticket for marketing operations

The email address associated with the “UTM Request Form” submission is used to lookup the Jira ID for this person
This Jira ID is then used in the “Reporter” field when creating the Jira ticket (see image below)
The Jira ticket is then moved from the backlog into the “To Do” column of the marketing operations person assigned to the ticket

Populating the ticket

A Slack message, email, or SMS notification containing the same information as in the Jira ticket could also be added here.

Manual Input Required from Marketing Operations

Since the Jira ticket contains a link to the UTM program that was created by the Zap, it is very easy for marketing operations to open up this ticket and go straight to the program.

While tokens can be used in “Change Data Value” flow steps they cannot be used in the “Querystring” constraint of the “Visits Web Page” trigger in the “01 Visit” smart list or in the “Lastest Touch” filters in the “02 Contacted” or “02 Converted” smart lists.

This is why manual input is required to update the smart lists with the UTM querystring for the ad campaign. Thankfully, our zap updated the description of these smart campaigns to be the UTM querystring so we can copy the querystring right from the description before pasting into the smart lists.

Once all the smart campaigns have been activated or scheduled the last piece of manual work required is just to set up the “Salesforce Campaign Sync” so that the leads generated from this program can be sent to a corresponding Salesforce campaign.