Tracking KPIs using SQL for Effective Revenue Growth Analysis

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 🙂

Tags: data team