Tracking revenue is crucial to understanding the health of your business. Whether your long-term growth is declining, has plateaued, or is heading upwards helps you define your long term strategy and goals. Many sales cycles are biased towards weekends, beginning-of-month, or end-of-quarter deals, and it can be hard to know day-to-day if your sales numbers are trending on target.
We track several KPIs as they progress over the course of the month. In this post, we are going to look at monthly recurring revenue (MRR) historically across the year, and segment each month by its progress up to the current date.
Constructing the Query
On today’s date, September 20th, we want to be able to compare our progress to July 20th and August 20th. We can add the context for the MRR at the end of the month by selecting the revenue made up to the 20th for each month, and contrast it against the total end-of-month revenue.
We have a table subscription_history with four columns
- dte: date
- mrr: MRR from the customer on date dte
- yesterday_mrr: MRR from the customer on the previous day from dte
- first_spend: true when this is the first dte for a new customer
We want to make a bar chart with the upgrade and new recurring revenue generated for each month, and segment the bars into two slices: revenue generated to the same day-of-the-month as today’s date, and revenue generated afterward.
We start off by creating two CTEs, new_cust will be revenue from new customers, and upgrades will be positive upgrades. We are only going to look at rows where MRR is greater than the previous MRR to get a snapshot of revenue without considering customers who downgraded their payment plan.
with new_cust as ( select dte , sum(mrr) new_mrr from subscription_history where first_spend = true group by 1 ) , upgrades as ( select dte , sum(mrr - coalesce(yesterday_mrr, 0)) upgrade_mrr from subscription_history where first_spend = false and mrr > coalesce(yesterday_mrr, 0) group by 1 )
We select upgrade_mrr by subtracting mrr on dte from yesterday_mrr. The yesterday_mrr value can be null, so we use the coalesce function to return 0 when yesterday_mrris null.
Next, we will combine our two CTEs into a base table, joining by date.
, base as ( select dte , coalesce(upgrade_mrr, 0) upgrade_mrr , coalesce(new_mrr, 0) new_mrr from new_cust full outer join upgrades using (dte) where dte <= date_trunc('day', now()) and dte > now() - interval '5 months' )
Now we will aggregate table base by month, taking the sum of the upgrade and new customer MRR for each month. We compute both the total for each month, and the sum for each month up to the current date using extract(‘d’ from now()).
, agg_totals as ( select date_trunc('month', base.dte) agg , sum(upgrade_mrr) agg_tot_upgrade_mrr , sum(new_mrr) agg_tot_new_mrr from base where date_trunc('month', base.dte) < date_trunc('month', now()) group by 1 ) , agg_to_date_numbers as ( select date_trunc('month', base.dte) agg , sum(upgrade_mrr) agg_td_upgrade_mrr , sum(new_mrr) agg_td_new_mrr from base where extract('d' from base.dte) <= extract('d' from now()) group by 1 )
We can combine the aggregation tables to create the summary table by using a full outer join on our two aggregation tables.
, pre_labels as ( select agg , agg_tot_upgrade_mrr , agg_tot_new_mrr , agg_td_upgrade_mrr , agg_td_new_mrr , agg_tot_upgrade_mrr - coalesce(agg_td_upgrade_mrr,0) as agg_upgrade_mrr_final , agg_tot_new_mrr - coalesce(agg_td_new_mrr,0) as agg_new_mrr_final from agg_to_date_numbers full outer join agg_totals using (agg) )
Finally, we select the rows we want to plot by limiting our data to the last five months.
select agg "Month" , agg_upgrade_mrr_final "Upgrade MRR final" , agg_new_mrr_final "New MRR final" , agg_td_upgrade_mrr "Month to date upgrade MRR" , agg_td_new_mrr "Month to date new MRR" from pre_labels where agg > date_trunc('month', now()) - interval '5 month'
We plot our table as a bar chart and get our final result!
Each bar represents the total new MRR that month. Based on this graph our monthly growth looks healthy! This month is on track with new customers and doing above average with plan upgrades. Happy growth 🙂