monthly recurring revenue, churn rate, net retention rate in the subscription world

  • 10 June 2022
  • 0 replies
  • 26 views

 

Very often, I work on subscription data, with a typical contract table with a start date, an end date, an amount and a contract length. How can you use this to understand your monthly recurring revenue, churn rate, and all these typical metrics typical of the ever increasing subscription world?
I used this article as a base, but expanding a bit and going into a bit more details

to make this work, we need to have a table where, on for every month, I need a row for every contract id from our contract table, the amount paid for this month, the amount paid the previous month and the amount paid the next month. 

  1. first step, you need to generate a date series table, as explained here:
    view: calendar {
    derived_table: {
    sql: SELECT date as calendar
    FROM UNNEST(
    GENERATE_date_ARRAY(DATE('2018-01-01'), DATE('2023-01-01'), INTERVAL 1 DAY)
    ) AS date group by calendar ;;
    }


    dimension_group: calendar {
    type: time
    timeframes: [date,month_num,quarter,quarter_of_year,year,raw, month, month_name, day_of_week, day_of_month, day_of_year]
    sql: cast(${TABLE}.calendar as timestamp) ;;
    }

    }

     

  2. in the model, we are then going to cross join our contracts table with this dates table:
    explore: contracts {

    join: calendar {
    type: cross
    relationship: many_to_one
    }
    }

     

  3. In the contracts view, we also created a new dimension called is_active_contract_calendar to check if a contract is active during each date of this calendar view.
    we also then created a measure called
    active revenue summing revenue only if the contract is active. The revenue needs to be a monthly fee (so if you contract amount is for a year, you didvie it by 12, if the amount is a monthly fee, you keep it as is. )
     

     dimension: is_active_contract_calendar {
    type: yesno
    sql: ${contract_start_date} <= ${calendar.calendar_date}
    and (${contract_end_date} is NULL or ${contract_end_date} > ${calendar.calendar_date});;
    }

    measure: active_revenue {
    type: sum
    sql: 1.0*${contract_value}/nullif(${contract_length});;
    filters: [is_active_contract_calendar:"Yes"]
    }
  4. then we used all the above to create native derived table called monthly_active_contracts for each account and for each month where we will get the active arr, the active arr of the previous month and the active arr of the following month usig lag and lead window functions:

    view: monthly_active_contracts {
    derived_table: {
    explore_source: contracts {
    column: active_revenue {}
    column: contract_id {}
    column: calendar_month { field: calendar.calendar_month }
    derived_column: prior_amt {
    sql: lag(active_revenue) over (partition by contract_id order by calendar_month asc) ;;
    }
    derived_column: post_amt {
    sql:lead(active_revenue) over (partition by contract_id order by calendar_month asc);;
    }
    derived_column: primary_key {
    sql: CONCAT(calendar_month,contract_id) ;;
    }
    filters: [calendar.calendar_day_of_month: "1"]
    sorts: [id: asc,calendar_month: asc]
    }
    }

    dimension: active_revenue {
    type: number
    }

    dimension: contract_id {
    }

    dimension: primary_key {
    hidden: yes
    primary_key: yes
    }

    dimension_group: calendar {
    label: "Calendar"
    convert_tz: no
    type: time
    timeframes: [raw,month]
    sql: ${TABLE}.calendar_month ;;
    }

    dimension: prior_amt {
    type: number
    value_format_name: usd
    }

    dimension: post_amt {
    type: number
    value_format_name: usd
    }

    measure: total_active_revenue {
    type: sum
    sql: ${active_revenue} ;;
    value_format_name: usd
    }

    dimension_group: current {
    hidden: yes
    type: time
    sql: current_date ;;
    }

    dimension: status {
    type: string
    sql: case when ${prior_amt} =0 then 'activation'
    when ${calendar_month}<${current_month}
    and ${post_amt} =0 then 'churn'
    when ${prior_amt} = ${active_revenue} then 'recurring'
    when ${prior_amt} <>0 and ${prior_amt} > ${active_revenue} then 'contraction'
    when ${prior_amt} <>0 and ${prior_amt} < ${active_revenue} then 'expansion'
    else null end ;;
    }

    dimension: net_mrr {
    type: number
    sql: case when ${prior_amt} =0 then ${active_revenue} --activation
    when ${calendar_month}<${current_month}
    and ${post_amt} =0 then -1*${active_revenue} --churn
    when ${prior_amt} = ${active_revenue} then 0 --recurring
    when ${prior_amt} <>0 then ${active_revenue} - ${prior_amt} --expansion contraction
    else null end ;;
    }

    measure: total_net_mrr {
    type: sum
    sql: ${net_mrr} ;;
    }

    dimension: is_activated {
    type: yesno
    sql:${prior_amt} =0 ;;
    }

    dimension: is_churn {
    type: yesno
    sql:${calendar_month}<${current_month}
    and ${post_amt} =0 ;;
    }

    dimension: is_recurring {
    type: yesno
    sql:${prior_amt} = ${active_revenue} ;;
    }

    dimension: is_contraction {
    type: yesno
    sql:${prior_amt} is not null and ${prior_amt} > ${active_revenue} ;;
    }

    dimension: is_expansion {
    type: yesno
    sql:${prior_amt} is not null and ${prior_amt} < ${active_revenue} ;;
    }

    measure: arr_activated {
    type: sum
    sql: ${active_revenue} ;;
    filters: [is_activated: "Yes"]
    }

    measure: arr_churn {
    type: sum
    sql: ${active_revenue}*(-1) ;;
    filters: [is_churn: "Yes"]
    }


    measure: arr_recurring {
    type: sum
    sql: ${active_revenue} ;;
    filters: [is_recurring: "Yes"]
    }

    measure: arr_contraction {
    type: sum
    sql: (${prior_amt}-${active_revenue})*(-1) ;;
    filters: [is_contraction: "Yes"]
    }

    measure: arr_expansion {
    type: sum
    sql: ${active_revenue}-${prior_amt} ;;
    filters: [is_expansion: "Yes"]
    }

    measure:churn_rate {
    type: number
    sql: ${arr_churn}*(-1)/ifnull(${total_active_revenue},0) ;;
    value_format_name: percent_2
    }

    measure:net_retention {
    type: number
    sql: ${arr_recurring}/ifnull(${total_active_revenue},0) ;;
    value_format_name: percent_2
    }

    we also added all the necessary metrics in a typical subscription scenario.

  5. then we just need to create an explore on this derived table, and join our contract table on it:

    explore: monthly_active_customers {
    join: contracts {
    relationship: many_to_one
    sql_on: ${monthly_active_customers.contract_id}=${contract.id} ;;
    }
    }

    and now we can have fun:

    gx7UcZYiE_Qi05mOZNgwf0x0x2bNHQQ8CDZI31tZGoiKjfYtLjdaD-k8Jft74dTleMIhuWcZerkIbou85TQVH937i9wmmySQTtE0CmcXvJ3mBQUuUgh6nFRiSTBctVTSfB2r_CLCYl-ccxK3mnRau_4Q9O18IB0wO-oGlA2WGseM2-63MDUcD9Wos-yCqQ

  6. cedZcCycP7gw4TatpSMSFI6WYT5exu0M6YVUuOF8C2bRhQVm-UZOuY8UmIx6RSOK4t5FXE1ocd2R7Lfzl4qIgN7RWo4DQFAHW5vEqs4IcUGTwKsCKHC3Qcs0SUlJbLCYJhNOxLiTXI0_Eg0xp1uDZzQPTdUOWyMcxU6uEZEvbF2dCoUmUCqgqY5CqY0cfA


0 replies

Be the first to reply!

Reply