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 Google.com data here (an available segment) and sorted by ‘lost impressions per day on Google.com.

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 ‘Google.com’ 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 ‘Google.com’ and ‘Google search partner’ data.
3. Divide the number of ‘Google.com 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 Google.com,’ 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!