Rows that aren't adequately aggregated to protect end-user privacy, or don't meet Ads Data Hub's other privacy checks, will be dropped from the results of a given query. You can configure a filtered row summary in analysis queries to return a single row containing constants and sums from dropped rows. This can help prevent discrepancies in your data totals, such as a total impression or total click count. A filtered row summary is enabled by default for new analysis queries created via the UI. Filtered row summaries do not apply to audience queries. Queries created via the API do not have a filtered row summary enabled by default.
In the example below, the rows containing campaigns 124 and 125 would be filtered, as they don’t meet the 50-user aggregation requirement.
However, using a filtered row summary, the filtered data can be combined to meet the 50-user aggregation requirement. This example uses a sum to tally filtered data, and a constant to label the row:
- A sum that aggregates filtered data from the
- A constant (used to label the filtered row summary), set to "Filtered row
summary" in the
|Filtered row summary||51|
By default, Ads Data Hub automatically enables and configures a filtered row summary for new queries created via the UI. As you edit the query, Ads Data Hub will automatically adjust the configuration, based on the selected columns.
- To view the automatically configured settings, go to Properties > Filtered row summary. The configuration is read-only.
- To disable automatic mode, click the Automatic toggle. The current configuration is retained and automatic mode is disabled.
- To enable automatic mode, click the Automatic toggle. In the Switch to automatic? dialog that appears, click Yes, reset. The current configuration is removed and automatic mode is enabled.
- To toggle automatic mode via the API, use
To manually add a filtered row summary to a saved query, or after disabling automatic mode:
- Identify the column that you would like to aggregate.
- Click > Filtered row summary in the right-hand-side of the query editor. Add rule under Properties
- Enter the name of the column you would like to aggregate in the Column name field.
- Select Sum from the Type dropdown menu. If the sum falls below aggregation thresholds, the filtered row summary will be dropped.
- Optional: Add more rules as you did in the previous steps.
- Optional: Add an identifier for the filtered row summary using a constant.
Your constant must be a valid data type for your chosen row.
- Enter the name of the column in the Column name field.
- Select Constant from the Type dropdown menu.
- Enter an identifier for the row in the Specify constant field.
- To remove a rule, click delete
- To remove all rules, click Remove all.
If you are exporting multiple tables using the in-sql syntax introduced in Exporting multiple tables, then you can add a filtered row summary in the column options:
CREATE TABLE `myproject.mydataset.mytable` ( campaign_id INT64 OPTIONS(merge_constant=0), ct INT64 OPTIONS(merge_type="SUM") ) OPTIONS(privacy_checked_export=true) AS SELECT campaign_id, COUNT(*) AS ct GROUP BY campaign_id;
This will produce a result table with a filtered row summary where
ct will be the sum of rows that were filtered from
the result due to privacy checks.
Currently, the available column options for filtered-row summary are:
merge_type="SUM": Outputs the sum of filtered values in the summary row. Valid only for numeric types.
merge_constant=<value>: Sets a constant value in the summary row. Must have same type as the column.
- columns with no specified option will have NULL in the summary row.
Only one of
merge_constant may be specified.
Any rules for filtered row summaries apply here, too (i.e. if the sum falls
below the aggregation thresholds, the filtered row summary will be dropped).