BigQuery to Data Studio – Getting the Most Out of Them

Google BigQuery allows you to accumulate, store, and process an enormous amount of data about your product. Understanding this data and making informed decisions based on it puts your business in an advantageous position. Combining BigQuery and Data Studio makes it easy to visualize your data, making it understandable for virtually anyone.

It’s easy to integrate both tools and feed Data Studio with data. Both on-demand and on-schedule. We’ll explore the best way to pull it off and answer some common questions you may have.

Data Studio & BigQuery – the basics

But first things first, let’s talk some basics about each tool in case you’re not familiar with either.

Google BigQuery is a data warehouse suitable for storing petabytes of your data. You can run simple and complex SQL queries on this data and obtain the calculation results within just seconds. BigQuery is easy to scale up and down, secure, and easily integrates with numerous other tools.

Read more in our BigQuery Tutorial.

Google Data Studio is a data visualization platform. It allows you to import nearly any type of data and turn it into charts, reports, or any other visual representation. All of this makes the data easily accessible for your and your teammates.

Read more in our Google Data Studio Tutorial.

Connect Data Studio to BigQuery

To display any data, you need to first connect Data Studio to BigQuery. You do it with a dedicated connector that will pull data and refresh it for you on a chosen schedule.

BigQuery isn’t, of course, the only option. There’s a multitude of possible sources, available via Google’s connectors or Partner Connectors – over 400 of which were available at the time of writing.

To see them all, head over to https://datastudio.google.com/. Then, click on Create to the left and pick Data source from the list.

Among Google Connectors, you’ll quickly spot a BigQuery connector. It’s a common method for connecting both services as using BigQuery data in Google Data Studio is an activity performed frequently.

How to connect BigQuery queries to Google Data Studio?

Pick the BigQuery connector from the list and authorize it to work with Data Studio if you haven’t before.

On the next page, find the dataset you want to use with Data Studio. BigQuery has several public datasets you can freely incorporate. For example, we’ll use the one demonstrating the mobile coverage in Catalonia in the years 2015-2017.

Select yours, and then find it on the list. It’s probably a good idea to name the data source if you haven’t done so yet.

Using your business data in the report

You can work with dummy data but you could also pull some real data from the services you use and bring it to Data Studio. This way, rather than theorizing about what you could build in Data Studio, you could visualize the important metrics of your business right away.

Coupler.io makes it easy to pull data from over 15 apps into BigQuery – automatically, without any coding required. The list of available BigQuery integrations includes Airtable, Xero, Quickbooks, Shopify, and plenty of others.

Once plugged into BigQuery, this data will be automatically refreshed according to your schedule – as often as once an hour. This guarantees that your Data Studio dashboards always display near real-time data.

Configuring the data source

Once you have picked the source, connect Data Studio to BigQuery by clicking on the Connect button in the top-right corner. The dataset will load and you’ll be welcomed with the list of fields found in your dataset. They’ll be split into dimensions (green fields)

and metrics (blue fields)

A quick recap from our Data Studio article:

• Metrics are numeric values used for measuring or counting.
• Dimensions are names, descriptions, or other details of the things that we measure/count with metrics.

Data Studio by default creates one standard metric: Record Count. You can, however, treat any dimension as a metric on your dashboard.

Data Studio will select a default aggregation for each field – text fields will end up with None, numerical values will likely be set as Sum. You can adjust these values yourself and, for example, for financial values select the currency you want to use.

Finally, click on the Create Report button in the top-right corner.

A new tab will open. Accept the next popup:

And wait for a few more seconds. A new report will be generated. It will feature a pretty ugly and, most of the time, a useless table that Data Studio randomly generates based on some of your data. 

We won’t be surprised if someday Data Studio can accurately guess what we wanted to demonstrate on the charts before we even considered it. But clearly, it’s not there yet. You may want to delete the table. To do so, highlight it and press the Delete button.

By the way – if you like one of the templates available from the get-go, you may plug in a data source to it to replace the dummy data it comes with. 

Select the template of your choice and then press the Use template button in the top-right corner. A popup will appear, letting you choose one of the existing data sources or add a new one in the same fashion as we did above.

BigQuery Data Studio dashboard – the basics

Once you’ve plugged in a data source to a report, it’s time to populate it with some useful information. You may already have some charts if you picked one of the templates. In any other case, your report is probably pretty empty at this point.

For starters, you may want to pick a theme for the dashboard or adjust some settings. At any time, you can also choose from several functionalities available from the menu on top:

• With the View button, check how a dashboard looks like for the folks you share it with. Go back to the editor by pressing the Edit button.
• With the Share button, you can share the report with your team. If you give the Edit access, they’ll also have the same capability as you do to edit the report.
• Under the three-dotted menu, you can refresh the data on the spot or make a copy of a report.

Adjusting the refresh rate

One thing about refreshing data. Once you press the button above, Data Studio will pull the latest version of your data from the source (BigQuery in that case). It’s useful if you made changes to the BigQuery table and need to check right away if the data was retrieved correctly.

Most often, though, the automatic refreshes will do just fine. To adjust those settings, pick Resources from the menu and then Manage added data sources. 

Then, click on Edit next to your source and find the Data freshness field on the following page.

Here, select how frequently the data should be refreshed.

Adding content to Data Studio report

The next step is to finally add some content to our report. The most common way is to use the Add a chart button in the menu. Clicking it will open a long list of available charts, many of which you’re certainly familiar with. Click on one of your choices and drop it anywhere onto the report page.

Alternatively, you can click the button directly to the right of Add a chart that features Community visualizations. It’s a selection of more advanced visualization methods, such as Gantt charts, candlesticks, heatmaps, and so on.

Going back to the basics, once you decide on the type of chart you want to use, usually all you need to complete the chart is its dimensions and metric(s). We’ll show you how it works with several examples.

As you remember, we’re using the public dataset of mobile coverage in Catalonia as our data source. There’s lots of interesting data in there that we can easily visualize using Data Studio and BigQuery.

• Each of the millions of records includes a mobile signal strength for a given area. The simplest thing we can do is calculate the average of this data and display it as a scorecard. All we have to do is pick signal as a metric, and on the leftmost side of the field, select average. By default, it will show sum, but that’s not something that would have any value to us. 

• There are two types of scorecards available out of the box, almost identical. To practice a bit more, we selected precision (probably referring to GPS precision) as a metric, and set it to aggregate as median.

• We also played a bit with a table. Here, we made a list of all network providers in the area (dimension – network) and for each calculated their average (connection) speed and added the number of records for each provider. Thus speed and Record Count metrics. By default, the table is sorted in descending order by the first metric on the list but we can adjust it.

• We played a bit with a pie chart, demonstrating the percentages of networks (dimensions) that folks in Catalonia (metric) were connected to while the data was gathered.

• Finally, we created a bubble chart, demonstrating the strength of a signal and the number of records (both are metrics) for each town in Catalonia (dimension) that made it into our BigQuery dataset. Yes, the big dot is Barcelona and the chart could certainly be more meaningful, but you get the idea ;-).

All in all, our BigQuery Data Studio dashboard that took maybe five minutes to prepare looks like this::

We’re not going to dive deeper into all available options. If you’re interested in exploring the topic further, please check our Data Studio tutorial.

Google BigQuery & Data Studio billing details

Data Studio is advertised as a free service and it technically is. However, using it on large datasets fetched from BigQuery will inevitably lead to some charges as BigQuery is a paid service.

The price you pay for BigQuery depends on how much data you store there and how much you query for. Every time Data Studio refreshes the data in your reports, certain queries are sent to BigQuery, getting fresh data in return, but this also incurs certain charges.

The storage pricing is more straightforward. The base rate is $0.020 per GB of data, with 10 free GB available for you each month. 

If you, however, have tables or table partitions that haven’t been used in over 90 days, the price for them goes down to $0.010 per GB, with another 10GB free.

When it comes to the pricing for querying the BigQuery database, the rate is $5 for each terabyte of data you query for, with 1TB available for free each month. Optionally, you can opt for flat-rate pricing, as detailed on the BigQuery pricing page.

In reality, it’s not simple at all to calculate in advance the query usage needed to maintain your BigQuery/Data Studio dashboards. You could look into the query history in BigQuery, find the Data Studio entries, check how many bytes each ate up, and sum it all up. But it’s not feasible at all if you have dozens or hundreds of tables to check each time.

The best idea is to try it out on a single dashboard and a limited amount of BigQuery resources. And use more resources when you have a better idea about the associated costs.

How to display date from BigQuery in Data Studio

Whatever type of data you export, one or more fields will inevitably include a date and/or time associated with a record. It’s essential for adding a data range control, available from the Add a control menu.

Having it in place lets users select a particular period and see the results only for that period. It also allows you to decide on the default value for this field and, for example, show results for the last seven days at first.

BigQuery supports many different BigQuery data types. The following can be used for expressing date and/or time:

• Date type
• Time type
• Datetime type
• Timestamp type

The great thing about the Data Studio BigQuery combo is that nearly always Data Studio will guess correctly which type it’s dealing with and will aggregate it correctly. In case it doesn’t, you can always go back to the list of fields present in your source (Resource -> Manage added data sources -> Edit) and fix it yourself.

How to join two BigQuery tables in Data Studio

BigQuery allows you to add multiple data sources to your report. It can be different applications – for example, BigQuery, Google Sheets, and Google Analytics – but it can also be multiple BigQuery tables. 

To add additional sources, go to Resource -> Manage added data sources. And there click on Add a data source.

Once added, every time you add or edit a chart, you’ll be able to choose the source you want to use.

But the power of using multiple sources lies in combining them – in other words, blending data from different sources. In BigQuery it’s very simple to do.

To blend two charts, add them first to a report. In our example, we added two public datasets concerning bike share stations in San Francisco. From one we extracted the table demonstrating the count of docks on every station. Another gave us the capacity of each station.

To blend the two, we need to highlight them both (click on one, hold Ctrl/Cmd key, and then click on the other), then right-click and choose Blend data.

The result will be a new chart combining information from both sources.

An alternative method for blending two or more datasets is via the Resource -> Manage blended data menu. Here, you need to specify a join key for each source – at least one dimension that each source will share. You complete the rest as usual.

Using BigQuery repeated data in Data Studio

Having repeated data in a single BigQuery table, and trying to use it in Data Studio can be a bit problematic. Data Studio will attempt to cut down on the resources needed to process the query, and there’s nothing you can do about it. 

As a result, it will aim to get the desired results in a single query, effectively cross joining the nested columns. Therefore, most likely, it will result in incorrect data displayed on your Data Studio dashboard.

Is there a way around it?

You could prepare the data differently in BigQuery itself. For that, use the UNNEST() command to turn an ARRAY into a table with a separate row for each element of the ARRAY.

A viable workaround is also to create two different data sources that will point to the same BigQuery table. By doing that, you force Data Studio to query both sources separately. It doubles the size of queries but effectively reduces the annoyance of working with repeated data.

Kudos to Felipe Hoffa for sharing the workaround on Stackoverflow pages.

BigQuery and Data Studio – final words

BigQuery can store an enormous amount of data while Data Studio can visualize it perfectly, almost in real-time. No wonder using both is becoming more and more common in the business world. 

We only touched on the basics of Data Studio but you should be already able to visualize plenty of your data on beautiful dashboards. If you’re only getting started with BigQuery, be sure to check out our other tutorials available in the BigQuery category of our blog.

Many thanks for your time and see you around!