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

[gdemontalivet]

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_contracts {
      join: contracts {
        relationship: many_to_one
        sql_on: ${monthly_active_contracts.contract_id}=${contract.id} ;;
      }
   }
   

   and now we can have fun:

   

6.