Time Series Visualizations in Google Data Studio
Including visualizations with running totals and with missing data.
When people need to use a word processor or a spreadsheet but don’t want to pay for Microsoft Word or Excel, they typically turn to Google Docs and Google Sheets. Likewise, if you need to make dashboards of data visualizations but don’t want to sign up for Power BI or Tableau, you can use Google Data Studio with the Google account you likely already have.
In this post, I’m going to use Google Data Studio to create some time series visualizations, including visualizations with running totals and with missing data.
Tracking Property Inspections
The Data
Say I work for a property management company, and my department needs to have 134 properties inspected between the first workday of January and February 15, 2022. The company has hired four teams to go around the city to do this work.
In a spreadsheet, there is one row per property; each row includes the address, square footage, as well as other property info, and there are also empty cells to be filled in once inspections are complete. These empty cells are to contain the date of inspection, the name of the team that did the inspection, as well as cells about the assessments.
The Goal
Suppose at this moment, we’re part way through the project, and our goal is to keep track of its progress overall and by team. Let’s say today is January 31, and inspections have been logged in the spreadsheet from January 3 up through today.
Here is the top portion the two relevant columns for this goal sorted by Date Inspected. (All values were generated at random for this Medium post.)
Starting the Dashboard
Ideally, this spreadsheet should be in Google Sheets on a Google Drive because Google products typically work best with other Google products. You can, however, upload files.
Go to datastudio.google.com, and open a blank report to get started. You will be prompted to add a data source. Then you can navigate to the Google Sheets file.
Visualization 1: Track Overall Project Progress
The toolbar has a button to Add a chart. Click on that, and you will be given options of a variety of charts. I’m choosing the first time series chart.
A chart is created automatically.
Whenever you add a chart and have it selected, a properties panel opens on the right with two tabs: Data and Style. The default metric in this case is Record Count that adds up the total inspections per day.
But I don’t want the ups and downs of a daily chart. I want a chart that tracks the project’s progress to date.
I click on Record Count under Metric, and it opens up a new window where I can customize things. I changed Running calculation where it had None and change it to Running sum, and I also renamed the Metric to “Running total”. Now I get this chart:
This chart looks more like what I want, but because the teams don’t work weekends, the line drops to zero on days where nothing is completed. That looks weird.
I can fix this issue in the panel’s Style tab. Under General by default Line To Zero is chosen for Missing Data.
This can be changed to Linear Interpolation. I make the change and voila:
I’m almost where I want to be, but remember, I’m using this chart to track the project’s overall progress. Again, the project ends February 15, and the teams must complete 134 inspections by then.
I need do two more things:
- In the Data tab, I change the default date range from Auto to Custom. A calendar opens up, I choose the existing beginning date and the end date as February 15.
- In the Style tab, there is a section called Reference Lines. I choose Add a reference line, assign a constant value of 134, and change the label to “Goal-134”.
The teams seem to be on track for meeting our goal.
Visualization 2: Track Daily Team Activity
The previous visualization is concerned with overall project progress, but now I want to take a look at how busy the teams have been.
I created a pivot table in another tab in the Google Sheet with the parameters Row: Date Inspected, Column: Team Name, and Value: Count of Date Inspected. That gives me a table like this:
Data Studio considers each tab, or worksheet, within a Sheets file to be its own data source. To work with this new table in Data Studio, I have to add it using the Add data button in the toolbar.
Data Studio can easily recognize a header row, but because the pivot table essentially has two header rows above the actual data, it can mess things up a bit. Here are a couple of ways to get around any issue:
- When adding the data, you can find an option where you can manually choose the cell range to use. (For the visualizations I’m creating in this post, I am not choosing the Grand Total row.)
- You can reference the needed cells in another tab and choose that tab when adding data.
For this visualization, I’m using four metrics, one for each team:
And I get the following graph:
In this screenshot, I have my mouse hovering on January 10, and the chart pop ups a team breakdown for that day.
Visualization 3: Track Progress per Team
I see that the teams can be busy some days as not as much other days. In general, how are the four teams doing compared to each other?
I copy the previous visualization and paste it into another spot on the dashboard. Then in the Data tab, I can edit each metric by clicking where it says SUM. Note how it changes to a pencil symbol.
Opening that up window, I now go to the Running calculation dropdown and choose Running sum:
And as I did with the first visualization above, I choose Linear Interpolation in the Style tab to account for the lack of data on weekends. I repeat this process for all four teams, and I get this chart to gain a visual understanding of how all the teams are performing over time:
In this post, I focused on time series charts, but naturally, I can also make bar graphs, pie charts, maps, gauges, and other types of visualizations.
