You are at an event with old friends, and you (a PPC Specialist) are asked: “what are you doing these days?”
You reply: “I do online advertising through search engines such as Google.”
The response back always seems to be something like, “So, what exactly does that entail”.
If you do not want to get into all of the details that go into paid search advertising, the typical response back is: “I do data analysis and make decisions on how much to bid on search queries and ad space online.”
Bid is one of the first words you associate with your job. It is one of the most important parts of your job. Is Paid Search simply bidding on keywords and finding the best bid amount for you to reach your company goals? No. However, it is a large part of the job, and when one does not want to explain the other details this is the part of the job that comes to mind.
Different companies and advertisers utilize different bidding strategies, but normally you can categorize your bidding strategies into two buckets: automated bids or manual bids.
Automation: Marin and Acquisio are just a few systems with bid automation tools. Automation can be extremely helpful, and in some cases it is absolutely necessary. Automation allows for bids to be done more often, and obviously helps streamline the process, which is very helpful in large accounts.
Manual Bids: Bidding manually can be time consuming, but with inconsistent accounts, or smaller accounts manual bidding could be the correct route to take. Some automation could change bids based off of one great week on keywords that historically performed poorly. In some cases, manual bidding is better than automation.
Other Options: Self-Automation (Utilizing Free Form functions), or Excel Formula Bidding.
Utilizing Excel formulas for bidding strategies allows advertisers to utilize automation and manual bidding together. They help streamline the process, but also allow for manual checking of the bids before setting them.
So let’s get to the point. Below is an example of how to set up an Excel Bidding sheet with projections on how the bid changes will impact performance.
First things first, what is your goal, and how high would you want to raise bids at one time?
Next, let’s set up the formula used when changing bids. For this particular account, we utilized the CTR for keywords with lack of performance, but in lower positions in order to give these keywords a chance. The thought process is that if CTR is high, but spend is low, the keyword may be relevant, but it needs a bid bump in order to have a chance to perform.
Below are the bid changes we will utilize in this situation:
How this reads: starting from the top left corner, if conversions are greater than 0, average position worse than 4, and CPL at $50 or lower, we will raise the bid 20%.
The sheet above was created to work for multiple accounts with different goals. In order to make this happen formulas utilizing the users CPL Goal and Max Bid Change % were put into place to come up with the numbers you see. For example, the first section on the left under the >0 conversions and >4 Average Position has a formula throughout the CPL column and Bid Modifier column as seen here without the equals signs:
In this case J3 = the CPL Goal you put into place, and J4 = the Max Bid Modifier you put into place.
So, if you change your goal and max bid change %, then these columns will change accordingly, as seen below:
On the first tab you then must put in your keyword information:
The above image shows all of the columns you need to carry over onto this sheet. A simple download of a keywords list (be sure to filter for keywords with at least one click, as any keyword without a click will not have any automation in this particular formula) with the columns listed above is all you need. Make sure the columns line up, copy the list, and paste the values.
This first tab will be named “CPL Goal & KW Info.” This information may be needed for formulas on the next tab: “Bid Changes” tab.
This is where the action takes place. First thing you are going to do is title each column in the 1st row. Keyword, Campaign, Ad Group, Max CPC, Match Type, Clicks, Impressions, Cost, Leads, and Avg Position will all be carried over from the previous tab, so these will take up Columns A-J. In Column K and Column L the CPL and CTR will be calculated. Column M, N, and O will be Bid Change %, Bid Modifier (1+Bid Change %), and the New Max CPC. Column P, Q, and R will be Cost Projection, Lead Projection, and CPL Projection columns.
To start, utilize the formula below in Cell A2:
=IF(‘CPL Goal & KW Info’!I8=””,””,’CPL Goal & KW Info’!I8)
Drag this formula across to J2, and then all the way down the page. Your are copying the information from the CPL Goal & KW Info tab to the Bid Changes tab. Why not just put this information here at the beginning? We will get to that in a bit.
Next in Cell K2 implement the simple CPL formula of:
=IF(I2=””,””,IF(I2>0,H2/I2,0))
Column I shows conversions, while Column H shows spend. Drag this formula down the entire row.
In Cell L2 implement the simple CTR formula of:
=IF(G2=””,””,F2/G2)
Column G shows impressions, while column F shows clicks. Drag this formula down the entire row.
Next up is the Bid Change column, which consists of one of the longest “If Statements” you will ever see in your life. As long as your columns match up with everything that was shown thus far the formula in Cell M2 would be as follows:
=IF(AND(I2>0,J2>4,K2<‘CPL Goal & KW Info’!$B$5),’CPL Goal & KW Info’!$C$5,IF(AND(I2>0,J2>4,K2<‘CPL Goal & KW Info’!$B$6),’CPL Goal & KW Info’!$C$6,IF(AND(I2>0,J2>4,K2<‘CPL Goal & KW Info’!$B$7),’CPL Goal & KW Info’!$C$7,IF(AND(I2>0,J2>4,K2<‘CPL Goal & KW Info’!$B$8),’CPL Goal & KW Info’!$C$8,IF(AND(I2>0,J2>4,K2>’CPL Goal & KW Info’!$B$11),’CPL Goal & KW Info’!$C$11,IF(AND(I2>0,J2>4,K2>’CPL Goal & KW Info’!$B$10),’CPL Goal & KW Info’!$C$10,IF(AND(I2>0,J2>4,K2<‘CPL Goal & KW Info’!$B$10,K2>’CPL Goal & KW Info’!$B$8),’CPL Goal & KW Info’!$C$9,IF(AND(I2>0,J2>2,K2<‘CPL Goal & KW Info’!$B$15),’CPL Goal & KW Info’!$C$15,IF(AND(I2>0,J2>2,K2<‘CPL Goal & KW Info’!$B$16),’CPL Goal & KW Info’!$C$16,IF(AND(I2>0,J2>2,K2<‘CPL Goal & KW Info’!$B$17),’CPL Goal & KW Info’!$C$17,IF(AND(I2>0,J2>2,K2<‘CPL Goal & KW Info’!$B$18),’CPL Goal & KW Info’!$C$18,IF(AND(I2>0,J2>2,K2>’CPL Goal & KW Info’!$B$21),’CPL Goal & KW Info’!$C$21,IF(AND(I2>0,J2>2,K2>’CPL Goal & KW Info’!$B$20),’CPL Goal & KW Info’!$C$20,IF(AND(I2>0,J2>2,K2<‘CPL Goal & KW Info’!$B$20,K2>’CPL Goal & KW Info’!$B$18),’CPL Goal & KW Info’!$C$19,IF(AND(I2>0,J2<2,K2>’CPL Goal & KW Info’!$B$28),’CPL Goal & KW Info’!$C$28,IF(AND(I2>0,J2<2,K2>’CPL Goal & KW Info’!$B$27),’CPL Goal & KW Info’!$C$27,IF(AND(I2>0,J2<2,K2>’CPL Goal & KW Info’!$B$26),’CPL Goal & KW Info’!$C$26,IF(AND(I2>0,J2<2,K2<‘CPL Goal & KW Info’!$B$26),’CPL Goal & KW Info’!$C$25,IF(AND(I2<1,J2>4,H2<‘CPL Goal & KW Info’!$E$5,L2>5%),’CPL Goal & KW Info’!$G$5,IF(AND(I2<1,J2>4,H2<‘CPL Goal & KW Info’!$E$6,L2>3%),’CPL Goal & KW Info’!$G$6,IF(AND(I2<1,J2>4,H2<‘CPL Goal & KW Info’!$E$7,L2>5%),’CPL Goal & KW Info’!$G$7,IF(AND(I2<1,J2>4,H2<‘CPL Goal & KW Info’!$E$8,L2>3%),’CPL Goal & KW Info’!$G$8,IF(AND(I2<1,J2>4,H2>’CPL Goal & KW Info’!$E$10),’CPL Goal & KW Info’!$G$10,IF(AND(I2<1,J2>4,H2>’CPL Goal & KW Info’!$E$9),’CPL Goal & KW Info’!$G$9,IF(AND(I2<1,J2>4,H2<‘CPL Goal & KW Info’!$E$9,H2>’CPL Goal & KW Info’!$E$8),”0%”,IF(AND(I2<1,J2>2,H2<‘CPL Goal & KW Info’!$E$15,L2>5%),’CPL Goal & KW Info’!$G$15,IF(AND(I2<1,J2>2,H2<‘CPL Goal & KW Info’!$E$16,L2>3%),’CPL Goal & KW Info’!$G$16,IF(AND(I2<1,J2>2,H2<‘CPL Goal & KW Info’!$E$17,L2>5%),’CPL Goal & KW Info’!$G$17,IF(AND(I2<1,J2>2,H2<‘CPL Goal & KW Info’!$E$18,L2>3%),’CPL Goal & KW Info’!$G$18,IF(AND(I2<1,J2>2,H2>’CPL Goal & KW Info’!$E$20),’CPL Goal & KW Info’!$G$20,IF(AND(I2<1,J2>2,H2>’CPL Goal & KW Info’!$E$19),’CPL Goal & KW Info’!$G$19,IF(AND(I2<1,J2>2,H2<‘CPL Goal & KW Info’!$E$19,H2>’CPL Goal & KW Info’!$E$18),”0%”,IF(AND(I2<1,J2<2,H2>’CPL Goal & KW Info’!$E$27),’CPL Goal & KW Info’!$G$27,IF(AND(I2<1,J2<2,H2>’CPL Goal & KW Info’!$E$26),’CPL Goal & KW Info’!$G$26,IF(AND(I2<1,J2<2,H2>’CPL Goal & KW Info’!$E$25),’CPL Goal & KW Info’!$G$25,IF(AND(I2<1,J2<2,H2>’CPL Goal & KW Info’!$E$24),’CPL Goal & KW Info’!$G$24,”0%”))))))))))))))))))))))))))))))))))))
This formula can just be dragged down the entire sheet as well.
Column N2 will then be a simple formula of:
=M2+1
And Column O2 will be the New Max CPC for the top keyword, and the formula here will be:
=IF(D2=””,””,N2*D2)
Both of these formulas will be dragged down the entire page.
You now have your automated bid changes. You can look through these automated changes and do a quick check on them manually before implementation.
Cell P2 will contain the formula:
=((((H2/F2)*O2)/D2))*(F2*N2)
Cell Q2 will contain the formula:
=IF(R2>0,P2/R2,0)
Cell R2 will contain the formula:
=IF(I2>0,((((H2/F2)*O2)/D2)/(I2/F2)),0)
All three of these formulas will be dragged down the entire page. This makes projections based off the new projected cost per click, and projected increase in clicks with conversion rates constant.
The final tab is a projections tab that should end up looking something like this image below:
As long as you named the second tab “Bid Changes” the formulas in these cells will be as follows:
For easy implementation through the editor moving forward, try hiding all Columns in the Bid Changes tab, except for Columns A, B, C, E, and O (Keyword, Campaign, Ad Group, Match Type, and New Max CPC). You can then copy and paste these columns into the editor when selecting “make multiple changes” within the keyword tab. Just be sure to go into the preview tool before hitting “process”, and make sure the New Max CPC column is labeled as Max CPC within the editor, as seen below:
Conclusion
My hope is that this article helps you create a bidding strategy sheet within Excel, This strategy allows advertisers to utilize automation, but continue to have the advantages of manual bid changes if needed.
