August 14, 2017
One of the most important parts of our job as PPC’ers is bidding. We place bids to win auctions to gain conversions and leads. A big part, then, of our PPC strategy is optimizing bidding. There are several bidding strategies out there, but they can be simplified down to two categories: automated bidding and manual bidding.
Automated bidding can be helpful, especially in larger accounts where bid changes might need to be made quickly. There are third-party systems like Acquisio and Marin, or in the interfaces themselves, like Google’s smart bidding strategies.
Manual bidding also has its advantages. In some cases, automation may change bids based off short-term performance on historically poor performing keywords, whereas with manual bidding you would know not to make those changes. You have a little more flexibility with the KPI you optimize your strategy on as well.
With Supermetrics, there is a third strategy where you automate your manual bidding. This strategy allows you to have a streamlined process, saving you time, and gives you the ability to check your bids before implementing them, giving you assurance your account stays optimized.
Supermetrics is an add-on to Google Sheets that automatically pulls in data from across your PPC platforms (AdWords, Bing, Facebook, etc.) allowing you to analyze your data more efficiently. I use Supermetrics regularly and have covered other uses for Supermetrics in the past, including how to automate budget projections. Now I am going to show you how to use Supermetrics to automate your manual bidding strategy. Specifically, I am going to build a bidding strategy for AdWords that optimizes on Average Position and Conversion Rate.
Supermetrics Bidding Sheet Steps
Launch
The first step is launching the Supermetrics Sidebar under the Add-Ons tab in Google Sheets. This is where you input the data you need pulled for your strategy.
Data Source
Next, we choose the Data source we are pulling our data from. Since this is going to be a bidding sheet for AdWords, we choose AdWords as the data source.
Account
Then we choose the account that we are creating the strategy for. If you work with multiple accounts, be sure you are pulling from the proper account for accurate data.
Metrics
After choosing the account, pick the metrics you need for the bidding strategy. In this case, we need Clicks, Impressions, Cost, Conversions, and Avg. Position.
Date Range
Next, we want to get the date range. With this type of bidding sheet, it is recommended you project back the amount of days you want to project forward. I would also recommend the minimum amount of data being 14 days to project out the next 14. For this set up, I am pulling the last 30 days of data and would use the modifiers for the next 30 days.
Split By
The last step in the settings is to select how the data is split out. We want the data by keyword, but also want it to be set up to implement efficiently, so we split out by match type, campaign, and ad group. We add Max CPC here too, which is useful in comparing how the bids are changing after we set up the bidding sheet.
Pulling the Data
After setting how the data is split out, make sure you have highlighted the cell you want the data to fill from within the sheet. I recommend cell A1, as it matches the data set I pulled for this article. Once you have highlighted cell A1, click “Get Data to Table” in Supermetrics and watch the data come in!
Potential Glitch
A quick note, after I pulled the Supermetrics data I noticed my Max CPC was reporting at one million times higher than the actual Max CPC. If you encounter this glitch, insert a column to the right of the Max CPC and take the Max CPC divided by one million to get the actual Max CPC. I have notified Supermetrics of the issue and expect it will be fixed shortly.
Bidding Set Up
With your data pulled, it is time to build out the rest of the bidding strategy. We add three columns in the Google sheet: Conversion Rate, Bid Adjustment, and New Max. CPC. Alongside these three columns, we will build an index for the bid adjustment column. The index takes Average Position and Conversion Rate into consideration and pulls into the bid adjustment column through an index function. Exciting stuff!
The Columns
We set up the columns at the end of the data pulled from Supermetrics. If you had the Max CPC glitch, these go in columns L, M, and N, if you did not have the glitch, then they go in columns K, L, and M.
Conversion Rate
This is the easiest column. Take Conversions divided by Clicks to get the Conversion Rate. Then drag down the column and format into a percentage.
Bid Adjustment
We now jump from the easiest column to the hardest. There are two parts to building out this column. First, we build the index table on a new sheet and then we use an index function to get the bid adjustment to the column.
Index Table
For the index table to take both Average Position and Conversion Rate into consideration we must add both to the table. The Average Position is the column header and Conversion Rate is the row header (highlighted in yellow in the picture below). Both have a range that is the base for the bid modifiers. For Average Position, I chose one through eight, as those are the potential first page positions and for Conversion Rate I went from 0% up to 30%, as that is the range for the account in this example. You might find having different conversion rates work better for your account, don’t be afraid to switch up the numbers to fit the account you are working in.
Once you have the headers in place, add your bid modifiers. The bid modifiers will vary depending on how aggressive you want to get in your account, but they should increase left to right and increase top to bottom with the set-up in this example. The thought process is keywords positioning low, but with high conversion rates should have bids increased, as the conversion rate is most likely to increase as well as the ad places higher on the SERP.
Index Formula
After the index table is built, we move back to the original sheet and to the Bid Adjustment column. We now add an index formula to get our index table data onto our sheet.
The formula is as follows:
=INDEX(‘Index Table’!$D$6:$J$13,MATCH(K2,’Index Table’!$C$6:$C$13),MATCH(L2,’Index Table’!$D$5:$J$5))
If this formula is blowing your mind, we have covered index functions on PPC Hero before and I recommend checking out that run through.
New Max CPC
With the bid adjustments complete, we are down to our last column! The New Max. CPC column is an easy formula to implement. Take the bid adjustment and add one then multiply by the original Max CPC. Shown below:
(Bid Adjustment+1)*Real Max. CPC
Automation
After adding the New Max CPC column, you have a complete bidding sheet strategy built out in AdWords! We have one final step and that is automating the report in Supermetrics. Go to the Add-Ons tab and then under Supermetrics click on the Schedule Refresh and Emailing tab. Set your refresh for when you want the bids to update and you can set an email to go to you when the report refreshes, allowing you to set it and forget it until it is time to update the bids again.
Conclusion
With the automation in place, you now have a manual bidding sheet that refreshes automatically. Take your new bids and upload into AdWords. Even though it does take some time to set up, once you have the sheet you can use it as a template for future accounts, making it an efficient tool over time. As I have said before, Supermetrics is a great tool for automating various tasks. How are you using Supermetrics? I would love to hear from you on Twitter! Don’t hesitate to reach out if you have any comments or questions.