There is no shortage of COVID-19 dashboards in our industry so don’t worry we’re not throwing another one into the ring. What we have for you today is a Google Sheet that you can connect to your own dashboards to provide additional context on the Australian landscape.
We have compiled a list of annotations for Australia in a variety of categories including government updates, restrictions, closures and travel bans. If you are seeing major behavioural changes in your analytics, we hope this will shine a light on the trends. Keep reading if you want to learn how to merge your Google Analytics data with these annotations in Data Studio.
Mehdi Oudjida enhanced our simple annotation method using blended data. It’s a great approach which works well with our use case with a few adjustments.
- Add your Google Analytics Data to the report via the connector.
- Add the Google Sheet data via the connector using the URL option and connect to the Australian Timeline sheet.
- Create a blended data source.
Resource > Manage Blended data > Add a data view
- Add your Google Analytics data source first
Your GA data needs to be first so this becomes the base data of the left join. If you add it second, you will only see Google Analytics data for the dates with annotations.
Use date as the join key and bring in all the metrics you will be using in your dashboard. If you are going to be doing any aggregated scorecards, it is important to bring in the base components of calculated fields as well. If you bring in something like Bounce Rate, it will try to sum the field like this:
Heres some common calculated metrics and how to recalculate them with their components in Data Studio:
- Bounce Rate = SUM(Bounces) / SUM(Sessions)
- Avg. Order Value = SUM(Revenue) / SUM(Transactions)
- Pages / Session = SUM(Page Views) / SUM(Sessions)
- Avg. Session Duration = SUM(Session Duration) / SUM(Sessions)
- e-Commerce Conversion Rate = SUM(Transactions) / SUM(Sessions)
- Add in your Data Runs Deep - COVID-19 Timeline Australia data source
Use Date as the Join Key and then bring all the fields from the sheet as dimensions and Record Count as a metric. Rename the Date dimension so it’s easier for you to distinguish later.
- Create filter to correct for duplication
Now that we are ready to create chart elements, we want to highlight a really important point. The blended data is a “one to many” join. There are some dates with multiple annotations. In any scorecards or a time series charts, you will have duplication on the days with multiple entries like this:
We have created a field in the data source called Update Number which records a 1 for the first entry that day. If you apply a filter that includes only Update Number = 1 or Is Null (for days without an update), you will avoid duplication in your charts.
- Create a time series chart
Create a time series chart from the blended data source with your Google Analytics date as the dimension. Add whichever metric you’re interested in and also the Record Count from our Google Sheet data source. Apply your “Update Number is null or 1” filter and tick the "Apply Filter" option.
Mehdi’s blog gives you more guidance on formatting but remember to move your Record Count field to the secondary axis and change it to bars.
- Create a table with the COVID-19 annotations.
Create a table from your blended data source. Bring in the dimensions you want, we used Timeline Date (renamed to Date), Category, State, Update, Reference. We sorted by Timeline Date in descending order, then as a Secondary Sort, Priority in ascending order. Priority will rank the government updates and restrictions higher than other categories like Sport / Event cancellations.
- Add any additional charts
Using the same blended data source, create any other charts. Remember to add the Update Number is null or 1 filter. Below is an example of creating a calculated scorecard for average session duration using the components.
- Click on chart to filter it
That’s pretty much it! Something cool using this method is that in the view mode, when you click on a single date you are interested in on the time series chart, it will filter the annotations to show you what was going on with restrictions on that date.
We will maintain this file going forward but if you want to enrich it with your internal insights, feel free to make a copy of the spreadsheet and add your own rows. Same goes if you are based in another country and want to pull in the headlines relevant to your region.
