Tired of resetting your bid adjustments on your ad schedule? Learn how to set up real-time autobid adjustments with a feed of fresh data with our easy-to-use template.
Jasmine Hippe, Paid Media Analyst at Augurian, walks you through how easy it is to automate bid adjustments on your ad schedule by combining Supermetrics, a script from Brainlabs and the Google Sheets template we are providing below.
Augurian AutoBid Google Sheets Template
Read On For Details On Setting Up Real-Time Autobid Adjustments
This folloing will provide you with all the resources you need to set up automated 24/7 bid adjustments that are based on up-to-date data from your Google Ads account. Before you begin, there are three things you need to accomplish this task.
The first is Supermetrics, a tool that pulls data from marketing platforms’ APIs. The second thing you will need is this free script from Brain Labs that allows you to set bid adjustments for each hour of each day. We are going to build on top of that script using a Google Sheet that the Script will reference. Make a copy of this templated Google Sheet we’re providing for your own use in this process. Let’s get started.
Source: giphy
Step 1: Set up your Supermetrics queries
We’ll begin by pulling historic data from Google Ads into the Google Sheet as the basis for our bid adjustments. Under the “Supermetrics Data” tab of the Google Sheet, set up a Supermetrics query that will pull in cost, conversion, and click data from the last 90 days (or whatever time period works best for your account) and split the data to rows by hour and day of week.
Next, within the same sheet using a column that doesn’t yet contain data, we will run a second query that pulls conversion rate from the same Google Ads account for the past 90 days (or whatever time period you are using). There is no need to split this data by any dimensions, as we will use this number in aggregate to assess hour-by-hour performance for each day.
You should set up an automatic daily refresh of your Supermetrics queries in order to keep this data fresh.
The template we’ve provided will base bid adjustments on how the hourly conversion rate compares to the overall conversion rate for the account within the same period. If your account doesn’t have much conversion data, you can optimize your scheduling around CTR or another metric that you are focused on.
Step 2: Determine your bid adjustment range
After running the two Supermetrics queries, you will see data populate in both the Pivot-CVR sheet and CVR-Adjustments sheet.
Source: giphy
Within the CVR-Adjustments sheet, you will notice three columns for each day of the week. The first is conversion rate, the second is a suggested adjustment based on the formula: [conversion rate at that hour] / [conversion rate for the account] – 1. In order to keep bid adjustments within a comfortable range, the third column serves to modify the suggested bid adjustment using the vlookup function and a basic table.
The current range in the template we’ve provided allows for a maximum positive bid adjustment of 50%, and a maximum negative bid adjustment of -75%. If you’d like to modify this range to better suit your Google Ads account, you can do so within the Vlookup sheet.
Look over the Ad Scheduling sheet to make sure bid adjustments are aligned with what you know about the account. You may want to make some adjustments that are custom to your business, such as using -100% bid adjustments during non-business hours.
Your work within this Google Sheet is completed now!
Source: giphy
Step 3: Implement the script
The last step in this process is to actually implement the script in your Google Ads account. Make sure to customize the script settings to suit your needs by following the guidance provided by Brain Labs. Per those instructions, you will want to set this script to run hourly. Don’t forget to ensure the script references the Google Sheet that houses your bid adjustments. Once you’ve implemented the script, you’re finished! Now your scheduled bid adjustments are based on fresh data from your Google Ads account.
Source: giphy
If you implemented this process, let us know how it’s working for you! Any kind of feedback, questions or comments are welcome.
VIDEO Transcription
Jasmine Hippe: Hi, I’m Jasmine from Augurian. I’m a paid media analyst here. Today, I’m going to show you how to set up automated 24/7 bid adjustments based on up-to-date data. Let’s get started, why would you want to do this? You want to be able to base your bid adjustments in your ad schedule on fresh data. So you don’t have to go in every season and reset your bid adjustments.
People are not always going to be searching at the same time of day in the summer versus in the winter, so this is a really good way to keep your bid adjustments up-to-date without having to spend a lot of time doing that. To set this up we will need three different things. The first is going to be Supermetrics, a marketing tool that pulls data from different platforms APIs.
The second thing that you’re going to need is the script from Brainlabs. We’re actually going to build on top of this Brainlabs script, it lets you adjust your bid 24/7. Every hour of every day you can set a different bid adjustment. Without the script Google only allows you to set six different ad schedule blocks or bid adjustments on your schedule. The third thing that you’re going to need is the template that we are providing here, it’s called the 24/7 bidder template.
This is going to make it super easy for you to set it up. Once you have these three things, set up your Supermetrics query, customize it to your client’s needs. First thing that we’re going to do is set up our Supermetrics query. We’ll be pulling in cost conversion and click data and we’ll be splitting that to rows by hour of day and day of week. Here’s how you run the query, you’ll select your account and you’re going to select your date range, so you want to use the last X days and then select a time range that works for you.
I’ve been finding 90 as a pretty good sweet spot the last 90 days of data, usually keep things pretty fresh and up-to-date. Then, we’re going to put in here the cost, the conversions, and the clicks. That’s the data that we want to pull and we want to spit that by the hour and day of week. Make sure that you have your headers on, don’t have no header row ticked because this template is set up in such a way that we are using the headers.
You’ll hit apply changes, it’s going to run the query for you. Great. You see the data populate here, super easy. We’re actually going to go and run a second query in the same sheet. What we’re doing with this query is we want to get a base conversion rate that we can base our update bid adjustments on. What we’re going to do is we’re going to compare the conversion rate for each hour of each day and we’re going to compare that against the base conversion rates for the entire account for that past 90 days or whatever time period you’re using.
Let’s pull in the conversion rate, super easy, set up your query, same account, select the same date range and then the only metric that you’ll need here is conversion rate. You don’t need to split it by anything, you don’t have to be concerned about headers this time because it’s just one metric. You see it get pulled in there. Now, what happens is you can jump over to the CVR adjustments tab and you’re going to see all this data have populated. Super easy, we set it up for you, you can thank us later.
What we’re looking at is for each day of the week we have the conversion rate, the suggested adjustment. This is using a formula of the conversion rate for that specific hour of day, over the based conversion rate for the entire account over the same time period minus one. That’s a pretty standard way that we calculate bid adjustments here. Sometimes you might see 80% or 100% adjustment or you might see a negative 80%.
That range might not be very comfortable for your client, and so what I’ve done used a VLOOKUP table to adjust the bid adjustments. [laughs] You can see we’ve set up a VLOOKUP function, you really don’t need to touch it. Its maximum positive bid adjustment is going to be 50% and then the maximum negative bid adjustment is going to be negative 75%.
You can use the VLOOKUP table and adjust it to fit your needs. Maybe you want to keep it in a smaller range like 30%, whatever suits you and your account. We have everything else set up there for you. The next step is to look at this ad scheduling tab, and basically make sure that you’re not seeing anything too crazy. Probably you don’t want to be bidding like 50% in the middle of the night. Other than that, it should all be set up for you in this sheet. We’re actually done with the sheet at that point. Next, grab the script from Brainlabs. Make sure you read through the instructions on how you can customize it to your account. For example, there’s options to apply it to your shopping campaigns or not. Make sure that you read through all the instructions provided by Brainlabs to make sure that the settings are customized to suit your needs.
Since you’ve customized the script, add it to the account. Then we’re going to set it to run hourly. Make sure that it’s referencing the Google sheet that you are using to build out your bid adjustments here. If you experience any issues, or if you have any questions or comments, please feel free to reach out and share with us. Thanks.
- Mental Health Awareness: Yoga and Meditation Guided Session - May 21, 2020
- 4 Meditation Apps for Beginners - May 12, 2020
- How to Overcome Obstacles in Client Communication - November 21, 2019
