Create a pacing graph with Google Sheets

Time series comparison for month over month data is one of the most frequently requested tasks. Here's how to do it in Google Sheets. Read "Everything Starts Out Looking Like a Toy" #172

Nov 20, 2023

Hi, I’m Greg 👋! I write weekly product essays, including system “handshakes”, the expectations for workflow, and the jobs to be done for data. What is Data Operations? was the first post in the series.

This week’s toy: Crocs designed to look like Grimace. Fast fashion, or business-to-business marketing trend? Either way, your feet will be purple. Edition 172 of this newsletter is here - it’s November 20, 2023.

If you have a comment or are interested in sponsoring, hit reply.

The Big Idea

A short long-form essay about data things

⚙️ Create a pacing graph with Google Sheets

Example of a simple cumulative sum graph with totals by the day of the month graphed for the previous 90 days

As an operator, how many times do you get asked: “how are we doing this month vs last month? (Or vs. some previous month). Probably a lot.

Whether you are comparing sales performance among reps, tracking the pacing of a metric by the historical day of the month, or just want to see how you are doing, cumulative sum graphs are the data visualization that you want to build to get more insight into how series data changes over time.

What’s a cumulative sum graph?

A cumulative sum graph adds up the column values of a series by row. In an example data set like the one below, this means that on September 1, the team saw 104 leads and then saw and additional 89 leads on September 2, meaning the cumulative total of leads for the team was 193 on September 2.

Building the graph in this way lets us compare different time periods (in this case, the day of the month by a month-over-month comparison) visually to see the changes in pace represented by the slope and the intercept. Said differently, that means that a better initial pace through the month will appear both steeper (up and to the right) and at a higher reference point overall.

Cumulative sum graphs display velocity and performance, which is key for any operations team to view.

How do you build this graph in Google Sheets?

First, you need a time series data set representing the information you’d like to display. I used a version of this lead arrival data set and added a random date in the last 90 days to establish a baseline. If you’d like to follow along, make a copy of the data here.

Next, you’ll need to pivot the data to achieve the rows and columns for the data. Transform the arrival date of the lead in the original data set (column B) as follows:

Use =Day(B2) to get the ordinal day of the month
Use =Month(B2) to get the ordinal month

Now you can pivot this information, selecting Day as the row, Month as the column, and the ID value of your lead (anything that’s unique) as the value for counting.

The resulting pivot table looks like this:

Google Sheets is missing something

You might have noticed a problem with this pivot table. There isn’t a straightforward way to create a cumulativeSum function (you can use tricks like this, but it’s not simple).

The pivot table gives you the count for that date, but not the cumulative count for the month where the count for day 1 is added to the count for day 2 and so on. This means you will need to create a parallel helper table to drive your visualization.

Here’s how to do that:

Copy the columns and rows of the pivot table to an adjacent set of columns so that you have the shell of the same pivot table
Start with the cell for the first month, day 1 and reference the pivot table’s cell for month 1, day 1, e.g. something like =B3
Move your cursor down one row to Month 1, Day 2 and create a formula to add the total from the cell above to the day total for day 2 created in the pivot table, or something like =B4+I3 where B4 is the total number of leads counted in the pivot table for day 2 and i3 is the cell for your new table in the row above
Now copy this formula all the way down your row
Now copy the column’s formulas to the right for as many months are in your data set
Touch up the column headers to match the month name, e.g. replace 8 with “Aug”. You can use =TEXT(DATE(2023, C2, 1), "mmm") as a shorthand to take the month number and format it as a short month name

Great! Now you have a data set that you can visualize for a month-over-month comparison.

Creating the graph in Google Sheets

Highlight the rows and columns in your new graph, select the ribbon option to Insert Chart, and you’re off and running!

Style your graph, rename the title, and you’re ready to share this with your team.

If you configure a reverse ETL automation to push the data into Google Sheets, you’ve now built a dashboard for your team to review progress month over month. Add filters on your original data set or on the data viz to constrain the date range, and your team will thank you!

What’s the takeaway? Cumulative sum graphs are a powerful method to compare time series among like groups and periods. Use them to measure rep performance, historical performance against goal, or other time-based measures. Once you get the hang of it in Google Sheets, you’ll be able to build this data structure elsewhere.

Links for Reading and Sharing

These are links that caught my 👀

1/ Roundabout x7 - If you’ve driven a roundabout (or “Rotary” as they call them in Massachusetts), you might find this massive roundabout daunting.

It’s 7 circles in one, and improves traffic flow. Watching it from overhead is sort of like watching a Rube Goldberg machine. This shouldn’t work, you think. But it does!

2/ Measuring if your north star is the right one - This brief post by Doubleloop is notable for the way it poses the question of measuring progress. “How do you know if your input metrics are any good?” We often assume that our metrics are correlated without checking that they are. This is a good reminder.

3/ Ai makes games - The addition of vision to GPT4 has made simple games possible from a picture. If building blocks of code become easier to build, what will we build next?

What to do next

Hit reply if you’ve got links to share, data stories, or want to say hello.

Want to book a discovery call to talk about how we can work together?

The next big thing always starts out being dismissed as a “toy.” - Chris Dixon