These days predictive utilities like Trellian, WordTracker and AdWords are only half the keyword research story. State of the art processes combine these standard search predict tools’ output with “what we already know,” by mashing in site-specific datasets at the keyword level.
Additional keyword data might include statistics culled from organic analytics (KW traffic, conversion, behavior criteria), PPC history (CPC, CTR, conversion, etc…) and SEOmoz’s Linkscape (SEO competitiveness-predict).
We all know it’s theoretically useful to “advise” the keyword selection process by aggregating datasets. Very few SEMs actually pull it off. This article will guide you through the process of using spreadsheets to pilot advanced techniques, for API automation later.
What Is Keyword Research For Anyway?
Classic keyword research is all about selecting words for SEO, PPC and offline clarity. However there are other compelling objectives attainable by analytic mashups.
This might include wire-framing SEO based on page strength, taking SERPs competitiveness into account, discovering natural search clues for more efficient PPC and a multitude of seriously useful applications. At the end of the day keyword research is about increasing ROI in All Channels. The approach proffered here can yield extremely deep insights.
Not Your Mother’s Keyword Basket
Using multiple datasets relies on the power of mashing data together and stacked sorting routines. Even within themselves, most web based tools only provide a single dimension of data sorting.
For instance AdWords Keyword Tool lets us sort by any single attribute at a time, like estimated average monthly searches or CPC. Creative demographic research artists rock these tools by exporting to .CSV, adding additional keyword-level metrics and executing multiple sorts.
Example: Intersection of Low PPC Cost & Search Frequency
Start with an AdWords keyword basket You’ll find available dataset attributes, in the show/hide columns menu.
Export keyword Basket to an Excel .CSV
Now use the data sort functionality in Excel to identify the intersection of low PPC cost & search frequency. This particular sort is not possible in the AdWords tool’s web UI, because it has 2 steps. That said, it’s super easy in Excel.
The Sort Reveals 269, 400 Annual SEO Keywords, estimated @ $.05 CPC.
Sorting Analytic Mashups!
Now it’s time to aggregate keyword predict, organic analytics & PPC datasets into a single spreadsheet for cross channel data sorting. It’s very simple actually. Just create new columns on the spreadsheet and enter each keyword’s data next to the keyword basket’s output.
Import/Collate Each Keyword’s Organic History (Remember, this data is from your site’s web analytics like Google Analytics, ClickTracks, Enquisite, etc…)
- Traffic Frequency
- Conversion Frequency
- Conversion Ratio
- Engagement (Time & Page Views)
Import/Collate Each Keyword’s PPC History (This data comes from your PPC Accounts)
- Click Through Count
- Click Through Ratio (CTR)
- Actual Cost Per Click (CPC)
- Conversion Count
- Conversion Ratio
- Cost Per Conversion
Import Average mR of Each Keyword’s Top 3 Organic SERPs
mR means “MozRank,” which is the LinkScape analogy for Google’s infamous PageRank metric. It’s is SEOmoz toolset’s contribution to measuring page value and organic competitiveness.
Query each each keyword and take the average mR of the top 3 organic results with personalized search turned off. Enter the value into the spreadsheet next to each keyword, as a viable estimate of the SERPs competitiveness for that keyword.
Ready to fly? Now that you have the the predictive, organic & PPC historical data for each keyword on one spreadsheet, there’s awesome collating power at your data-sorting fingertips. Here is a partial list of “sorts” to cross-advise organic and PPC marketing efforts
High Organic Traffic / Low Organic Conversion
Use this report to identify where organic traffic intersects with low conversion. Obviously the list of keywords portends site-needs, in order to convert traffic.
Higher Predict Frequency / Lower Organic Competitiveness
This handy little sort finds the intersection of AdWords search volume predict, with easy organic SERPs for the keyword.
Lower Predicted PPC Cost Per Click / High Organic Conversion Ratio
Find low cost keywords that are proven to convert well organically. This is a classic aggregated dataset keyword research report.
High PPC Conversion Frequency / High Organic Traffic / Low Organic Conversion
This triple-sort identifies deficiencies in organic conversion, for keyword where PPC has proven the site converts. It’s especially useful to seek targets for improvement where there’s already good organic traffic to the site.
Other Fun Keyword Attributes For Research Mashups
There are any number of other (provocative) attributes which comprise organic and PPC historical datasets. They can be mashed into our aggregated-sort approach for spectacular clarity.
- Behavioral Triggers & Funnels
- Users’ Proclivity To Socialize Content
- Geographic Data
- Page Strength (Page on Which The SEO Is Placed)
Pioneering algorithms using spreadsheets is easy. Practicing these techniques without a number crunching army is quite another story. After refining advanced keyword research sorting with exported data, bring the processes to your development team for automation on the various APIs that comprise keyword basket, organic analytic and PPC reports.
Keep in mind that not every dataset is available for every website. Use what’s available to make keyword research even more valuable. These days predictive keyword basket tools are only part of the show. Also mash in keyword-level metrics for cross-channel sorting, using both what is predictable and known.
