Blog

Step by Step Guide to Spying on the Competition with KeywordSpy (Downloadable Excel File Included)

Written by Bonnie Schwartz 6 Comments < back to blog home

At SEER, we are always trying to stay one step ahead of our competitors. There a bunch of great tools out there that can really help to jump start your keyword research or look for areas for expansions.

Adam Riff on Search Engine Land, highlights a few tools here and summarizes how he uses these tools.

Competitive tools are a great way to find out what keywords your competitors are using so that when you launch a new account or expand an existing account you’re not missing out on any opportunities. However, these tools are only part of a PPC keyword build. They only show you what has been done by competitors, not what has worked well for the competition or more importantly what will work for you. These tools also have their limitations in terms of the accuracy of the data. The budget estimates they provide , for example are often low. They will also bring back keywords that are irrelevant that the competition is probably not bidding on. Still, competitive tools are a great way to get an idea of what your competitors are up to and to find keyword suggestions that you may have otherwise missed.

Attached is a sample excel file that is referenced starting in step five. Download Here!

Below is a step by step guide as to how the SEER team uses the data from keywordspy.com, to analyze the competition

1. Gather a list of competitors - we recommend at least 5. Choose competitors based on their PPC activity.
   Login to Keywordspy.com - You will need the paid version of KeywordSpy.com for this analysis.
2. Place each competitor domain in the search bar at the top of the keywordspy homepage. Make sure that the “domain” box is checked underneath in the list of options and select a country.



3. Click search. Afterward, keywordspy will direct you to an overview of the keywords your competitor bids on and the other websites your chosen competitor competes against (if found). For PPC, click on the tab “PPC Keywords.” This tab shows all the PPC keywords triggering your chosen competitors ads with metrics such as volume, cpc’s, days seen, and when the ad triggered by that keyword was last seen.
4. Export the data by clicking on the, Excel icon in the top right



5. Compile the competitor export data into one worksheet. Insert an extra column on the left after you drop the data in so that you can label it according to competitor. Make sure to put the competitor name in after you bring each client and drag down the name for that specific competitor. Repeat steps for all competitors.
6. Once the competitor data is compiled into one worksheet, separate the “days seen” column into 2 columns so that it will not appear as a fraction To do this, highlight the “days seen” column and under the data heading in excel, hit “text to columns”. Make sure “delimited” is selected, and then hit next. Under “delimiters” uncheck tab and in the other box put a back slash “/”. Then select next and if the preview is correct, select finish. Now the days seen numerator should be in its own column. Label this column “days seen” and the separated denominator column as “days shown.” See Raw Data Tab in the attached workbook



7. Finally, filter the data by days seen. To cut down on the amount of data, delete any keywords that were seen less than 10 days. This will eliminate many of the one off irrelevant keywords and make the file more manageable. Also, if a competitor has been seen for a keyword for more than 10 days, there is a higher likelihood this keyword is performing relatively well.
8. Next, insert pivot table and select all data on the consolidated “competitor data.” Select a new worksheet as the pivot table destination.
9. Use keyword as the row labels, the competitor as the column data, and count of days seen as the values. This will easily show which competitor’s showed up for each keyword. The Grand Total column shows a count of how many competitors are bidding on the keyword. Sort the “Grand Total” column from largest to smallest to get the most highly bided keywords at the top. See Pivot Table in attached workbook. You can actually paste your data in the raw data tab and refresh the Pivot Table.



10. For manageability and analysis purposes, copy and paste the pivot table data from the pivot table into a separate worksheet (noted as “analysis tab”). If there is too much data to weed through than you may want to delete any keywords that do not have more than 3 competitors bidding on it (as an example).
    

11. Lastly, highlight all data and do “find and replace” (ctrl +h). In the “find what:” box put 1. Then for replace put 1 and then hit options. In the replace box select the format drop down and hit format. Then under the “fill” tab, select any color. This will replace all 1′s with a color so that you can easily see (according to color) major competitors, important keywords, etc. You will end up with a sample list seen in the Analysis tab of the attached work book.

When kicking off a project have an intern compile a list of competitor keywords using this approach. A PPC strategist will have to weed through the list to find terms to bid on, but less experienced staff can start the process. This time efficient approach to competitive keyword analysis will help with any keyword build, keyword expansion or competitive analysis.

Excel File:Download Here!

By Bonnie and Morgan.

Follow us on Twitter:
Twitter.com/bonnieschwartz
Twitter.com/morganbt410

Posted: 09.22.11