These last couple of days, I've been working on a dashboard for one of our Shopify apps so we can get a quick overview of how our app is doing revenue-wise.

One of the metrics we'd like to be able to see instantly is the month-over-month revenue growth rate. For a subscription-based SaaS (software as a service) product, this is a very important metric to track as it tells you if your business is growing at a healthy rate.

In our case, Shopify provides us a CSV of the entire payment history for each app. We simply import that CSV into a PostgreSQL database so we can run SQL queries against the data.

Below is a sample SQL query that you can run to figure out your month-over-month growth over the last 12 months. It will return both the difference in amount and percentage from the previous month.

(Note: The code below was only tested on PostgreSQL, but it may work on other database systems as well such as MySQL and MSSQL.)

SELECT to_char(payment_date, 'YYYY-MM') as revenue_date,
       sum(partner_share) as revenue,
       sum(partner_share) - lag(sum(partner_share), 1) OVER w
              as revenue_difference_amount,
       round((sum(partner_share) - lag(sum(partner_share), 1) OVER w) /
             (lag(sum(partner_share), 1) OVER w) * 100, 1)
              as revenue_difference_percent
FROM payment_history
WHERE payment_date > date_trunc('month', CURRENT_DATE) - INTERVAL '1 year'
GROUP BY revenue_date
WINDOW w as (ORDER BY to_char(payment_date, 'YYYY-MM'))
ORDER BY revenue_date DESC

The result will look something like this:

Note that my test data only goes all the way back to December 2016 so that was the last entry in the table. If your data goes much further and you want to see your month over month growth from the beginning, simply remove the WHERE clause in the query.

Just to have a bit more explanation on what the query does:

1. Convert the payment_date field to a string containing just the year and month so we can group the data based on this value.
2. Take the sum of the partner_share field, grouped by the revenue_date (i.e. the payment_date with just the year and the month). Shopify takes 20% from our app sales, so the partner_share field is the amount that we get from each app purchase.
3. The lag() function allows us to reference the value of a previous row over the result rows. It takes an integer as an offset value. In the query, I specified an offset of 1 to reference the previous row (i.e. 1 row before the current row).
4. The WINDOW clause in this case is more of a shortcut so we can just write OVER w instead of having to repeat OVER (ORDER BY to_char(payment_date, 'YYYY-MM')) a bunch of times.

Next up is calculating monthly churn, another important metric for SaaS. :)