Getting started with filters - Supermetrics for Google Sheets

This guide teaches you the basics of building filters, describes each of the logical operators available and mentions some of the most common errors and issues regarding filters. 

 

Filtering is a way to control the end results of your query. All the fields available to you on the data source can be used in a filter. A filter is built by choosing the field to filter by, choosing the logical operator and then entering a value to filter by. 



TABLE OF CONTENTS



Building a filter


On the sidebar, go to the Filter section and click on the text “Add filter” or the little plus next to it.

This will open up the first filter to be created. Choose the field you wish to filter by from the "Field:" dropdown menu, choose the operator (see "Operators explained" below) to function by, and enter the filter string of your choice. For example:



Once you are happy with the filter, click “Apply changes” to run the query with the filter. 


You can make changes to your filters by modifying the query on the sidebar, or remove them by clicking the trashcan icon next to the filter, then clicking “Apply changes”.


You can add multiple filters to a single query. To do this, simply click on the “Add filter” or plus icon under your filter. Now you will be presented with a choice of “AND” or “OR” join. This will decide how your two filters will behave. If you choose “AND”, then your results must match both filters in order to be shown/discarded. If you choose “OR”, then results that match either one of your filters will be shown/discarded.


Good to know:

- Letter case is irrelevant in EQUALS, DOES NOT EQUAL, CONTAINS and DOES NOT CONTAIN operators (meaning that “TOFU 2020” and “tofu 2020” will both get the same results).

- Letter case is relevant in MATCHES REGEX and DOES NOT MATCH REGEX operators (meaning that “TOFU 2020” and “tofu 2020” will get different results).

 

 

Operators explained

 

EQUALS

The EQUALS operator compares the query results to the string you enter into the Value-field, and it will include only the results that match your string completely.


DOES NOT EQUAL

This is the direct opposite of EQUALS. This operator will leave out matching results. 


CONTAINS

The CONTAINS operator takes the string you enter into the Value-field, compares the query results to it, and it will include only the results that have your string as a part of them (that part still has to fully match the value you enter into the filter).


DOES NOT CONTAIN

This is the direct opposite of the CONTAINS. This operator will leave out matching results.


MATCHES REGEX

The MATCHES REGEX operator takes the regular expression string you enter into the Value-field and compares it to the results, leaving in the results that match your regex string.


Sources for learning about regular expressions can be Googled easily. Here is one example:

https://regexone.com/


DOES NOT MATCH REGEX

The DOES NOT MATCH REGEX operator is the direct opposite of MATCHES REGEX. This operator will leave out the matching results.


GREATER THAN

GREATER THAN is a mathematical operator that will include any results which are HIGHER than the filter value. This means that if you wish to filter in values that are for example 500 and higher, your filter value should be 449.


LESS THAN

The LESS THAN operator is also a mathematical operator that will include results which are LOWER than the filter value. For example, if you want values that are 500 and under, your filter value should be 501.



Dynamic cell references


You can also use cell references as values for your filter:If you wish to have several cell values in a single filter, you will have to build the reference on the SupermetricsQueries sheet - read this guide to learn how to do that:
Using cell references to dynamically update the query



Common issues with filters


If your filter is giving you the "No data" error, or is producing wrong results, it is most likely due to mistake in the set up. Please check the following most common errors, and double check your filter set up.

TIP: A good way to troubleshoot your filter is to make sure the field used in your filter is selected also in the query as a metric or a dimension. Remove the filter, run the query and see if the results for that field would be caught by your filter or not.


  1. Leaving a whitespace out, or adding extra whitespace at the start/end of line. Check your string for extra or missing whitespace
  2. Mistakenly selecting the wrong field than what you are actually trying to filter by - double check the chosen field
  3. Filter has a deprecated field that does not work anymore (especially happens with old reports that have not been checked in a long time). Check that the fields used are not deprecated (you can change data source on the left-hand panel): https://supermetrics.com/docs/integration-facebook-ads-fields/
  4. Letter case when using REGEX filtering - other operators are not case sensitive, but both REGEX operators are, due to the nature of Regular Expressions - check that you have correct letter case, so that your filter matches the results you are trying to filter.


Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.