Skip to main content
Data Runs Deep Menu

Main navigation

  • Home
  • Who We Are
    • Careers
    • What We're Up To
    • What We Believe In
    • Ethical Policy
  • What We Do
  • Google Marketing Platform
    • Analytics 360
    • Tag Manager 360
    • Salesforce and GA360
    • Google Analytics App+Web
    • Data Studio
    • Optimize
  • Google Cloud Platform
    • Google BigQuery
      • Google BigQuery Training
      • Google Analytics Integration
  • Consulting
    • Strategy
    • Implementation
      • Google Analytics Audit
      • Google Tag Manager Audit
      • Enhanced Ecommerce Implementation
      • Google Tag Manager
    • Analysis
      • Google Data Studio
        • Google Data Studio Case Study - Mona
      • UX Analysis
  • Training
    • Five Day Google Analytics Online Workshop
    • Advanced Google Analytics Training
    • Google Tag Manager Training
    • Google Analytics 360 Training
    • Advanced Analytics Bootcamp
  • Partnerships
    • Conductrics
  • Blog
  • The Flying Beagle
  • Merch
  • Contact

Add COVID-19 Annotations to your Dashboards

COVID-19 timeline of events in Australia
By: Corinne Brooker
Posted in
  • Data Studio
  • Google Analytics
  • Google Data Studio
Article Date
April 29, 2020

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. 

List of COVID-19 updates

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. 

  1. Add your Google Analytics Data to the report via the connector. 
Add Google Analytics data source

 

  1. Add the Google Sheet data via the connector using the URL option and connect to the Australian Timeline sheet. 

https://docs.google.com/spreadsheets/d/1iRlAkpfsGXifdObKR5Y7Q2iGOEfT8UnTpRn0cyIdw-8/edit#gid=969901414 

Add Google Sheet data source via URL

 

  1. Create a blended data source.

Resource > Manage Blended data > Add a data view

  1. 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.

Blend data with Google Analytics source first

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:

Incorrect aggregation of bounce rate

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)

 

  1. 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.

Blend the Google Sheet with date as the join key

 

  1. 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:

Duplication caused by one-to-many join

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. 

Filter to apply to avoid duplication

 

  1. 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. 

Timeseries chart

 

  1. 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. 

Annotations table

 

  1. 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.

Re-calculate metrics like Avg. Session Duration

 

  1. 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. 

Click to filter

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.


Related Posts

  • Simple Annotations in Google Data Studio Time Series Charts

    Article Date
    March 26, 2019

Get in touch

To find out more, email us on hello@datarunsdeep.com.au or complete a quick contact form.

Get in touch

Talk to us: 1300 737 885

08:30 - 16:30, Monday to Friday. We love to chat

Visit us, we’re at:

Level 8, 313 Little Collins Street Melbourne, VIC 3000

Footer

  • Home
    • Who We Are
      • What We Do
      • What We're Up To
      • Careers
      • Contact
  • Training
    • Google Analytics Training Courses
    • Advanced Google Analytics Training
    • Google Tag Manager Training
  • Consulting
    • Analysis
    • Implementation
    • Strategy
    • Google Analytics Audit
    • Google Tag Manager Audit
  • Google Cloud Partner
  • Google Marketing Platform Sales Partner
  • Bitcoin accepted here
  • Registered Looker Consulting Partner Melbourne
  • Twitter
  • LinkedIn
  • Google Plus
  • Blog RSS feed
Copyright © 2009-2021 Data Runs Deep Pty Ltd. All Rights Reserved. Google Analytics is a trademark of Google Inc.
Website by Marin Sekesan