Using SQ Reports to Prioritize Keywords (And Write Better Content)

Disclaimer: I am not an SEO dude. I don’t have the same link building chops as Old Man Suarez, nor the architecture savvy of Senor Snyder. However, I am well versed in the timeless art of PPC and like to think I know a thing or two about the intertubez.

So picture this: After months of hard work, you’ve got top rankings for some of your keywords (bravo SEO’s!) and want to write some content to expand your reach into the long tail. More than likely, you would head over to Google Suggest/Promediacorp’s Suggester/Soovle or any other tool of choice and get some ideas for keyword variations you’d like to rank for. Then, you drop these variations into your traffic estimator of choice and boom; you’ve got keywords with volume. Finally, you would start developing content that’s designed to rank well, attract links and (above all) customers. You know the content development phase is going to take a long time, but eventually you’ll get those rankings and sweet, glorious traffic!

The fatal flaw in the (commonly used) method above is that you have no real idea how that traffic is going to convert. You’ve got about 900 possible long tail versions of that keyword that you’d love to rank for. But, can you really write content for all of those 900 keywords? Which keyword do you choose if there are 10 variations, all with the same search volume?

The answer to all of these questions lay within a gigantic dataset that your resident PPC Professional already has – Search Query reports! For those of you that don’t know, a search query report is a tool offered  by most major search engines and contains key metrics (impressions, clicks, cost, conversions) broken down by the exact query searched. That means you can find the exact keyword variations that haven’t just driven traffic but (drumroll please) CONVERTED!

Pretty valuable stuff from a PPC perspective, but here’s a trick that Wil and I came up with to help the SEO’s of the world to write content with keywords that will not only generate traffic volume, but are most likely to drive conversions as well.

Note: The guide below assumes you have excel 2007 or later. If you need old school instructions, leave a comment and I’ll write a guide for other versions.

1: Pick your lead horse

The first step is picking the lead keyword for which you’ll be optimizing the page. In this case, we’re going after Help Desk Software, which has 6,600 monthly searches.

2: Pull your SQ report

Navigate to the “keyword” tab of your AdWords interface and click on “See Search terms – All.” You could filter now, but I prefer to toy with the excel sheet itself once we have all the data so we don’t miss anything.  Make sure to pull it for a long time period – in this instance I’m going for the last 14 months but the more data you have, the better.  

3: Clean the data.

After you’ve exported the data into Excel, we need make it easier to work with. First, eliminate all of the columns you don’t need. Eliminate EVERY column except for Clicks, Impressions and Conversions (we’ll need conversion rate as well, but don’t worry about that now). If you really want, you can keep cost in there, but for this purpose it’s not necessary.

4: Organize the data

Hope you’ve got plenty of RAM. Depending on how much you spend and how large the account is, you’ll likely have a TON of data. In this example I ended up with approximately 18,000 rows, many of which are duplicates (a single query could be sourced from several different campaigns/countries/keywords). This is where our good friends Pivot Tables and Patience come into play. First, create a pivot table with the entire Search Query report included.

Add Search Term as the row label. Add Impressions, Clicks & Conversions as the values. This will create a dynamic chart that totals all the duplicates for you.

Ta-da – we have totals! (Note: you could use Sub-totals for this, but I find that takes a bit longer and is kind of annoying). Copy the entire pivot table into a new sheet (copy – paste special – paste values) so you can...

5: Play with the data

There’s a ton of great data, but in this case we’re only concerned with tails that contain the phrase “help desk software.” On the excel sheet execute a find-and-replace for help desk software, only changing the color.

Sort the cells by color (custom sort – sort on cell color – your color on top) and voilà, you’ve got all the “help desk software” queries isolated. I’d advise you to set a threshold from here on out (i.e. remove any queries with less than 10 conversions) so you’re only looking at significant data. Add in and calculate a “conversion rate” column, and sort the list in descending order. Depending on how big your initial data set was, you should have a nice little list of tail terms to weave into content.

It’s not a bad idea to run these through a traffic estimator just to make sure they get some traffic. In this case, all but 2 keywords showed up in the estimator.

6: Write!

So what do you think? Will you be using query data to help with your content efforts?

For a few more PPC-to-SEO ideas, check out Our Powers Combined Part 1, written by Ryan Fontana and myself!