Submit queries in Ads Data hub

Test queries

You should first write and test your query in the adh project of the Ads Data Hub sandbox for advertisers or the Ads Data Hub sandbox for vendors. If it is error free, you are ready to run your query against real data.

Create, and edit queries

  1. Go to the Ads Data Hub interface.
  2. Navigate to My Queries.
  3. Click + Create New Query.
    1. Give your query a name.
    2. Write BigQuery compatible SQL arguments.
  4. Click on a listed query to edit or view its arguments.
    1. Reviewing a query's arguments is the best way to determine its functionality.
    2. Some queries are system default and not editable.

Row merge configuration

Data that doesn't meet Ads Data Hub privacy restrictions is dropped from the results of a given query. You can configure a row merge to sum usable data columns from dropped rows into a single row. This can help prevent discrepancies in your data totals. For example, a total impression or total click count.

  • To sum numeric data click + New Merge Column then Sum from the drop down.
    • The numeric data from the column you sum will be retained from any dropped rows and aggregated into a single row.
  • To set the data value of a column to a constant click + New Merge Column then Constant from the drop down. 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. 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 My Jobs.
  2. Choose your customer name.
  3. Choose the query to run.
    1. To view each query's arguments navigate to My Queries, and then click on a query to view more details.
  4. Select the table where your query results should be saved.
  5. Enter start and end dates to determine the date range of your query.

Query best practices

  • Referencing many ADH tables in a single query will increase memory usage, and may cause the query to fail.
  • Querying large amounts of data, or over large date ranges, will increase memory usage, and may cause the query to fail.
  • Don’t join ADH tables back to themselves. For example, using two WITH clauses to join the same ADH table to itself. Use an aggregate function like COUNTIF or a conditional expression like SUM (IF()) instead.
  • Due to ADH privacy checks, more results are better than fewer. For example, use a Left Join instead of an Inner Join to join clicks or conversions to impressions. The Left Join will return more impressions results.