Adwords Analysis – Search Query Optimizer

While scanning for the search query in search terms report, you might come across some ‘search terms’, which

  • Might be irrelevant to your business or
  • Have 0 conversions.

To decide whether to add them as negative or not its always good to know the impact, i.e change in conversions, cost, CPA etc.
Here, what it looks when you run/preview the script

Copy the Script Here



Let me explain some of the rows as shown above:

Current Performance

It will give you the performance based on the date range selected. When you run the script for the first time it will give last 7 days data by default. If you want to analyze data for different date range, just input the date range in the cell B1 and C1 and re-run the script.

Filtered Query

It will give you the performance of the word used in Query  filter (B3&B4). If you input ‘what’, it will output all the data containing ‘what’ word.

After Excluding Query

It will give you the overall performance for the specified date range, if you plan to exclude (add as negative) the word used in the query filter. If you input ‘what’, it will exclude the data containing ‘what’ word. Basically its Current performance minus Filtered Query.

How To Use
1.) Input any word in your search query, lets say ‘what’. You can see in ‘Query Word Contribution’, the %spends is 1.26% for this word in the query. It is also contributing 0% conversions of the total conversions. So after excluding or adding this keyword as negative, my CPA would reduce by 1.26%.


2.)You can also see the ‘Filtered Query’ row – Here CPA is actually taken as the Cost, only if conversions are 0. i.e I have spent 10,053 on the ‘what’ containing search queries and had 0 conversions, so CPA is 10,053. I did this just to know the total wasted spends. In the current performance row, my current CPA is 1532. If I add ‘what’ as negative , my resultant CPA would be 1513. Not much change, but still I saved ₹10,053!!

Lets take another example:


  1. )The Query Word Contribution for the ‘how’ word is 6.4%Cost and contributes to 0.77%. Also if I add this word in negative list, there will be 5.71% reduction in CPA, and only 0.77% reduction in conversions. I might exclude these if I want to bring my CPA down without impacting conversions too much.
    To summarize, in ‘After Excluding’ table, if %CPA change is much more than %Conv change, its better to add that word in negative list (only if you have CPA constraint, without sacrificing much on conversion). If you want to look for more than one keyword ,just type keyword1.*|.*keyword2  or keyword1.*|.*keyword2.*|.*keyword3,etc.

Also, its good to look at some metrics like assisted conversion,engagement metrics,micro goals(if any) for that word, before taking any decision.
Happy analyzing folks..!!!

Ritwik is a Web Analyst & Product Marketer. He loves to write technical & easy to understand blogs for Marketers & Entrepreneurs. Focused on Google Analytics, Facebook Analytics, Tag Management, Marketing & Automation Scripts & more. Google Certified Professional. A Firm Believer in Teaching -> Learning -> Growing. :)

Leave a Reply

Your email address will not be published. Required fields are marked *