Creating Google Ads Data Transfer in BigQuery

Google Ads offers a vast amount of data, but efficiently managing and analyzing this data can be challenging. BigQuery not only helps with analysis but also streamlines data ingestion of Google Ads data.

With the latest updates, users now have greater control over the data they import by creating custom reports using Google Ads Query Language (GAQL).

This post will guide you through setting up a custom Google Ads data transfer in BigQuery.

What’s New

On March 6, Google announced that BigQuery Data Transfer Service now supports custom reports for Google Ads. Previously, running a full Google Ads data transfer resulted in the creation of over 100 tables and 100 views. This approach often led to unnecessary data ingestion and storage costs, because most users need only five or six tables.

With the new feature, you can now specify exactly which data you want by writing GAQL queries.

This enhancement brings several benefits:

Efficiency: Only relevant data is transferred, reducing storage costs.
Flexibility: Users can define custom reports by selecting only the necessary fields.
Simplicity: A single table and view are created, making data organization much more manageable.

When setting up the Google Ads data transfer, BigQuery creates one table and one view per transfer:

Table (p_ads_tableName_yourCustomerID): This is a physical table that stores the actual data imported from Google Ads.
View (ads_tableName_yourCustomerID): This is a virtual table that provides a structured way to query the underlying data. Views do not store data but reference data stored in the table, offering a convenient way to work with structured reports. You won’t see View table data in a preview because it needs to be queried first.

How to Set Up a Google Ads Data Transfer

Follow these steps to configure a Google Ads data transfer using BigQuery Data Transfer Service:

1. Create a Dataset in BigQuery

Navigate to BigQuery in the Google Cloud Console.
Select your project and create a new dataset to store the Google Ads data.

2. Set Up the Data Transfer

Go to BigQuery Data Transfers and click Create Transfer.
Choose Google Ads as the data source.

3. Configure Transfer Settings

Enter your Google Ads customer ID.
Select Custom Report as the report type.
Specify Table Name: This will define how your table and view are named (p_ads_yourTableName_yourCustomerID).
Insert GAQL Query: Write a query that defines the specific data you need from Google Ads. For example, you can use the query below. The data transfer uses a simplified version of GAQL, so do not include any WHERE or ORDER BY clauses. Simply define the reports, segments, and metrics.

SQL

    SELECT
        campaign.id,
        campaign.name,
        customer.id,
        customer.descriptive_name,
        campaign.status,
        segments.date,
        metrics.all_conversions,
        metrics.clicks,
        metrics.cost_micros,
        metrics.ctr,
        metrics.impressions,
        metrics.interactions
    FROM campaign

Set Refresh Window: The default is the last 7 days, but you can configure up to 30 days.

Select Destination: Choose the dataset you created in Step 1.

Name the Transfer: Give your transfer a recognizable name.

Schedule the Transfer:
- Run immediately or schedule it for a later time.

Use Default Service Account: No need to create a new one.
Enable Notifications (Optional): Get alerts about the transfer status.
Click Save to create the transfer.

If you opt to run it immediately, the data will begin importing into BigQuery.

The transfer run has completed successfully

How to Backfill Google Ads Data

If you need to load historical Google Ads data into BigQuery, you can use the backfill option. This allows you to retrieve past data for a specific date range. However, it’s important to note that the transfer runs every 35 minutes and processes one day of data at a time. So, if you’re backfilling a longer period, you’ll need to wait for a while.

To schedule a backfill:

In Data Transfers, open the transfer you previously set up.

Click Schedule Backfill.

Select Run for a date range. Choose the desired range and confirm.

And voilà! Once the transfer is complete, you can easily query both your latest and historical Google Ads data in BigQuery. With all your ad performance metrics in one place, you can run analyses, create custom reports, and integrate the data with other sources for deeper insights.

selnekovic.com