Creating data feeds for custom metrics: Filtering data in the query builder

This article includes information on filtering in the query builder when creating data feeds for custom data feed metrics for the following services:

• Airtable
• Facebook and Facebook Ads
• Google Ads
• Google Analytics 4
• HubSpot
• Instagram Business
• LinkedIn Ads and LinkedIn Pages
• Mailchimp
• QuickBooks
• Salesforce
• Shopify
• Smartsheet
• Stripe
• X Ads (Twitter Ads)
• Xero
• YouTube
• Zendesk
• Zuora

This article includes the following sections:

• Why apply filters to my data?
• Where can I apply filters?
• How do I apply filters?
• General notes on filtering
• Filtering using Match All and Match Any
• Filtering options with examples
  • Date filtering
  • Text filtering
  • Numeric filtering
  • Boolean filtering

Why apply filters to my data?

Applying filters to your data enables you to:

• Query only the data you need. Using filters helps you narrow down the data you’re looking for, ensuring you only query the data you need. By pinpointing your desired data, you optimize the volume of data being queried, which not only makes your data easier to understand and handle, it often makes the query run faster!
   
• Select the dimensional members to use later in your custom metrics. Applying filters now saves you time later when you’re creating metrics (because you’ve already chosen the dimensions and members that will be available for your metrics). When creating custom data feed metrics, you choose the data (data view), dimensions (columns/fields), and members (filters on the columns/fields) to query and include in your data feed. When you later use the data feed to create custom metrics, you’ll have access to all the data, dimensions, and members you previously selected!

Where can I apply filters?

You can apply filters to:

• The data view - This is a subset of your data. It’s the first item you select when defining the data you want to query.
   
• Columns (fields) - These are the columns of data (sometimes called fields) from within the selected data view. You apply filters here if you want to filter your data before previewing it.
   
• Columns in the data preview table - After selecting a data view and one or more columns (fields), you preview the data to see the columns you selected displayed in a table format.

Data view filtering

Adding filters to the data view helps define the data to be queried.

Some services require you to apply mandatory data view filters. You can also apply optional data view filters to further specify your query. “StartDate” and “EndDate” are common examples of optional data view filters. (See below.)

Note: If applicable, we recommend you set date filters at the data view level (instead of at the columns/fields level). Applying date filters here, at the query (source) level, rather than later at the query results level can improve efficiency and simplify data handling.

Columns (fields) filtering

Applying filters to columns/fields can be a little tricky because you can’t see the data while you’re applying the filters (unlike applying filters in the data preview table). However, if you know your data well, you might prefer to set filters here. Doing so helps remove excess data before creating the data preview table, and may be necessary when there’s a large amount of data that cannot be queried all at once. Setting filters for boolean data is a good example of simple, effective column/field filtering. (See below.)

Data preview table filtering

You may find it easier to apply filters to the columns in the data preview table because you can see your data set. However, you should keep in mind that the data preview may not show all of the data if you’re bringing in a particularly large amount. The data preview table can display up to the first 1000 rows of your data.

How do I apply filters?

Filtering methods differ slightly depending on where you're applying them.

Applying filters to a data view

Data view filters (both required and optional) enable you to further define the data view query.

To add data view filters:

1. After selecting a data view, for required data view filters, click the Click to add filter box and select the necessary operators and values to retrieve the desired data. After selecting each operator and value, click the Filter button. Click anywhere outside the filter dialog to close it and save your filters.
   Note: Only some services and data views include required data view filters. If applicable, they will display under the data view after it’s selected. See below for an example of a required data view filter (“TimeIncrement”). This example uses Facebook Ads as the service and “AdInsights” as the data view.
   
2. To add optional data view filters (for example, “StartDate” and “EndDate”), click +Add filters (optional).
3. Select the checkboxes for the settings you want to add and click Apply.
4. Click the Click to add filter box and select the necessary operators and values to retrieve the desired data. After selecting each operator and value, click the Filter button. When you’re done adding filters, click anywhere outside the filter dialog to close it and save your filters.
   For more detailed instructions, refer to examples in this section of the article.

Applying filters to columns/fields

Filters for columns/fields in the left sidebar and columns in the data preview table enable you to further define the results of the data view query.

To add column/field and data preview table filters:

1. In the left sidebar, click the filter button for a column/field. (See below.)
   • Click the Click to add filter box and select the necessary operators and values to retrieve the desired data. After selecting each operator and value, click the Filter button. When you’re done adding filters, click anywhere outside the filter dialog to close it and save your filters.
     

2. In the data preview table, click the filter button in the header row of a column. (See below.)
   • Select the necessary operators and values to retrieve the desired data. After selecting each operator and value, click the Filter button. When you’re done adding filters, click anywhere outside the filter dialog to close it and save your filters.
     
     For more detailed instructions, refer to examples in this section of the article.

General notes on filtering

Here are a few things to note about filtering:

• You can add a maximum of 10 filter conditions to each data view or column, except for boolean filters and single data filters (such as “StartDate” and “EndDate”) where you can only add one. When adding filter conditions, click the Filter button after each addition.
• If a column has filters applied to it, there'll be an indicator beside the filter icon (in the left sidebar and in the data preview). See below for an example of a column with 1 filter applied.
  
  

• To remove all filters, click the filter button, click Clear all. 
• To remove some filters, click the filter button, click the  remove filter button beside the value you want to remove.
• To edit an existing filter, select the filter, choose a different operator or enter a different value, then, click Update. (See below.)
  

• Service APIs are unique. As a result, there’s no single rule for filtering out null and empty values. Unless you specifically want to include null or empty values in the data, we recommend you filter out both by adding two filters, one for “Is not empty” and one for “Is not null”.

Filtering using Match All and Match Any

Filters for some data formats use “Match All” and “Match Any” operators.

When filtering, you can either use one of these operators or combine them to pick and choose the data you want to include in your data feed.

You can apply up to 10 filter conditions for each data view, field, or column. When deciding whether to use Match All or Match Any, consider which method requires fewer filter conditions.

What is a Match All operator?

When you apply filters using Match All, the data is filtered according to all the conditions you set. All statements must be true for a match.

What is a Match Any operator?

When you apply filters using Match Any, the data is filtered according to at least one of the conditions you set. Any one of the statements must be true for a match.

Filtering options with examples

You can apply filters to these four data formats:

• Date
• Text
• Numeric
• Boolean

Date filtering

Date filters enable you to filter data based on operators you select and dates you enter.

Date filter operators include:

• Is, Is not, Before, On or before, After, and On or after, Is empty, Is not empty, Is null, Is not null.

Note: Some date data is applicable to a single day only. In such cases, “Is” will be the only available operator (for example, “StartDate” and “EndDate”).

Date filter date options include:

• Today, Yesterday, 7 days ago, Last week, 30 days ago, Last month, 6 months ago, Last year, and Custom.

Note: We recommend that, when possible, you apply date filtering at the data view (query) level rather than at the column/field level. Applying filters at the query (source) level, rather than later at the query results level can improve efficiency and simplify data handling. The following procedure is an example of adding date filters at the data view level.

To apply date filters for a data view:

1. In the left sidebar, under Data view, click +Add filters (optional).
2. Select StartDate and/or EndDate and click Apply.
3. Click the Click to add filter box.
   
4. Click the drop-down arrow beside Select a date.
5. Choose a preset date option or select Custom.
   If you selected Custom, enter the time period you want to include (value > days ago, weeks ago, months ago, or years ago).
6. Click the Filter button.
7. Click anywhere outside the filter dialog to close it and save the filter.
   Note: You can only apply 1 filter condition for this type of single day date filter.

Date filter examples

Example 1: You want to include data for yesterday. This example uses a single day date filter on a data view.

• Apply a filter to StartDate, select Is as an operator, and select Yesterday as a date. Click the Filter button.

Example 2: You want to include data for the last 2 years, excluding today. 

• Apply a filter to Created_At, select On or after as an operator. Select Custom > 2 years ago as a date. Click the Filter button. Select Before as an operator, and select Today as the date. Click the Filter button.

Text filtering

Text filters enable you to filter data based on operators you select and values you enter.

Text filter operators include:

• Match All, Match Any, Equals, Does not equal, Contains, Does not contain, Starts with, Does not start with, Ends with, Does not end with, Is empty, Is not empty, Is null, Is not null.

Tip: If applying a filter using "Equals" as a condition isn't working, try selecting a less specific condition: "Contains", "Starts with", or "Ends with".

To apply text filters:

1. Click the filter button.
2. Select either Match All or Match Any.
3. Select an operator from the drop-down list.
4. Enter a value.
5. Click the Filter button.
6. Repeat steps 2 - 5 until all desired filters have been added.
7. Click anywhere outside the filter dialog to close it and save the filter(s).
   Note: You can apply up to 10 filter conditions per column/field.

Text filter examples

Example 1: You want to exclude null (not set) values.

• Apply a filter to the applicable field or column, select Match Any and Does not equal as operators, and enter (not set) as the value. Click the Filter button.

Example 2: You want to include data for only two items from a list. In this example, the filters are set to include values for Canada and the United States from a list of countries.

• Apply a filter to the field or column that includes country names, select Match Any and Equals as operators, and enter Canada as the value. Click the Filter button. Enter United States as the value. Click the Filter button.

Numeric filtering

Numeric filters enable you to filter data based on operators you select and values you enter.

Numeric filter operators include:

• Match All, Match Any, Equals, Does not equal, Less than, Less or equals, Greater than, and Greater or equals, Is empty, Is not empty, Is null, Is not null.

To apply numeric filters:

1. Click the filter button.
2. Select either Match All or Match Any.
3. Select an operator from the drop-down list.
4. Enter a value.
5. Click the Filter button.
6. Repeat steps 2 - 5 until all desired filters have been added.
7. Click anywhere outside the filter dialog to close it and save the filter(s).
   Note: You can apply up to 10 filter conditions per column/field.

Numeric filter examples

Example 1: You want to see a limited range of values. In this example, the filters are set to include values ranging from 1000 to 4000. Values outside of this range will not be included.

• Apply a filter to the applicable field or column, select Match All and Greater than as operators, and enter 1000 as the value. Click the Filter button. With Match All still selected, choose Less than as an operator, and enter 4000 as the value. Click the Filter button.

Example 2: You want to see values on either side of a range. In this example, the filters are set to include values that are less than 500 and greater than 3000. Values between 500 and 3000 will not be included.

• Apply a filter to the applicable field or column, select Match Any and Less than as operators, and enter 500 as the value. Click the Filter button. With Match Any still selected, choose Greater than as an operator, and enter 3000 as the value. Click the Filter button.

Boolean filtering

Boolean filters enable you to filter data based on the operator you select and the value you enter.

Boolean filter operators include:

• Equals and Does not equal.

To apply a boolean filter:

1. Click the filter button.
2. Select an operator (either Equals or Does not equal).
3. Select a value (either True or False).
4. Click the Filter button.
5. Click anywhere outside the filter dialog to close it and save the filter.
   Note: You can only apply one boolean filter condition per column/field.

Boolean filter example

You want to see only the Google Analytics profiles that have ecommerce tracking enabled.

• With Profiles as the selected data view, apply a filter to the field called eCommerceTracking. Select Equals as the operator and True as the value.