3 Ways to add Annotations to Data Studio time series charts

Márton Kodok
Apr 1 · 7 min read

At the time of the writing Data Studio doesn’t offer out of the box Annotations on time series charts. You can however add your annotations as seen below in easy ways.

Timeseries chart with two annotations applied
Timeseries chart with two annotations applied
Time series chart with two annotations applied

We will cover 3 ways how to add Data Studio Annotations:

  • Adding by using a calculated field
  • Using a CSV file and data source blending
  • Using Google Sheets, some optional pivoting and data source blending

1. Add Annotation by using calculated field

To get going, Edit your data source (with the Pencil icon ), when you see your current fields, find and choose Add a field, name it after your event eg: Google Algorithm Update and add the below formula:

Section to add a calculated field to a datasource, using a CASE formula
Section to add a calculated field to a datasource, using a CASE formula
Section to add a calculated field to a datasource using a CASE formula

Timestamp is an existing field in our primary dataset. Please be aware that you might have a different field, or date format, and you need to experiment a bit with it. You might have 2019–09–30 or 20190930 so do retries until you get data on your screen.
Now you have your new field in the Available Fields to choose for your custom annotation.

Add the newly created field to the chart. Whenever you are required to choose an aggregation choose SUM.

You will notice no immediate visible change.

Customizing the Style

You need to customize settings on the Style tab for your chart.

By having the above steps done, you ensure that you will get the annotation to display as a bar that uses the whole 100% high if you set axis max to 1. Also you might get some awkward grid lines if you omit to set Custom Tick interval 1, so make sure all the above steps are done. In the Style tab you can customize the color of the bar as well.

The width of the bar is automatically inferred based on the data-points-width, so if you have a large days span it will be very thin. In this situation you might want to add a date range match for your custom field to spawn multiple days, and be wider on the chart.

2. Use a CSV for annotations source

While calculated field allows quick experiment with Data Studio Annotations, it’s not productive when you need to apply on multiple charts, reports and you need to carry forward on weekly basis your annotation definition. For this, the best is if you store your Annotation definitions in a CSV file or Google Sheet. I named this section CSV, but it can be a Google Sheet as well.

To make Annotations work from a tabular dataset, you have to ensure the following column structure.

Image for post
Image for post

Organize your CSV by having each timestamp on rows, and the name of the annotation as a column as seen in the picture above. Whenever you want a new annotation you add a new column and it’s respective date row.

Add this file to your report as a new Data Source.

Blending

Now that you have your annotation data source added to your report, you need to Blend with your primary data source. To blend the two you need to have a join key, in our example this was “timestamp”, as also the primary datasource was having this key for time series data. You may need to adapt your annotation data source to match the format for the join key, it could be a date in different date format.

The process is the following:

  • In the report Dashboard below the data source for a chart find option to Blend data
  • Start the Blending wizard
  • Select from your data source the Annotation data source
  • Set the join keys, in our example: timestamp
  • On the annotations data source: add all fields to the Metrics group
  • On the primary data source: add all fields if they are missing to the Metrics group
  • No need to add fields to other groups like Dimensions, Date range, Join Keys.
  • On the right you should have a live view of your blended data source, give it a name, and make sure you have all your primary source fields and the annotation fields you just added
  • In case a field shows up duplicate, find from which data source panel you need to remove

In the end you end up having a screen like:

Image for post
Image for post

Warning, blending will work only if you a have a join/blending column and the data matches exactly to your primary data source. In database terms this is a left outer join. So set wisely the column and value for your CSV rows.

At this time your chart shows up unconfigured, you may need to redrag some metrics, and now you can drag your newly added fields to the metrics, and follow the guidelines to set the Styling to get it as a bar annotation.

At any time you need to change or extend your annotations, you can just add the new annotation definition to your CSV or Google Sheet and in Datastudio, you need to go to Manage datasource, and try to Reconnect it, so new fields will show for you. You then follow the procedure to add to the chart the metric and style it according to the previous sections.

3. Google Sheet for annotations source

Maintaining CSV files and keeping in sync can be additional work, but you can choose to have the Annotations data in Google Sheet. In this chapter we will describe how to store your Annotations as a row database, and how setting up Pivoting can help to get them to blend with your primary timeseries datasource, which could be a BigQuery table for example.

Image for post
Image for post
Annotations defined as a list

The easiest way is by organizing your data in a rows of annotation time and labels. This way you don’t need to expand horizontally like a matrix, you extend only vertically by adding new rows.

Having this data source shared across your company, can be used by different people: marketing team, analysts, or SEO experts to mark them on Google Analytics reports.

In this format the data doesn’t blend with a primary datasource, as we need the series andthe labels to be available as column headers, like a matrix you’ve seen in previous section.

Pivoting the data into columns results the desired view starting row 2.

Image for post
Image for post
Pivoted view of the annotation list

Start pivoting your annotations data from menu Data -> Pivot Table

The wizard guides you through the following steps:

  • Choose a range for your data: choose a large range something like: Sheet1!A1:B25. This means you will adapt your pivot table to support in the future 25 rows.
  • Choose to place the Pivot table into a New Sheet

Now you need to configure your Pivot settings:

Image for post
Image for post

Build out the above configuration:

  • Choose for rows the “timestamp” column in ascending order. Uncheck Show totals we don’t need that.
  • Choose for column the “label” column in ascending order. Uncheck Show totals we don’t need that.
  • For the Calculated Field section you need to build a formula, it can be equals 1 and choose SUM, as seen in the screenshot.
  • Also to avoid empty cells as we have chosen 25 rows, we need to set a Filter that is configured for label and Cell is not empty rule.

When ready you should have a sheet like shown above, with a line for Calculated Field, and a table with your annotations transposed from rows to columns, and each date on it’s own row, with value 1 in each corresponding cell. This value 1 will be the height of the right axis when will be joined with the primary dataset.

Whenever you add a new annotation to your first sheet, this pivoted table automatically gets updated.

Now it’s time to add this as Datasource to your Data Studio report. At the process of add, you will be asked the Sheet and the data range.

Image for post
Image for post

You need to choose the Sheet that was created specially for the Pivot data, and you need to edit a bit the formula to skip the first line. In order to do this, you need to go back to your Sheet, and right click Row 1, and choose Hide. This way your Calculated Field line will be hidden, and you will see rows numbering starting from 2. In your Data Studio adding wizard, you see an option to skip Hidden rows. Make sure that is unchecked.

At this time you have Reconnected your datasource, and ready to do the Blending section already described above.

Getting new Annotations to Data Studio

Whenever you added a new annotations entry to your CSV or Google Sheet, you need to Reconnect your Data source so the new series get updated.

To do that, you need to go Manage data sources inside Data Studio, find you annotations data source, and choose reconnect, then you can visually the Fields, but you prompted with the changes on the screen.

Image for post
Image for post
After annotation definition changes a Reconnect dataset screen with the field changes

Once you confirm the changes, you are able to drag to the charts the new metrics and configure their styling to match the described process.

This concludes how we at REEA.net apply Annotations on Data Studio time series.

Google Cloud - Community

Google Cloud community articles and blogs

Márton Kodok

Written by

Speaker at conferences, a Google Developer Expert top user on Stackoverflow, software architect at REEA.net, co-founder IT Mures, life-long learner, mentor

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Márton Kodok

Written by

Speaker at conferences, a Google Developer Expert top user on Stackoverflow, software architect at REEA.net, co-founder IT Mures, life-long learner, mentor

Google Cloud - Community

A collection of technical articles and blogs published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store