Calculating Monthly Recurring Revenue (MRR) Using FiveTran ELT, NetSuite, and Snowflake

Monthly Recurring Revenue (MRR) is a vital financial metric for businesses with subscription-based revenue models. To calculate it, we've established an ELT (Extract, Load, Transform) pipeline using FiveTran and Snowflake, pulling sales and revenue data directly from NetSuite.

Before we delve into the details, you must be aware of two custom fields in NetSuite: CUSTCOL_RRSTART and CUSTCOL_RREND. These fields must be manually updated with the contract start and end dates, respectively, for each sales transaction.

Step 1: Create the Sales Order Details View

To begin with, we create a view in Snowflake named mrr_1_so_detail in the silver.finance schema. This view extracts the essential sales order details, including contract start and end dates, contract length, total contract amount, and amount per contract period. We also convert the status codes to their meaningful equivalents for easy interpretation.

Here's the SQL script for creating this view:

create or replace view silver.finance.mrr_1_so_detail as
select 
    customer.companyname,
    transaction.trandate,
    transaction.tranid,
    transaction.transactionnumber,
    transaction.type,
    transaction.memo,
    line.custcol_rrstart as contract_start,
    date_trunc('month', contract_start) as contract_start_month,
    line.custcol_rrend as contract_end,
    -- determines length of contract and drops partial months
    case
        when day(contract_start) = 1 then 
            datediff('months', date_trunc('month', contract_start), date_trunc('month', contract_end)) + 1
        when day(contract_end) + 1 >= day(contract_start) then 
            datediff('months', date_trunc('month', contract_start), date_trunc('month', contract_end))
        else 
            datediff('months', date_trunc('month', contract_start), date_trunc('month', contract_end)) - 1
    end as contract_months,
    dateadd('months', contract_months, contract_start_month) as contract_end_month,
    classification.name as class,
    classification.fullname as class_full,
    line.netamount * -1 as amount,
    iff(contract_months = 0, 0, amount / contract_months) as amount_per_period,
    -- assigns textual description to status codes
    case 
        when status = 'A' then 'Pending Approval'
        when status = 'B' then 'Pending Fulfillment'
        when status = 'C' then 'Cancelled'
        when status = 'D' then 'Partially Fulfilled'
        when status = 'E' then 'Partially Fulfilled'
        when status = 'F' then 'Pending Billing'
        when status = 'G' then 'Billed'
        when status = 'H' then 'Closed'
        else 'NA'
    end as status,
    month_dim.year,
    month_dim.month
from 
    fivetran.netsuite_suiteanalytics.transactionline as line
left join fivetran.netsuite_suiteanalytics.transaction as transaction 
    on transaction.id = line.transaction
-- creates a separate record for each month in the contract period
left join gold.dates.my_month_dimension as month_dim 
    on month_dim.first_day_month >= contract_start_month 
    and month_dim.first_day_month <= contract_end_month
left join fivetran.netsuite_suiteanalytics.classification 
    on classification.id = line.class
left join fivetran.netsuite_suiteanalytics.customer 
    on customer.id = transaction.entity
where 
    custcol_rrstart is not null
    and type = 'SalesOrd'
order by tranid desc, year desc, month desc

Step 2: Preparing the Legacy Revenue Recognition Details

Next, we prepare the legacy revenue recognition details using another view mrr_2_je_legacy in the silver.finance schema. We handle legacy transactions differently from current ones to ensure accurate MRR calculations.

Here's the SQL script for creating this view:

create or replace view silver.finance.mrr_2_je_legacy as
select
    companyname as companyname,
    elementdate as trandate,
    recordnumber as tranid,
    revenueelement.fullname as transactionnumber,
    sourcerecordtype as type,
    '' as memo,
    revrecstartdate as contract_start,
    date_trunc('month', revrecstartdate) as contract_start_month,
    revrecenddate as contract_end,
    case
        when day(contract_start) = 1 then datediff('months', date_trunc('month', contract_start), date_trunc('month', contract_end)) + 1
        when day(contract_end) + 1 >= day(contract_start) then datediff('months', date_trunc('month', contract_start), date_trunc('month', contract_end)) 
        else datediff('months', date_trunc('month', contract_start), date_trunc('month', contract_end)) - 1
    end as contract_months,
    dateadd('months', contract_months, contract_start_month) as contract_end_month,
    classification.name as class,
    classification.fullname as class_full,
    salesamount as amount,
    iff(contract_months = 0, 0, amount / contract_months) as amount_per_period,
    revenueplanstatus as status,
    month_dim.year,
    month_dim.month
from
    fivetran.netsuite_suiteanalytics.revenueelement
left join fivetran.netsuite_suiteanalytics.customer 
    on (revenueelement.entity = customer.id)
left join fivetran.netsuite_suiteanalytics.classification 
    on classification.id = revenueelement.class
left join gold.dates.my_month_dimension as month_dim 
    on month_dim.first_day_month >= contract_start_month 
    and month_dim.first_day_month <= contract_end_month
where 
  sourcerecordtype = 'journalentry'
order by revrecstartdate asc;

Step 3: Merging Current and Historical Data

In this step, we combine current sales order details with historical revenue recognition details. We accomplish this through a union operation in the view mrr_3_detail, situated within the silver.finance schema.

Here's the SQL script for creating this view:

create or replace view silver.finance.mrr_3_detail as
select * from silver.finance.mrr_1_so_detail
union
select * from silver.finance.mrr_2_je_legacy

Step 4: Creating the Waterfall

Afterward, we create a 'waterfall' view, mrr_waterfall, within the gold.finance schema, which shows the total MRR per month for each company.

Here's the SQL script for creating this view:

create or replace view gold.finance.mrr_waterfall as
select 
    companyname,
    class_full,
    sum(amount_per_period) as sum_amount_per_period,
    year,
    month
from silver.finance.mrr_3_detail
group by companyname, class_full, year, month

Step 5: Final Visualization

Lastly, we utilize Snowflake's pivot function to reshape our data for the final visualization. This can also be achieved using PowerBI for a more graphical representation.

Here's the SQL script for the pivot operation:

select * from (select * from gold.finance.mrr_waterfall where year = '2023')

pivot(sum(sum_amount_per_period) for month in ('1', '2', '3', '4','5','6','7','8','9','10','11','12'))

as p;

This approach presents an efficient way to calculate MRR by taking full advantage of the capabilities provided by FiveTran ELT, NetSuite, and Snowflake. It offers the potential for accurate MRR tracking and useful insights into your business' revenue performance.