Are You Maximizing Your Revenue? Impression Share Reporting in Excel
May 21, 2015
Lost Impression Share Due to Rank – The estimated percentage of impressions on the Search Network that your ads didn’t receive due to poor ad rank.
This seems to be a very important metric. Especially when talking about the top performing keywords in your account. So, why is it so underutilized?
A lot of the paid search community has appeared to put all their eggs in the Average Position basket. The philosophy behind using Average Position is fairly simple. You raise your bid, you improve your Average Position, you get more clicks.
Recently I wrote an article about bidding based on Top v Other positioning along with Lost Impression Share. This post helps explains why the philosophy of only using Average Position when making bid changes can be flawed. You have to take the Lost Impression Share into account. As you can begin showing in more auctions when you raise your bids, which could theoretically lower your Average Position.
So maybe you are convinced now that using Lost Impression Share Due to Rank more often would be a good idea. But in what ways should we use this metric? The first area I like to utilize this metric for is to get a “room for growth” estimate on keywords that are currently in the account.
Below is the step-by-step of this process:
Step 1: Download a keyword list with performance metrics such as: Clicks, Impressions, Cost, Conversions, Total Conversion Value, and Lost IS Due to Rank.
Step 3: Show how many impressions have been received and how many impressions have been missed by using a formula for missing impressions:
=(Impressions/(1-Search Lost IS Due to Rank))*Seach Lost IS Due to Rank
Step 4: Add filter and sort by your performance metric
Step 5: Based on your goals, label each keyword how you would want to see statistics. An example of this is to label keywords: 1000% ROAS and Over, 700-999% ROAS, 500-699% ROAS, 300-499% ROAS, 100-299% ROAS, Under 100% ROAS
Step 6: Create a Pivot Table using these labels to view what % of impressions have been lost on your top performing keywords. The formula: lost impressions/(impressions + lost impressions) will give you a % of auctions you missed out on for each set of keywords.
On step 6, why not take the average of the lost IS due to rank? The reasoning is because this would then treat each lost IS due to rank number the same regardless of the amount of impressions connected to that keyword. With splitting it up and completing the formula in a raw data sheet and bringing it back to a percentage in the pivot table we are able to get the correct amount of impressions that have been lost on keywords at each ROAS level.
At the end of this report you should have something like we see here:
Let’s say that this account has a goal of 500% ROAS. The bottom three sections we would focus on changing bids and optimizing in order to gain more of those lost impressions on keywords that are reaching our goals. This report would show that we are missing out on 61,094 impressions on keywords that are reaching goals.
Let’s dig deeper into this type of reporting and try to see what % of eligible auctions we are on top of the page, are showing but not on top of the page, and are not showing at all.
For the initial set up we can follow the step-by-step process used when bidding by top v other data.
Here is that process taken straight from the previous blog post:
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 vs 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.
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:
This is the correct formula where keywords are listed in row A on the initial keyword list and in row B for the network breakout tabs. While, impressions are listed in row G on the network breakout tabs.
This will get you the amount of impressions for the top of the page for each keyword in that line. Utilize this same formula for each tab. If you utilize Search Partners you can add Search Partner impression data with Google Search impression data in order to get a total “Top of Page Impression Amount” and “Other Impression Amount”. You can use the same type of formula for clicks, but you would use B:H and 7 respectively within the VLOOKUP formula to receive the click data.
Quick Note: If you have the same keyword in your account multiple times for whatever reason, doing a column with =keyword&campaign&adgroup, then doing the VLookUp off of this column rather than the keyword column can be helpful.
Once this is all set up, we can do Step 2 through Step 6 from the initial process again in order to get ROAS and/or Cost per Conversion numbers organized the way we want to look at them based on the goals of the account.
When the Pivot Table is finished you will end up with something that looks similar to:
This report shows where growth can be had at each level of ROAS, and can really tell you where you are in terms of maximizing sales.
Browse By Category
Seven insights hiding in Google’s new Christmas shopping research
In December 2017, Google released a set of statistics about the Christmas shopping season. Use these insights in AdWords to make this your best December ever.
Stop Using Google's First Page Estimates to Set Your Bids
Google's First Page Bid Estimates can't be trusted. Adjust your bid strategy based on average position and not using this metric.
Your Guide to Getting Organized for PPC Success
Organization is key to success in the PPC industry. Here is a guide to organizing your space, time, resources, and workload for efficient PPC management.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
A Reminder About Work-Life Balance
Proper Work-Life Balance Is Something Easily Forgotten. Here Is A Reminder That It's Okay To Have A Life Outside Of Work & Constantly Checking Performance.
The Advertising Principles That Guide Facebook's Decisions
Facebook highlights the advertising principles that guide their decision making in response to recent events.