Build a Google Data Studio report using BigQuery Public Data-Sets

In this post I’ll show you how to build a dashboard using one of the many Public data-sets available to users in BigQuery.

According to Google, BigQuery is a “fully managed, petabyte scale, low-cost analytics data warehouse”. Personally, I’ve barely scratched the surface with how one could use BigQuery for marketing. But using one of their public data-sets is a good way to get an idea of the scale of the data available, and how you could potentially use it for your marketing needs.

How to build a dashboard using BigQuery public data-sets

The first step is to create a BigQuery account. Here’s a guide for getting started from Google Cloud.

Big Query – Getting Started

Once you’ve setup an account, you should be able to add BigQuery as a data source for your Data Studio report.

Add BigQuery data source to google data studio . big query

When selecting which data you want to use you’ll need to choose ‘Public data-sets’ then choose the relevant ‘Billing Project’ from your account (you’ll need to set up your account before you can do these steps). Then under ‘Public data set’ you’ll see the many different data-sets available. For the example dashboard I’ve chose usa_names and for the Table I’ve chosen usa_1910_current.

Adding public data-sets from big query into google data studio

We then get to see the fields in our data set. You may need to adjust some of them. For example, I needed to make sure State was set as a ‘Region’ data type and ‘Year’ was set to a Date type and not a number.

a public-data set

Once we have our data set up correctly, we can proceed to building the report in Google Data Studio!

If we create a few tables, we get a sense of the massive scale of the data in this public data-set table.

several tables of data in google data studio

The finished US names Data Studio report

Below is the interactive report.

Below is an example of the types of filtering you can do. We can see which names are popular in which US states.

showing how to interact with the map of the united states showing most popular names

If we look in BigQuery’s Query History section we can see how Data Studio has been interacting with the data-set.

bigquery queries

I hope you enjoyed this blog post about using the public data-sets available through BigQuery.

Here’s a good article about how BigQuery can help your marketing; Google BigQuery: A Tutorial for Marketers,

Other Blog Posts

If you liked this post, you might like some of my other blog posts.