Filter by a field that is not in your report
A filter does not have to use one of the columns of your report. You can filter a query by any field of the connector, also by a field that is not in Metrics or Dimensions. Two Minute Reports uses the field to select the rows. The field does not go into your sheet.
This is useful when something else reads your columns, for example a dashboard, a script or a formula that reads a fixed range. You can change which rows count, and the report keeps the same columns.
How it works
When you filter by a field that is not one of your columns:
- Two Minute Reports uses the field to select the rows that match the filter.
- It adds up the numbers of those rows for the metrics and dimensions that you selected.
The result has the columns that you selected, in the same order. The filter field is not written to the sheet.
Each filter row in Filters is a rule. A row must obey all the rules to count. In one rule, you can give more than one value:
- With CONTAINS, NOT CONTAIN, EQUALS and NOT EQUAL, select OR under the value to add one more value.
- With IN and NOT IN, select more than one value in the list.
- With GREATER THAN, LESSER THAN, MATCHES REGEX and DOES NOT MATCH REGEX, you can give only one value.
Example: count only one type of conversion
Conversions are the usual reason to use this type of filter. An ad platform gives one Conversions number for all the conversion actions that you set up: purchases, form submissions, page views and add-to-carts. If a person adds a new conversion action, your Conversions column starts to include it, and you get no warning.
On Google Ads, you can count only some conversion actions, and keep the same columns:
Open the filters of the query
In the query, open Filters and select + Filter.
Select the conversion field
In the field list, select Conversion action name or Conversion action category. Do not add this field to your dimensions.
Set the condition
For example, set Conversion action name CONTAINS Purchase, or Conversion action name NOT CONTAIN Page view. To add more values to the same rule, select OR.
Run the query
Your columns do not change. Conversions and Conversions value now count only the conversion actions that match the filter.
Use only conversion metrics in this query
Google Ads does not give delivery metrics split by conversion action. Thus, Two Minute Reports does not let you use a conversion action filter with the metrics of the Performance group, for example Impressions, Clicks and Amount spent, or with Cost per conversion and Conversion rate %. You get an error before the query runs. In a query that you filter by conversion action, use Conversions, All conversions and Conversions value. Get impressions, clicks and cost from a different query. See Fields that you cannot use with these methods.
Count only purchases, for each connector
Each platform has a different method to show conversion types. Thus, the field that you use is different on each platform.
| Platform | How to count only purchases |
|---|---|
| Google Ads | Filter by the Conversion action name or Conversion action category dimension, as in the example above. Google Ads has one Conversions metric for all conversion actions, so a filter is the only method to count only purchases. |
| Facebook Ads | There is no conversion type dimension to filter by. Select a purchase metric: Purchases (all), Website purchases or Purchases value (all). Do not select a general conversion or results metric. |
| TikTok Ads | Select Complete payment and Total complete payment Value. Do not select Conversions. |
Click-through and view-through conversions
A filter cannot remove view-through conversions. The attribution window sets if a view-through conversion counts.
- Facebook Ads: in the query, open Report Config, then FACEBOOK ADS. In Conversion/Attribution Window(s), replace Default with a click-only window, for example 7 d click. If you select only one window, the column names do not change. If you select more than one window, you get one column for each window, for example Purchases (all) (7 d click).
- TikTok Ads: CTA conversions (click-through) and VTA conversions (view-through) are two different metrics. Select CTA conversions.
Blended queries
A blended query uses the fields of more than one connector. When you select a blended metric, for example Conversions, each connector gives a different metric of its own:
| Blended metric | Google Ads | Facebook Ads | TikTok Ads |
|---|---|---|---|
| Conversions | Conversions (all conversion actions) | Conversions | Conversions |
| Conversion value | Conversions value | Conversion Values | Total complete payment Value |
| Purchases | All conversions | Purchases (all) | Purchase events |
| Leads | Conversions (all conversion actions) | Leads (all) | Total generate lead |
Thus, the same change does not give the same result on each connector. To count only purchases in a blended query:
- Facebook Ads and TikTok Ads: replace Conversions with Purchases. On these connectors, Purchases counts only purchases. You replace one column with a different column, so the position of the column does not change.
- Google Ads: do not use Purchases. On Google Ads, it gets All conversions, which includes more conversions than Conversions. Keep Conversions, and add a filter on the Google Ads field Conversion action name, as in the example above.
A filter on the field of one connector applies only to the rows of that connector. A filter on the Google Ads field Conversion action name changes only the Google Ads numbers. The Facebook Ads and TikTok Ads numbers do not change. A filter on a blended field applies to the rows of all the connectors.
Related pages
- Running queries: where to find Filters in a query
- Custom fields: make a calculated field when a filter is not sufficient
Speed up your Google Data Studio reports
5 practical tips to make your Google Data Studio reports load faster and feel more responsive.
Report Google Ads lead form submissions
Get the number of leads from a Google Ads lead form, also on Performance Max campaigns, into Google Sheets or Google Data Studio.