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 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.
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?
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.
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.
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.
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.
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”.
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.
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”.
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.
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.
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.
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.
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.”
We can also add a member/user to the project by clicking on the notification bell and select “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.
To double-check, on the IAM members list of our project, you should see your CM360 email/user, with those two added roles/permissions.
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.
On the bottom of the report we can now select Format: BigQuery Export, and under BigQuery Link, select created by us Project/DataSet.
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.
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.
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.
Done! Data Imported
Kaboom! All dimensions and metrics are imported with the CM360 data. Now you can start working on the dashboards!
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.