Google Ads Keyword Audit

When it comes to optimizing paid search campaigns, keyword optimizations can oftentimes be time-consuming, scattered, or – even worse – ineffective. A keyword with a high CTR and low CPC may be performing well at first glance, but these are surface-level metrics and they don’t necessarily correlate with the most important metrics.

Organizing your keywords and search queries into a format that helps you focus on what really matters the most – in this case, ROAS – will give you a clear picture of which keywords to invest more money into and which keywords to scrap from your campaign(s).

To use this spreadsheet, export your keyword report from Google Analytics for your chosen time period. Be sure to include the following attributes: Keyword, Campaign, Clicks, Cost, CPC, User, Sessions, Ecommerce Conversion Rate (or Goal Conversion Rate), Transactions (or Goals Completions), & Revenue.

The first row in this spreadsheet maps to these attributes. In Column K, you’ll find a ROAS calculation equipped with Conditional Formatting that will provide you with a quick visual representation of your Keyword success.

As a starting point, we’ve got Conditional Formatting set up to turn cells in Column K green when the ROAS is greater than or equal 250% and red when the ROAS is less than or equal to 150%.

With your exported data placed in this sheet, you’ll quickly see which of your keywords are driving the best ROAS.

To help sort, we have a filter placed in row 1 that provides you with endless filtering and sorting options. There’s also a nifty word count formula in Column L which will provide some insight into how your long tail and short tail keyword performance compare.

Run the same report at the Search Query level and compare it with your Keyword list. You may find some standout queries that can be added as Exact Match keywords to your campaigns.