As mentioned in our SEO+PPC Webinar, below are the steps for finding keyword opportunities using insights from Google AdWords and Google Search Console. For reference, you can download the Keyword Opportunities Template built in Excel.
Google AdWords – Keyword List
1. Set the Date Range to a year to ensure a good data set.
2. Navigate to the Keywords tab and download the Keyword List in XLSX.
- Remove all keyword modifiers such as brackets, quotes and plus symbols. In the spreadsheet template this is labeled “adwords keyword list”.
- Create a Sum PivotTable to get a combined count of conversions by keyword. This is labeled “adwords pivot table” in the spreadsheet template formulas. For more information on creating PivotTables, check out Microsoft’s “Calculate values in a PivotTable report” article. I would recommend using the Recommended PivotTables wizard in Excel and ensure Keyword Path is under Rows and Conversions is under Values for the PivotTable.
3. Next, navigate to the Dimensions tab, change View: to Paid & Organic. Download the report in XLSX format. This data will be placed in the “Paid vs. Organic” sheet. In the template, the cells highlighted yellow is to indicate where to copy & paste the data from the AdWords export. This will also serve as the primary comparison data and will aggregate data from the other sheets.
Google Search Console – Search Analytics
1. Navigate to Search Traffic > Search Analytics and check all four boxes.
2. Change the data range to the last 90 days.
3. Download the results as CSV and copy / paste the results into the “search console” worksheet in the template.
Now that you have the data in the template, begin by filtering on the search result type of Ad Shown Only and sort Conversions by Descending values. The keywords to look for are those that have a good number of conversions but low organic ranking. Based on this information, identify a Core Keyword Phrase and a Target Page on the site that should be garnering the organic traffic.