How to Automate and Improve Google Ads with the G Sheets QUERY Function

January 24, 2019

How to Use G Sheets to Improve Google Ads Performance

One of the most useful functions in Google sheets is the QUERY function.  It gives you the ability to efficiently determine the performance of your Google Ads. This article is going to focus on the Google Sheets QUERY function and how this one function can streamline your process of sorting keywords...  Without any paid tools.

Who Would Use The G Sheets Query Function??

If you are working with Google Ads Search or Display Network, this tutorial will help you streamline your campaign optimization process.  If you manage marketers working directly in the Google Ads platform or are responsible for teams of media buyers working in the platform.  After this tutorial, you will be able to replicate many of the functions of the best paid PPC tools for FREE!

What Is The Google Sheets QUERY Function?

The Google Sheets QUERY function is one of the most powerful functions of Google Sheets.  It allows you to set up customized searches and sorting based on a “query language” similar to SQL, (the main language used to pull information out of a database).  

SQL is designed specifically to allow for very complex data searches.  Google Sheets QUERY function is modeled after this language and provides a similar level of flexibility.  Essentially, the QUERY function is like Google Sheets filters, except an order of magnitude more flexible and another order of magnitude more powerful.  It lets you execute almost any search you can think of.

What Are The Downsides of Using Google Sheets Query Function?

The main drawback to using this function is the initial learning curve for using the function.  You need to learn how to structure a query (search).  An introduction to this type of language is beyond the scope of this article.  If you really want to use this in your day to day workflow you will probably have to devote at least a few hours to learn the basics of SQL.  For an excellent 2 part tutorial, I recommend Ben Collin’s fantastic article “Learn The Most Powerful Function In Google Sheets”.  

How Is The Query Function Used For Google Ads?

The QUERY function is best used to sort out keywords by combinations of search criteria.  For example, in our client accounts, we like to automate searches for 6 basic categories of keywords.  A few of our default searches include the following:

  • Tops 50 Keywords we are running for clients by conversion performance.
  • Top 50 Keywords our clients are running independently by conversion performance.
  • Top 50 Keywords our clients are running by total amount spent
  • Top 50 Keywords we are running by total amount spent
  • Keywords that have spent over a certain threshold (client dependent)
  • Keywords that have more than one conversion but are below a certain CPA threshold.

The key to this breakdown is that it provides a general direction for our analysis.  When most people sort in a Google Ads account, there is an overwhelming amount of information to take in.  Breaking down keywords into predetermined buckets gives you context for the rest of your analysis.  This is especially true if you intentionally create the buckets you know are important to you.  

In our case, we just needed to understand where our client’s money was being spent and what keywords were wasting the most money.  The QUERY function expedited the process of getting the overall context for the performance of various keywords and search terms.

Setting Up The QUERY Function With Google Sheets

In the set of examples below, I am going to be giving examples for 1 specific use case for Google Sheets QUERY functions, Google search term reports.  These are the reports advertisers use to gauge the performance of their keywords and the search terms that are resulting in your ads showing up.  In order to make the below examples work, you will need to follow a similar setup procedure, otherwise, these functions will not work.

You need to set-up two things before you are ready to use the Google QUERY function:

  1. Standard Google Ads Search Term Report Format
  2. Search Term Report Format that mirrors the one I use below.  

If you are not familiar with the google search term report, you can reference this video on using the search term report.  Once you understand how to create a report, you will need to customize the report layout.  Don’t worry, this “customization” takes about 20 seconds.

For an example of the search term report “customization” process, you can check out this quick tutorial (insert video here).  You can see our basic setup here below.  The first image is the format for the report in the Google Ads platform before it is downloaded.  The second image shows the resulting .csv file uploaded into Google Sheets.

If you are interested in using the formulas below exactly the way they are, your report will have to have the exact same row column layout as the report above. You will also need to make one other formatting change to the report.

Breakdown of A Google Sheets QUERY Function

=QUERY(Keywords,"SELECT * WHERE Z <20.0 AND Y >= 1 ",1)

The first example function filters all of the keywords and search terms for those that have a CPA below $20 and at least 1 conversion.  The purpose of this function is to show all the keywords with CPAs below a certain threshold while excluding and keywords that are showing low CPAs simply because they are showing a conversion value in between 0 and 1.  This can happen when Google partially attributes a keyword for a sale. You shouldn’t have many of these.  However, if you are working in a new account that has a questionable analytics setup, these partial conversions can be an issue.

=QUERY(Keywords,"SELECT * WHERE Z <20.0 AND Y >= 1 ",1)

In this search, you can see the initial part of the function “Keywords” is a named range or predefined group of ranges.  In this case, I set the named range “Keywords” equal to A2:AE so that it includes all rows and columns that this type of search term report includes.  Alternatively, you could replace “Keywords” with A2:AE in the above formula.

=QUERY(Keywords,"SELECT * WHERE Z <20.0 AND Y >= 1 ",1)

The next part of the formula “SELECT *” indicates that I am selecting all the columns in the range “Keywords” in order to be included in this search.  This is immediately followed by the condition under which the function selects the information from those ranges.  

=QUERY(Keywords,"SELECT * WHERE Z <20.0 AND Y >= 1 ",1)

In this case, the condition includes all information in “Keywords” where the value in column Z is less than 20 and the value in Y is greater than or equal to 1.  As you can see in the above sheet formatting example, columns Z and Y correspond with the cost per conversion and the total number of conversions respectively.

=QUERY(Keywords,"SELECT * WHERE Z <20.0 AND Y >= 1 ",1)

Finally, the “1” is the last parameter for the Query function and indicates that the functions 1st row contains labels.

Example Google Sheets QUERY Functions

=QUERY(Keywords,"SELECT * WHERE W >20.0",1)

This is a basic search for any keyword that spent more than $20.  Not much to explain here.  We mostly use this to make sure we are accounting for any keyword that was spending a “significant” amount of money.  “Significant” is going to depend on the budget.  Feel free to swap out any other number for 20.

=QUERY(Keywords,"SELECT * WHERE A CONTAINS('[ATTN]') ORDER BY W DESC LIMIT 50 ",1)

This is a query function that limits the results to the top 50 keywords by the total amount spent.  This search only includes results where the campaign title includes ATTN.  This allows us to easily identify campaigns that we are running for clients vs. campaigns clients are running independently as we use ATTN in our campaign nomenclature.

=QUERY(Keywords,"SELECT * WHERE A CONTAINS('[ATTN]') ORDER BY Y DESC LIMIT 50 ",1)

This is a query function that limits the results to the top 50 keywords by the total conversions.  This search only includes results where the campaign title includes ATTN.  This allows us to easily identify campaigns that we are running for clients vs. campaigns clients are running independently as we use ATTN in our campaign nomenclature.

=QUERY(Keywords,"SELECT * WHERE NOT A CONTAINS('[ATTN]') ORDER BY W DESC LIMIT 50 ",1)

This search returns that top 50 keywords by the cost that clients are running independently.  This search works by excluding campaigns that include our agency standard campaign nomenclature (“ATTN”).

//Top 50 non ATTN Keywords by conversions

=QUERY(Keywords,"SELECT * WHERE NOT A CONTAINS('[ATTN]') ORDER BY Y DESC LIMIT 50 ",1)

This search returns that top 50 keywords by conversions that clients are running independently.  This search works by excluding campaigns that include our agency standard campaign nomenclature (“ATTN”).

How To Use The Example Google Sheets Query Functions

You can use these example functions by copying our search term format exactly and running the functions as they are.  In this case, you need to do 3 things.

  1. Format your search term report exactly the same as the example above.  Download your search term report as anCSV file.  Upload the search term report to your Google Drive and open it in Google Sheets.
  2. Set the named range you are going to use in place of “Keywords” in the function.
  3. Switch out “ATTN” for the campaign nomenclature you use.  If you don’t have any part of your campaign names this is universal, simply pick the campaign name you want to include or exclude from your searches.

If you want to create your own QUERY functions using the example above I suggest going through part 1 of Ben Collins’ Google Sheets QUERY Function tutorial.

Automation Using QUERY Function, Macros, and Google Apps Script

If you are interested in speeding up your workflow, even more, I suggest learning how to use basic Google Sheets Macros and then learning Google Apps Script.  These tools combine with the QUERY Function allow you to create automated workflows for sorting your Google Ads data.

If you want to get started learning Google Apps Script and Google Sheets Macros, I suggest the following resources.

Google Developers YouTube - How To Get Started With Apps Script

Complete Guide To Macros

Beginners Guide To Apps Script

Intro to Google Sheets Named Ranges

Final Thoughts

Google’s QUERY function is the most powerful function in Google Sheets.  It allows you to utilize the same type of searches you would use to pull information from a database.  You even use a language that is derived from SQL (the language used to work with most databases) which means the syntax you learn is a transferrable. Skill.  Just think of this as an introduction to the world of databases!

Heading