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.
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
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.
Adding a PPC Rank and SEO Rank is as simple as using this formula:
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:
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.
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.