SEO Workflow – Profitability Indicators

Part 2 – SEO Workflow

Are your keywords worth anything? And how can you tell?

Keyword research services such as SEMrush and SpyFu have valuable research tools for spying on your competitors.

If an advertiser is continually bidding on a particular word, it may be a valuable prospect to further investigate. PPC (pay per click) advertising is very competitive, difficult to do, and usually expensive. If an advertiser is using the same words over time, it usually indicates a good CTR (click-thru rate), and the ad text is relevant to the landing page, which in turn is usually optimized for that particular word. These three components make up what Google Adwords calls the “Quality Score” of a keyword. Advertisers must maintain at least a 5/10 score, and meet the minimum bid for the ad to be displayed.

SEMrush has added a new matrix tool where you can view the past 6 months per an advertiser’s Adwords keywords. SpyFu has a similar tool that is also 6 months.

Both services have a feature where you can overlap the words of 2 or more sites to find words common to each. SEMrush has a graphical Venn diagram that you can click on to see the result. Words that multiple advertisers are competing for and continually bidding on may be a value indicator also.

These past points can give you some indication of value, however testing out a PPC campaign and measuring the CTR and ROI of different ads is the way to get a clear view of how much a term is worth.

Semrush expanded their historical grid to the past 12 months.

If you click on “Ads History” under Advertising Research title, you can view a grid of ads up to 12 months. This data can then be downloaded to a spreadsheet as a csv file.
You could then take sizeable csv files and insert them into the sql database of your choice to analyze and narrow your keyword selection.
  • Libre Office Base – Limited to around 1.0 MB import
  • Microsoft Access – Limited to about 2.0 GB
  • SQLite – 140 TB
  • PostgreSQL
MYSQL is my favorite database for this task. In order to import the csv you could use phpmyadmin or use LOAD DATA INFILE:
  1. create the database
  2. create the table structure
  3. execute the above command
Upon having the database, you can then run SQL queries to get the data you want.
Example, run something like:
SELECT keyword, * 
FROM table WHERE cpc < 1.00 
AND keyword <> 
IN (negative_word1, negative_word2) 
GROUP BY id having count(*) > 12

For SEO: target higher CPC words that appear more frequently
For PPC: target relevant words to your site’s page, select words according to your budget
An even better method for CSV searching is to use the windows Log Parser 2.2 Program.

Enter Your Email Address