Noise injection is a technique used to protect user privacy when querying a
database. It works by adding random noise to an aggregating `SELECT` clause of a
query. This noise protects user privacy while providing reasonably accurate
results with a minimal required aggregation threshold for output. Most existing
queries can be executed in noise mode,
[with some limitations](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#unsupported_query_patterns).

> [!IMPORTANT]
> **Important:** Ads Data Hub's [MRC accreditation](https://developers.google.com/ads-data-hub/marketers/resources/description-of-methodology) does not extend to queries run in noise mode.

## Learn the benefits of using noise injection

**Troubleshooting is simplified:** Rows are only omitted due to aggregation
requirements, making it simpler to troubleshoot and adapt queries.

**There is no new syntax to learn:** You don't need to learn any new query
syntax or be versed in privacy concepts to use noise.

**Result accuracy is reported:** A successful job shows the total percentage of
data that could have been affected by noise.

## Learn how noise impacts privacy requirements

**Aggregation requirement:** Noise injection outputs impression data represented
by approximately 20 or more unique users, and click or conversion data
represented by approximately 10 or more unique users.

**Static checks:** No impact.

**Budgets and query limits:** Noise injection imposes limits on how many times
the same query can be executed on the same dataset. When recomputing the same
aggregate results either within a single query or across multiple query runs,
your results may be redacted. This can happen if you run sliding window queries,
or if you make the same request multiple times. For more information, see
[Repeated results](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#repeated_results).

[Learn more about privacy checks](https://developers.google.com/ads-data-hub/marketers/guides/privacy-checks).

## Understand how noise injection affects results

Ads Data Hub injects noise to mitigate disclosure risk---the risk that
someone can learn information about an individual user. It balances privacy
against utility.

Noise injection in Ads Data Hub transforms the query results as follows:

- It clamps outlier users' contributions in aggregate results. It sums each user's contribution in each aggregation and then caps each contribution with minimum and maximum clamping bounds.
- It aggregates the clamped per-user contributions.
- It adds noise to each aggregate result---the result of each aggregation function call in each row. The scale of this random noise is proportional to the clamped bounds.
- It computes a noisy user count for each row and eliminates rows with too few users (approximately 20 users per row). Because of noise, jobs running on the same dataset can drop different rows. This requirement can be avoided using [public groups](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#public_groups).

The final result is a dataset where each row has noisy aggregate results and
small groups have been eliminated. This masks an individual user's effect on
returned results.

### About aggregation clamping

Noise injection in Ads Data Hub uses implicit or explicit aggregation
clamping to limit the contribution of outliers. You can choose which type of
clamping to use, depending on your use case.

#### Implicit clamping

You don't need any special SQL syntax to use implicit clamping, it is applied by
default. Implicit bounds are derived from the data itself and are determined for
each aggregation. If some aggregations have a wider range of values than others,
implicit bounding can infer different bounds for different aggregations as
appropriate. This typically results in lower errors. Note that `COUNT(DISTINCT
user_id)` automatically clamps the per user contribution to `1`.

#### Explicit clamping

Explicit clamping clamps the total contribution from each user to a specified
range. Explicit bounds are uniformly applied to all aggregations and must be
[literal
values](https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#literals).
Explicit clamping may provide better results when bounds are generally known.
For example, bounding ages between 0 and 100 reflects public information because
the age of most people is generally within this range.

Ads Data Hub provides supplemental [`ADH.ANON` aggregate
functions](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#supplemental_aggregate_functions) for explicit clamping. To use
explicit clamping, set the bounds for each supported aggregate function by
adding integers representing the lower bound and the upper bound. For example:

    SELECT
    campaign_name,
    -- Set lower and upper bounds to 0 and 1, respectively
    ADH.ANON_COUNT(*, contribution_bounds_per_group => (0,1))
    FROM data
    GROUP BY 1

## Run a query using noise injection

1. Open a report.
2. Click the **Privacy noise settings** toggle to the **Use noise** position.
3. [Run the query](https://developers.google.com/ads-data-hub/marketers/guides/run-queries#run_a_query).
4. [Review the impact](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#review_noise_impact) of the added noise.
5. Optional: [Adapt the query](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#adapt_queries) to reduce noise impact.

## Review noise impact

Once a job completes successfully, Ads Data Hub displays the reliability
of the result in the privacy summary. Reliability is based on the percentage of
cells in the output that may be highly impacted by noise. A value in the result
table is considered impacted if the scale of the added noise is greater than 5%
of the result in the cell.

For impacted output data sets, the privacy summary lists the ten noisiest
columns from highest to lowest impact and their corresponding contribution to
noise. This is the breakdown of the noise impact labels.

| **% of impacted results** | **Indicator color** | **Impact** |
|---|---|---|
| \<5% | Green | Low impact |
| 5%-15% | Yellow | Medium impact |
| 15%-25% | Orange | High impact |
| \>25% | Red | Very high impact |

You can also preview the privacy summary for recent report jobs on the **Home**
page. To preview the privacy for a particular job, hold the pointer over the
privacy tip icon privacy_tip in
the job card under **Recent activity**.

## Adapt queries

Aggregations are more likely to be impacted by noise when few users contribute
to the result. This can happen when aggregations are computed from small user
sets or when some users don't affect results, which can happen, for example,
with the `COUNTIF` function. Based on the noise report, you may want to adjust
your query to reduce the percentage of impacted results.

The following are general guidelines:

- Expand the date range.
- Rewrite the query to reduce the granularity of the data, such as by grouping by fewer parameters or replacing `COUNTIF` with `COUNT`.
- Remove noisy columns.
- Try [explicit clamping](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#explicit_clamping) when reasonable bounds can be chosen.

*** ** * ** ***

## Supported aggregate functions

The following aggregate functions are supported with noise:

- `SUM(...)`
- `COUNT(*)`
- `COUNT(...)`
- `COUNTIF(...)`
- `COUNT(DISTINCT ...)`
- `APPROX_COUNT_DISTINCT(...)`
- `AVG(...)`

The `DISTINCT` keyword is only supported with the `COUNT` function. When used
with a direct reference to the `user_id` column from an Ads Data Hub table
or an expression that returns either `user_id` or `NULL`, such as
`COUNT(DISTINCT IF(..., user_id, NULL))`, the `COUNT DISTINCT` and
`APPROX_COUNT_DISTINCT(...)` functions are computed by clamping the per user
contribution to `1`. When `COUNT DISTINCT` references a non-`user_id` column, it
is approximated using `APPROX_COUNT_DISTINCT` with implicit clamping.

### Supplemental aggregate functions

In addition to supporting regular aggregators, Ads Data Hub introduces
supplemental `ADH.ANON` aggregate functions that support explicit clamping.
These aggregators share the syntax with the [BigQuery differentially private
aggregate
functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-dp-functions),
however, they don't require the `WITH DIFFERENTIAL_PRIVACY` clause:

- `ADH.ANON_SUM( ..., [ contribution_bounds_per_group => (lower_bound,
  upper_bound) ] )`

- `ADH.ANON_COUNT( *, [ contribution_bounds_per_group => (lower_bound,
  upper_bound) ] )`

- `ADH.ANON_COUNT( ..., [ contribution_bounds_per_group => (lower_bound,
  upper_bound) ] )`

- `ADH.ANON_AVG( ..., [ contribution_bounds_per_group => (lower_bound,
  upper_bound) ] )`

- `ADH.ANON_PERCENTILE_CONT( ..., percentile, contribution_bounds_per_row =>
  (lower_bound, upper_bound) )`

- `ADH.ANON_COUNT_DISTINCT( ..., [ max_contributions_per_group => upper_bound
  ] )`

`ADH.ANON_SUM`, `ADH.ANON_COUNT` and `ADH.ANON_AVG` parameters:

- `contribution_bounds_per_group`: Contributions per user are clamped for each partition defined by the `GROUP BY` keys. The upper and lower bounds are applied to values per group after the values are aggregated per user.
- `lower_bound`: Numeric literal that represents the smallest value to include in an aggregation.
- `upper_bound`: Numeric literal that represents the largest value to include in an aggregation.

`ADH.ANON_PERCENTILE_CONT` parameters:

- `percentile`: The percentile to compute, a literal in the range `[0, 1]`.
- `contribution_bounds_per_row`: Contributions per user are clamped on a per-row (per-record) basis. Note that explicit clamping bounds are required for the percentile and therefore it is only supported as a supplemental function.
- `lower_bound`: Numeric literal that represents the smallest value to include in an aggregation.
- `upper_bound`: Numeric literal that represents the largest value to include in an aggregation.

`ADH.ANON_COUNT_DISTINCT` parameters:

- `max_contributions_per_group`: Contributions per user are clamped for each partition defined by the `GROUP BY` keys. The upper bound limits the maximum user contribution per group after the values are aggregated per user.
- `upper_bound`: Numeric literal that represents the largest value to include in an aggregation.

### Compute MIN and MAX

The `MIN` and `MAX` functions are not directly supported in noise aggregations,
but there are often alternative methods to compute these results.

If you have a `MIN` or `MAX` of values that can be used as grouping keys, such
as event date, you can first GROUP BY that value, then compute `MIN`/`MAX`
afterward. This returns the minimum or maximum value that passes aggregation
thresholding.

Example:

    WITH campaign_date_ranges AS (
      SELECT campaign_id, MIN(event_date) AS min_date, MAX(event_date) AS max_date
      FROM (
        # Aggregation thresholding will be applied here
        SELECT DISTINCT
          campaign_id,
          DATE(query_id.time_usec, @time_zone) AS event_date
        FROM adh.google_ads_impressions
      )
    )
    SELECT campaign_id, num_impressions, min_date, max_date
    FROM (
      # Noise and aggregation thresholding will be applied here
      SELECT campaign_id, COUNT(*) AS num_impressions
      FROM adh.google_ads_impressions
    )
    JOIN campaign_date_ranges USING(campaign_id)

Alternatively, if you have a MIN or MAX of granular values with known bounds,
you can use `PERCENTILE_CONT` with [explicit bounds](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#explicit_clamping) for an
approximate result.

Example:

    SELECT
      campaign_id,
      COUNT(*) AS num_impressions,
      ADH.ANON_PERCENTILE_CONT(
        query_id.time_usec, 0,
        contribution_bounds_per_row => (@min_timestamp, @max_timestamp))
        AS min_timestamp,
      ADH.ANON_PERCENTILE_CONT(
        query_id.time_usec, 1,
        contribution_bounds_per_row => (@min_timestamp, @max_timestamp))
        AS max_timestamp
    FROM adh.google_ads_impressions

### About integer results

Although Ads Data Hub will automatically inject noise for these aggregate
functions, the function signatures don't change. Because functions like `COUNT`
or `SUM` of `INT64` return `INT64`, any decimal part of the noised result is
rounded. This is usually negligible relative to the size of the result and
noise.

If you need the granularity of the decimal in your result, then avoid writing
functions that return `INT64`--for example, by using `SUM` with its input cast to
`FLOAT64`.

### About negative results

In principle, noise with very small values can result in negative numbers, even
when this should be semantically impossible for the query. To maintain expected
behavior, all forms of `COUNT` and `COUNTIF` are automatically clamped at zero,
so they never give negative results. If you want this same behavior with another
function, such as `SUM`, then you can clamp results manually using `GREATEST(0,
SUM(...))`.

This change is usually negligible, but it does introduce a small positive bias
to overall results.

### Public groups

With a `GROUP BY` clause, the anonymized results of a query are aggregated over
groups. Aggregation thresholding is applied to make sure that a sufficient
number of users are present in the group so that individual user data is
protected. The process of determining which groups can be released is called
"partition selection".

In many cases groups may be public knowledge. For example, grouping by browser
version, day of the week, or a geographical region does not depend on user data
if the grouping key values are known in advance. In this instance, the partition
selection can be omitted, since the presence or absence of a group in the output
does not provide any new information about the users.

Ads Data Hub identifies queries eligible for public groups and does not apply
aggregation thresholding to these queries. This means that no output rows are
filtered out. Note that results computed from a small number of users can be
heavily impacted by noise.

To be eligible for public groups, the query must be structured to ensure that
all grouping keys are known in advance. The grouping columns must satisfy the
following conditions:

- They come from a public table (a table or `SELECT` clause with no Ads Data Hub user data).
- They have `SELECT DISTINCT` applied to enforce unique values.
- They are joined into the query with an `OUTER JOIN` on all of the individual columns.

When a query is eligible for public groups, the Ads Data Hub UI will display the
following validation message: 'Join supports public groups and is not subject to
aggregation thresholding.'

Examples of public groups queries:

    SELECT age_group_id, COUNT(*) FROM adh.google_ads_impressions
    RIGHT OUTER JOIN (SELECT DISTINCT age_group_id FROM adh.age_group)
    ON demographics.age_group = age_group_id
    GROUP BY age_group_id

    SELECT age_group_id, COUNT(*) FROM adh.google_ads_impressions
    RIGHT OUTER JOIN (SELECT DISTINCT * FROM UNNEST([1, 2, 3]) AS age_group_id)
    ON demographics.age_group = age_group_id
    GROUP BY age_group_id

In the first example, the protected `adh.google_ads_impressions table` is joined
with the `adh.age_group` table that does not contain user data on the
`age_group_id` column. The same public table `age_group_id` column appears in
the `GROUP BY` clause.

Similarly, in the second example the protected `adh.google_ads_impressions`
table is joined with the public table, which is provided explicitly as
`UNNEST([1, 2, 3])`. Notice that in both examples, the grouping key
`age_group_id` comes from the public table.

Multiple grouping items can be provided as well, for example:

    SELECT campaign_id, COUNT(*) FROM adh.google_ads_impressions
    RIGHT OUTER JOIN (SELECT DISTINCT campaign_id, customer_id FROM adh.google_ads_campaign)
    USING (campaign_id, customer_id)
    GROUP BY campaign_id, customer_id

    SELECT p.campaign_id, p.browser, COUNT(*) FROM adh.google_ads_impressions AS i
    RIGHT OUTER JOIN (
      SELECT DISTINCT * FROM UNNEST([1, 2]) AS campaign_id
      CROSS JOIN UNNEST(['Chrome', 'Other']) AS browser
    ) AS p
      ON i.campaign_id = p.campaign_id AND i.browser = p.browser
    GROUP BY campaign_id, browser;

To properly structure the `RIGHT OUTER JOIN` with a subquery defining the public
groups, ensure the `SELECT DISTINCT` statement within the subquery only includes
the fields used in the `ON` clause of the `JOIN`. These same fields must also be
present in the main query `GROUP BY` clause. For example, the following query
won't be eligible for public groups:

    SELECT age_group_id, COUNT(*) FROM adh.google_ads_impressions
    RIGHT OUTER JOIN (
      SELECT DISTINCT * -- Incorrect: May include fields not in the JOIN/GROUP BY
      FROM adh.age_group
    )
      ON demographics.age_group = age_group_id
    GROUP BY age_group_id

The absence of filtering in the public groups queries can be beneficial for
recurrently run queries, since the output is always returned for the same fixed
grouping keys' values. This can be particularly useful, for example, for
building periodical dashboards.

Caveats:

- If a public table provides a very large number of grouping key values, then you may get many rows with little or no data, and these rows will all be reported as having high noise impact. In this case, you should consider explicitly providing a smaller list of keys with just the values you are interested in.
- While public groups don't suppress rows, computed results can return as null due to implicit clamping when there is insufficient data to compute bounds. This typically occurs when all input values are zero or null, or when very few users contribute non-null values. To debug this, we recommend adding a `COUNT(DISTINCT user_id)` aggregation to verify how many users contribute to partitions. Note that `COUNT(DISTINCT user_id)` employs explicit clamping, making it useful for identifying sparse partitions.

### GROUP BY ROLLUP

`GROUP BY ROLLUP` extends the `GROUP BY` clause to include additional rows
representing subtotals and grand totals based on the hierarchy defined in the
grouping list. In Ads Data Hub, `GROUP BY ROLLUP` is available only when using
noise injection. The syntax follows the standard BigQuery
[specification](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_rollup).

#### Consistent Totals with Public Groups

When noise injection is used, noise is applied independently at each level of
aggregation. This means totals don't necessarily have perfect consistency, so
subtotals don't add up to grand totals (e.g., the sum of city counts might
not match the region total).

When using `GROUP BY ROLLUP` in combination with [public groups](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#public_groups)
Ads Data Hub will eliminate this inconsistency in results. This is possible
because no results are filtered out due to thresholding. This means, for
example, that the counts for cities will sum up to the count for their
respective region. (Note: For integer results, minor differences due to
floating-point rounding off errors are possible.)

#### Benefits of Using Public Groups with ROLLUP

1. Consistent aggregates: Ensures subtotals and grand totals align.
2. Improved accuracy: The consistency process enhances overall data accuracy. Higher levels in the rollup hierarchy (like country-level aggregates) are based on larger datasets and tend to have a smaller relative noise impact. This more stable information from higher levels is used to adjust and improve the quality of the noisier, more granular levels (like city-level aggregates).

It is highly recommended to use `GROUP BY ROLLUP` with public groups whenever
possible to benefit from these advantages.

#### Examples of ROLLUP with public groups:

`ROLLUP` with a single key:

Using a table:

    SELECT country_code, COUNT(1) AS count
    FROM adh.google_ads_impressions
    RIGHT OUTER JOIN
     (SELECT DISTINCT country_code FROM adh.city)
     ON location.country = country_code
    GROUP BY ROLLUP (country_code);

Using an inline list:

    SELECT country_code, COUNT(1) AS count
    FROM adh.google_ads_impressions
    RIGHT OUTER JOIN
     (SELECT DISTINCT * FROM UNNEST(['US', 'CA', 'CN', 'MX']) AS country_code)
     ON location.country = country_code
    GROUP BY ROLLUP (country_code);

Sample result:

| **country_code** | **count** |
|---|---|
| CN | 6155 |
| CA | 16439 |
| US | 256695 |
| MX | 10780 |
| null | 290067 |

The row with `NULL` for `country_code` represents the grand total. The sum of
the individual country counts, 290069, is very close to the grand total row,
290067, with the small difference arising from rounding.

`ROLLUP` with multiple grouping Keys:

    SELECT p.campaign_id, p.browser, COUNT(*) as count
    FROM adh.google_ads_impressions AS i
    RIGHT OUTER JOIN (
     SELECT DISTINCT * FROM UNNEST([1, 2]) AS campaign_id
     CROSS JOIN UNNEST(['Chrome', 'Other']) AS browser
    ) AS p
     ON i.campaign_id = p.campaign_id AND i.browser = p.browser
    GROUP BY ROLLUP (campaign_id, browser);

Sample result:

| **campaign_id** | **browser** | **count** | **notes** |
|---|---|---|---|
| 1 | Chrome | ... |   |
| 1 | Other | ... |   |
| 2 | Chrome | ... |   |
| 2 | Other | ... |   |
| 1 | null | ... | Subtotal for campaign_id = 1 |
| 2 | null | ... | Subtotal for campaign_id = 2 |
| null | null | ... | Grand total |

`ROLLUP` with groupable item sets:

To treat multiple columns as a single unit within the rollup hierarchy, enclose
them in parentheses, for example: `ROLLUP ((column1, column2), column3)`. This
rolls up by the pair `(campaign_id, browser)` as a single entity.

`GROUP BY ROLLUP ((campaign_id, browser))` will produce subtotals for:

- `(campaign_id, browser)` - most granular
- `()` - grand total

It won't create intermediate subtotals for just `(campaign_id)`.

    SELECT p.campaign_id, p.browser, COUNT(*) AS count
    FROM adh.google_ads_impressions AS i
    RIGHT OUTER JOIN (
     SELECT DISTINCT * FROM UNNEST([1, 2]) AS campaign_id
     CROSS JOIN UNNEST(['Chrome', 'Other']) AS browser
    ) AS p
     ON i.campaign_id = p.campaign_id AND i.browser = p.browser
    GROUP BY ROLLUP ((campaign_id, browser));

Sample result:

| **campaign_id** | **browser** | **count** | **notes** |
|---|---|---|---|
| 1 | Chrome | ... |   |
| 1 | Other | ... |   |
| 2 | Chrome | ... |   |
| 2 | Other | ... |   |
| null | null | ... | Grand total |

A caveat: Using `ROLLUP` with many hierarchical levels or a large number of
distinct values in the grouping keys can generate a large number of output rows.
This can potentially lead to errors due to Ads Data Hub's limits on
[repeated results](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#repeated_results).
It is recommended to keep the number of hierarchical levels and the cardinality
of the grouping keys relatively small.

*** ** * ** ***

## Supported query patterns

**Important** : Most of Ads Data Hub's standard [best practices](https://developers.google.com/ads-data-hub/marketers/guides/best-practices)
still apply to queries that use noise injection. In particular, we recommend
that you review the guidance on [repeatedly querying the same
data](https://developers.google.com/ads-data-hub/marketers/guides/best-practices#dont_query_the_same_data_more_than_necessary).

This section describes query patterns that are supported when running queries
using noise injection.

### User-level aggregates

Noise is only injected in aggregations that combine data across multiple users.
Aggregations that explicitly group or partition by `user_id` don't receive any
noise or restrict function usage. User-level aggregations that don't explicitly
group by `user_id`, for example `GROUP BY impression_id`, are treated as
cross-user aggregations, so noise is added.

Grouping by external_cookie is not enough. While external_cookie can be used to
join \*_match tables with customer-owned tables, any single-user aggregations
should explicitly group by user_id column, not just the external_cookie column.

Aggregate function example:

    WITH user_paths AS (
      # Grouping by user_id, no noise needed, all functions allowed
      SELECT user_id, STRING_AGG(campaign_id, ">" ORDER BY query_id.time_usec) AS path
      FROM adh.google_ads_impressions
      GROUP BY 1
    )
    # Noise applied here to num_users
    SELECT path, COUNT(*) AS num_users
    FROM user_paths
    GROUP BY 1;

Analytic function example:

    WITH events AS (
      # Partitioning by user_id, no noise needed, all functions allowed
      SELECT
        campaign_id,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY query_id.time_usec) AS index
      FROM adh.google_ads_impressions
    )
    # Noise applied here to first_impressions
    SELECT campaign_id, COUNT(*) AS first_impressions
    FROM events
    WHERE index = 1
    GROUP BY 1;

### Parallel aggregates

Each cross-user aggregation receives noise independently. You can run multiple
such aggregations in a single statement, combining results into one table using
a `JOIN` or `UNION`.

Example:

    WITH result_1 AS (
      # Noise applied here to num_impressions
      SELECT campaign_id, COUNT(*) AS num_impressions
      FROM adh.google_ads_impressions
      GROUP BY 1
    ), result_2 AS (
      # Noise applied here to num_clicks
      SELECT campaign_id, COUNT(*) AS num_clicks
      FROM adh.google_ads_creative_conversions
      GROUP BY 1
    )
    SELECT * FROM result_1 JOIN result_2 USING(campaign_id)

### Aggregated data joined with unaggregated data

> [!NOTE]
> **Note:** Each aggregation receives noise independently, so the total values may not match. In this case, summed values of `imps` for a particular campaign_id may not match the corresponding value of `campaign_imps`.

Since Ads Data Hub only supports analytic windows that partition by
`user_id`, it is a common workaround to aggregate these results separately and
self-join them before aggregating again.

Example:

    WITH campaign_totals AS (
      # Noise applied here to campaign_imps
      SELECT campaign_id, COUNT(*) AS campaign_imps
      FROM adh.google_ads_impressions
      GROUP BY 1
    )
    # Noise applied here to imps
    SELECT campaign_id, demographics, campaign_imps, COUNT(*) AS imps
    FROM adh.google_ads_impressions JOIN campaign_totals USING(campaign_id)
    GROUP BY 1,2,3

Noise mode [discourages](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#direct_reaggregation) reaggregating aggregate results,
such as `AVG(campaign_imps)`.

*** ** * ** ***

## Unsupported query patterns

This section describes query patterns that are not supported when running
queries using noise injection.

### Repeated results

In noise mode, Ads Data Hub limits how often you can repeat the same
aggregation. If you reach these limits, your queries may return NULL values in
the repeated cells. The following are examples of how this can occur.

**Query repetition** happens when the same query is run multiple times with the
same parameters or highly similar parameters, such as overlapping date ranges.
You can avoid this by using data that is already exported to your BigQuery
project.

Note that if two jobs are querying overlapping date ranges, they might produce
repetitions if performing the same computation on the same users. For example,
the following query, executed on overlapping date ranges, creates repetitions
because it partitions by date:

    SELECT DATE(TIMESTAMP_MICROS(event.event_time)) AS date,
    COUNT(*) AS cnt
    FROM adh.cm_dt_clicks
    GROUP BY 1

In this case, you should run the query on disjoint date segments.

Another example of a repetition happens when data is somewhat date independent.
The following query produces repetitions when executed on overlapping dates,
where both jobs cover the entire lifetime of a campaign:

    SELECT campaign_id, COUNT(*) AS cnt
    FROM adh.google_ads_impressions
    GROUP BY 1

In this case, you should run this query only when the underlying data may have
changed to get updated results.

**Aggregation repetition** happens when the same aggregation is repeated
multiple times within a query:

    SELECT COUNT(*) AS cnt1, COUNT(*) AS cnt2
    FROM table

In this case, you should remove one of the repetitions.

Note that even if the aggregations are syntactically different but compute the
same value, it would count as a repetition. In other words, if the values of
`condition1` and `condition2` are the same for all users with some value of
`key`, the following query would have a repetition:

    SELECT key, COUNTIF(condition1) AS cnt1, COUNTIF(condition2) AS cnt2
    FROM table
    GROUP BY key

If you have conditions that are very similar for some groups of users, you might
consider rewriting the query to have only one `COUNT`.

**Row duplication** happens when an Ads Data Hub table is joined with a
BigQuery table in a way that each row from the Ads Data Hub table matches
multiple rows in the BigQuery table. For example, the following query produces a
repetition if there are multiple rows with the same campaign ID in `bq_table`:

    SELECT r.campaign_id, COUNT(*) AS cnt
    FROM adh_table
    INNER JOIN bq_table ON l.campaign_id = r.campaign_id

In this case, you should restructure the query so that `bq_table` would have
only one row per join key value (`campaign_id`, in this case).

Note that unnesting an array from the Ads Data Hub table could produce the
same effect if most users have the same arrays of values:

    SELECT in_market_id, COUNT(*)
    FROM adh.dv360_youtube_impressions,
    UNNEST(in_market) AS in_market_id
    GROUP BY 1

As a general rule, you should limit computation of the same results to 10 or
fewer times. As you approach the threshold, Ads Data Hub will show warnings. If
the same results continue to be computed beyond the threshold, those repeated
cells may be redacted to NULL.

[Learn about other query best practices](https://developers.google.com/ads-data-hub/marketers/guides/best-practices).

#### About lookback windows

Some query patterns generate reports over a large timeframe, periodically
regenerating to include new results. These queries may need adjustments to work
in noise mode because if they recompute previous results, they will be blocked.
Instead, each job should only generate new results, then new results can be
combined with results from previous jobs for a full report.

For example, if you are creating a report of metrics by date, refreshed daily:

    SELECT
      campaign_id,
      DATE(TIMESTAMP_MICROS(query_id.time_usec), @time_zone) AS event_date,
      COUNT(*) AS impressions
    FROM adh.google_ads_impressions
    GROUP BY 1,2

You shouldn't run this with a large date range as this will recompute results of
previous days. Instead, you should run each job only the latest day, which has
new data, then combine with results from previous jobs. If you want to collect
all results in a single table across many jobs, consider using a
[`MERGE` statement](https://developers.google.com/ads-data-hub/marketers/guides/create-tables#create_and_update_bigquery_tables).

You can still recompute previous date ranges to refresh results (for example, to
account for late arriving data), but you should avoid recomputing any single
result too many times, as described earlier.

#### Handling redaction

Most query pipelines can avoid all redaction by limiting jobs to new results or
results that may have changed. However, if redaction is unavoidable, you can use
previous results in place of any redacted repetitions.

For example, suppose you have a query computing stats per campaign, with results
like this:

| **campaign_id** | **impressions** | **reach** |
|---|---|---|
| 100 | 1005 | 701 |
| 101 | 2020 | 1502 |
| 102 | 3300 | 2850 |

It may be that certain campaigns are inactive, so new jobs don't get new data to
process, and future jobs may have redacted cells like so:

| **campaign_id** | **impressions** | **reach** |
|---|---|---|
| 100 | 1010 | `NULL` |
| 101 | 2550 | 2106 |
| 102 | 3403 | 3040 |

> [!NOTE]
> **Note:** each cell is redacted independently using a non-deterministic process, so some values may appear while others don't. Regardless, you can fall back on previous results when values are NULL.

Here is an example upsert in Ads Data Hub to handle redacted values utilizing
the [`MERGE` statement](https://developers.google.com/ads-data-hub/marketers/guides/create-tables#create_and_update_bigquery_tables):

    CREATE TEMP TABLE new_data OPTIONS(privacy_checked_export=TRUE) AS
    SELECT campaign_id, COUNT(*) AS impressions, COUNT(DISTINCT user_id) AS reach
    FROM adh.google_ads_impressions
    GROUP BY 1;

    CREATE TABLE IF NOT EXISTS PROJECT_NAME.DATASET.TABLE_NAME
    (campaign_id INT64, impressions INT64, reach INT64);

    MERGE PROJECT_NAME.DATASET.TABLE_NAME a
    USING new_data b ON a.campaign_id = b.campaign_id
    WHEN MATCHED THEN
      UPDATE SET
        a.impressions = COALESCE(b.impressions, a.impressions),
        a.reach = COALESCE(b.reach, a.reach)
    WHEN NOT MATCHED THEN
      INSERT (campaign_id, impressions, reach)
        VALUES(campaign_id, impressions, reach);

### Direct reaggregation

Noise is applied to the first layer of cross-user aggregation in the query.
Queries with multiple layers of aggregation will combine noisy results, so final
aggregates may have much higher noise. These queries receive a warning on
validation:

    WITH layer_1 AS (
      # Noise applied here to partial_result
      SELECT campaign_id, demographics, location, COUNT(*) AS partial_result
      FROM adh.google_ads_impressions
      GROUP BY 1,2,3
      HAVING partial_result > 5
    )
    # Reaggregation of partial_result with no user-level data, will be rejected
    SELECT campaign_id, SUM(partial_result) AS final_result
    FROM layer_1
    GROUP BY 1

To get the best results from noise, compute all cross-user operations within a
single aggregation. For example, take a `SUM` of events rather than a `SUM` of
intermediate counts.

If multi-layer aggregation is unavoidable, you can resolve the warning by
exporting results directly from the first layer instead. To do this within a
single job without changing script results, create a temp table (or a table
exported to your BigQuery project) with the
`OPTIONS(privacy_checked_export=true)` syntax. For example:

    CREATE TEMP TABLE layer_1 OPTIONS(privacy_checked_export=true) AS (
      # Noise applied here to partial_result
      SELECT campaign_id, demographics, location, COUNT(*) AS partial_result
      FROM adh.google_ads_impressions
      GROUP BY 1,2,3
      HAVING partial_result > 5
    );
    # Reaggregation of privacy checked data, no noise needed
    SELECT campaign_id, SUM(partial_result) AS final_result
    FROM layer_1
    GROUP BY 1

[Learn more about temp tables](https://developers.google.com/ads-data-hub/marketers/guides/create-tables#temporary_tables).

If the first layer of aggregation is too granular for privacy checks, consider
rewriting the query with [user-level aggregates](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#user-level_aggregates). If
this is not possible, then this query is not supported in noise mode.

### Unjoined user IDs

Queries in noise mode must not combine data from separate users into a single
row, except when performing an aggregation with noise. As a result, joins of
unaggregated Ads Data Hub data should explicitly join on the `user_id`
column.

This query does not explicitly join on the `user_id` column, which results in a
validation warning:

    SELECT ...
    FROM adh.google_ads_impressions
    JOIN adh.google_ads_creative_conversions USING(impression_id)

Joins like this may not behave as expected because only rows with the same
`user_id` value will match. This can be fixed by adjusting the `USING` clause to
explicitly include `user_id` -- for example, `USING(impression_id, user_id)`.

Note that this limitation applies only to joins between Ads Data Hub
tables (with the exception of dimension tables). It does not apply to
customer-owned tables. For example, the following is allowed:

    SELECT ...
    FROM adh.google_ads_impressions
    JOIN bigquery_project.dataset.table USING(any_column)

### Ads Data Hub-BigQuery right joins

Outer joins with customer-owned data can lead to rows with missing user
identifiers, which prevents noise from working well.

Both of these queries result in validation warnings because they allow for
unmatched rows with missing user identifiers on the Ads Data Hub side:

    SELECT ...
    FROM adh.google_ads_impressions
    RIGHT JOIN bigquery_project.dataset.table USING(column)

    SELECT ...
    FROM bigquery_project.dataset.table
    LEFT JOIN adh.google_ads_impressions USING(column)

Note that either join would work if the order of the tables was reversed. There
is also an exception for RDID tables that join directly on `device_id_md5`. For
example, the following query will work with no warnings:

    SELECT ...
    FROM bigquery_project.dataset.table
    LEFT JOIN adh.google_ads_impressions_rdid USING(device_id_md5)

### Filtered Row Summary

The filtered row summary spec is not supported in noise mode. This feature is
most often unnecessary with noise due to lower filtering rates and the lack of
filtering from difference checks.

If you observe significant data filtering in a noise result, then increase the
aggregated data. You may perform a parallel aggregation over the full dataset to
compare an estimate of the total using either [`ROLLUP`](https://developers.google.com/ads-data-hub/marketers/guides/noise-injection#group_by_rollup) or a
separate aggregation, for example:

    SELECT campaign_name, COUNT(*)
    FROM data
    GROUP BY 1
    UNION ALL
    SELECT 'Total', COUNT(*)
    FROM data
    GROUP BY 1

Note that the total count is independently noised and total values may not add
up, but the total count is often more accurate than taking the sum of noised
rows.

### Cross-mode created tables

Unexported tables in Ads Data Hub can only be used with the same privacy
mode where they were created. You can't create a table in normal aggregation
mode and use it in noise mode, or the other way around (unless that table is
exported to BigQuery first).