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 🙂
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.
You can simply do that by following these four simple steps:
- Select the project dropdown (#1 in the picture)
- Click on “New Project” (#2 in the picture)
- Then select “Project name”, no need to worry about “Location” at this point
- Click “Create”
It might take up to a minute for the project to configure, but you should see a checkmark when done.
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”.
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.
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)
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.
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.