Steps to create a Key Phrase Research Report

  1. Go to Word Tracker site.. Click on "Projects" - choose appropriate project.. In the Wordtracker row to the right click on "Export"
  2. Copy the keywords into a text file (save to SEO folder in related website folder)
  3. Using WEBCEO's Keyword research tool - insert the text file
  4. Move the results into the WEBCEO basket.
  5. In WEBCEO run a competition analysis report choosing only the "Title" criteria.(choose criteria by clicking the arrow just to the right of the "Analyze" button at the bottom)
  6. Click the "Analyze" button. While the above report is running, complete the next steps...
  7. Go to Word Tracker site.. go to "Home" Choose "View All Results".
  8. Choose "Download Results" at the bottom of the page. Choose #1 (results separated by search engine).
  9. Open excel file.. Delete columns: Nos | Category | Share | KEI | Count |
  10. Cut the Keyword column and insert into a new column furthest to the left.
  11. Create a new Excel book with 3 sheet. Name the sheets Google, MSN, and Yahoo.
  12. Copy and paste each search engine results to their respective sheets.
  13. After pasting, you can delete the "Engine" column in each sheet.
  14. Insert Web Pros logo, Title of Report, and Search Engine at top of each sheet.
  15. Change the columns:| Keyword | 24 hrs | Competing | to: |Keyword Phrase | Daily Searches | Competing Sites | (bold these headers - left justify "Keyword Phrase" and right justify the others)
  16. Add a new column to the right of "Competing Sites" called "Ratio"
  17. In the highest row of the "Ratio" column create the formula: Daily Searches/Competing Sites x 1000 ex: =(F4/H4)*1000
  18. Now we are going to change all the numbers in the "Competing Sites" Column (this is the purpose of the WEBCEO report)
  19. Export the WEBCEO report to an excel file. copy the "Title" rows (Sort alphabetically!) from the report (number data only) and paste and replace the "Competing Sites" rows (be sure these are sorted alphabetically first!) in each sheet of the new Excel file.
  20. Save as an Excel file and an HTML file. Send excel file to client and create link to html file in clients SEO report page.

Final pages should look close to this picture: