Master SQL query for SaaS startups analytics

We saw earlier the master table needed for SaaS analytics in order to get subscribers, MRR (monthly recurring revenues) and ARPPU
(average revenue per paying users) per acquisition channel and persona. From this base, and with the help of SQL craft we can get more insight on what is happening.

Presently, we just know if the MRR is increasing, stable or decreasing. For instance in the chart below, there is a drop in the MRR in early 2017. But just with this chart, it’s difficult to understand why and act.

MRR explained

What we need is a detail by various MRR variation explanation :

• New MRR : New customers generate an increasing in MRR.
• Expansion MRR : Existing customers that consume more service or take a more expensive billing plan (upsell and cross sell)
• Winback MRR : Customers that stopped using the service but are coming back.
• Lost MRR : Customers cancelling their subscription.
• Reduction MRR : Customer consuming less or switching to a cheaper billing plan.

Notice that we don’t split Lost MRR between those who come back at some point and those lost forever. That’s because this can change in the future and we should avoid chart that change with time.

With the chart below, you can easily see that early 2017 there was a issue with the Reduction MRR (maybe a repricing of the offer?). There was also a reduction in the New MRR. The next month was not very significant from an acquisition standpoint (New MRR in line) but more a correction of the previous Reduction MRR, now in Expansion MRR.

The query

The SQL query to generate those indicators is given below. In order to simplify a bit, I didn’t split by persona. The query works fine on PostgreSQL.

with lifetime_ext_1 as (
  select 
    customer_id, period, 
    mrr, 
    -- The cohort is the date of the first subscription period
    min(period) over (partition by customer_id ) as cohort,
    -- What is the previous period for this customers if any?
    lag(period, 1) over 
      (partition by customer_id order by period asc) as prev_period,
    -- What is the next  period for this customers if any?
    lead(period, 1) over 
      (partition by customer_id order by period asc) as next_period
  from saas.lifetime
),
-- We create a new CTE in order to access the new columns
lifetime_ext_2 as (  
  select *,
    -- Get the previous month MRR if any
    case when prev_period = period - interval'1 month'
        then lag(mrr, 1) 
            over (partition by customer_id order by period asc) end 
              as prev_mrr,
    -- Get the next month MRR if any
    case when next_period = period + interval'1 month'
        then lead(mrr, 1) 
             over (partition by customer_id order by period asc) end 
               as next_mrr,
    -- Nomber of month from cohort up to this period
    extract(year from age(period, cohort))::int*12
      + extract(month from age(period, cohort))::int as life_month
  from lifetime_ext_1
),
-- Active customers rows
active as (
  select customer_id, period, 
    cohort, life_month,
    1 as customer_count, 
    case when life_month = 0 then 1 else 0 end as new_customer, 
    0 as lost_customer, 
    -- Not a first time customer but no MRR last month? => Winback
    case when life_month > 0 and prev_mrr is null 
      then 1 else 0 end as winback_customer,
    mrr, 
    -- If the customer is in a first month
    case when cohort = period then mrr else 0 end  as new_mrr, 
    0 as lost_mrr, 
    -- Not a first time customer but no MRR last month? => Winback
    case when life_month > 0 and prev_mrr is null 
      then mrr else 0 end as winback_mrr, 
    -- If MRR is decreasing vs last month => reduction MRR
    case when prev_mrr is not null and prev_mrr > mrr 
      then prev_mrr - mrr else 0 end as reduction_mrr, 
    -- If MRR is increasing vs last month  => expansion MRR
    case when prev_mrr is not null and prev_mrr < mrr 
      then mrr - prev_mrr else 0 end as expansion_mrr
  from lifetime_ext_2
),
-- Churning customers rows (one period after their last presence)
churners as (
  select customer_id, (period + interval'1 month')::date as period, 
    cohort, 
    life_month+1 as life_month,
    0 as customer_count, 0 as new_customer, 
    1 as lost_customer, 0 as winback_customer,
    0 as mrr, 0 as new_mrr, mrr as lost_mrr, 0 as winback_mrr, 
    0 as reduction_mrr, 0 as expansion_mrr 
  from lifetime_ext_2
  -- When there is no MRR in the next period (and no rows therefore)
  where next_mrr is null
),
-- Merging active customers rows and churning rows
fusion as (
  select * from active
  union all 
  select * from churners
)
select *
from fusion
-- Avoiding to predicting that everyone will churn.
where period <= (select max(period) from active)
order by customer_id, period