top of page
  • Chris Green

Using Blended Data to Add Annotations onto Time Series in Google Looker (Data) Studio

Updated: Oct 18, 2022

I love Looker (Data) Studio, but one of the features I miss the most is the ability to annotate charts with key dates/types.

Coming from SEO, my most frequent annotation for reporting is Google Algorithm updates, but you could want to add in dates of key above-the-line marketing activities, new product launches, and much more. In lieu of Google supporting this somehow (Please! I think we all want this feature), I've been creating a similar effect by blending analytics data with a list of key dates from Google Sheets.


A Time Series Chart in Looker (Data) Studio with data annotations.


In case you're wondering, here's a live version of my blended chart.

I like this approach for a few reasons:

  • The data source for the update dates can be used across multiple charts

  • I only need to update one spreadsheet to add the data across all charts which use it

  • It works if you need to change/alter the date ranges

I don't like that it's a little hacky and relatively fiddly to setup, but it works well enough for now.


How to do it


It's as easy as the following (click to jump to a specific section or read on for the full walkthrough)

  1. Create Your Google Algorithm Update List

  2. Create Your Timeseries for Traffic/Revenue

  3. Link your Google Sheets as a Data Source

  4. Blending User Data & Algorithm Updates

  5. Configuring Your Annotated Chart

Create Your Google Algorithm Update List

You can get your algorithm update data from whichever source you wish (or complete your own), but I like to borrow my data from Google Search Central.


As we're building this in Google Sheets we can use IMPORTXML() to nab the update dates directly.


Here's the formula with XPATH to speed things up: =importxml("https://developers.google.com/search/updates/ranking","//td/h3")

When you drop that into sheets, it'll pull the data down.

Finally, you'll need to select all the values & then format the numbers to "date".

Remember, this process is for grabbing Google algorithm dates from a specific page. In reality, you can have whichever key dates you like. Just remember that you need a list of dates, formatted correctly.


Create Your Time Series for Traffic/Revenue

In this example, we're going to assume you want to use Google Analytics (UA, no GA4 here - sorry) to plot a time series of either traffic over time. This could be revenue or anything which can be plotted like this - I just want to keep things simple for now.

So, over to Looker (data) Studio and create your first data source.

I like to use the Demo Account (Google Merchandise Store) for things like this - stops me from getting into trouble. You can grab that from here in case you needed it.


Select the view you want, and then create a time series chart with your dimension to "Date" and metric to "New Users".

Link your Google Sheets as a Data Source

Now, let's link up that Google Sheet you created as a data source. You just need to select "Google Sheets" and then search for the sheet you have just created.

If you can't find the sheet, it likely means you created it in a different Google Account. You can always open it via the URL if that helps.

Now, create a bar chart with this data.


It doesn't look all that amazing at the moment but stick with me.

Blending User Data & Algorithm Updates


If blends break your brain a little, don't worry - I like to do this the lazy way.


Select the two charts you have just made, right click & select "Blend Data". Once the new chart has been created, delete the other two and you'll be left with this:

Change this to a combo chart, using chart settings on the right under "setup":

Not quite there, yet. Next, open up the blend settings - we need to tweak things a little.

Click "Cross Join"

Here you'll need to change it to a "left join" and then select the Date dimensions from each data source.

Click Save & then rename "Record Count" to something a little more useful. I changed this to "algorithm updates".


Nearly there now. Save the blend, and head back to the chart setup.

Configuring Your Annotated Chart

You want to set "Date" as the dimension (this will be the date from our Google Analytics data source). Then add "New users" as your first metric and then "Algorithm Updates" as the second.


Finally, ensure the sort is set to "Date" by Ascending, otherwise, you won't get a very useful time series.


You may notice something really important, there are no algorithm updates on the chart!


There are x2 more things we need to do there. Firstly, change the date range to something longer and edit the chart formatting itself.


For the date range, I've gone for an advanced one which shows 12 (full) months. You can set what you like, but this one seems clean and useful.

Once you've done this, click "Style" & set series 2 to "Pubs, Bars" and the Axis to "Right". This is what will ensure your algorithm updates are represented by the bars. Also, shifting the axis to the right will mean that the bars will display the full height of the chart.

For the final styling changes, I like to make the grid colour "transparent" (otherwise it's really ugly) and shift the legend below the chart.


This is looking a lot closer now!

The final change is a closely-guarded secret. I feel slightly ashamed by this - and you will too - but trust me it works. You see the right axis? 0 to 1 isn't exactly useful and it doesn't look great. Time to address that...


So using a rectangle to hide the axis isn't the highlight of this post - but unless I'm missing something obvious, it's the most effective!


Wrapping Up

This isn't a perfect method - and your mileage may vary - but I have built a number of dashboards that use a method like this to give additional context to marketing data.


At-a-glance cues like this on reporting dashboards can really quickly assist analysis and drawing insights from data - a real must.

Give it a go & let me know how you get on!


Other Solutions

Turns out there are a few similar versions of this solution out there. I wanted to call out the creativity and resourcefulness of the community working with Looker Studio here:


Go check out their blogs and the other super-smart things they're doing - it'll be worth it.

5 comments

Recent Posts

See All
  • Twitter
  • LinkedIn
bottom of page