bonkerfield

Timeline Streamgraph from Google Sheets Data

I've made a new kind of visualization called a Timeline Streamgraph. It's a modification of a conventional streamgraph, flipped on its side so that time flows from bottom to top.

I built it this way to evoke the sensation we experience when we view online media timelines. Although a standard streamgraph can convey the same information, having to scroll down for older information tries to remind the viewer that, like media streams, the data is still being generated "in front" of them.

As an example, I generated this figure of political ad spend on Google Ads.

I can imagine a lot of other interesting uses so I've made the viz very easy to replicate and modify. The coolest part is that it pulls data directly from a Google Sheet so I can easily keep the data fresh by just copy and pasting the new data into the sheet.

Below I have a tutorial that explains how to take my code and build one yourself.

How to Make a Timeline Streamgraph

To replicate this figure with your own data you just need to

Fork a gist or codepen

I've made the code available on gist or on codepen so you can copy it from whichever is easiest for you.

Fork from codepen

If you have a codepen account, you can view this pen and click "Fork" in the bottom right corner to make a copy that you can edit.

Fork a gist

If you have a Github account you can view this gist and "fork" a copy of it that will make it editable by you. When you do this the project will be set up at https://gist.github.com/GITHUB_USERNAME/ID_OF_PROJECT.

If you want to view your visualization you can use the amazing bl.ocks.org from Mike Bostock. Just go to the following URL, using the same GITHUB_USERNAME and ID_OF_PROJECT that you see for your gist.

https://bl.ocks.org/GITHUB_USERNAME/ID_OF_PROJECT

You should see an exact copy of the original vizualization. Next you just need to modify it to point to your own data.

Putting Data in a Google Sheet

The viz just pulls from any published Google Sheet. It looks for data in three columns with the following format and headings.

For this demo, I wrote a query to get political ad spend from Google Ads data on BigQuery. But, keep in mind, you can use any data you want as long as it follows the key, date, value format. IMPORTANT, your data can not have any repeated key, date pairs. Make sure that the same data does not appear repeated for any individual key.

To allow access to your data, you need to publish the sheet. You can find the publish dialog here.

Select to publish the entire document as web page.

Connect D3 viz to Google Sheet

On codepen or gist you'll see at the top of the HTML there is a variable named "google_sheets_id." Fill that in with the corresponding key from your published google sheet. You can see the key directly in the url on the edit page.

If you're using Codepen, then the page should update right away with your new data. If you are using gist you'll need to save the gist, and wait a few minutes for the changes to propagate to bl.ocks.org.

Bonus: Add custom colors

I've also added a way to specify the colors from the same Google Sheet. You just need to add a second sheet with a column for key and a column for color. You need to have one color for every key on the first sheet. Any key that isn't included will show up black. Also, make sure that you don't change the order of the sheets.

Example uses

I'm very curious what other uses people come up with for this viz. If you try something out, please let me know and I'll a link to it here.

  • political ad spend 2020 - example from this blog post
  • will.stedden project history - visual curriculum vitae that shows all the projects I've worked on concurrently over the past few years

Discussion Around the Web


Join the Conversation