The Beginner’s Guide to Using BigQuery with Google Data Studio

bigquery and data studio

In this guide, I want to explore the combined capabilities of BigQuery and Google Data Studio. In particular, the most effective ways to connect both tools and visualize the output of your SQL queries. Since this will be a series of a few posts, I am starting with an easy one here that also covers Google Cloud Platform (GCP) account setup. For the more advanced users, feel free to skip.

That being said, even though this post is meant for all knowledge level, it assumes a bit of basic SQL. Therefore, if you are not clear about a certain section, feel free to comment and I will elaborate or try to help ๐Ÿ™‚

1. What is Google BigQuery?
2. How to get a FREE Google Cloud Platform (GCP) account?
3. How to create a GCP project?
4. How to configure your datasets for Data Studio within BigQuery?
5. How to run a SQL query and visualize the output in Google Data Studio?
Scenario 1: Explore your SQL query in the Data Studio Explorer (Temporary)
Scenario 2: Connect a BigQuery table with Data Studio (Permanent)

What is Google BigQuery?

BigQuery is Google’s fully managed enterprise data warehouse, which uses SQL and enables you to query massive amounts of data in a matter of seconds. A lot of marketers see it as a complex “analyst-only” product, but that’s not necessarily the case. In fact, BigQuery does a lot of the technical behind-the-scene work for you (eg. infrastructure, database management), so you can spend more time analyzing and extracting insights from your data. To me, BigQuery is truly a performance tool that when paired with Google Data Studio can enhance your marketing efforts.

How to get a FREE Google Cloud Platform (GCP) account?

BigQuery is a service within the broader Google Cloud Platform (GCP) family of products. Therefore, you would need to get a GCP account in order to access it. Luckily, Google offers a Free Tier account with a $300 credit for the first 12 months. Plus, specifically for BigQuery, you get 1TB of queries per month for free. Of course, the account application requires a credit card, but you won’t get charged if you stay within these limits.

How to create a GCP project?

Once you are ready with your account setup, you will be redirected to the GCP dashboard. Think of this as your project summary, where all of your services will be running. The first step is to create a new project, so you can later use it for BigQuery. As I mentioned, a “Project” organizes all your GCP resources.

new project in google cloud platform

You can simply do that by following these four simple steps:

  1. Select the project dropdown (#1 in the picture)
  2. Click on “New Project” (#2 in the picture)
  3. Then select “Project name”, no need to worry about “Location” at this point
  4. Click “Create”

It might take up to a minute for the project to configure, but you should see a checkmark when done.

google cloud platform project created successfully

How to configure your datasets for Data Studio within BigQuery?

Now, let’s navigate to BigQuery, as so far we have been only exploring the Google Cloud Platform interface. You can do that by expanding the navigation menu and clicking on BigQuery. The option can be found under “Big Data”.

google cloud platform navigation menu with bigquery selected

Once you are in the BigQuery interface, you would have to add a data source that we can run queries against. In this case, we are going to use a public dataset that includes twelve months of obfuscated Google Analytics 360 data. It is a fairly rich set of data that you can do plenty with, even beyond this guide.

When you click “Add Data” (#1 in the picture), choose the “Explore public datasets” option and then search for the Google Analytics dataset. The “View Dataset” selection will open in a new window and all available datasets would show on the left side of the BigQuery UI.

There is an additional step that we have to complete before we begin to run and write our queries. This is not going to make sense immediately, but it will be used later when we have to save the output of our query into a table. This specifically relates to Scenario 2: Connect a BigQuery table with Data Studio (Permanent).

Firstly, click on your project name and then on “Create Dataset” (#2 on the picture). You can proceed with the default values Google provides for location, encryption and expiration. The only field you have to fill in is the dataset id. Then simply confirm by pressing “Create dataset” again.

How to run a SQL query and visualize the output in Google Data Studio?

Now we are ready to write a query. The Query Editor is standard and follows the SQL syntax. I have pre-written a sample query that pulls user-level data, total visits and page views. You can directly paste that into you query editor and press “Run”.

SELECT fullVisitorId,
       visitId,
       trafficSource.source,
       trafficSource.medium,
       totals.visits,
       totals.pageviews,
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`

The result of the query should look something like this. BigQuery will always provide you with a preview when you run any query, which is always handy.

query results in google bigquery
Scenario 1: Explore your SQL query in the Data Studio Explorer (Temporary)

More importantly, once you run your query, you can click on the “Explore Data” button, which will immediately open the Data Studio Explorer report. This will allow you to visualize the output of the query fairly fast. This is a great solution for quick data manipulations, while you are still in exploratory mode.

If you decide to save your Data Studio explorer findings into a proper report, then you can select the “Create a new report and share” option under “Share”. However, I want to show you also what happens behind the scene when you press that button, as it is going to be different than our second scenario.

When the Google Data Studio report loads, what actually happens is you are querying the data directly from BigQuery. In order to see that, you will first have to edit the data source (#1 in the picture) and then the connection; this is the button with the arrow called “Edit Connection”. This will lead you to the screen below. The “Custom Query” option renders the data based on the query you have already written. You can modify it directly in Data Studio as well. If you decide to do any changes, to confirm them, you have to click on “Reconnect”.

Scenario 2: Connect a BigQuery table with Data Studio (Permanent)

This scenario is a bit different. You can actually create a permanent table that can be queried and updated in BigQuery. Instead of going for the Data Studio Explorer, we are going to save the results of our query into a BigQuery table (#2 in the picture)

saving a query output into a bigquery table

When you pick the “BigQuery” table option, you have to select a dataset and table name. This is the reason why we created a dataset within your project early on. You are now able to simply select it in the menu (#1 in the picture below). This saves a lot of time and it doesn’t interrupt our flow.

save query results popup screen in bigquery

When your table is ready, you can go back to Data Studio and pick the BigQuery connector. You will find your newly created table under “My Projects”. It’s that easy! However, remember, this is a permanent table that is visualized in Data Studio. Not an active query, so any modifications have to be done in BigQuery.

bigquery data studio connector my projects menu

In addition, you can customize the data refresh rate for BigQuery data sources in Google Data Studio. The custom refresh interval lets you choose from increments of 1, 2, 3, 4, 5, 10, 15, 20, 25, 30, 40, 50 minutes, and from 1 to 12 hours.

Bonus: If you just want to experiment with different datasets, you can also select “Public Datasets” using the BigQuery connector.

I hope this was helpful! If you have any questions, feel free to DM me onย LinkedInย orย subscribe to my newsletter.


Want to learn more about Google Data Studio and BigQuery?

If you want to learn how to build powerful data visualizations and further analyze marketing data with SQL take a look at my courses on Udemy. Each course includes practical hands-on exercises that will give you a chance to play around with real datasets.

4 comments
  1. Thanks for your effort. Do you have course which teaches about DataStudio + GA with all possible scenario (I mean with practical examples.)

    1. Hey Ravi, appreciate the comment! ๐Ÿ™‚

      My current Data Studio course has Google Analytics practical examples in it that focus on website funnel creation, so that might be of interest. You can take a look at the full curriculum here.

      Thanks,
      Lachezar

  2. Hi Lachezar thank you for the information, love the details!

    Just looking for some info on the cost on accessing BigQuery table data through Google Data Studio, if I decided to save my data to a BQ table through GDS, would that incur any cost other than the BigQuery storage cost itself?

    Looking forward to your answer, thank you!

    1. Hey Ivan, thanks for reaching out, glad you enjoyed the article ๐Ÿ™‚ To answer your question, since Google BigQuery is a paid product, you will incur usage fees if you connect it to Data Studio. Often, those fees comprise the storage itself, plus cost per query of the tables where the data is hosted. Luckily, BigQuery query pricing provides the first 1TB per month free of charge and if you are using the free tier, you should be all set. The costs to use BigQuery are fairly low. Here is a resource that could be helpful to you from the official Google site.

Leave a Reply

Your email address will not be published. Required fields are marked *