Google Ads to BigQuery template

The Google Ads to BigQuery template is a batch pipeline that reads Google Ads reports and writes to BigQuery.

Pipeline requirements

  • The Google Ads account IDs to be used.
  • The Google Ads Query Language query to obtain the data.
  • OAuth credentials for the Google Ads API.

Template parameters

Parameter Description
customerIds A list of Google Ads account IDs to use to execute the query. For example: 12345,67890.
query The query to use to get the data. See Google Ads Query Language. For example: SELECT campaign.id, campaign.name FROM campaign.
qpsPerWorker The rate of query requests per second (QPS) to submit to Google Ads. Divide the desired per pipeline QPS by the maximum number of workers. Avoid exceeding per-account or developer token limits. See Rate Limits.
googleAdsClientId The OAuth 2.0 client ID that identifies the application. See Create a client ID and client secret.
googleAdsClientSecret The OAuth 2.0 client secret that corresponds to the specified client ID. See Create a client ID and client secret.
googleAdsRefreshToken The OAuth 2.0 refresh token to use to connect to the Google Ads API. See 2-Step Verification.
googleAdsDeveloperToken The Google Ads developer token to use to connect to the Google Ads API. See Obtain a developer token.
outputTableSpec The BigQuery table location to write the output to. The name should be in the format <project>:<dataset>.<table_name>. The table's schema must match input objects.
loginCustomerId Optional: A Google Ads manager account ID to use to access the account IDs. For example: 12345.
bigQueryTableSchemaPath Optional: The Cloud Storage path to the BigQuery schema JSON file. If this value isn't set, then the schema is inferred from the Proto schema. For example: gs://MyBucket/bq_schema.json.
writeDisposition Optional: The BigQuery WriteDisposition value. For example, WRITE_APPEND, WRITE_EMPTY, or WRITE_TRUNCATE. Defaults to: WRITE_APPEND.
createDisposition Optional: The BigQuery CreateDisposition value. For example, CREATE_IF_NEEDED, CREATE_NEVER. Defaults to: CREATE_IF_NEEDED.

Run the template

Console

  1. Go to the Dataflow Create job from template page.
  2. Go to Create job from template
  3. In the Job name field, enter a unique job name.
  4. Optional: For Regional endpoint, select a value from the drop-down menu. The default regional endpoint is us-central1.

    For a list of regions where you can run a Dataflow job, see Dataflow locations.

  5. From the Dataflow template drop-down menu, select the Google Ads to BigQuery template.
  6. In the provided parameter fields, enter your parameter values.
  7. Click Run job.

gcloud

In your shell or terminal, run the template:

gcloud dataflow flex-template run JOB_NAME \
    --template-file-gcs-location=gs://dataflow-templates-REGION_NAME/VERSION/flex/Google_Ads_to_BigQuery \
    --project=PROJECT_ID \
    --region=REGION_NAME \
    --parameters \
       customerIds=CUSTOMER_IDS,\
       query=QUERY,\
       qpsPerWorker=QPS_PER_WORKER,\
       googleAdsClientId=GOOGLE_ADS_CLIENT_ID,\
       googleAdsClientSecret=GOOGLE_ADS_CLIENT_SECRET,\
       googleAdsRefreshToken=GOOGLE_ADS_REFRESH_TOKEN,\
       googleAdsDeveloperToken=GOOGLE_ADS_DEVELOPER_TOKEN,\
       outputTableSpec=OUTPUT_TABLE_SPEC,\

Replace the following:

  • JOB_NAME: a unique job name of your choice
  • VERSION: the version of the template that you want to use

    You can use the following values:

  • REGION_NAME: the regional endpoint where you want to deploy your Dataflow job—for example, us-central1
  • CUSTOMER_IDS: the Google Ads account IDs
  • QUERY: the Google Ads Query Language query
  • QPS_PER_WORKER: the required Google Ads request rate per worker
  • GOOGLE_ADS_CLIENT_ID: the OAuth 2.0 client ID that identifies the application
  • GOOGLE_ADS_CLIENT_SECRET: the OAuth 2.0 client secret that corresponds with the specified client ID
  • GOOGLE_ADS_REFRESH_TOKEN: the OAuth 2.0 refresh token to use to connect to the Google Ads API
  • GOOGLE_ADS_DEVELOPER_TOKEN: the Google Ads developer token to use to connect to the Google Ads API
  • OUTPUT_TABLE_SPEC: the BigQuery output table

API

To run the template using the REST API, send an HTTP POST request. For more information on the API and its authorization scopes, see projects.templates.launch.

POST https://dataflow.googleapis.com/v1b3/projects/PROJECT_ID/locations/LOCATION/flexTemplates:launch
{
   "launchParameter": {
     "jobName": "JOB_NAME",
     "parameters": {
       "customerIds": "CUSTOMER_IDS",
       "query": "QUERY",
       "qpsPerWorker": "QPS_PER_WORKER",
       "googleAdsClientId": "GOOGLE_ADS_CLIENT_ID",
       "googleAdsClientSecret": "GOOGLE_ADS_CLIENT_SECRET",
       "googleAdsRefreshToken": "GOOGLE_ADS_REFRESH_TOKEN",
       "googleAdsDeveloperToken": "GOOGLE_ADS_DEVELOPER_TOKEN",
       "outputTableSpec": "OUTPUT_TABLE_SPEC",
     },
     "containerSpecGcsPath": "gs://dataflow-templates-LOCATION/VERSION/flex/Google_Ads_to_BigQuery",
     "environment": { "maxWorkers": "10" }
  }
}

Replace the following:

  • PROJECT_ID: the Google Cloud project ID where you want to run the Dataflow job
  • JOB_NAME: a unique job name of your choice
  • VERSION: the version of the template that you want to use

    You can use the following values:

  • LOCATION: the regional endpoint where you want to deploy your Dataflow job—for example, us-central1
  • CUSTOMER_IDS: the Google Ads account IDs
  • QUERY: the Google Ads Query Language query
  • QPS_PER_WORKER: the required Google Ads request rate per worker
  • GOOGLE_ADS_CLIENT_ID: the OAuth 2.0 client ID that identifies the application
  • GOOGLE_ADS_CLIENT_SECRET: the OAuth 2.0 client secret that corresponds with the specified client ID
  • GOOGLE_ADS_REFRESH_TOKEN: the OAuth 2.0 refresh token to use to connect to the Google Ads API
  • GOOGLE_ADS_DEVELOPER_TOKEN: the Google Ads developer token to use to connect to the Google Ads API
  • OUTPUT_TABLE_SPEC: the BigQuery output table

What's next