Automate reports in Google Data Studio Based on Data from Google BigQuery

167
3908
Downloads

In this article, we explain how to set up automatic updates to reports in Google BigQuery and visualize them using Google Data Studio.

By the way, with OWOX BI, you can automate data collection and any marketing reports. Sign up for a demo and we'll show you how it works. 

SIGN UP FOR A DEMO

Table of Contents

Our clients
grow 22% faster

Grow faster by measuring what works best in your marketing

Analyze your marketing efficiency, find the growth areas, increase ROI

Get demo

About working with Google Data Studio

A couple of words about Data Studio for those who don't know it yet. We often use this tool inside OWOX BI and recommend it to our customers for several reasons:

  • First of all, Data Studio is convenient because it has many connectors for third-party services. They can easily connect to almost any data source. Both native connectors from Google and those developed by other companies are available, for example, for Yandex.Metrics, Yandex.Directory, Facebook, Twitter, etc. If necessary, you can create your own connector.
  • The service is easy to use and it’s easy to visualize data. Multiple sources can be connected to a single dashboard.
  • It's easy to share reports in Data Studio with colleagues, providing them access to viewing or editing. There's no need for a colleague to have authorization, all they have to do is open the dashboard by clicking the link.
  • Almost all the features of the tool are available in the free version.

Here's a sample report in Data Studio with the main KPIs and traffic sources of an online store:

Example of Data Studio report

This is an interactive dashboard where you can see how metrics change across days, channels, device categories, etc. There can be multiple pages within a single report. Here’s why it’s so convenient: once you set up a dashboard and share a link with colleagues, then no more changes need to be made (unless you add new parameters). Just choose the right dates and get up-to-date information.

Now let's explain how we can create such a beauty. We build the report based on data from Google BigQuery, which will update automatically according to the specified schedule.

Step 1. Collect data from Google BigQuery

BigQuery has ready-to-use libraries and connectors that let you upload information about transactions, customers, and items from your CRM system to cloud storage. And OWOX BI will help you automatically collect all other data in GBQ:

  • Actions of users on the site (unsampled real-time data).
  • Cost data from advertising services.
  • Call and chat data from call tracking systems.
  • Email information from email services.

Once you have merged data from different sources into BigQuery, you will need to:

  • Use SQL-query to select the metrics you want to see in the report in a separate table.
  • Use Google App Script to set up a rule that updates data in this table.
  • In Data Studio, connect the table with data from GBQ and visualize it.

Schematically combining data and automating reports with OWOX BI can be depicted as such:

Combination of data in Google BigQuery

OWOX BI has a 14-day trial period - combine all your data and build any marketing report in Data Studio for free

TRY OWOX BI

Step 2. Prepare a table in Google BigQuery with the data for the report

We described the instructions in no great detail as we suppose you are familiar with the Google BigQuery interface. If you need a refresher on how to deal with table creation, check out this article: BigQuery data structure in Google: How to get started with cloud storage. 

To save Google BigQuery resources, we recommend that you first create a SQL-query that produces a table with a set of parameters and metrics you want over a specific period:

Table making in Google BigQuery

Make a query and save the result as a separate BigQuery table:

Save a result

Then create a view that calculates the same metrics, only over a shorter period. For example, you created a source table for December. Then each day the view will request yesterday’s data and add it to the table. 

To do this, run another query and click Save View:

save a View

Step 3. Create Apps Script to automatically update the table

Now you need to create a script that will automatically start the view and send updated data from it to the source BigQuery table. 

To do this, open Apps Script, click Create Script, name it, and write the following code, replacing the project name, dataset, and BigQuery tables with your own.

Apps Script

Fill out the form to get the full text of the code in your email

Then click the clock icon to set the schedule at which the script will run. Click the Add Trigger button in the lower right corner. Select the Time Trigger event source, specify the run frequency you want, and click Save. 

autorun of the script

It's ready! Now the data in the GBQ table will be updated according to the specified schedule.

Step 4. Create a report in Google Data Studio

Go to the Data Studio home page, click New in the upper left corner, and select Report. Then click Create Data Source in the lower right corner and select BigQuery: 

GBQ sources

Then specify the project, dataset, and GBQ table with the data for the report and click Link in the upper-right corner:

GBQ connects to Data Studio

In the opened window you will see all the parameters and metrics that are stored in the connected table and that are available for you to work with. Here you can rename them if you need by clicking on the right metric.

parameters and metrics

You also have the opportunity to create your own custom metrics. Use the Add Field button and formulas (if you click on a question mark, Help appears):

Help

When you have defined the fields, click the Add to Report button in the upper-right corner:

add report

You then select the visualizing tool (graph type) and highlight the area on the dashboard where you want to place it.

Data Studio

The control panel opens on the right, where you can customize the report as you need: add parameters for comparison, customize filters, date range, dashboard style, etc.

control panel in Data Studio

Then all that’s left is to share the report with colleagues using the Grant Access button.

Useful Links

Used tools

You might also like