Posted on February 11th, 2009

aggregated

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.

dataset-attributes

Export keyword Basket to an Excel .CSV

lowcost-and-search-frequency

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.

cost-and-search-frequency


The Sort Reveals 269, 400 Annual SEO Keywords, estimated  @ $.05 CPC.

predict-frequency-low-cost


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.

aggregated

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.

Subscribe Today

We'll keep you updated on the latest Aimclear musings & appearances

  • Bill Scully

    Marty, I don’t know how you do it. SMX and a fantastic original post.

  • Marty Weintraub

    @Bill Scully: Thank you *blushes*. I had to! This is the essence of my Presentation on the Advanced Keyword Research Panel tomorrow.

  • David King

    Great post!

    a lot of info I’m going to have to read it twice! lol…

  • Marcel Rivera-Baillargeon

    Great SMX presentation!

  • Marty Weintraub

    Thanks for stopping by @DavidKing and @MarcelRivera

  • Andrew Shotland

    Beautiful stuff as usual Marty. You can sort my columns any time…

  • Marty Weintraub

    @Andrew Shotland: Don’t make promises you’re not going to keep, baby boi. 🙂

  • Andrew Shotland

    Is that a macro in your spreadsheet or are you just happy to see me?

  • David Camma

    That is a fantastic use of the tools, I have always had trouble when dealing with huge keyword lists for clients, trying to find the value. Thank you very much. This page is bookmarked.

  • Goran Web

    All the data from most of these sources can be exported in a .csv. Do you know of anyone who has made an application that we can purchase were we import all this data so that we can easily manipulate it. What you are saying makes sense, but it is time consuming when there are many different domains that need to be worked on. Thank you.

  • Eduard Blacquière

    Good post, Marty! This is especially useful to determine the quality of your initial keyword research, looking back at how well keywords performed.

    This method is hard though for researching new keywords, because you don’t always have organic and/or ppc data available.