Executive Summary
With the advancement of technology and tools that we use for data tracking and measurement, we are highly empowered to perform advanced analysis and reporting not just by using the user interface based tools, but going to the source and working on the raw data directly in the cloud. Considering the current state, when pushing audiences from Google Analytics 4 (GA4) into DV360 or Search Ads 360, using native integrations is becoming more of a norm for exporting cost and campaign performance data from the Ad Tech platforms to the analytics platform. Analyzing campaign performance is something most companies are willing to implement—a significant push to get raw data directly from the source platforms (like campaign manager) into BigQuery to perform high value and high impact advance analysis. This article details one such method: BigQuery Data transfer services for Campaign Manager to get your data into the cloud.
Although getting raw data from Campaign Manager to BigQuery (BQ) is not the only benefit of setting this data import, this adds value in various ways:
- Once we have completed the data import set-up and Campaign Manager data is available in BQ, it sets the organization up for success by utilizing this data for many other advance analysis and predictive modeling like campaign budget analysis and optimization. It also helps with retaining the historical data into the cloud for future-proofing.
- It also builds an opportunity to integrate an organization’s media performance data with their CDP data and build first-party activation strategies.
- Comprehensive visualization and dashboards can be built on this data for the business to monitor their media performance and plan ahead for any optimization. Using BQ DTS services not only helps with media data integration with various other data sources, but also aids data transformation and metrics calculations as per success metrics and KPIs defined by business. Therefore, this helps with building powerful visualizations for business leaders representing exactly how they want to visualize the drivers of success.
BigQuery Data Transfer Services for Campaign Manager
The BQ Data Transfer Service for Campaign Manager allows you to automatically schedule and manage recurring load jobs into BQ for Campaign Manager reporting data. Instead of using the campaign manager API to set up the export, this is another way to get the data extract by using an automated pipeline that Google sets up for us to get the client’s Campaign Manager data first into Google’s Storage bucket in GCP, from where it can be extracted and loaded into our own BQ project.
But before we look at the process of data extraction and load, let’s look at the types of reporting tables and their schema in BQ. The BQ Data Transfer Service for Campaign Manager currently supports the following two reporting options. Once the BQ Data Transfer Service enables both DTv2 files and match tables, the following will be available for the user to work with:
Data Transfer v2 (Campaign Manager DTv2) files – Consists of performance data files. For example, impressions, click and activity data like Impression ID, Asset ID, Campaign ID, DBM data fields, etc.
- All Data Transfer Files are stored as comma separated values (CSV). Due to the customizable nature of Data Transfer, your files may not contain all fields below.
- The first row of the file is always a header row containing the column names.
- For fields containing special characters, the contents of the field are wrapped in quotation marks.
- Missing values are left blank.
- Rows are not sorted.
- Match tables for Display & Video 360 fields are in entity read file format.
- DBM fields will only populate if advertisers are linked between DCM<>DBM and permissions are granted. You must grant permission to each advertiser. You can also allow the partner to enable every new advertiser by default. In Display & Video 360, navigate to: Advertiser (or partner) > Basic Details > Dcm Configuration > then enable Include Display & Video 360 Data in DCM Data Transfer.
Data Transfer v2 (Campaign Manager DTv2) match tables – Consists of dimensions that we can use to enrich performance data. For example, asset, browser, campaign, etc.
- All match table files are stored as comma delimited text files on Google Cloud Storage and are compressed using gzip.
- The first row of the file is always a header row containing the column names.
- For fields containing special characters, the contents of the field are wrapped in quotation marks.
- Rows are not sorted.
- Files are in CSV format and support UTF-8 character encoding.
- Fields without values are exposed as empty, unless specified otherwise.
For information on how Campaign Manager reports are transformed into BQ tables and views, visit Campaign Manager report transformations.
Reporting and Schedule
The reports are uploaded every eight hours in Google’s storage bucket and are ready for us to extract and load into our own project.
How to Begin to Set Up BigQuery Data Transfer Services for Campaign Manager
Step 1 – Before you begin, verify that you have enabled the BigQuery Data Transfer Service.
Step 2 – Create a BigQuery dataset to store the Campaign Manager data.
Step 3 – Ensure that your organization has access to Campaign Manager Data Transfer v2 (Campaign Manager DTv2) files. These files are delivered by the Campaign Manager team to a Cloud Storage bucket. To gain access to Campaign Manager DTv2 files, your next step depends on if you have a direct contract with Campaign Manager. In both cases, additional charges might apply.
- If you have a contract with Campaign Manager, contact Campaign Manager support to set up Campaign Manager DTv2 files.
- If you do not have a contract with Campaign Manager, your agency or Campaign Manager reseller may have access to Campaign Manager DTv2 files. Contact your agency or reseller for access to these files.
After completing this step, you will receive a Cloud Storage bucket name similar to the following: dcdt_-dcm_account123456
Required Permissions
- BigQuery: Ensure that the person creating the transfer has the following permissions in BQ:
- bigquery.transfers.update permissions to create the transfer
- Both bigquery.datasets.get and bigquery.datasets.update permissions on the target dataset
- The bigquery.admin predefined IAM role includes bigquery.transfers.update, bigquery.datasets.update, and bigquery.datasets.get permissions. For more information on IAM roles in BigQuery Data Transfer Service, see Access control.
- Campaign Manager: Read access to the Campaign Manager DTv2 files stored in Cloud Storage. Access is managed by the entity from which you received the Cloud Storage bucket.
Create a Data Transfer for Campaign Manager
- Go to the BigQuery page in the Google Cloud console.
- Click Transfers.
- Click Create Transfer.
- On the Create Transfer page:
- In the Source type section, for Source, choose Campaign Manager.
- In the Transfer config name section, for Display name, enter a name for the transfer such as My Transfer. The transfer name can be any value that allows you to easily identify the transfer if you need to modify it later.
- In the Schedule options section, for Schedule, leave the default value (Start now) or click Start at a set time.
- For Repeats, choose an option for how often to run the transfer.
- Daily (default)
- Weekly
- Monthly
- Custom
- On-demand
- If you choose an option other than Daily, additional options are available. For example, if you choose Weekly, an option appears for you to select the day of the week.
- For Start date and run time, enter the date and time to start the transfer. If you choose Start now, this option is disabled.
- For Repeats, choose an option for how often to run the transfer.
- In the Destination settings section, for the Destination dataset, choose the dataset you created to store your data.
- In the Data source details section:
- For Cloud Storage bucket, enter or browse for the name of the Cloud Storage bucket that stores your Data Transfer V2.0 files. When you enter the bucket name, do not include gs://.
- For DoubleClick ID, enter the appropriate Campaign Manager ID.
- (Optional) If your files have standard names like these examples, leave the File name prefix field blank. Complete File name prefix only if the files in your Cloud Storage bucket have custom file names like this example.
- (Optional) In the Notification options section:
- Click the toggle to enable email notifications. When you enable this option, the transfer administrator receives an email notification when a transfer run fails.
- For Select a Pub/Sub topic, choose your topic name or click Create a topic. This option configures Pub/Sub run notifications for your transfer.
- Click Save.
Analyze your Campaign Manager Data in BigQuery
Once you have verified the accuracy of the data getting into BQ through the pipeline that we have created above, now is time to get the most value out of this data. There are many use cases that we can achieve with this data (for example, analyzing the camping activity or performing location/geography based campaign performance analysis). You can find some of the queries to get you started here (scroll to the bottom of the page) and please feel free to reach out to InfoTrust for assistance with set-up and data analysis.
