Filtering for custom metrics

This article includes information on filtering when creating custom metrics (or modelled data sources) for the following services:

  • Google Analytics and Google Ads
  • Facebook and Facebook Ads
  • HubSpot
  • Twitter and Twitter Ads
  • Instagram Business
  • LinkedIn Pages and LinkedIn Ads
  • QuickBooks
  • Xero
  • Mailchimp
  • Mailchimp

This article includes the following sections:

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 metrics, you choose the data (data view), dimensions (columns/fields), and members (filters on the columns/fields) to query and include in your modelled data source. When you later use the modelled data source 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 + button. When you’re done adding filters, click Apply.

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. 

  1. To add optional data view filters (for example, “StartDate” and “EndDate”), click +Add filters (optional).
  2. Select the checkboxes for the settings you want to add and click Apply.
  3. 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 + button. When you’re done adding filters, click Apply.

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.
  • 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 + button. When you’re done adding filters, click Apply.

  1. In the data preview table, click the filter button in the header row of a column. (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 + button. When you’re done adding filters, click Apply.

For more detailed instructions, refer to examples in this section of the article.

General notes on 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 + button after each addition before clicking Apply.
  • To remove all filters, click the filter button, click Clear all and then click Apply.
  • To remove some filters, click the filter button, click the  remove button beside the value you want to remove and click Apply.

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 modelled data source.

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 filtering

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

Date filter operators include:

  • Equals, Does not equal, Less than, Less or equals, Greater than, and Greater or equals.

Note: Some date data is applicable to a single day only. In such cases, “Equals” 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.

  1. Click the drop-down arrow beside Select a date.
  2. 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).

  1. Click the + button.
  2. Click Apply.

Note: You can only apply 1 filter condition for this type of single 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 Equals as an operator, and select Yesterday as a date.

Example 2: You want to include data for the last 2 years. This example uses Twitter as the service and “Followers” as the data view. Filtering is applied to the column/field called “Created_At”.

  • Apply a filter to Created_At, select Greater or equals as an operator. Select Custom > 2 years ago as a date. Click the + button. Select Less or equals as an operator, and select Yesterday as the date. Click the + button. Click Apply.

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.

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 + button.
  6. Repeat steps 2 - 5 until all desired filters have been added.
  7. Click Apply.

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.

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 + button. Enter United States as the value. Click the + button. Click Apply.

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.>

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 + button.
  6. Repeat steps 2 - 5 until all desired filters have been added.
  7. Click Apply.

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 + button. With Match All still selected, choose Less than as an operator, and enter 4000 as the value. Click the + button. Click Apply.

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 + button. With Match Any still selected, choose Greater than as an operator, and enter 3000 as the value. Click the + button. Click Apply.

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 + button.
  5. Click Apply.

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.
Have more questions? Submit a request