BigQuery basics

Datasets
are a grouping mechanism that controls access to zero or more tables.

Tables
are a standard, two-dimensional table with individual records organized in rows,
and a data type assigned to each column (also called a field).

Jobs
used to start all potentially long-running actions, for instance: queries, table import,
and export requests.

BigQuery setup

Sign up for BigQuery using
Google Cloud Platform Console. If you
already have an existing project, activate BigQuery by enabling the
BiqQuery API on the APIs & Services screen. Note
the project ID, as you will need this in your Google Ads script.

Enable BigQuery in your Google Ads script by clicking on the Advanced APIs
button and ticking the checkbox next to BigQuery. Click Save.

How it works

The script starts off by creating a BigQuery
Dataset.
Afterwards, the script creates a BigQuery
Table
for each configured report. Finally, each report is processed. Processing a report
consists of: retrieving a report as a csv from Google Ads, converting it to a
Blob,
and creating an insert
job to load data into BigQuery. Afterwards, the script polls the status of each insert job until
all jobs are DONE. An email is sent to notify recipients upon completion.

Scheduling

The reports rely on previous day's statistics. Schedule it
Daily, 3am or later to guarantee accuracy, since
Google Ads statistics may be
up to 3 hours delayed.

Setup

Create a new script with the source code below.

Update BIGQUERY_PROJECT_ID and BIGQUERY_DATASET_ID
to link to your BigQuery project and dataset. If you do not have an existing dataset,
use any id.

Set TRUNCATE_EXISTING_DATASET and TRUNCATE_EXISTING_TABLES. If
set to true, any exisiting data will be deleted. If set to false, data will be
appended to existing tables. For any schema changes, you do not need to truncate the
dataset, but should truncate tables.

Set WRITE_DATA_TO_DRIVE to true to back up your report data to
Google Drive. Specify
your drive folder name by setting DRIVE_FOLDER.

Update the ACCOUNTS in case you need to run report only on
a subset of accounts under your manager account.

Configure your reports by specifying a NAME, CONDITIONS,
and FIELDS for each report. The report
NAME is also the table name in BigQuery. Whereas the
FIELDS are used to infer the table schema by setting the column name and
type. Any
type conversion should be handled in retrieveAdwordsReport like stripping
'%' off FLOAT or converting a date to YYYY-MM-DD HH:MM:SS.