Build auto-updating report in Data Studio (Looker Studio) with data from BigQuery

Hi

This is Jing, a data analyst with great passion for data science and big data technology. In this blog I want to share my way of setting up an auto-updating report in Data Studio (now is called Looker Studio) with data source from BigQuery. Connect BigQuery and Data Studio (Looker Studio) is not something complicated since there are so many ways of doing it. So easy just to make the connection work. What I am sharing here is the way I find works best in terms of computation cost, speed and flexibility of modifying the Query for me so far.

Auto Update Data Studio (Looker Studio) Report with data from BigQuery

Prerequisite

I don’t know how popular Data Studio (Looker Studio) is, since it is free and easy access for every Google account user, it is definitely one of my favourite visual tools. I assume you are also a Google account user. And you have some knowledge about BigQuery, able to write simple queries also some experience with Visualisation in Data Studio

BigQuery

First a Query for extracting data in the BigQuery is needed. To be able to make the data warehouse structure clear, please create a separate dataset for your report. For example, if you are creating a report for an ongoing AB test for a product team, then you could create a dataset called abTestTeamJing. After you write your query for your specific user case, follow steps below:

• Save the Query in the team’s dataset, you just created. It will be saved as a view.
  • e.g. abTestTeamJing.testReport_QUERY
  • The reason why save the QUERY is to make the query more transparent for others and also easy to access for modification by yourselves as well.

save query and result in BigQuery

• Run the query and save the result in the same dataset
  • e.g. abTestTeamJing.testReport
• Then query the abTestTeamJing.testReport_QUERY

SELECT
  *
FROM
  `projectname.abTestTeamJing.testReport_QUERY`

• Schedule the Query shown above
  • The reason for doing this is, if you want to change you actual test query, you don’t have to go to the schedule to find your query and make change. The scheduling will be stopped if you set an end time for you scheduled query.

Schedule BigQuery

Data Studio (Looker Studio)

When you fixed your query and got a table for your test, then it is time for visualising the result. Data Studio is very well connected to BigQuery, so follow the steps below to set up.

• Connect the test result table to the Data Studio Report
  • Use the “testReport” table as the data source for your report
• If you want to reuse the report (visualisations) as a temple, just copy the report and change the data source. The the same set of visualisations (report) can be easily used for different use cases. In my case, I use the same report for different AB tests. A lot of AB tests share a lot of metrics, such as test users, conversions, click through rate, bounce rate and etc …

copy data studio report by changing data source

End

The whole process is not complicated at all and there are other ways doing similar things. I find this way works really well for me. And if you just started working with BigQuery and Data Studio (Looker Studio), try this and probably improve or adjust it to the way suits you to the best. Thanks for reading. I am Jing, a data scientist aiming to be better and better.

Advertisement