Write and run queries

Create a query

  1. Navigate to the Queries tab in Ads Data Hub.
  2. Click + Create query.
  3. Select Choose template or Start from scratch.
    1. Choose template:
      1. Pick an applicable template.
        Preview a template's SQL by clicking the down arrow next to it.
    2. Start from scratch:
      1. Write BigQuery compatible SQL.
  4. Give your query a name.
  5. Optionally, add parameters to your query.
  6. Optionally, add a filtered row summary to your query.
  7. Click Save.

Filtered row summary

Rows that aren't aggregated enough 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 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.

To add a filtered row summary to a query:

  1. Identify the column that you would like to aggregate.
  2. Click + Add rule under "Filtered row summary" in the right-hand-side of the query editor.
  3. Enter the name of the column you would like to aggregate in the Column name field.
  4. Select Sum from the dropdown. If the sum falls below aggregation thresholds, the filtered row summary will be dropped.
  5. Optionally, add an identifier for the filtered row summary using a constant. Your constant must be a valid data type for your chosen row.
    1. Enter the name of the column in the Column name field.
    2. Select Constant from the "Datatype" dropdown menu.
    3. Enter an identifier for the row in the Specify constant field.

Example

The following example specifies two filtered row summaries:

  • A sum that aggregates filtered data from the Users column.
  • A constant (used to label the filtered row summary), set to "Filtered row summary" in the Geo column.
Users Geo
51 Filtered row summary
223 US-CA
124 US-WA

Edit a query

  1. Navigate to the Queries tab in Ads Data Hub.
  2. Click the name of the query you want to edit.
  3. Edit the query.
  4. Click Save.

Run a query

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

  1. Navigate to the Queries tab in Ads Data Hub.
  2. Click the name of the query you want to run.
  3. Click the play icon in the upper left corner of the editor.
  4. Select a customer in the Ads data from dropdown. (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 dropdown.
  6. Specify where your query results should be saved in the Destination table field.
  7. Enter start and end dates to determine the date range of your query.
  8. Click Run.

View your results

After your query finishes running you can view the results and explore the data in BigQuery, Google Sheets, or Data Studio.

BigQuery

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

Google Sheets and Data Studio

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