SEO+PPC Steps and Template

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.

What Now?

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.

Sean Van Guilder About the author

Sean Van Guilder has been helping businesses get found on the internet for over 20 years. He has a 10,000 foot view of digital marketing that helps shed light on SEO as a business driver.  Sean has worn almost every digital marketing hat out there and brings strategies and tactics that have knocked the socks off of companies such as Microsoft, MSN and WONGDOODY, to name a few.  Currently, Sean is leading the SEO Practice for premier digital marketing agency, Point It, in Seattle.  Sean is a self-taught digital marketer and loves every minute of it.  When he’s not in front of a computer, he’s teasing his co-workers, playing sports, hanging with his kids, making music and collecting HO scale trains.

No Comments

Leave a Comment: