Write and run queries

Create a query

1. Navigate to the Queriestab in Ads Data Hub .
2. Click + Create query. The Analysis query templatespage opens.
3. Optional: Preview template SQL by clicking expand_more Expand.
4. Select the template you would like to use by clicking Use template, or click Blankto 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 tablestab.
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 Namefield. 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 jobdialog will contain a Parametersfield. 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 Propertiestab.
3. In the Parameterscard, Click delete Deletenext 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.

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 Queriestab 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 Queriestab in Ads Data Hub.
2. Click the name of the query you want to run.
3. Click play_arrow 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 fromfield. 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 fromfield.
6. Select the privacy mode. Learn more about privacy modes .
7. Specify where your query results should be saved in the Destination tablefield.
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 Previewwithin the finished job. The first 20 results will appear below the job.

BigQuery

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

Sheets and Data Studio

Open the Exploredropdown menu and select Explore with Sheetsor Explore with Data Studiofrom the options. This opens the data in a new window.