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
Required parameters
- customerIds : A list of Google Ads account IDs to use to execute the query. (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
. (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 (https://developers.google.com/google-ads/api/docs/best-practices/rate-limits).
- googleAdsClientId : The OAuth 2.0 client ID that identifies the application. See Create a client ID and client secret (https://developers.google.com/google-ads/api/docs/oauth/cloud-project#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 (https://developers.google.com/google-ads/api/docs/oauth/cloud-project#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 (https://developers.google.com/google-ads/api/docs/oauth/2sv).
- googleAdsDeveloperToken : The Google Ads developer token to use to connect to the Google Ads API. See Obtain a developer token (https://developers.google.com/google-ads/api/docs/get-started/dev-token).
- outputTableSpec : The BigQuery output table location to write the output to. For example,
<PROJECT_ID>:<DATASET_NAME>.<TABLE_NAME>
.Depending on thecreateDisposition
specified, the output table might be created automatically using the user provided Avro schema.
Optional parameters
- loginCustomerId : A Google Ads manager account ID to use to access the account IDs. (Example: 12345).
- bigQueryTableSchemaPath : The Cloud Storage path to the BigQuery schema JSON file. If this value is not set, then the schema is inferred from the Proto schema. (Example: gs://MyBucket/bq_schema.json).
- writeDisposition : The BigQuery WriteDisposition (https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#jobconfigurationload) value. For example,
WRITE_APPEND
,WRITE_EMPTY
, orWRITE_TRUNCATE
. Defaults toWRITE_APPEND
. - createDisposition : The BigQuery CreateDisposition (https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#jobconfigurationload). For example,
CREATE_IF_NEEDED
andCREATE_NEVER
. Defaults toCREATE_IF_NEEDED
.
Run the template
Console
- Go to the Dataflow Create job from template page. Go to Create job from template
- In the Job name field, enter a unique job name.
- Optional: For Regional endpoint, select a value from the drop-down menu. The default
region is
us-central1
.For a list of regions where you can run a Dataflow job, see Dataflow locations.
- From the Dataflow template drop-down menu, select the Google Ads to BigQuery template.
- In the provided parameter fields, enter your parameter values.
- 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 choiceVERSION
: the version of the template that you want to useYou can use the following values:
latest
to use the latest version of the template, which is available in the non-dated parent folder in the bucket— gs://dataflow-templates-REGION_NAME/latest/- the version name, like
2023-09-12-00_RC00
, to use a specific version of the template, which can be found nested in the respective dated parent folder in the bucket— gs://dataflow-templates-REGION_NAME/
REGION_NAME
: the region where you want to deploy your Dataflow job—for example,us-central1
CUSTOMER_IDS
: the Google Ads account IDsQUERY
: the Google Ads Query Language queryQPS_PER_WORKER
: the required Google Ads request rate per workerGOOGLE_ADS_CLIENT_ID
: the OAuth 2.0 client ID that identifies the applicationGOOGLE_ADS_CLIENT_SECRET
: the OAuth 2.0 client secret that corresponds with the specified client IDGOOGLE_ADS_REFRESH_TOKEN
: the OAuth 2.0 refresh token to use to connect to the Google Ads APIGOOGLE_ADS_DEVELOPER_TOKEN
: the Google Ads developer token to use to connect to the Google Ads APIOUTPUT_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 jobJOB_NAME
: a unique job name of your choiceVERSION
: the version of the template that you want to useYou can use the following values:
latest
to use the latest version of the template, which is available in the non-dated parent folder in the bucket— gs://dataflow-templates-REGION_NAME/latest/- the version name, like
2023-09-12-00_RC00
, to use a specific version of the template, which can be found nested in the respective dated parent folder in the bucket— gs://dataflow-templates-REGION_NAME/
LOCATION
: the region where you want to deploy your Dataflow job—for example,us-central1
CUSTOMER_IDS
: the Google Ads account IDsQUERY
: the Google Ads Query Language queryQPS_PER_WORKER
: the required Google Ads request rate per workerGOOGLE_ADS_CLIENT_ID
: the OAuth 2.0 client ID that identifies the applicationGOOGLE_ADS_CLIENT_SECRET
: the OAuth 2.0 client secret that corresponds with the specified client IDGOOGLE_ADS_REFRESH_TOKEN
: the OAuth 2.0 refresh token to use to connect to the Google Ads APIGOOGLE_ADS_DEVELOPER_TOKEN
: the Google Ads developer token to use to connect to the Google Ads APIOUTPUT_TABLE_SPEC
: the BigQuery output table
What's next
- Learn about Dataflow templates.
- See the list of Google-provided templates.