Creating an “Opportunity Report” – PPC

SMS Text
Creating an “Opportunity Report” – PPC

Creating an “Opportunity Report” to Show How to Get More Out of Your Accounts

A lot of marketing managers and C-level executives I run across really aren’t sure how good their AdWords account is. That’s certainly to be expected because, in general, the people expected to evaluate the performance aren’t really experienced in PPC enough to know the ins and outs. Paid search is pretty complex nowadays, and it’s simply unfair to expect anyone outside of the Account Manager to be knowledgeable enough to evaluate performance accurately. So, how does the Account Manager show they’re best of breed during evaluations? Do they depend on Google’s overly simplified and vague ‘analyze competition’ tab? Can they trust Google account reps whose focus is spread across dozens of accounts, and, in all likelihood, have been in PPC only a year or two? Do they bring in a third party for an account audit that knows nothing about your business? What about using something like Compete or Hitwise?

Working at an agency, we’ve learned (the hard way) that none of these are ideal options. So, as in most things related to PPC, I prefer a less passive approach. Here’s how you can construct your own “Opportunity Report” that gives an accurate, detailed view of your account performance – and highlights the quickest ways to improve it.

Step One:

Structure your account in a way that results in the most reliable performance metrics – including ad group impression share.

Google is really doing a bang-up job of giving marketers more data. The biggest leap forward in this area, in my opinion, has been ad group-level impression share data. This information will be a vital component of the account’s opportunity report. In order to leverage ad group level impressions share data, however, you need to make sure you’re working with a valid metric that can be applied all the way down to the query level. Hence, the following must be true:

  • The ad group must contain only one keyword
  • The keyword in the ad group must be in exact match (i.e. the keyword is also required to be the query)
  • Keyword-to-query mappings must be forced by utilizing negative match (i.e. when a user searches a given query, that query can only be sent to the keyword in your account)

Because of all of these requirements, you should limit yourself to structuring things in this manner for only the high-volume queries/exact match keywords (PPC Associates calls these ‘Alphas’). I tend to use some sort of conversion-based cut-off to determine where to draw the line on high-volume queries (for instance, the query must have 10+ all-time conversions in the Search Query Report). For most accounts, you can set up at least 70% of your traffic to go through this keywords setup without the structure becoming too large to work with.

One result of this setup is that you now have query level impression share data.

Step Two:

Translate the ‘Impression Share’ report into a ‘Lost Impressions Report” for the keywords in the campaigns described in the first step.  

I was a high school math teacher in a former life, and I used to say ‘fractions are your friends’ to my students all the time (this tended to make them rather annoyed, which was a nice bonus). But, as it relates to creating an opportunity report however, fractions aren’t your friend! To quantify and sort by opportunity, you need to know how many impressions were lost, not what percent of impressions were lost (volume matters!). To do this, take your ad group-level impression share (which, if you’ve set up as described in step one, is query-level impression share at this point), and apply some math.

Here’s how:

  • Google tells you how many impressions you have, your impression share, and your lost impression share
  • So, say you have 80% impression share and 1,000 impressions. This means ‘80% of the total number of impressions is 1,000.’
  • In an expression, that is 0.80X=1000, where X is the total number of impressions
  • X = 1000/0.80 = 1250. This means there were 1,250 impressions available (i.e. there 1250 searches performed over your report’s given time period)
  • The number of lost impressions was your lost impression share, times the number of total impressions. In this case 0.20(1250)= 250
  • Last, divide the number of lost impressions by the number of days in your date range to create a new metric of ‘lost impressions per day.’

There are a several other equivalent ways to calculate the number of lost impressions as well, but however you choose to do it, you should end up with a nice ‘lost search query impressions report’ that will look something like this for the keywords in campaigns that follow the described structure in step 1. I just used data here (an available segment) and sorted by ‘lost impressions per day on

From there, it’s pretty easy to determine why the impressions were lost. If your PPC account is on par, it’ll always be because you weren’t able to bid high enough, because it would have resulted in a CPA (or ROAS, ROI, etc.) that was higher than allowable based on the account’s goals.

Step Three:

Create an ‘Other Impressions Report” for the keywords in the campaigns described in the first step.  

The lost-impressions-per-day report is pretty neat, but it’s only half of the ‘opportunity report.’ The rest can be formed by leveraging ‘top vs. other’ data (if you need a reminder, here’s why getting the top spots is so important).

Here’s how to do this:

  1. Export a keyword (or ad group) report with a ‘top vs. other’ segmentation for campaigns structure in the manner described in step 1.
  2. Only use ‘’ data. Partner search data is, unfortunately, going to skew the results in a massive way! (Many partner sites don’t have ‘top’ impressions available.) At the very least, create a different report for ‘’ and ‘Google search partner’ data.
  3. Divide the number of ‘ other impressions’ by the number of days in your date range

The result should look this (after a bit of Excel formatting).

Again, at this point you should be able to create a rather clear narrative at the query level as to why you’ve had the given number of ‘other’ impressions. Hopefully the reasons for an ad not appearing on the ‘top’ is due the inability to raise bids based on the account’s current goal set.

Step Four:

Create the finished ‘opportunity report’

If you’d like to merge these reports, you’ll need to modify your ‘lost impressions report’ to a ‘lost impressions report on,’ like I had (so you’re not mixing some data that has partner data, and other data that doesn’t). If you choose to do this, you’ll end up with a lot of really great data that you can trust! Since along the way you’ve created a narrative as to why each keyword is under the circumstance it’s in, the merged results represent opportunities that could be captured if either:

  1. The goals of the account changed, allowing higher bidding
  2. The conversion rate improved, allowing higher bidding
  3. Your CTR changed by improved ad copy, which leads to higher ad rank.

Of these, only the CTR component is directly controlled by the account manager – meaning the variables for which you’re responsible have been limited to one.

Here’s an example of how you can merge the data and create a hypothetical output. In this hypothetical, I wanted to know what would happen if we had 100% query-level impression share for high-volume queries, and all these impressions were on the ‘top.’

If you plan on creating a merged report like this, make sure you’re a VLOOKUP master! You need at least an intermediate knowledge of Excel to keep the ‘opportunity report’ from turning into a long project. Additionally, make sure you keep using query-specific metrics, since you’ve got conversion rate and CTR information for each query from the original exported reports.

What’s great about a report like this the ability to quantify the value in something like improving the conversion rate (this could also help you determine how much to invest in things like funnel optimization). I’ve run this exercise with one of my clients and concluded that if the client could improve conversion rate by 100%, we could get 300% more conversions at the same margin per sale. The client held up their end of the bargain, and I came back with 310% more conversions! So, while fractions might not be our friends, more data certainly is.

Happy ‘opportunity report’ creating!

Mike Nelson
Mike Nelson is a senior account manager at PPC Associates, an SEM agency with offices in the Bay Area and downtown Chicago.
Get the latest news from Search Engine Journal!
We value your privacy! See our policy here.
  • Seo Training Ahmedabad

    Nice article …Really informative…but can you provide some screen shot for better understanding ……

  • Tim

    “The ad group must contain only one keyword”

    Not really possible for SEM in industries where the bulk of queries are spread out over the long-tail like Real Estate, Employment etc.
    Think of the combinations – “java jobs in Seattle”
    “apartments in Tribeca”

    You can structure it this was for the head searches like ‘java jobs” and “apartments”, but these are typically helper queries that don’t convert well.

    What do you suggest in these fields?

    • Mike Boudet

      It’s not only possible, it’s the right way to do it. If you’re lumping long tail keywords targeting various different cities in the same ad group, then you’re doing it wrong.

      • Tim

        We’re not ‘lumping’ more than we have to.
        We do have 5k+ ad groups, representing close to 200k keywords.
        Still a far cry from each ad group containing only one keyword.

        And that is not even going into the real long tail yet, which would easily double or triple the number of keywords we could bid on.
        I’m still struggling to get my head around the idea that you should aim for a 1-1 mapping between keywords and ad groups on large campaigns.
        Mike – are you managing campaigns where there are more than a few hundred thousand keywords you are bidding on?

      • Tim

        to further clarify, let’s say I have a geo-targeted campaign to a particular region.
        Within that region I have an ad group for the town or city.
        Within that ad group I have up to 30 keywords.
        Let’s pretend that the ad group is for the english town of Essex.
        So within that ad group I would have keywords like:

        flats for sale in essex
        property for sale in essex
        flat essex
        apartment essex
        house in essex
        house for sale in essex
        properties in essex
        farms for sale in essex
        real estate agents in essex

        These are all exact or phrase match.
        I have further campaigns for broader terms like “apartments for sale”, “Houses to rent” etc.

        This keeps the overall campaign manageable.
        Is the ‘best practice’ advice really to create hundreds of thousands of ad groups, each containing only one keyword?

      • Mike Nelson

        Hey Guys

        I tend to agree with both of you to various extents.

        Tim, there will always be a ‘head’ in your account, where you can use 1 keyword ad groups. For some accounts, jobs and housing verticals included, that ‘head’ will actually be pretty small. In fact, it might be so small that you don’t want to bother with making them into 1 keyword ad groups. The head will still exist however. It’s certainly easier to work with accounts that have a large ‘head’ as it relates to day-to-day management, as well as reporting performance. Your ‘head’, for example, might be in those ‘introducers’ you mentioned. You also need to ask yourself – ‘Do I even need to increased reporting capabilities or 1 keyword ad groups’?

        So, you are certainly correct that you need to draw the line with one keyword ad groups. This is the pretty common in our world. What’s optimal and what’s practical don’t always coincide. For example, I’d prefer to be in every auction for every client, but we’d be bidding fractions of a cent for most of these auctions – that’d make my clients the most profit at the end of the day. This, however, isn’t practical (or possible) for several reasons. The same is said for 1 keyword ad groups. It’d be optimal for all high volume queries to be in 1 keyword ad groups due to improved reporting capabilities (among other things), but it’s not practical, and a huge time suck if you take it too far. That’s why, in my article, I said you need to draw the line at some point, and I tend to use a conversion based cutoff. I don’t have more than a few thousands single keyword ad groups for any 1 client, though certainly these clients have several thousand of lower volume keywords that are set-up in multikeyword ad groups.

        I’m not sure if it’s true across the rest of your account – but if you are in a ‘tail’ vertical, which it seems like you’re managing, you should probably have less emphasis on phrase and exact match in order to expand your reach a bit. In these types of accounts, I typically run a lot of modified broad match – which will bring what I call a ‘class’ of queries. So, if you bought ‘+house +sale +essex’, you’d probably want all queries in that ‘class’. You could setup your account in that manor, and run a slightly modified ‘opportunity report’ . Be very careful however – even when dealing with modified broad match you’re dealing with a ‘class’ of queries, so make sure you’re on top of your Search Query Report to assess the validity of your Impression Share and CTR metrics. Typically I don’t hold these metrics in high regard for these match types.

        Looking at your sample ad group, I would argue that it’s not well grouped. If I were to group them, all keywords would have the same location, and contain the same ‘seed’ (such as house, flat, apartment, etc). Again, this seems daunting if you’re using phrase and exact match only, but with modified broad match (or even regular broad) match, it’s not much effort – you’ve just gotta make sure you’re on top of your SQR.


      • Mike Boudet

        Mike Nelson said it perfectly

  • Tushar

    Really nice, never read this kind of reports in any tutorial not even Google adwords online certification tutorial.

    Thanks for the informative article.

  • Tim

    Thanks for your detailed response Mike. Your answer makes a lot of sense, and I guess the bottom line is to be pragmatic and put the effort in where it’s going to pay off.
    I’ll take a closer look at the structure and see if there is some restructuring we can do