Google Data Studio is one of the most popular tools for visualizing data. It can be used to pull data directly out of Google’s suite of marketing tools, including Google Analytics, Google AdWords, and Google Search Console. It also supports connectors for database tools such as PostgreSQL and BigQuery, it can be accessed at datastudio.google.com. In this article, we will learn to visualize BigQuery Data with Google Data Studio.
[box type=”note” align=”” class=”” width=””]This article is an excerpt from the book, Learning Google BigQuery, written by Thirukkumaran Haridass and Eric Brown. This book will serve as a comprehensive guide to mastering BigQuery, and utilizing it to get useful insights from your Big Data.[/box]
The following steps explain how to get started in Google Data Studio and access BigQuery data from Data Studio:
SELECT trafficsource.medium as Medium, COUNT(visitId) as Visits
FROM `google.com:analytics- bigquery.LondonCycleHelmet.ga_sessions_20130910` GROUP BY Medium
This query will pull the count of sessions for traffic source mediums for the Google Analytics account that has been exported.
A bar chart should appear, with the Traffic Source Medium and Visit data from the query you ran:
A properties prompt should also show on the right-hand side of the page:
Here, a number of properties can be selected for your chart, including the dimension, metric, and many style settings. Once you’ve completed your first chart, more charts can be added to a single page to show other metrics if needed.
For many situations, a single bar graph will answer the question at hand. Some situations may require more exploration. In such cases, an analyst might want to know whether the visit metric influences other metrics such as the number of transactions. A scatterplot with visits on the x axis and transactions on the y axis can be used to easily visualize this relationship.
The following steps show how to make a scatterplot in Data Studio with the data from BigQuery:
SELECT trafficsource.medium as Medium, COUNT(visitId) as Visits, SUM(totals.transactions) AS Transactions FROM `google.com:analytics-
bigquery.LondonCycleHelmet.ga_sessions_20130910` GROUP BY Medium
The report should autoselect the two metrics you’ve created.
Data Studio includes a map chart type that can be used to create simple maps. In order to create maps, a map dimension will need to be included in your data, along with a metric. Here, we will use the Google BigQuery public dataset for Medicare data. You’ll need to create a new data source:
SELECT CONCAT(provider_city,", ",provider_state) city, AVG(average_estimated_submitted_charges) avg_sub_charges FROM `bigquery-public-data.medicare.outpatient_charges_2014`
WHERE apc = '0267 - Level III Diagnostic and Screening Ultrasound' GROUP BY 1
ORDER BY 2 desc
This query will pull the average of submitted charges for diagnostic ultrasounds by city in the United States. This is the most submitted charge in the 2014 Medicaid data.
Right click on the City dimension and select the Geo type and City subtype. Here, we can also choose other sub-types (Latitude, Longitude, Metro, Country, and so on).
Data Studio will plot the top 500 rows of data (in this case, the top 500 cities in the results set).
Hovering over each city brings up detailed data:
Data Studio can also be used to roll up geographic data. In this case, we’ll roll city data up to state data.
Once completed, the map will be rolled up to the state level instead of the city level. This functionality is very handy when data has not been rolled up prior to being inserted into BigQuery:
We explored various features of Google Data Studio and learnt to use them for visualizing BigQuery data.To know about other third party tools for reporting and visualization purpose such as R and Tableau, check out the book Learning Google BigQuery.
Getting Started with Data Storytelling
What is Seaborn and why should you use it for data visualization?
Pandas is an effective tool to explore and analyze data – Interview Insights
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…
View Comments
Excellent post. Are there any pointers to look out for , with respect to costs incurred on BigQuery, with each request made by end-users ?
Hi Mahesh,
Thanks for the feedback. I'm glad you enjoyed the post. This link will give you more clarity on how pricing is for each product offered by Big Query. https://cloud.google.com/bigquery/pricing