You should write and test your queries using the sandbox before running them on real data.
Create, and edit queries
- Go to the Ads Data Hub interface.
- Navigate to My Queries.
- Click + Create New Query.
- Give your query a name.
- Write BigQuery compatible SQL arguments.
- Click on a listed query to edit or view its arguments.
- Reviewing a query's arguments is the best way to determine its functionality.
- 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.
- Navigate to My Jobs.
- Choose your customer name.
- Choose the query to run.
- To view each query's arguments navigate to My Queries, and then click on a query to view more details.
- Select the table where your query results should be saved.
- 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.