Are You Maximizing Your Revenue? Impression Share Reporting in Excel

By Jacob Brown | @jakebrownppc | Account Manager at Hanapin Marketing

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 2: Add ROAS or Cost/Conversion columns (whichever you utilize to judge performance)


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:


Lost Impression Data Example


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.