Our Foundational Keyword Research process will provide you with an overview of your website's keyword visibility and form the basis for your keyword strategy.
The Foundational Keyword Research process will allow you to see what your site is being indexed for and can be used to identify keyword/topic cannibalisation.
In addition, this initial KWR serves as the basis for our Ontological Keyword Research process which we will cover later on.
Please note that you will require an Ahrefs account in order to complete this KWR.
Step 1: Pull Keywords from Ahrefs
1. First, we’ll want to export a list of all keywords the client is currently indexed for.
2. Click on the organic keywords page in the left sidebar.
3. Click the Export button.
4. Select Full Export for Microsoft Excel. Click Start Export. Your download may take a few minutes to complete.
5. Select the tray icon from the upper right corner and click on the new file to download it.
Step 2: Import to Google Sheets
1. Type spreadsheet.new into your browser bar and it will automatically take you to a new Google spreadsheet. Alternatively, you can go to Google Sheets and select a blank spreadsheet.
2. Select File > Import
3. A pop-up will ask you how you want to import your sheet. Select Upload.
4. Drag the downloaded .csv to the upload screen.
Alternatively, you can search for the file on your computer.
Importing files to Google Sheets only works with .CSV files, not .XLS, .XLSX or other Excel documents
5. Select Replace Spreadsheet. Click the Import Data button.
This may take a moment while Google Sheets imports the data from the downloaded .csv file.
6. Name your spreadsheet [site title] Keyword Pivot.
Step 3: Delete Unnecessary Tabs
1. Once the data has populated the sheet, delete all columns except for the following:
- Keyword – This is the keyword the client is currently ranking for.
- Position – This is the client’s current rank position for that keyword.
- Volume -This is the estimated volume of people who are searching for that keyword per month.
- URL – This is the client’s URL that is ranking for that keyword.
- Difficulty – This is the estimated difficulty to rank that keyword in organic search.
- CPC – This is the average cost per click for paid advertising. This allows us to see what searches the site’s competitors consider to be valuable.
2. Rename this tab to Raw Data.
3. Select the first row that contains column names.
4. Go to View > Freeze > 1 Row to freeze the column names in place.
Step 4: Create a Pivot Table
1. Right click on the Raw Data tab and select Duplicate. This will create a sheet called Copy of Raw Data.
2. Click the blank cell in the upper-left corner of the spreadsheet to highlight all the data.
3. Click the Filter icon in the toolbar.
4. With your data still highlighted, click the Data tab and select Pivot Table. This will create a second tab in the spreadsheet called Pivot Table 1.
5. Rename this tab to Keyword Map.
6. In the Pivot Table Editor sidebar, add all rows available in the following order:
URL > Keyword > Position > Volume > Difficulty > CPC
Be sure to deselect the “Show totals” checkboxes with each addition, otherwise the pivot table may show an error as it tries to produce too many columns.
7. Select the first row with the column names.
8. Go to View > Freeze > 1 Row to freeze the column names in place.
9. Stretch the URL column to show the full URLs.
10. Right-click on the Copy of Raw Data sheet.
11. Click Hide Sheet.
And there you have it, your foundational keyword research is complete!
To summarise, this Basic Keyword Research document allows you to see which keywords your website is currently being indexed for and can be used to identify keyword/topic cannibalisation. Everything done up to this point has created the foundation of the next few steps of the SEO process.
Now, let’s take a look at how you should go about taking this to the next level with our Ontological Keyword Research process.