Automate reports in Google Data Studio Based on Data from Google BigQuery
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.
Table of Contents
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:
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:
OWOX BI has a 14-day trial period - combine all your data and build any marketing report in Data Studio for free
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:
Make a query and save the result as a separate BigQuery table:
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:
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.
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.
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:
Then specify the project, dataset, and GBQ table with the data for the report and click Link in the upper-right corner:
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.
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):
When you have defined the fields, click the Add to Report button in the upper-right corner:
You then select the visualizing tool (graph type) and highlight the area on the dashboard where you want to place it.
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.
Then all that’s left is to share the report with colleagues using the Grant Access button.
Learn more about working with BigQuery and Data Studio in our articles: