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.

At Periscope 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 afterwards.

We start off by creating two CTEs, new_cust will be revenue from new customers, and upgrades will be 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.

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_mrr is null.

Next, we will combine our two CTEs into a base table, joining by date.

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()).

We can combine the aggregation tables to create the summary table by using a full outer join on our two aggregation tables.

Finally we select the rows we want to plot by limiting our data to the last five months.

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 :)

Thank you