Write and run queries

Test queries

You should write and test your queries using the sandbox before running them on real data.

Create and edit queries

Create a query

  1. Navigate to the My Queries tab in Ads Data Hub.
  2. Click + Create New Query.
    1. Give your query a name.
    2. Write BigQuery compatible SQL.
  3. Optionally, add parameters to your query.
  4. Optionally, add a row merge configuration to your query.

Edit a query

  1. Navigate to the My Queries tab in Ads Data Hub.
  2. Click the Details button in the row containing the query you want to edit.
  3. Edit the query.
  4. Click Save.

Row merge configuration

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 row merge 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 sum numeric data click + New Merge Column then Sum from the dropdown.
    • The numeric data from the column you sum will be retained from any dropped rows and aggregated into a single row. The remaining columns in your aggregate row will show null.
  • To set the data value of a column to a constant click + New Merge Column then Constant from the dropdown. Your constant must be a valid data type for your chosen row.
    • The data from the column will be set to your constant for any dropped rows and aggregated into a single row. The remaining columns in your aggregate row will show null. This is a useful way to merge string values, or to label a merge row.

Run queries with Ads Data hub

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 My Jobs tab.
  2. Select Analysis from the Query type dropdown.
  3. Select the query to run in the Query name dropdown.
  4. Select the customer in the Ads data from dropdown.
  5. Select the table where your query results should be saved.
  6. Enter start and end dates to determine the date range of your query.
  7. Click Start.

Best practices

  • Avoid querying large amounts of data or querying over large date ranges. Instead, query over small date ranges, or otherwise restrict the set of data being processed, appending the results as you go. Querying large amounts of data, or over large date ranges, will increase memory usage, and may cause your query to fail.
    • Avoid referencing many tables in a single query, as this uses large amounts of memory and can cause your query to fail.
  • Avoid reaggregation. You should attempt to keep rows at the event or user level while processing and then combine with a single aggregation.
  • Avoid joining fully aggregated results in Ads Data Hub. If you join a privacy-unsafe row with a privacy-safe row, the result will be dropped.
  • Join your data so as to have as many results as possible.
    • Use a LEFT JOIN instead of an INNER JOIN to join clicks or conversions to impressions. The LEFT JOIN will return more impressions.
  • Don't run very similar queries in quick succession with only small changes to the SQL. Instead, develop universal queries that pass privacy checks and take custom parameters.
    • Small differences in the aggregation of your results from query to query can lead Ads Data Hub to drop rows or the entire results table.
    • Utilize the sandbox when you're experimenting with different ways to aggregate data.
  • When subtracting one group of users from another, don't LEFT JOIN on a result that is filtered using IS NULL. Ads Data Hub filters rows containing 0 users, so the query won't return any results. Instead, subtract one aggregate from the other, like in this example.