Filter Operators Reference

Filters typically consist of three parts:

  • The name of the field you’re filtering on.
  • The value that you’re filtering for.
  • The operator, which describes the relationship between the field and the value (for example, the field is equal to the value, the field is greater than the value, etc.).

Or, to put it a bit more graphically:

The Parts of a Filter

By default, each filter you add starts off with the is equal to operator. However, you can change the operator at any time by clicking the operator dropdown list:

Changing a Filter Operator

The dropdown list shows all the operators that can be used with the specified field. Keep in mind that the list of available operators changes based on the datatype of the associated field. For example, if you’re working with a datetime value you’ll see the in the past operator, which enables you to create a query that shows, say, everything that has happened in the past 30 days. If you’re working with a string field, however, you won’t see the in the past operator; that’s because that operator makes no sense when using alphanumeric values.

The operators available in Customer Insights are described below.

is equal to

Returns records if the specified field matches the target value. The filter Country Name is equal to United States only returns records where the country name is United States.

contains

Returns records if the specified field includes the target value. For example, the filter Country Name contains w returns all the records that have the string value w anywhere in the country name:

  • Botswana
  • Kuwait
  • New Zealand
  • Norway
  • Sweden
  • Taiwan
  • Zimbabwe

The filter Country Name contains we returns all the record that have the string value we anywhere in the country name:

  • Sweden
  • Zimbabwe

starts with

Returns records if the specified field begins with the target value. For example, the filter Country Name starts with j returns all the records where the country name starts with the string value J:

  • Jamaica
  • Japan
  • Jordan

ends with

Returns records if the specified field ends with the target value. For example, the filter Country Name ends with u returns only those records where the country name ends with the string value u:

  • Nauru
  • Palau
  • Tuvalu
  • Vanuatu

is null

Returns all records where the specified field is null. See Null Values vs. Blank Values for more information.

is blank

Returns all records where the specified field is blank. See Null Values vs. Blank Values for more information.

is not equal to

Returns records where the specified field does not match the target value. For example, the filter Country Name is not equal to United States returns all records except those where the country name is equal to United States.

doesn’t contain

Returns records where the specified field does not contain the target value. For example, the filter Country Name does not contain ire will not return Ireland or Cote d’Ivoire.

doesn’t start with

Returns records where the specified field does not begin with the target value. For example, the filter Country Name doesn’t start with d returns all countries except Denmark, Djibouti, Dominica, and Dominican Republic.

doesn’t end with

Returns records where the specified field does not end with the target value. For example, the filter Country Name doesn’t end with i will not return countries such as Brunei, Burundi, and Fiji.

is not null

Returns all records where the specified field is not null. See Null Values vs. Blank Values for more information.

is not blank

Returns all records where the specified field is not blank. See Null Values vs. Blank Values for more information.

is in the past

Returns all the records where the specified field occurred X in the past X number of days (it’s up to you to type in the value of X). For example, this query returns all the records from the past 45 days:

The Is in the Past Operator

This operator is only available when working with datetime values.

is on the day

Returns all records where the specified field matches the selected date.

This operator is only available when working with datetime values.

is in range

Returns all the records where the event occurred within the specified time range. For example, you might select all the events recorded between February 1, 2018 and February 10, 2018.

This operator is only available when working with datetime values.

is before

Returns all the records where the event occurred before the specified date. When indicating the target date you have two options:

  • Absolute, which shows you all the events that occurred before a specific date (e.g., March 1, 2018).
  • Relative, which lets you specify a time interval prior to today (for example, all events that happened on or after the date 7 days ago or the date 3 months ago).

This operator is only available when working with datetime values.

is on or after

Returns all records where the event occurred on or after the specified date. When indicating the target date you have two options:

  • Absolute, which shows you all the events that occurred on or after a specific date (e.g., March 1, 2018).
  • Relative, which lets you specify a time interval prior to today (for example, 7 days ago, 24 hours ago, 3 months ago, etc.).

This operator is only available when working with datetime values.

is in the year

Returns only those records that occurred in the specified year; for example, all the registrations recorded in the year 2017.

This operator is only available when working with datetime values.

is any time

Returns all records, regardless of the date or time.

This operator is only available when working with datetime values.

matches a user attribute

Valid only if your administrator has configured user attribute matching.

matches (advanced)

Enables you to do advanced filtering. For example, suppose you return data from the Region field, and get back results similar to this:

Region Data

As shown above, at least one of the entries in the database - Arizona - is invalid; the Region field is supposed to consist of two-letter abbreviations (for example, AZ instead of Arizona). Is there a way to filter out these invalid entries? Here’s one way to do that:

length(${location_dim.region}) = 2

In the preceding filter, we’re returning only values in the Region field that have a “length” of 2; that means we only want to get back regions that contain exactly 2 characters. For example:

Filtered Region Data

For more information, see Looker Functions and Operators.

Null Values vs. Blank Values

One of the most confusing aspects about database querying (which, at heart, is what Customer Insights really is) is the difference between null values and blank values. You might have noticed that the operators dropdown list includes both the operators is blank and is null. That’s great, except for one thing: what’s the difference between a blank value and a null value?

To be honest, the answer to that question is perhaps more philosophical than it is practical. In database terms, however, a null value is a value that doesn’t exist: the field does not contain a value of any kind (not even a blank value). By contrast, a blank value is a real value: it just happens to be a string value containing 0 characters. Two scenarios are largely responsible for blank values:

  1. The user fails to complete a field on their registration form. For example, a user does not enter his or her country or phone number.
  2. Your registration form is configured to save empty fields as blank values rather than null values.

Fortunately, all you really need to know is this: blank values are possible only when working with strings. For example, click on an operator for any other datatype and you won’t see either the is blank or the is not blank operators listed:

No Is Blank Operator

Consequently, the blank/null distinction doesn’t apply if you’re working with datetime or numeric values. And what if you are working with string values and you want to filter out items that don’t seem to have a value? In that case, just filter out both blanks and nulls:

Filtering for Null Values and Blank Values

Do that and you’re covered regardless of whether that empty value is blank or null.

See Also

Scroll ↓