Create and run reports

This guide teaches you how to create and run reports, as well as how to view your results.

Key Term: A query is the SQL text that is the foundation of a report. A report is made up of multiple components including the query, input and output settings, schedule, and execution details.

Create a report

Analysts and superusers can create and edit reports. Learn more about access control in Ads Data Hub.

Important: To prevent losing changes, click Save after making any updates to a report, including schedules.

1. In Ads Data Hub, open the Reports page.
2. Click + Create report. The Create a new report page opens.
   • You can filter templates by searching, selecting product icons, and choosing drop-down options.
3. Choose a template and click Use template, or click SQL to start from scratch.
   • Preview template details and SQL by clicking expand_content Expand.
4. To edit the report, click Edit query. Use the query editor to:
   • Customize the report using BigQuery-compatible SQL.
   • Configure parameters in the Properties tab.
   • View a list of available tables and fields in the Google tables tab.
   • View a list of imported 1P tables and their schemas in the 1P table tab.
   • Run the SQL query by clicking Run. Note that executions initiated from the SQL query editor don't contribute to a report's execution history, and won't be present in the Recent activity section of the Home page.
   • Click Done to confirm the changes to the query and return to the report setup page.
5. Enter a name for your report.
6. If applicable, set the parameter values in the Setup card.
7. Configure the input and output for jobs run on this report:
   • Select a data source in the Ads data from field. This should be the Ads Data Hub account ID associated with the ads data you want to query. (If you would like to query sandbox data, select ADH Sandbox Customer.)
   • If using a match table, select the match table in the Match table account field.
   • Select a date range and time zone for the report. The report time zone should match the time zone of the linked ads account, such as a Google Ads account. Note: Custom date range for query testing is only supported when you run the query from the SQL editor screen.
   • Optional: Change your privacy settings and configure the filtered row summary.
   • Choose the BigQuery project, dataset, and table where your report results should be saved.
8. Optional: Set your report to run on a schedule.
9. Click Save.

---

Parameters

Parameters let you specify how a given report should be run. When using SQL to create a report (or when editing the SQL from a report template), parameters allow you to reuse the same SQL code across reports, and set different parameter values on each copy of the report. For example, you can run two reports that share the same SQL query or template on different sets of campaign IDs by configuring a parameter and setting its value in the Setup section of the report details page. Using parameters keeps your SQL code clean, reduces your chances of introducing errors through editing, and makes it possible to reuse the same SQL query across reports without editing values in the SQL itself.

In the following example, the report contains a parameter called campaign_ids, which accepts an array of int64 campaign IDs:

  /* Parameters:
   *   @campaign_ids (ARRAY of INT64):
   *   A list of campaign IDs to analyze
   */

  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

Parameter types

The following types of parameters are permitted:

• int64
• float64
• bool
• string
• date
• timestamp
• array (of any of the permitted types)

Add a parameter to a report

1. Open a saved report, or create a new one.
2. Click Edit query.
3. Under Properties, click Add parameter.
4. Enter a name in the Name field. You'll use this name to reference the parameter in the report text.
5. Use the drop-down menu to select the parameter type.
   • If you choose the array type, an additional drop-down menu appears. Select the array type in this drop-down menu.
6. In the SQL query, add the parameter using the standard SQL parameter format: @PARAMETER_NAME. See the example that follows these steps.
7. Click Done, then click Save.

Use a parameter

1. Open a report that has parameters configured.
2. In the Setup pane, enter values for each parameter you've defined
3. Click Save to preserve the parameter values.

Remove a parameter

1. Open a report that has parameters configured.
2. Click Edit query to open the SQL editor.
3. Under Properties, click delete Delete next to the parameter you want to delete.
4. Click Done, then click Save.

Reserved parameters

The following table lists parameters reserved by Ads Data Hub. You can use these parameters in your reports, but you cannot create a parameter with the same name as a reserved parameter.

start_date	date The start date (inclusive) for the report 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 report 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 report job. A user-defined parameter with this name will be ignored and replaced with the values set in the New job dialog. Tip: To prevent discrepancies when using timezones in your report, such as in WHERE clauses, you can use time_zone to match the timezone set in New job. It's also important to use the same timezone as the data source.

---

Filtered row summary

Note: Filtered row summaries are unnecessary when you use noise mode. Learn about the benefits of using noise instead of difference checks.

Filtered row summaries tally data that was filtered due to difference 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. For queries that use the difference checks privacy mode, Ads Data Hub automatically enables and configures a filtered row summary. As you edit the query, Ads Data Hub will automatically adjust the configuration, based on the selected columns. To learn more about filtered row summaries, or to configure one manually, see Filtered row summary.

---

Edit a report

1. In Ads Data Hub, open the Reports page.
2. Click the name of the report you want to edit.
3. Make changes to the report. For example, edit the SQL, manage parameter values, or set a schedule.
4. Click Save.

---

Run a report

Key Point: You can test SQL queries using the sandbox before running them on real data. Learn more best practices for writing queries in Ads Data Hub

Before running a report, 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. In Ads Data Hub, open the Reports page.
2. Click the name of the report you want to run.
3. Click Run.

Run a report on a schedule

1. In Ads Data Hub, open the Reports page.
2. Click the name of the report you want to set a schedule for.
   • To set a schedule for a new report, create the report, and then click Save.
3. Click + Set schedule.
4. In the Schedule pane, click the Schedule this report to run with below details toggle to the on position.
5. Under Frequency, select an interval and time of day, and then enter a time zone.
   • To use a cron expression instead, click the Set schedule using a cron expression toggle to the on position, and then enter the expression in the box.
6. Click Done.
7. Click Save to commit your changes to the report.

To show scheduled jobs on the Home page, select Anyone from the Owner filter list.

---

View your results

After your report finishes running, you can preview the results in the report details page. To view the results at a later time, open the report from the Home 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 drop-down menu and select Explore with Sheets or Explore with Data Studio from the options. This opens the data in a new window.