Visualizing BigQuery Public Datasets in Data Studio

Visualizing BigQuery Public Datasets in Data Studio

Practicing with different types of data is a great way to develop your skills in data analysis and visualization. Each dataset you encounter presents you with new challenges, and working through these challenges will help diversify your experience as an analyst and build your capability in your visualization tool of choice.

An excellent source of open datasets for use in Google Data Studio are the Google Cloud Public Datasets. These datasets are compiled by Google through partnerships with various organizations and are hosted in BigQuery. There are datasets on real estate, crime, sports, and everything in between. Using Data Studio’s native BigQuery connector, this wealth of data is only a few clicks away!

In this post, we’ll go through a step-by-step process for connecting to BigQuery public datasets in Data Studio. We’ll also showcase our visualization using the San Francisco Street Trees dataset. Check it out below!

Visualizing a BigQuery Public Dataset in Data Studio

Before you begin, you must have a Google Cloud project and billing account setup. Note that you will only incur costs if your exceed BigQuery’s free tier, which at 1TB of queries per month is generous. Although BigQuery also charges for storage, the public datasets are not stored in your project, so you won’t incur any storage costs when accessing them directly. If you are creating a Google Cloud account for the first time, you can also take advantage of the free trial.

Once you have your Cloud project and billing setup, here’s how to connect to a BigQuery public dataset from Data Studio:

  1. Create a new Data Studio report and add a new data source.
  2. Select the BigQuery connector from the list of built-in Google connectors. If this is your first time connecting to BigQuery, you will need to authorize Data Studio to connect to your BigQuery projects.
  3. Select Public Datasets. Then select your Google Cloud Platform billing project.
  4. Select a dataset from the list of public datasets. Then select a table within the dataset. More information about each dataset is available in Cloud Marketplace. For our example report below, we have connected to the san_francisco_trees dataset and the street_trees table.
  5. Save your data source and add it to your report.

It is always a good practice to familiarize yourself with the data before you start analyzing or visualizing. This lets you get to know the available fields and helps ensure that you will interpret the data correctly. For BigQuery public datasets, a good place to start is the Cloud Marketplace directory page for each dataset (e.g. San Francisco Street Trees).

You can also view the schema of dimensions and metrics within the data source in Data Studio. Here’s an example of what the Street Trees data source looks like. Data Studio imports the field names, types, and descriptions from the BigQuery schema.

Beyond getting to know the schema, adding charts to your report will let you see what the data looks like. You may find that some cleanup, transformation, or calculation is needed to obtain your desired visualization. For example, we added a “Latitude, Longitude” dimension to our street trees dataset by concatenating the individual latitude and longitude fields. We also split the species dimension into “Common Name” and “Species.”

We created this simple visualisation to explore how different types of trees are localized to specific areas of San Francisco.

View in Data Studio

For example, filtering on Monterey Cypress, one particular location stands out. You can zoom in on the map to confirm that this road is Sunset Boulevard, running through San Francisco’s Sunset District.

On a normal map, this is likely the most information you would get. But this is Google Maps! We can use Street View to take a virtual stroll down Sunset and see these trees for ourselves. Just click on the yellow Pegman, drag-and-drop him somewhere along the street, and bam you’ve got Street View inside your Data Studio report!

We hope this inspires you to try out BigQuery public datasets with Data Studio. If trees aren’t your thing, don’t worry. You’ll find public datasets on everything from Bitcoin to basketball to bike rentals. Let us know what you create!

Complete list of posts in our 2020 Data Studio series:

1. Visualizing the COVID-19 Pandemic in Google Data Studio
2. Creating a Google Analytics Dashboard in One Click
3. Using Google Maps in Data Studio
4. Visualizing BigQuery Public Datasets in Data Studio (this post)
5. Measuring Web Vitals – Part 2: Monitoring in Data Studio
6. Using Parameters in Data Studio
7. How to Style Links in Data Studio
8. Using Filter Controls in Data Studio
9. Extracting a Theme from an Image in Data Studio
10. Building a Google Analytics 4 Dashboard in Data Studio
11. How to Convert Text to Dates in Google Data Studio
12. 12 Tips for Enhancing Your Tables in Data Studio

By |2020-12-16T16:31:56-05:00December 4th, 2020|0 Comments
Categories: Data Studio

Leave A Comment