Creating a Covid-19 Dashboard with Google Open Dataset, BigQuery and DataStudio
In my last article we created a project in Google BigQuery to connect to their Covid-19 Open Dataset. We will be using this connection now to build a tracking dashboard (see final product here) in Google DataStudio. So if you haven’t done that yet, go and do the first section of the set up here.
This article will go through the setup of a basic dashboard using the powerful and simple to use Google DataStudio which is free to use! To start off, head over to DataStudio first as you’ll need to accept their terms and conditions before being able to get the data connection working. Just have a read and accept the T&Cs as shown below and finish all 3 steps.
The next step is to start a New Report in the homepage of DataStudio which will prompt you to select a new data source. Assuming you have set up the BigQuery Covid-19 data connection previously or have some other source in there, select BigQuery. Then Custom Query, select your Covid-19 project and enter the query below into the Query box. We wished to just look at the Australian cases so we have filtered the query to that, but feel free to remove that or change it to the countries you want to monitor.
SELECT * FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE country_name = 'Australia';
Note: You can also export the data directly from BigQuery by entering this same query in the Editor then running it and then Clicking “Explore Data”.
Once you’ve added the custom query you will be straight into a default view of the dashboard with a single prefilled but not very useful table as shown below, so first things first you may want to click on the title and rename the Dashboard as you wish.
So, the next step is to create a useful chart, we are going to start with an overall trend chart for total cases in Australia split by state (Qld, NSW, Vic etc.). To do this click on “Add a chart” and choose the “Time Series Chart”. I’ve also added a Date Control to the top right of the dashboard to allow users to hone in on a specific date range and 2 text boxes to add a title for the page and the chart itself.
Once the time series chart is added, the date range will likely be already chosen as the date range dimension, otherwise, click on this setting and choose “date”. We also want a breakdown dimension and have chosen “subregion1_name” which is the name of the states. The metric to be plotted in this chart is “cumulative_confirmed”. This will produce the following:
You’ll notice that the overall cases in this chart is called “null” as the field doesn’t contain value for subregion1 for the overall Australia numbers. So a custom field was made to replace the subregion1_name dimension in this chart. To do this, click on the chart and down the bottom of the list of dimension there is an option “Add a Field”. Then enter a name for the field, “States” and the following case statement into the query field. This will replace all nulls with “Total” and use the names of the states for all the other values. Click Done and then replace the subregion1_name dimension in use with the new States dimension created yielding the final chart as shown below.
Now, cumulative cases might have been a good metric at the start of the pandemic, but nowadays most of those people have hopefully recovered or unfortunately have died. Some of the better metrics are how many active cases there are and how many new cases there are on any given day. So let’s add another chart that has a time series of the new cases confirmed per day in each state. To do this add another time series chart with the date as the date axis and the States as the breakdown dimension but this time use the metric “new_confirmed”. Producing the following. You’ll notice that through the style options for the charts we have also moved the legend to the right hand side to make it easier to read.
Adding a filter to the page makes it easier for the user to explore the data, in this case we wanted to filter by state to reduce the noise in the charts when needed. To do this click on “Filter Control” which will allow you drop a filter onto the page which for us defaulted to States but otherwise just choose the States dimension in the filter options.
So let’s add one more feature to this overview page. At the top of the page some simple headline metrics to let the user know how many cases there have been to date, how many new cases there have been, total deaths, total recoveries. Also we will add a comparison to the previous days numbers to allow the user to see the change day to day.
To build a headline metric, click to Add a chart and choose Scorecard. Add the metric “cumulative_confirmed” and click to edit this metric and rename it to “Total Cases”. Then modify the default date range to custom and choose Today minus 2 days for both the for and to dates. Also set the comparison date range to Today minus 3 days which will automatically add a comparison on each scorecard between the current value and the previous days value.
Now add a Filter to this scorecard which we have named “RemoveTotal” and Exclude where subregion1_name is Null. This will remove all of the total values for the day as the metric is already a summation of the other individual states cases. Otherwise you will have double the number of cases in your scorecard. Now repeat this scorecard building process for the “new_confirmed”, “cumulative_deceased”, “cumulative_recovered” and “Active Cases” which doesn’t exist yet but we will now build this new metric which isn’t in the original dataset.
Finally, let’s build a final metric for the scorecards, “Active Cases”. Active cases is calculated by taking the Total Cumulative Cases and removing the number of Deaths and Recovered cases. First start creating your final scorecard and click on the metric, this will have a list of metrics and at the bottom the option to “Add Field”. Click this and the following screen will appear, fill in the formula as shown to build the metric and name it “Active Cases” then just click Apply. Set up the scorecard as per the other metrics and you’re done!
We have finished a simple overview dashboard, pretty much all of the different charts are added in the same way so we are going to add some more pages to the dashboard with interesting metrics. Feel free to do the same and see what interesting visualisations you can monitor. Here is an image of the final product which you can also see and interact with by clicking here.
That’s it, enjoy using DataStudio with BigQuery, which is remarkably simple to use and powerful for a free dashboarding tool from Google.
