No Time To Read? Watch The Video Tutorial
How This Works
Essentially, you're going to sort through all of the keywords that your competitors already rank for.
Why? They've already done the legwork of creating relevant content. Now, we can use tools to spy on what's driving customers to their business so that we can focus our efforts on the most important terms.
Adding Potential Keywords
The first thing that we need to do is find out who our search competitors are. To do this, add your "vanity" or "primary" keyword into Google.
This should be the term that all of your direct competitors would want to be #1 for. Some examples are
- Boston Roofer
- Tampa car accident attorney
- Commercial HVAC Austin
Next, I recommend opening the websites for all of the businesses that appear on page 1. From here, copy each root domain into a separate document.
Domain.com not http://domain.com or domain.com/foobar
NOTE: You can follow this process for as many keywords as you want. You'll get more data, but you'll also make more work for yourself.
Add one of your competitors root domains into the search bar and click the search button.
Use the left-side navigation to click "Organic Keywords"
On the "Organic Keywords" page, click the "Export" button and choose "Full Export". Download the CSV file.
Repeat this process for all of the competitor websites you found earlier.
Once you have all of your CSV files, it's time to add them to the spreadsheet.
On the "Import Sheet" sheet, go to File > Import > [Select where you stored the CSV files] and select "Append to current sheet."
Repeat this process until all of the CSV files have been added to this sheet. It's very important that you select "Append to current sheet" every time.
Before going any further, we need to create our target page sheets.
Create a list of each "money page" or pages that you want to rank. These will most likely be your service pages.
Make a duplicate of the "Homepage" sheet and name each new sheet according to your "money pages." Example sheet names could be
- Car accident
- Lawn care
On the "Research" sheet, we now have a list of all of our competitors keywords (duplicates are automatically removed) as well as their corresponding monthly search volume.
For each keyword, add a "Yes" or "No" to the "Target" column to indicate whether or not you want to rank for that keyword.
Use filters on the "Keyword" column to parse through the list much faster.
After all of the keywords have been gone through, filter "Target" by "Yes" so that you only see the keywords that you want to target.
For every keyword, add the name of the "money page" that keyword will be associated with to the "Target Page" column. Make sure that it matches the sheet names we made earlier!
On your "Money page" sheets, you'll see that the keywords and search volume from the "Research" sheet has been pulled into each sheet depending on what you listed the "Target Page" as.
The "Money page" sheets also have a "Ranking Pages" column. This column checks to see what competitor webpages are ranking for that keyword. By default, the formula is only in cell C2, you'll need to drag the anchor down to the rest of the cells.
If you're working with a lot of competitor websites, then the "Ranking Pages" column can become quite overwhelming. I suggest hiding this column until you need it.
NOTE: You may encounter a "money page" sheet that looks like this
This is because the sheet uses a script based on the name of the sheet. When duplicating the Homepage sheet, the sheet name is "Copy of Homepage." It can take a bit for the 'system' to recognize the new sheet name. This is why we made our duplicates early, so that we don't have to wait.
If you're impatient, there's a work around that as of right now... works.
For each 'problem' "money sheet" select cell A2 and you'll see this formula
=QUERY(Research!A2:D,"select A where D='"&SHEETNAME()&"'")
Update it to
=QUERY(Research!A2:D,"select A where D='"&SHEETNAME(GOOGLECLOCK())&"'")
This seems to force it.
Navigate to the "Stats" sheet.
There's a couple of automated cells and a couple where you'll need to make edits.
Total Imported Terms - How many keywords were imported from Ahrefs.
Total Selected Terms - How many keywords you chose to target.
Total Search Volume - The total monthly search volume for your selected terms.
Est. CTR - What percent of searchers will click through to your website. If you ranked #1 for all of your selected keywords then you can assume roughly 20-30% CTR.
Est. Website Conv. Rate - The percent of visitors to your website that become a lead. If you're currently tracking this, then you'll be able to add your numbers here.
Lead Value - This is how much a lead is worth to you. To figure out this number, use this tool.
Est. Monthly Traffic - Total search volume multiplied by the CTR.
Est. Monthly Leads - Monthly traffic multiplied by conversion rate.
Est. Monthly Value - Monthly leads multiplied by lead value.
This process allows you to spy on what's already working for your competitors, while automating the keyword mapping process and evaluating the estimated value of your keywords.
Download the spreadsheet (link above) and get to work!
Compared to doing this manually, you'll save hours of work. If you have any questions, email me at Jarod (at) teambluedog (dot) com.