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 🙂
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.
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.
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.
You can simply do that by following these four simple steps:
It might take up to a minute for the project to configure, but you should see a checkmark when done.
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”.
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.
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.
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”.
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)
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.
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.
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.
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.
I've used Zapier for years to automate small tasks and create the lead generation system…
When I started creating custom GPTs for various tasks, one of the areas that I…
Over the past few months, I've had the opportunity to dive into Canva Magic Studio,…
In my nano tips series on ChatGPT so far, I've covered Data Storytelling and Visualization,…
It has been a while since my last post, but I finally managed to wrap-up…
Although cliché, the phrase "a picture is worth a thousand words" has never been more…
View Comments
Thanks for your effort. Do you have course which teaches about DataStudio + GA with all possible scenario (I mean with practical examples.)
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
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!
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.