Welcome to part 2 of the Clicks Rule special.

You may be familiar with a technique I shared in recent post called the 10% Clicks Rule (if not, you may want to come back once you’ve skimmed through Part 1: Overview). In essence, the 10% Clicks Rule is a technique that aims to improve the relevancy of ads for search queries which have broad or phrase-matched to one of you keywords. Since it is impractical to give every possible keyword or search query its own ad group with personalised ads, the 10% Clicks Rule helps to identify those ad groups which are most likely to benefit from your time and effort.

Part 1 was all theory. What I want to do now is provide a step-by-step guide explaining how to identify those ad groups in your own AdWords account which could greatly benefit from your insight. All we’re trying to do here is run a Google AdWords search query report at ad group level, filter out exact match keywords (to leave broad and phrase match only) and highlight those ad groups with more than 10% of broad and phrase clicks. These are the ad groups we want to look at. So if you’re a seasoned AdWords and Excel pro, feel free to skim through the bullets or jump ahead to Part 3: Does it Work?. For everyone else who might need a little more guidance, continue reading for a detailed step-by-step guide.

Download a search query report

  • Log in to Google AdWords, go to the Report Centre and click ‘create a new report’
  • Click ‘Search Query Performance’
  • Select ‘ad group’ as the level of detail, ‘summary’ as the unit of time
  • Select an appropriate date range
  • Click ‘create report’, open it once it completes and export it to Excel

Pivot the data

Once we have the search query report in front of us, we want to summarise clicks by ad group.

  • Delete anything above the campaigns/ad group/search query row so ‘campaigns’ is in cell A1
  • Scroll to the bottom and delete the ‘totals and overall averages’ row
  • Select all data and headings
  • Go to Insert, click ‘PivotTable’, the ‘OK’
  • This should create a new sheet

Calculate clicks by ad group

Next we want to filter out exact match clicks and calculate each ad group’s broad and phrase match click volume.

  • Make sure you can see the ‘PivotTable Field List’ toolbar on the right-hand side (if you can’t, try clicking on the blank PivotTable or go to Options > Field List) – your sheet should now look like this
  • Drag ‘ad group’ into the ‘row labels’ box – this should list all your ad groups in column 1
  • Drag ‘clicks’ into the ‘values’ box and ensure it says ‘sum of clicks’ – this should show click totals in column 2
  • Drag ‘Search Query Match Type’ into the ‘report filter’ box which should add a drop-down filter in cells A1 and A2 – your field list should now look like this
  • Click the drop-down filter, click ‘select multiple items’ and ensure only broad, broad (session based) and phrase  are ticked – like this

Calculate percentages by ad group

You should now have a list of ad groups with totals of their broad and phrase clicks. Let’s now calculate each ad group’s percentage of total broad and phrase clicks.

  • Scroll to the bottom and make a note of the ‘grand total’ number of broad and phrase clicks
  • Click anywhere on the PivotTable to ensure it’s selected and click Options > Formulas > Calculated Field on the toolbar
  • Type “Percentage” as the name
  • Type “= Clicks / total_clicks” into the Formula box, where ‘total_clicks’ is your grand total of broad and phrase clicks you made a note of earlier
  • Click OK – this should add a new column with each ad group’s percentage
  • Ensure the ‘grand total’ of this new column equals 1
  • Change the formatting so that each number reads as a percentage

Highlight high-volume ad groups

Now that we have percentages calculated for each ad group, make a note of those ad groups…

  • Right-click anywhere in column 3, go to ‘Sort’ and select ‘Sort Largest to Smallest’
  • Make a note of ad groups with more than 10% of clicks

These are the ad groups with a lot of broad-matching and phrase-matching going on. These are the ad groups that could benefit with your time and effort.

The fun part

You’ve now got everything you need to start make powerful changes to you AdWords account. You can stop here and use your own intuition to make improvements to these ad groups, or continue reading for some ideas and suggestions on what to do next.

Search query analysis

  • Go back to your original search query report sheet so you can see all your search queries
  • In the ‘ad group’ column, filter so that only the ad groups you made a note of earlier are ticked
  • In the ‘Search Query Match Type’ column, filter so that only broad, broad (session) and phrase are ticked
  • Sort the clicks largest to smallest

Your search query report should look something like this.

Take a moment to familiarise yourself with your search queries. People are typing these searches to find your products or services. You need to decide what action to take. For each of your search queries, you could either:

  1. Add it as a negative keyword (if it’s not relevant)
  2. Add it as a new keyword in its own ad group (if ads in that ad group are relevant)
  3. Add it as a new keywords in a new ad group (if ads in that ad group are not relevant and new ads are needed)

First decide if any of the search queries are irrelevant to your business (option 1). Make a note of any irrelevant searches in a separate Excel sheet – you can add them as negative keywords later.

Great. Let’s now assume that all remaining searches are relevant to your business. You now need to decide between options 2 and 3 – whether to add the search query as a new keyword in that ad group, or in a new ad group.

To decide whether option 2 or 3 would work best, have a look at the ‘ad group’ column for the search query. It is this ad group the search query is being matched to. Open up AdWords Editor and now find that ad group. Look at the ads. These ads are being shown whenever someone searches for the search query. Are they relevant? Do they mention the search query in the heading or descriptions? Could they be improved in any way to increase relevancy, Quality Score, CTR and conversion rate?

If you think the ads are relevant to the search query, add the search as a new keyword to that ad group (option 2). If you think you could write better, more relevant ads for the search query, add the search query as a new keyword in a new ad group and write better ads for it (option 3).

Great rule, but does it work?

Hopefully if you’ve made it this far you’ve managed to have a go yourself and found some juicy ways to improve your AdWords campaign. While I hope you found it simple and straightforward to follow, feel free to share your thoughts and comments.

In the final part of the Clicks Rule trilogy, I look at real AdWords examples and explore how it can actually help improve results of AdWords campaigns. Part 3: Does It Work?

Alan Mitchell

Alan Mitchell

14 Years Experience. 10,000 Hours Research & Innovation. Exclusive Self-Built Technology. World-Leading PPC Marketing by Alan Mitchell

Go direct to the expert


Melbourne, Australia