Decoration Circle
Advanced SEO Textbook

Foundational Keyword Research

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.

Topic Details
Clock icon Time: 20
Difficulty Easy

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.

FKR - PKA - step 4 - Starting export

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 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.

FKR - IGS - step 1 - New spreadsheet

2. Select File > Import

FKR - IGS - step 2 - 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.

FKR - IGS - step 6 - Name spreadsheet

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.
FKR - DUT - step 1 - Delete columns

2. Rename this tab to Raw Data.

FKR - DUT - step 2 - Raw data

3. Select the first row that contains column names.

FKR - DUT - step 3 - Select names

4. Go to View > Freeze > 1 Row to freeze the column names in place.

FKR - DUT - step 4 - freeze column

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.

FKR - CPT - step 1 - Duplicate

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.

FKR - CPT - step 3 - Filter

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.

FKR - CPT - step 4 - Pivot table

5. Rename this tab to Keyword Map.

FKR - CPT - step 5 - 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.

FKR - CPT - step 6 - Add to pivot table

7. Select the first row with the column names.

FKR - CPT - step 7 - Select column names

8. Go to View > Freeze > 1 Row to freeze the column names in place.

FKR - CPT - step 8 - Freeze

9. Stretch the URL column to show the full URLs.

FKR - CPT - step 9 - Stretch URLs

10. Right-click on the Copy of Raw Data sheet.

11. Click Hide Sheet.

FKR - CPT - step 11 - 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.