SEO Marketing

Leveraging PPC Search Query Performance for Targeted SEO

January 11, 2011 Posted In: SEO Marketing Comments: 3

Every year about this time we find ourselves finalizing our goals and strategies for the New Year. Our bosses are looking to us for actionable strategies and suggestions to meet our new revenue goals. How much will PPC contribute? What is our primary SEO strategy, and what growth can we expect?

Making accurate forecasts and predictions for yearly goals can be difficult. We try to take what we already know from past performance to make intelligent estimates about the future.

One great place for an SEO team to look is at PPC search query performance data (not keyword data); especially if your PPC strategy includes using broad match keywords with search query mining. Inside of your PPC search query data you will find search queries that may be big contributors for PPC and don’t rank well at all for SEO.

These search queries might be the low-hanging fruit your SEO team has been looking for, and because you already have conversion and revenue data, it’s much easier to justify the needed resources and make reasonable predictions. 

Getting the right data

Each of us may have a different web analytics solution, so I’m going to describe a generic example of how to get the right data into Excel for analysis. For example, I write SQL that pulls data directly into Excel from our web analytics data warehouse. Others may need to pull data from Google Analytics, SiteCatalyst, Clicky, etc.

Regardless of your web analytics solution, you have to have proper tracking in place to distinguish your paid search queries from your organic search queries as well as conversion tracking enabled.

Here are the columns you will need to pull for both your PPC and SEO search queries for the same date range (I would pull a year’s worth of data, since we are making a forecast for yearly revenue):

  • Search Query
  • Sum of Clicks (or Visits, Visitors)
  • Sum of Conversions
  • Sum of Revenue (if you can)

Once you get your data into Excel, you should have two separate worksheets: one for PPC and one for SEO.

PPC Search Query Data

Preparing your data for analysis

In order for us to analyze our data easily in Excel, we are going to add a few columns to our PPC worksheet using both the RANK and VLOOKUP formulas.

Here are the columns we are going to add:

  • PPC rank
  • SEO rank
  • SEO clicks
  • SEO conversions
  • ConvDiff

Before we get started, remember to format your data as a table in Excel by highlighting all your data and clicking on Format as Table from the Home ribbon. This will make working with your data set much easier.

Format as Table

Adding a PPC Rank and SEO Rank is as simple as using this formula:

=RANK([@Conversion],[Conversion])

Using the rank formula allows you to know how important your search query is (i.e., your #2 PPC search query could be your #30 SEO search query).

Use a VLOOKUP formula to bring over your SEO Clicks and SEO Conversions for comparison in your PPC Search Queries worksheet. I’ve included the IF(ISNA formula for you as well.

=IF(ISNA(VLOOKUP([@[PPC search query]],Table13[#All],2,FALSE)),0,VLOOKUP([@[PPC search query]],Table13[#All],2,FALSE))

Here is what your data should look like after you’ve prepared it for analysis:

Web Analytics Data

And here is a FREE download of the example above so you can get a better look at the formulas.

Analyzing your data

Now that you’ve done the work to prepare your data for analysis, you will be able to see easily which contributing PPC search queries are underrepresented in your SEO search query list. Several of these will be low-hanging fruit for your SEO team to go after.

While you’re analyzing this data it is a good idea to try to understand the relationship between your PPC search queries and SEO search queries that are appearing on the same SERP.

What is the difference in contribution for a PPC search query with an average position of 1.5 and an SEO search query that ranks 1 in the organic results? Is it a 50/50 split in contributed conversion, or is one outperforming the other quite a bit? Spend some time getting to know how your PPC and SEO search queries complement each other.

Once you get a feel for how your PPC and SEO search queries play together, you will be able to make better predictions about getting new SEO search queries on the first page, etc. (i.e., we know that similar SEO search queries typically produce 20% more revenue than the PPC search queries, so we can expect X revenue if we can get this search query ranking in the top 3 organically).

Of course this analysis works both ways. You can also easily see which SEO search queries are bringing in conversions where PPC isn’t.

Conclusion

You’ve already paid for clicks, so you might as well leverage your PPC search query data as much as possible. Sharing your PPC performance data with your SEO team makes good sense and can help you focus on highly targeted search queries instead of relying on keyword research tools which lack conversion data. If you know a search query works for PPC, most likely it will work for SEO also.

By Chad Summerhill, author of the blog PPC Prospector, provider of PPC tutorials, and in-house AdWords Specialist at Moving Solutions, Inc. (UPack.com and MoveBuilder.com).

AdWords Performance Grader




If you enjoyed this post, please consider leaving a comment.

Comments

Friday January 21, 2011

Weekly PPC Roundup – AdWords Updates Edition (not verified) Said:

[...] Boost CTR put out another in a series of Ad Text Guidelines best practices tips–this week’s post shows advertisers Ad Text Limits, Ad Text Lengths, and Editorial Guidelines for AdWords and AdCenter.  Another great ad writing how-to this week was Marketing Experiments’ How To Test Your Value Proposition Using PPC Ad Text.  Our friends at WordStream also had an awesome guide on integrating PPC Search Query Performance into with SEO strategy. [...]

Wednesday March 16, 2011

Anonymous (not verified) Said:

Looks interesting, but how do you handle match type and the fact that your keyword in ppc might not be what the user typed in?

Wednesday April 10, 2013

SEO Specialist (not verified) Said:

I just want to find out how you obtain the Conversions generated by each particular organic/ SEO Search Query. With PPC I can get that information upfront, how can I pull this Data for my SEO words?

Leave a Comment

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.
 
Free Keyword Tool

Get thousands of relevent keyword suggestions - more,
faster, free!

Free Keyword Niche Finder

Discover profitable pockets of keywords for your
business.

Free Negative Keyword Suggestion Tool

Identify wasted spend before it happens and increase
your paid search ROI.

Contact Us | Company | Support | Site Map | Trademarks | Privacy Policy © 2007-2014 WordStream, Inc. All rights reserved.