Since I started my job as the online marketing manager for BuildASign, I’ve been working with a lot more SEO data. I’ve always thought that SEO and PPC work well in conjunction – data from one platform can help you succeed in the other. But now that I have responsibility for both marketing channels, I’m getting a lot more opportunities to do interesting stuff with our analytics data.
Here’s a neat little trick I learned to use organic search query data to prospect for profitable PPC keywords. To do this, you’ll need an analytics platform that tracks revenue data by keyword (for this example, I’m using Google Analytics) and the Google Keyword Tool.
First, you’ll need to pull up your organic keyword performance. In Google Analytics, you can see this by selecting Traffic Sources > Sources > Search > Organic from your navigation:
Once you’re here, choose a nice, long period of time to look at (12 months or so should be good) and click over to the “Ecommerce” tab:
Now, you should have several columns of data for your analysis: Visits, Revenue, Transactions, Average Value, Ecommerce Conversion Rate, and Per Visit Value. Export all of these into a CSV and open it up in your favorite spreadsheet program. Go ahead and delete any rows of keywords that have no revenue associated with them – they’re not important for our purposes.
Once you have a list of organic keywords that have generated revenue, move on over to the Google Keyword Tool. Copy all the keywords, enter them in the keyword tool, and run a report. Make sure you have the columns “Local Monthly Searches” and “Approximate CPC” active. Also, you should run the analysis for Exact match type only to ensure the most accurate data.
After the report finishes, you should have each of the keywords in your analytics export associated with an exact match search volume and an estimated CPC. Create three new columns in your original spreadsheet: two to hold this new data and one to add a formula to. Paste in the search volume and CPC data, making sure that each figure is associated with the right keyword. Next, add this formula to each cell in the third column:
Where X1 = the value in the “Per Visit Value” column and Y1 = the value in the “Approximate CPC” column. Your final result should look something like this:
Once you have copied down this formula to all keyword rows, you’ll start to see some patterns emerge. Some of these calculated values will be positive, and some will be negative. Since the per visit value number equals your number of visits divided by your revenue, it should give you a rough idea of how much each visit is worth. And since a PPC click is just another visit, it stands to reason that each of those should be just as valuable as an organic click. By subtracting the cost of a PPC visit from the total visit value, we get the difference between them. If you get a positive value, you stand to make more money than the cost of the click with each new visit. If you get a negative value, you might end up losing money.
This is a rather crude calculation, but is a good estimate on whether or not a PPC keyword has the potential to be profitable. At the very least, it’s a quick sanity check before you go ahead and add a lot of new keywords to an account. Using this analysis, you can filter down your organic keywords to only ones that a) have revenue and b) will generate more revenue per visit than the average CPC. Once you have that, you can review your keyword list to see which ones are not currently in your PPC campaigns and add them to your account.