View query history audits

Query history audits allow you to generate a report of all jobs run using your Ads Data Hub account. This allows you to answer questions relating to who accessed your data and when they did it.

Query history audits are written as BigQuery tables containing log entries for all queries run using your Ads Data Hub account. To view query history audits for your account, you need to first generate the report via an API. Each audit log contains 1 day’s worth of data. You can generate an audit log for any day within the past 30 days.

Query history audits are only available to superusers. Learn more about role-based access

Query history audit format

Each query history audit uses the following schema:

Field name Description
customer_id The Ads Data Hub customer ID
ads_customer_id The ID of the sub-account, if used (will be identical to customer_id otherwise)
match_table_customer_id The ID of the account containing the match table, if used (will be identical to customer_id otherwise)
user_email Email address of the user who ran the query
query_start_time The time the query began running
query_end_time The time the query finished running
query_type Differentiates between analysis queries and audience queries
query_resource_id The ID associated with the query
query_text The query’s SQL
query_parameters
query_parameters.name The name of the query’s parameter
query_parameters.value The value passed via the query’s parameter row_merge_summary
row_merge_summary.column_name The name of column
row_merge_summary.merge_type The type of row merge summary
row_merge_summary.constant_value The value of the constant set (will be null if no constant is used)
destination_table The location (in BigQuery) that the query was written to

Accessing query history audits

In order to access the query history audits, you’ll need to call the API. Find sample code for calling the API below, or view the reference documentation and write your own query.

The results of the API request will be written to the BigQuery dataset that you specify in the body of the API request.


"""This sample shows how to create a query history audit.

For the program to execute successfully, ensure that you run it using Python 3.
"""

from __future__ import print_function
from json import dumps
from google_auth_oauthlib import flow
from googleapiclient.discovery import build

appflow = flow.InstalledAppFlow.from_client_secrets_file(
  # Replace client_secrets.json with your own client secret file.
  'client_secrets.json',
  scopes=['https://www.googleapis.com/auth/adsdatahub'])
appflow.run_local_server()
credentials = appflow.credentials
developer_key = input('Developer key: ').strip()
service = build('adsdatahub', 'v1', credentials=credentials,
                developerKey=developer_key)

def pprint(x):
  print(dumps(x, sort_keys=True, indent=4))

customer_id = input('Customer ID (e.g. "customers/123"): ').strip()
bq_project = input('Destination BigQuery project ID (e.g. "your-project"): ').strip()
dataset_id = input('Destination BigQuery dataset (e.g. "your-dataset"): ').strip()
start = input('The start date for your query history audit. Formatted as "mm/dd/yyyy": ').strip().split('/')
end = input('The end date for your query history audit. Should be 1 day later than start_date. Formatted as "mm/dd/yyyy": ').strip().split('/')

choice = input("Do you want to enter a timezone? Defaults to UTC otherwise. (y/n) ")

if choice.lower() == 'y':
  timezone = input("Timezone (e.g. 'UTC'): ")
else:
  timezone = 'UTC'

body = {
  'project_id': bq_project,
  'dataset': dataset_id,
  'start_date': {
      'year': start[2],
      'day': start[1],
      'month': start[0]
  },
  'end_date': {
      'year': end[2],
      'day': end[1],
      'month': end[0]
  },
  'time_zone': timezone
}

pprint(service.customers().exportJobHistory(customer=customer_id, body=body).execute())