5 steps to automate Google Marketing Platform reporting • BigQuery & Google Data Studio

5 steps to automate Google Marketing Platform reporting • BigQuery & Google Data Studio


Wojtek Andrzejczak
Wojtek Andrzejczak
5 steps to automate Google Marketing...

The most difficult part of advertising is reporting. Manually generated reports take a lot of time, and manually combining data in Excel does not help with this task. However, there is a way to optimize this process.

Why could export to BigQuery be better than a direct Google Data Studio connector?

CM360 and DV360 connectors in Google Data Studio do not include all reporting fields. Additionally, in the Google Reporting system, you can not choose certain combinations of the dimensions like Browser/Platform and Environment. So those fields are not included in the standard connector.

For an example, if you want to generate a financial report based on the CM360 booked flight information to control ad delivery, you would need to generate a unique report, import it to Google Spreadsheet and import it to Google Data Studio as a data source.

Google Campaign Manager 360 / Standard Report example
Google Campaign Manager 360 / Standard Report example

When should we consider export to BigQuery?

First of all BigQuery is a payed service. It is not free, so you need to know what you are doing.

BigQuery makes sense if we have a huge amount of data that we would like to consolidate and merge with other reporting data like Google Analytics. And also, if you would like to have historical data to analyze in the future (Data Warehouse).

Additionally, to improve reporting dashboard loading performance (Google Data Studio or Tableau), prepare the data structure without unnecessary calculations on the dashboard side, which slows down everything.

Can we use BigQuery and a standard GDS connector?

Absolutely yes.

As I said before, a standard data connector does not include many reporting fields, which might be important to us. Using BigQuery connection, you could skip Google Spreadsheet + AppScript workaround.

Idea

Recently Google has enabled a new export option to create a campaign report in CM360 and export it directly to BigQuery without any necessary manual operations. So these data can be exported to BigQuery, where they could be imported to Google Data Studio to be used in a custom-created dashboard.

Prepare BigQuery

Before we start, you need to visit Google Cloud Platform, set up billing information, etc. Also, make sure you have administrative permissions.

I also highly recommend pin “BigQuery” and “IAM &Admin” to appear on the top of the left menu. Otherwise, you will spend a lot of time scrolling down and looking for a BigQuery position on the menu.

Google Cloud Platform / Pined Menu Sections
Google Cloud Platform / Pined Menu Sections

How to pin? On each option in the menu, you will see a small icon on the right side. If you click it, it will change to blue color, and the selected menu option will appear on the top of the menu.

Google Cloud Platform / Pin BigQuery in menu
Google Cloud Platform / Pin BigQuery in menu

Create BigQuery project

When Cloud Cloud Platform is ready, we can create a new project by clicking on the box on the right from the logo.

In our test case project name is “Test GMP – CM360 Reports”.

Google Cloud Platform / BigQuery / Create Project
Google Cloud Platform / BigQuery / Create Project

Create BigQuery project new data set

From the left menu now, you can select “BigQuery,” and on the view, you will see a position with a similar name as your project. By clicking on the 3-dots menu, select the “Create data set” option.

Google Cloud Platform / BigQuery / Project / Data Sets Overview
Google Cloud Platform / BigQuery / Project / Data Sets Overview

Data set is something like a group of tables congaing data. All tables in the data set should have a similar context/purpose to keep everything organized.

Creating a new data set

Now we can define a name of our data set. In our case let it be “CM360_Reports”.

Google Cloud Platform / BigQuery / Project / New Data Set
Google Cloud Platform / BigQuery / Project / New Data Set

There are also other DataSet configuration fields worth consideration. But they are not relevant for our test use case. However, in your case, it might be worth attention.

A new data set is created

After we confirm creation of the new data set, we now see it after expanding project.

Our data set has a unique Dataset ID: test-gmp-cm360-reports:CM360_Reports created by the system automatically.

Google Cloud Platform / BigQuery / Project / Data Set / Overview
Google Cloud Platform / BigQuery / Project / Data Set / Overview

It is important to keep in mind that “Dataset ID” is needed in the next steps.

Prepare Google Campaign Manager 360

Our first configuration step is done. Now we can proceed to Google Campaign Manager and manage the next necessary steps.

Visit CM360 account settings

In CM360, under the Admin menu on the left side, please select “Account,” if you don’t see it, it means you don’t have administrative permissions.

Google CM360 / Admin / Account
Google CM360 / Admin / Account

Enable Big Query Exported setting

In the Admin -> Account section, on the bottom, you will find the “BigQuery Exporter” section. After expanding, please click on the button to enable the feature. Then please click the “Save” button to apply changes.

Google CM360 / Admin / Account / Enable BigQuery Exporter
Google CM360 / Admin / Account / Enable BigQuery Exporter

Link with the BigQuery DataSet ID

After you save changes from the section above, you will see more options available under the “BigQuery Exporter.”

  • Permissions: please copy highlighted e-mail address. We will need it to add it as a user in BigQuery.
  • Linked BigQuery DataSets: in the DataSet ID field please copy Data Set ID from our created DataSet in BigQuery: “test-gmp-cm360-reports:CM360_Reports“, and save changes.
Google CM360 / Admin / Account / BigQuery Exporter / Setup
Google CM360 / Admin / Account / BigQuery Exporter / Setup

Connect CM360 with BigQuery

After we have enabled the BigQuery Exporter feature and connected CM360 with our test DataSet, now we can proceed further with the permissions.

Assign BigQuery project member

In CM360, we have connected DataSet, and we have copied from the “Permissions” section an email address. We need to add this email to the project so that CM360 will have access to export data to BigQuery.

We can access the “IAM & Admin” section from the left menu (we have pined it, so it should be placed on the top). But, first, we need to select the “Add Members.”

Google Cloud Platform / IAM & Admin / Members

We can also add a member/user to the project by clicking on the notification bell and select “ADD MEMBERS.”

Google Cloud Platform / Notification / Add Members
Google Cloud Platform / Notification / Add Members

Assign BigQuery project member roles

After we chose to add a new member, we will be able to use an email from the CM360 under the “Member” field.

Then we need to add two permission roles:

  • BigQuery Data Editor
  • BigQuery Job User

With those two roles/permissions added, we can now create a user. After we do this, CM360 will be able to export data to BigQuery.

Google Cloud Platform / IAM & Admin / New Member & Roles
Google Cloud Platform / IAM & Admin / New Member & Roles

To double-check, on the IAM members list of our project, you should see your CM360 email/user, with those two added roles/permissions.

Google Cloud Platform / IAM & Admin / New Members ready
Google Cloud Platform / IAM & Admin / New Members ready

Setup report export from CM360 to BigQuery

We have created a BigQuery project, connected CM360 with a DataSet, assigned all necessary roles and permissions. Now we can finally proceed to the main point of the journey.

Standard Report export

As an example lets prepare an example campaign standard report with a few example fields.

Google Campaign Manager / Reporting / Example campaign fields
Google Campaign Manager / Reporting / Example campaign fields

On the bottom of the report we can now select Format: BigQuery Export, and under BigQuery Link, select created by us Project/DataSet.

Google Campaign Manager / Reporting / BigQuery Export
Google Campaign Manager / Reporting / BigQuery Export

You can also enable Schedule to let Google run your report periodically at certain days and times. Each time report will be executed, new campaign data will be placed into the BigQuery.

As you notice, I’ve placed “01” under “BigQuery Table Prefix.” You will probably have multiple reports/tables, and instead of placing multiple names, it is much easier to use a number-based prefix like 01, 02, 03, etc. It is much easier to find a table with a number ID instead of letters. And much easier to sort as well. But this is just my personal preference which works for me.

Check data in BigQurey

After we executed the CM360 report, we should see a new table created in our project/DataSet. When we preview changes, it will contain all current campaign data, which dimensions and metrics selected in our CM360 report.

Google Cloud Platform / BigQuery / Project / DataSet / First Exported Table ready
Google Cloud Platform / BigQuery / Project / DataSet / First Exported Table ready

Connect BigQuery with Data Studio

Let’s make now a connection between Google Data Studio and BigQuery to pull data that we have generated in CM360.

Select BigQuery connector

First of all let’s create a Data Source using BigQuery connector.

Google Data Studio / Data Source / Connectors
Google Data Studio / Data Source / Connectors

Select BigQuery table to connect

After selecting the connector, we able now find and select Project/DataSet/Table, which we would like to connect with.

Google Data Studio / Data Source / BigQuery / Select Table Source
Google Data Studio / Data Source / BigQuery / Select Table Source

Done! Data Imported

Kaboom! All dimensions and metrics are imported with the CM360 data. Now you can start working on the dashboards!

Google Data Studio / Data Source / BigQuery Conneted
Google Data Studio / Data Source / BigQuery Conneted

Summary

Setup and configuration for the reporting between CM360 and BigQuery is just a one-time operation. After this, everything we need to do is to have a plan to prepare report templates which we would like to use in the Google Data Studio.

However, if you decide to use BigQuery, you need to have a plan. What data you would like to put there, and how you would like to use them. You can easily create more problems than you would like to solve.

Subscribe to our newsletter!

[newsletter_form]

Links


  • Contact Me
    Contact me if you need advice or if you need help. Would you please choose the most suitable contact channel for you?
  • My Services
    If you need a technical specialist to technical support to fix your problems, you are in the right place.
  • Who I am
    Hello, my name is Wojtek, I’m an experienced Software Engineer, with many many years of experience in the advertising industry.
Show Comments (0)

Comments

Related Articles

3rd party tags impressions & clicks discrepancy problem • TCF 2.0
Reporting

3rd party tags impressions & clicks discrepancy problem • TCF 2.0

TCF 2.0 as a new source of problems with the campaign discrepancies for 3rd party tags used in the digital campaigns. What is TCF 2.0? Shortly, TCF became a privacy standard to...

Posted on by Wojtek Andrzejczak
Google Analytics • DFA/CPM • campaign traffic problem
Google Analytics

Google Analytics • DFA/CPM • campaign traffic problem

Google Analytics doesn’t track your Google Marketing Platform campaign traffic correctly if your traffic is identified as DFA/CPM.

Posted on by Wojtek Andrzejczak