Google Ads data to BigQuery using Data Transfer Service
What is the reason for uploading information to a single storage unit? Google BigQuery is used to generate reports from raw data, an honest assessment of effective marketing performance.
Google BigQuery will provide the opportunity to analyze terabytes of data just in seconds.
Google Ads is one of the best platforms that are widely used to place Google Ads in Google BigQuery. Many companies, regardless of turnover, promote their services and products by creating online advertisements in Google Ads.
Google Ads produces a huge amount of data every day. In this article, we’ll talk about transferring data from Google Ads to Google BigQuery to prepare user reports for analysis.
Connecting Google Ads data to BigQuery – reasons for action
To promote a personal business through Google Ads, you need to allocate a certain amount of money. The costs of the budget should be detailed as much as possible. A detailed analysis should be completed to optimize costs. Unlimited capabilities will be necessary to store, and process voluminous data. It’s a Google BigQuery cloud-based data warehouse that will help with it.
Its storage capacity is unlimited; voluminous data is processed at high speed. With its extensive functionality, ETL allows customers to download and store data quickly, and refer to them by SQL queries. The obtained results can be saved or uploaded.
The main options of Google BigQuery include:
providing access control;
By analyzing marketing data, the user can understand which actions are performed effectively and which ads do not bring positive results. Consequently, the budget will be wisely spent and saved.
Marketing data analysis will allow you to see the range of interests of the company’s potential customers and plan future business decisions.
Solution 1: Transferring Google Ads data to BigQuery
To export information, you need to use the official BigQuery data pipelines. This option allows you to transfer important Google Ads information to BigQuery automatically, keeping them up to date.
Before you start the action, it is recommended to make sure that you have administrator rights, access to the corresponding Google Ads account or the manager account.
The following steps should be taken to configure the right:
go to the Google Cloud platform;
open the BigQuery console;
select “Transfers in the left panel”;
click on “Create transfer”;
press in the “Source Type” field;
select Google Ads (previously AdWords);
choose a distinctive name for the transfer;
schedule a transfer (to do this, select the frequency, start time of the transfer) – every day;
create/select your dataset to generate a table;
designate the identifier of the Google Ads account client (123-123-1234 or other);
add a configurable update window (from 0 to 30), the default setting is 7 days;
turn on email notifications;
By applying this function, the user will be able to create a set of tables, reports with all the necessary information. Updating the data will help to avoid the double entry of information into DWH.
Solution 2: Exporting Google Ads reports to BigQuery
Using CSV files is the surest and easiest way to load BigQuery data. In BigQuery, it is possible to create a table by taking advantage of CSV schemes. Everything is done automatically. How is the action done? You should:
open Google Ads;
go to the desired report;
click on “Load” at the top of the report;
choose the CSV format.
The report is then uploaded. After that, you have to add the report to Google BigQuery. This is not difficult, just perform the following steps:
open the Google BigQuery console;
select data for the new report table;
click on “Create table” in the “Data set” menu;
press “Load” as the source of the table, your file and in CSV format;
select data set, table name for the loaded report;
find the “Autodetect” section;
mark “Scheme and input parameters”;
click “Create table”.
You made it! Uploading the report to BigQuery was successful. Next, you can move on to analysis.
Solution 3: Send data using Google Ads script
The data transfer feature allows users to easily and quickly export their data to Google BigQuery. However, in some cases, the client does not need all the reports, actions with the data before uploading, but only some of them. We suggest using Google Ads scripts. If you have a minimal understanding of programming, this option is ideal for you.
open Google Ads;
select the account you are interested in;
click “Tools” on the top panel;
choose “Scripts” in the “Mass Actions” category;
click on the + sign;
create a new script;
name the script (Google Ads to BigQuery or other);
click on “Extended APIs”;
By setting up Google Ads scripts, the user can directly connect Google Ads reports to BigQuery. Access to Google Ads reports, their management, and their uploading to Google BigQuery is given after writing your own scripts.
The official documentation provides information related to the Google Ads script. The user can independently create his scripts, and upload necessary information to the Google cloud.
This article presents three effective methods of importing data into BigQuery. Now, a nice bonus is another additional data transfer tool. It is based on Google Ads add-in for Google Sheets and Renta Marketing ETL, which is a third-party application for importing data. There are simple steps to follow:
request data from Google Ads to Google Sheets;
synchronize Google Sheets with BigQuery using Renta Marketing ETL;
install Google Ads add-ons for Google Sheets;
request the necessary data from the Google Ads account;
regular updating of this data in Google Sheets.
The setup is simple. You can do it in the following way. It is necessary to visit the Google Ads add-in page in the Google Workspace Marketplace, and then, click the “Install” button.
You should provide the necessary permissions. And you can use the add-in in the “Extensions” tab of any Google Sheets spreadsheet.
To extract the data, you should click “Create Report” and make the settings:
Account. It is used as a source of information.
Date range. It is used to query the data (the default is the last 7 days).
Report type. It is filled in to speed up the query (campaign report, keyword report).
Name of the report. You can create it on your own.
Columns. The columns that should be included in the report are marked.
Filters. Any filters that will be needed in the report are used.
Scheduled reports. You can set a daily, weekly or monthly report update.
After performing the simple steps, you can create the necessary report and add your own configurations.
Synchronizing Google Sheets with BigQuery using Renta Marketing ETL
How do you load data directly into BigQuery using Renta Marketing ETL? Let’s take a look at it.
Renta Marketing ETL is a multi-service data integration tool that enables automatic synchronization between cloud services for reporting, management and data collection.
Data is downloaded from Google Sheets, since Renta Marketing ETL does not support Google Ads as a source.
Using your Google account, pass the registration on Renta Marketing ETL. Then, click on “Add new importer”, give it a name.
Next, you should follow three steps:
connect your Google account to Google Spreadsheets;
select the spreadsheet, the sheet with the imported information from Google Ads;
specify a range in the selected worksheets;
click “Go to Destination Parameters”.
select BigQuery as the target application;
connect your BigQuery account;
enter the name of the BigQuery dataset and the table where Google Ads data from Google Sheets will be loaded;
change the import mode and add a column with the date and time of the last data update (optional).
To automatically export Google Ads data from Google Sheets into BigQuery, the user needs to set up a schedule.
Note. We recommend that you synchronize your schedule to export data from Google Ads to Google Sheets and from Google Sheets to BigQuery. Ideally, the user should have at least one hour of time buffer between them.
After clicking on the “Save” and “Run” buttons, you will see your Google Ads entries in BigQuery with the option to stop and run them in Google Sheets.
Choose the most appropriate option for connecting Google Ads to BigQuery
In this article, the Renta team described for you a 3 + 1 bonus method of extracting and loading your user Google Ads data into BigQuery. Which is the best method? It’s really simple: it depends on the requests, the user’s preferences, and the information he needs.
The data transfer service is helpful when extracting all the account data (accounts), keeping it up to date in real-time.
You should apply Google Ads Scripts if you are familiar with coding. This method allows you to make a selection of downloaded data. At the same time, you can perform data transformation before loading it into BigQuery.
If you need a specific report to do a one-time analysis, you just need to choose a way to send Google Ads data to BigQuery using Google Ads scripts, export the desired report, and do a file upload. And you need to remember the popular method of uploading custom data to BigQuery using Renta Marketing ETL.
You can update a particular report with the latest data and automatically upload it to BigQuery using the official Google Sheets add-in.
Need Help with your Digital Marketing
Submit Your Info and We’ll Work Up a Custom Proposal