Back in April, I wrote a post on why top v other bidding methods are better than using average position. This article stated that using impression share when changing bids is extremely important and average position does not take impression share into account. I ended this article by hinting that a formula was in the works on how to bid based on top v other v lost IS. That formula has been completed.
The first step to creating this formula was coming up with some sort of metric similar to average position to base the bid formula, but making sure the metric took impression share into consideration. I introduce to you: the impression metric. Okay, I could have come up with a much better name for this metric, so feel free to decide for yourself what the metric should be called in the comments below, or via Twitter.
What this metric shows is impression share, but only considering impressions on the side or bottom of the page as half of an impression. The formula is: (Top Impressions+(Other Impressions/2))/Potential Impressions. We can take a look back to the previous article that showed how to come up with top Impressions, other Impressions, and lost impressions for a keyword:
Step One: Filter keywords by > 1 impression and download the entire keyword list
Step Two: Go to Segment > Top vs Other and download the keyword list once again
Step Three: Filter across all columns on the top v other sheet
Step Four: If your campaign includes the Search Partner Network make 4 copies of the top v other data, if not then make 2 copies of the top v other data
Step Five: Filter each of the 4 copies by a different segment. Delete the other three segments on each sheet. (if you don’t utilize Search Partners do this for the two copies). You should now have a tab for Top of Google, Other of Google, Top of Search Partners, and Other of Search Partners.
Example (You should have one tab of data like this for each segment):
Step Six: Copy all tabs into one document, so you have 5 total tabs. 1) regular keyword data, 2) top on google data, 3) other on google data, 4) top on search partners data, 5) other on search partner data
Step Seven: Utilize the excel formula:
=IF(ISNA(VLOOKUP(A:A,Top!B:G,6,FALSE)),0,VLOOKUP(A:A,TOP!B:G,6,FALSE)) in order to retrieve the amount of impressions from each area. Then, add the two tops and two other columns together to get the total top impressions and total other impressions.
Step Eight: Get lost impressions by using: =(Impressions/Impression Share)*Lost IS, or =(Impressions/(1-Lost IS))*Lost IS
From here it should be easy to come to the impression metric by using the formula given above.
You then can utilize your goal side-by-side with the impression metric in a formula to come up with your bid change within Excel.
Here is an example of how you could decide to set up your formula:
In this sheet, the formula changes when the ROAS Goal, Max Bid Change, or AOV change on the sheet in the “Your Bidding Strategy” section, and the IF Statements are then tied to the numbers within the table. So, for example the first line would read: =IF(AND(Impression Metric>30%,ROAS>5000%), 20%). The IF statement here would live in another tab where we would have all of the keyword information from the step-by-step process above. In this example, the IF statement is an extremely in-depth bid change solution.
You can also make things much easier by not creating any sort of table once the step-by-step process is finished. In this example the IF Statement is extremely long, and you can have two separate if statements (one for if the keyword has a sale, another using the second table if the keyword does not have a sale), then from there in a third column use an IF Statement as to if the keyword has a conversion use the sales column as a bid modifier, if not then use the no sales column as a bid modifier. Below you will see that in the particular sheet used here, there are four different IF Statement sections: one for keywords with 5+ sales in the used date range, one for 3-4 sales, one for 1-2 sales, and one for no sales:
This IF Statement directly follows a table similar to the color coded table you see above, as do each of the IF Statements used in the sheet. When reading the first line in this IF Statement you will see it notes:
IF(AND((Conversions>=TableConversions,ImpressionMetric<TableImpressionMetric,ROAS>TableROAS),TableBidChange
And this phrase/statement continues throughout the entire table.
The Bid Change column then uses a much easier IF Statement: =IF(Conversions>5,AC2,IF(Conversions>3,AD2,IF(Conversions>1,AE2,IF(Conversions=0,AF2)))) as seen here:
The Bid Modifier column then adds the 100% current bid to the equation, and the new bid is based on the recommended bid change across all the formulas utilizing the amount of sales, ROAS (or current cost compared to AOV on keywords without sales), and the impression metric.
There are a lot of ways to simplify this process in terms of creating a formula that works best for you. For example, you could just write a formula for if we are above the ROAS goal, and Impression Metric is below 75%, raise bids by X percentage. Also – our team is coming up with utilizing the index formula across the table in order to shorten up the process of creating these types of bid sheets, but keeping the granularity of the bid changes. The options are unlimited, but the concept is unchanged.
Understanding that the exact formula is a tough thing to follow here, I will leave with this: Try using impression metric rather than average position. It will help drive more impressions where you are missing out on impressions rather than basing bids on an average of a variety of auctions on broad terms.
Questions are encouraged in the comments section as helping fellow PPCers out is what PPC Hero is all about.
