# Write and run queries

## Create a query

> [!NOTE]
> **Note:** Not all users can create and edit queries. Learn more about [access
> control](https://developers.google.com/ads-data-hub/guides/assign-access-by-role) in Ads Data Hub.

1. Navigate to the **Queries** tab in [Ads Data Hub](https://%0Aadsdatahub.google.com/#).
2. Click **+ Create query** . The **Analysis query templates** page opens.
3. Optional: Preview template SQL by clicking **Expand**.
4. Select the template you would like to use by clicking **Use template** , or click **Blank** to start from scratch.
5. Enter a name for your report.
6. Write or edit the query using [BigQuery compatible](https://cloud.google.com/bigquery/docs/reference/standard-sql/) SQL. You can find available tables and fields and insert them into your SQL from the **Google tables** tab.
7. Optional: Configure [parameters](https://developers.google.com/ads-data-hub/guides/run-queries#parameters).
8. Optional: Configure the [filtered row summary](https://developers.google.com/ads-data-hub/guides/run-queries#filtered_row_summary).
9. Click **Save**.

### Parameters

Parameters make queries more flexible. For example, you may want to run
the same query over different campaigns. Rather than making duplicate
queries or hardcoding the campaign IDs before each execution, you can
configure a parameter that accepts one or more campaign IDs to be entered from
the Run card when you run a query. Using parameters keeps your code clean,
reduces your chances of introducing errors through editing, and makes it
possible for your query to be reused without editing. Parameters are
scoped to the query where they are created, so you can reuse a parameter
name in another query.

#### Parameter types

The following types of parameters are permitted:

- `int64`
- `float64`
- `bool`
- `string`
- `date`
- `timestamp`
- `array`, of any of the permitted types

#### Create a parameter

1. Open a saved query, or [create a new one](https://developers.google.com/ads-data-hub/guides/run-queries#create_a_query).
2. Click **Properties** \> **Parameters** \> **Add parameter**.
3. Enter a name in the **Name** field. You'll use this name to reference the parameter in the query text.
4. Use the drop-down menu to select the parameter type.
   - If you select array, an additional drop-down menu appears. Select the array type in the drop-down menu.
5. Optional: Repeat steps 2-4 until you have added all the parameters you want.
6. Click Save

#### Use a parameter

In the query text, use the standard SQL parameter format, which is to
precede the parameter name with `@`. In the following example, the query
contains a parameter called `@campaign_ids`:

      /* Parameters:
      * @campaign_ids (ARRAY of INT64): A list of campaign IDs
      *   to conduct analysis on */

      WITH user_reach AS (
      SELECT 
        user_id,
        count(*) AS num_views
      FROM 
        adh.google_ads_impressions
      WHERE 
        campaign_id in UNNEST(@campaign_ids)
      GROUP BY 
        user_id
      )
      SELECT
      COUNT(*) AS unique_users,
      COUNTIF(num_views = 1) AS one_view,
      COUNTIF(num_views > 1 AND num_views <= 5) AS less_or_equal_five_views,
      COUNTIF(num_views > 5) AS more_than_five_views
      FROM 
        user_reach

When you [run the query](https://developers.google.com/ads-data-hub/guides/run-queries#run_a_query), the **New job** dialog will contain a
**Parameters** field. Enter values for each parameter you've defined.

#### Remove a parameter

1. Open the query that has the parameter you want to delete.
2. Open the **Properties** tab.
3. In the **Parameters** card, Click delete **Delete** next to the parameter you want to delete.
4. Click Save.

#### Reserved parameters

The following table lists parameters reserved by Ads Data Hub. You can use these
parameters in your queries, but you cannot create a parameter with the
same name as a reserved parameter.

|---|---|
| `start_date` | `date` The start date (inclusive) for the query job. A user-defined parameter with this name will be ignored and replaced with the values set in the **New job** dialog. |
| `end_date` | `date` The end date (inclusive) for the query job. A user-defined parameter with this name will be ignored and replaced with the values set in the **New job** dialog. |
| `time_zone` | `string` The time zone for the query job. A user-defined parameter with this name will be ignored and replaced with the values set in the **New job** dialog. > [!TIP] > **Tip:** To prevent discrepancies when using timezones in your query, such as in `WHERE` clauses, you can use `time_zone` to match the timezone set in **New job**. It's also important to use the same timezone as the data source. |
| `params` | `string` Namespace for naming exported tables. Use in the following format to create a placeholder in your query, then provide the table name when you run the report: params.TABLE_NAME (do not use a preceding @ used by other parameters). For more information and examples using this parameter, see [Export multiple tables using a single query](https://developers.google.com/ads-data-hub/guides/create-tables#export_multiple_tables). |

### Filtered row summary

[Filtered row summaries](https://developers.google.com/ads-data-hub/guides/filtered-row-summary) tally data that was filtered due to privacy checks. Data from filtered rows is summed and added to a catch-all row. While the filtered data can't be further analyzed, it provides a summary of how much data was filtered from the results.

> [!NOTE]
> **Note:** Aggregation requirements apply to filtered row summaries themselves. If the resulting filtered row summary falls below aggregation requirements, the filtered row summary itself will be filtered.

## Edit a query

1. Navigate to the **Queries** tab in [Ads Data Hub](https://%0Aadsdatahub.google.com/#).
2. Click the name of the query you want to edit.
3. Edit the query.
4. Click **Save**.

## Run a query

> [!IMPORTANT]
> **Key Point:** You should write and test your queries using the [sandbox](https://developers.google.com/%0Aads-data-hub/guides/sandbox) before running them on real data. [Learn more
> best practices for writing queries in Ads Data Hub](https://developers.google.com/ads-data-hub/guides/best-practices)

Before running a query, ensure that you've given the service account
`dataEditor` permission to the dataset that will contain the output of your
joins.
[Learn more about access control in BigQuery](https://cloud.google.com/bigquery/docs/access-control#bigquery.dataEditor).

1. Navigate to the **Queries** tab in Ads Data Hub.
2. Click the name of the query you want to run.
3. Click **Run** .
   - The expected number of bytes that the query will use shows at the top of the page.
4. Select an ID in the **Ads data from** field. This should be the account ID associated with the ads data you want to query. (If you would like to query sandbox data, select **ADH Sandbox Customer**.)
5. If using a match table, select the match table in the **Match table from** field.
6. Select the privacy mode. [Learn more about privacy
   modes](https://developers.google.com/ads-data-hub/marketers/guides/privacy-checks#privacy_modes).
7. Specify where your query results should be saved in the **Destination
   table** field.
8. Enter start and end dates to determine the date range of your query.
9. Enter a time zone. This should match the time zone of the buying door.
10. Optional: Enter values for any parameters.
11. Click **Run**.

## View your results

After your query finishes running, you can preview the results on the "Jobs"
page. You can also explore the data in BigQuery, Sheets, or Data Studio.

### Preview

Click **Preview** within the finished job. The first 20 results will appear
below the job.

### BigQuery

Your results are exported to BigQuery by default. Click **View table** to open
BigQuery in a new window.

### Sheets and Data Studio

Open the **Explore** dropdown menu and select **Explore with Sheets** or
**Explore with Data Studio** from the options. This opens the data in a new
window.