Measuring rates of change

Introduction

In Comparing time periods, we looked at a revenue metric across two time periods (for example, month over month) to see if the revenue was growing, shrinking, or plateauing over time.

Now, what if you want to calculate a MoM trend over 24 months of data? The good news is that you don’t have to repeat the time period comparisons 23 times. You can calculate the rate of change across all of the time periods in a dataset at once!

If you’re a spreadsheet aficionado, you might be used to calculating rates of change using a row or column offset. In this tutorial, we’ll calculate rates of change using self-joins in the query builder.

We’ll create the following trend reports:

Dollars per month:

Dollar change from last month to this month:

Percentage change from last month to this month:

We’ll also show you how to set up a chart tooltip like this:

Creating a monthly trend report

When you’re doing time period comparisons month over month using the sumif function, you have to “hard code” or specify the exact months you want, like this:

SumIf([Payment], between([Date Received], "2022-10-01", "2022-10-31"))

But hard coding gets tedious real quick—you’d have to add a new expression every month, just to keep up! To get the monthly trend over all of the months in the Invoices table at once (even as the table gets updated with fresh data), we want to set up a table like this:

Here’s how we’ll create the table and display our findings:

1. Calculate the total payments per month to get “Sum of Payment”.
2. Add a column to get “Previous Month”.
3. Reshape the data with a self-join to get “Sum of Payment (Previous Month)”.
4. Calculate “MoM Dollar Change”.
5. Visualize and interpret our results as:
   • Dollars per month.
   • Dollar change from last month to this month.
   • Percentage change from last month to this month.

Summing payments by month

First, we’ll sum the payments per month in the Invoices table to get a results table that looks like this:

1. Click + New > Model > Use the notebook editor.
2. Click Raw Data > Sample Database > Invoices.
3. Click Pick the metric you want to see”.
4. Select “Sum of…” > “Payment”.
5. Click Pick a column to group by.
6. Select “Date Received: by month”.

You can click the play button to preview the results (but don’t click save just yet):

Getting the previous month

Next, we’ll create a custom column called Previous Month to store a one month offset from Date Received: Month.

1. Click Custom column.
2. Enter the following datetimeSubtract expression:

     datetimeSubtract([Date Received], 1, "month")
   

3. Name the custom column “Previous Month”.
4. Optional: go to Metadata in the top nav to format the date in “Previous Month”.
5. Save the model and name it “Monthly Payments ($)”.

Creating a self-join on the previous month

Now, we’re going to reshape our data so that each row looks something like this:

We’re going to do this “reshaping” by joining Monthly Payments ($) with itself (also known as a self-join).

1. Click + New > Question.
2. Click Models and select the “Monthly Payments ($)” model.
3. Click the Join data button (venn diagram icon) to start a left join.
4. Click the back arrow (<) in the popup menu.
5. In the popup menu’s search bar, type Monthly Payments ($) and select the model.
6. For the join condition, select Previous Month: Month = Date Received: Month (make sure “Previous Month” is on the left).
7. Click the dropdown arrow beside Monthly Payments ($) (inside the Join data box).
8. Uncheck “Date Received” and “Previous Month”.
9. Click Visualize.

Now, each row of our model contains all of the information for a month-over-month calculation.

To rename the second “Sum of Payment” column:

1. Click the Settings button (beside Visualization).
2. Click the three-dot menu beside the second “Sum of Payment”.
3. Change the Column title to “Sum of Payment (Previous Month)”.
4. Click Done.

Calculating the month-over-month difference

Finally we’ll add a column with that summarizes the month-over-month difference.

1. Open the query builder and click Summarize.
2. Click Custom column icon (square grid with a plus symbol).
3. Enter the expression:

   [Sum of Payment] - [Question ID → Sum of Payment]
   

4. Name the column “MoM Dollar Change”.

Note that “Question ID → Sum of Payment” is the previous month’s “Sum of Payment”. You can type “Sum of Payment” to select the right column name (you’ll probably see a different question ID).

Visualizing a monthly trend as a line chart

To look at the total payments each month, we can plot “Sum of Payment” by “Date Received: Month”.

If we do this directly from a question on the Invoices table, we’ll be able to say that “the revenue increased from $7,701.40 to $9,236.80 between Jan 2021 and Feb 2021”.

But since our model has a column with the MoM change per month, it’ll be easier to say that “the revenue increased by $1535.40 from Jan 2021 to Feb 2021”.

1. Click Visualization button.
2. Select the Line chart.
3. For the X-axis, select “Date Received”.
4. For the Y-axis, select the first “Sum of Payment”.

Optional: add points to the line chart:

1. Click the three-dot menu beside “Sum of Payment”.
2. From the popup menu, select Style.
3. From Show dots on lines, click On.

To clean up the chart tooltip so that it doesn’t show the previous month’s “Sum of Payment”:

1. Open the query builder.
2. From the Join data box, click the dropdown arrow beside Monthly Payments ($).
3. Uncheck “Sum of Payment”.

This kind of trend chart can show us how the absolute value of revenue changes over time. If we were to analyze this (fake) data, we can see that the trend in payments:

• Grows rapidly month over month (the upward trend is nearly vertical).
• Seems affected by seasonality (the trend resets and repeats every 12 months).
• Behaves a bit suspiciously (the trend line never goes down within each 12 month cycle).

Visualizing rate of change in dollars

Okay, now that we know our revenue is growing, we might want to know when this growth is happening the fastest. To get an intuitive sense of the rate of change, we’ll plot the MoM change (in dollars) over time:

1. Click Settings button (beside Visualization).
2. Change the Y-axis to “MoM Change”.

Compared to the monthly trend chart, the rate of change chart shows us how fast the revenue is changing over time:

• The upward spikes have slight curved pattern, so growth might have been accelerating over the course of a 12 month cycle.
• The seasonal effect causes revenue to decelerate very fast (sharp downward spike) between each June and July.

Visualizing rate of change as a percentage

In the dollar rate of change chart, we have to use the visual slope of the line to estimate the speed of revenue growth.

We can also look at the quantitative rate of change by calculating the percentage change from month to month. So, instead of saying that “the revenue increased by $9326.80 from Jan 2021 to Feb 2021”, we can say that “the revenue increased by 19.94% from Jan 2021 to Feb 2021”.

To add MoM percentage to our current question:

1. Open the query builder.
2. From the Custom column section, click the plus button (+) to add another column.
3. Enter the formula (and remember to replace the Question ID):

   [MoM Dollar Change] / [Question ID → Sum of Payment] * 100
   

4. Name the custom column “MoM Percent Change”.
5. Click Visualize.
6. Click Settings button (beside Visualization).
7. Change the Y-axis to “MoM Percent Change”.

Now, if we compare this chart to the rate of change in dollars, we can get more specific details:

• The relative change is greatest from Oct 2020 to Dec 2020 (likely since the payments per month are small).
• The growth rate fluctuates over the course of each 12 month period (so the revenue isn’t growing by 2%, 4%, 8%, etc).
• The annual decline in revenue corresponds to the near 100% drop each July.

Examples of more trend reports

To create:

• Weekly or quarterly reports, change the group by (step 6) to use “Date Received: by week” or “Date Received: by quarter”.
• Trends of averages, change the summary function (step 4) function to “Average of…”.
• SQL queries that match the query builder logic, check out Working with dates in SQL.

Further reading

• Visualizing trends
• Calculating LTV
• Ten common analytics mistakes startups make