1. Growth rate
Tracking change over time lets you see whether a company is growing or shrinking.
You'll learn two ways to calculate change, deltas and growth rates, applying them to the MAU KPI to see whether or not Delivr is growing in active users.
2. Deltas - query
Let's set up the CTEs needed to calculate deltas.
First of all, what is a delta? A delta is simply the difference between two values. Here, you'll calculate the delta of the current and previous months' active users.
The maus CTE should be familiar by now. maus_lag fetches the previous month's MAU and stores it in the last_mau column using the LAG function.
Now that you have both the current and previous months' active users in one row, you can calculate the MAU deltas.
3. Deltas - result
In the final query, select the month and the MAU from the maus_lag CTE, then subtract the last_mau column from the MAU column to get the MAU delta.
In the results, the first month's MAU delta is the first month's MAU, since there is no previous month to which to compare it. July, the second month, has an MAU delta of 103, so 103 more users were active in July than in June. If the MAU delta is negative, that means that LESS users were active in that month than in the previous month.
4. Deltas - pitfalls
This is a good start, but you can do better. The delta is a raw, absolute number, which doesn't show the full picture.
The delta really only shows one of three things about a variable: The variable is decreasing if the delta is negative, stable if the delta is zero, and increasing if the delta is positive.
It'd be better if you could turn the delta into a relative value or percentage that scales.
5. Growth rate - overview
Enter the growth rate. The growth rate is a percentage that tracks change in a variable over time relative to that variable's initial value. The growth rate's formula is that variable's current value minus its previous value (that variable's delta) divided by the previous value.
Here's an example. Let's say that Delivr had 50 active users last month, and 67 active users this month. The MAU growth rate this month is (67 - 50) / 50 = 0.34, so the MAU grew by 34% this month.
When the interval separating the current and previous values is a month, the growth rate is called the month-on-month growth rate.
6. Growth rate - query
Let's write a query to calculate the MAU month-on-month growth rate. The query uses the same CTEs used in the deltas query.
In the final query, subtract last_mau from mau, then divide the result by last_mau to get the growth rate.
Use ROUND to round the result to the second decimal for a cleaner result. Remember to cast the numerator to NUMERIC; otherwise, ROUND will return an error.
The first month's growth rate is meaningless, since it has no previous month, so ignore it. The second month has a growth rate of 84%, so MAU increased by 84% that month.
7. Growth
Growth is the primary way to measure how a KPI changes over time. In the following exercises, you'll write queries that first calculate a KPI's delta, then expand on that to get the growth rate.