Write and run queries

Create a query

  1. Navigate to the Queries tab in Ads Data Hub.
  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 SQL. You can find available tables and fields and insert them into your SQL from the Google tables tab.
  7. Optional: Configure parameters.
  8. Optional: Configure the 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.
  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, 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.

Filtered row summary

Filtered row summaries 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.

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 BigQuery.

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