Stage 1: Learning From Competitors Rankings and AdWords Campaigns

If you have already gone through the Competitive Analysis course, the you will already be familiar with SEMrush.com and should have already exported and saved the organic ranking reports and AdWords campaign reports for each of your top competitors. If you have done this, then just skip to the next section “Formatting Reports”. However, if you have not yet gone through that course, SEMrush.com is an indispensible tool that is well worth the $69.95/mo for the pro recurring account. Make sure that you are signed up at SEMrush.com and that you have your list of top competitors that we will be analyzing. When logged in to SEMrush.com you will automatically be taken to the dashboard, where you will enter your competitors URL and click “Search”.

Organic Search Reports: Scroll down to the second “Organic keywords” block (the one that lists the actual keyword terms). In the lower right corner of the same “Organic keywords” block, click “Export” and Export the Organic Search Report. This is a report of all of the keywords the competitors is ranking for on the first two pages of Google’s organic search results. Once all of the reports are downloaded, we will format them in once Excel Document.

AdWords Campaigns: To the right of the “Organic keywords” block is the “Ads keywords’ block. If your competitor is adveritising in AdWords, their campaign will show up here. Now Export the Competitors in AdWords Report by clicking “Export” in the bottom right corner of this same block.

Additional Tools for finding competitors Ad campaigns

There are numerous tools available online that can help you find your competitors AdWords campaigns. SEMrush.com is the one I prefer, but it there are a couple others that I would suggest taking a look at:

Spyfu: Spyfu offers most of what SEMrush can do, but they have also recently added keyword tracking and a few other bells and whistles that have increased its value. Overall Spyfu is a very good tool with a starting price of $79.95

KeywordSpy: Keyword Spy is a great tool that basically offers everything that SEMrush and Spyfu offer for a starting price of $89.95

The Competitor Rankings Workbook:

1. Open a new Excel Workbook and save as “Competitor Rankings Workbook”
2. Open each of the organic keyword reports one-by-one.
3. Select column G (Traffic %) and paste it into column E (CPC).
4. Then delete column C (Previous Position).
5. Copy columns A through E, and paste them into a new sheet. You will repeat this process for each competitor, with each competitors Organic Keyword Ranking data on its own tab in the new “Competitor Rankings Workbook”.
6. You now have a great big list of all the keywords your competitors are ranking for. But there is likely to be a lot of duplicate keywords in this list, and we want to get rid of the duplicates while finding out which keywords are the most common among our top competitors. To do this, we will make a quick Pivot Table.

Once you have aggregated all of your competitors keywords from organic rankings reports and AdWords campaign reports, into one huge list, you will first want to get rid of all of the duplicate keywords. This can go a long way to reducing the size of the list. Follow these simple steps.

1. Open a new Excel Workbook and save as “Keyword Patterns Workbook”
2. Go to the Competitor Rankings Workbook and one by one copy and paste all of the keywords that each competitor is ranking for into one long list in column A on the first tab of the Keyword Patterns Workbook.
3. Next select column A, then go to the Excel Menu and click “Data”, from the drop down list choose “Sort”, just sort column A in alphabetical order.
4. Next, with column A selected, under the “Data” menu, select “Pivot Table” and click “OK”. This will create a pivot table in a new sheet.
5. The new sheet will have a blank pivot table, and a Pivot Table Builder. There should be one option in the Field Name box within the Pivot Table Builder, drag and drop that field name into both the row labels box and the values box.
6. This effectively removes all of the duplicate keywords from the big list of competitor keywords, and in the values column it will tell you how many times each keyword was repeated in the list. So, the next thing we want to do is to sort the list by the number of repeats to figure out which keywords are the most popular among our competitors.
7. Select the entire Pivot Table by highlighting columns A and B and copying them.
8. Go to the top cell in column D, right click (PC) or control+click (mac) and choose “Paste Special” select “Values” from the pop up menu. The entire pivot table should appear in columns D and E.
9. Delete the first three cells in each column so that the list starts with the Row Labels and Total.
10. Select both columns D and E and from the “Data” menu, select “Sort”. Make sure that the option “My list has headers” is selected, the sort the list by Total and from Largest to Smallest.
11. You now have a list of all of your competitors keywords, sorted by popularity among your top competitors.
12. Repeat this process for AdWords campaign keywords