Making Accurate Internet Sales Forecasts
Every year about this time dealership Internet Departments are finalizing their goals and strategies for the New Year. GM’s and owners are looking to the Internet Managers for actionable strategies and suggestions to help meet new revenue goals. How much will PPC contribute? What is the 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 Internet Manager 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 could be the low-hanging fruit your Internet Department 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, we have written SQL that pulls data directly into Excel from Google Analytics. We do this for multiple dealerships and we also have a web analytics data warehouse that allows us to benchmark results and create best practices. You may need to pull data from Google Analytics, Site Catalyst, Clicky, etc. Of course our clients do not have to do any of this since we provide all this for them as part of our Lead Harvesting bundle.
Regardless of your web analytics solution (you know from prior articles we prefer Google Analytics). 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 are 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:
Email me directly and I will send you an excel template 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 Internet Department 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. Leveraging your PPC performance data to enhance your SEO 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.
If you have any questions you can contact us directly. You can also leverage our best practices and proven keywords lists to really enhance your growth.