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.
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) ;;
}
}
explore: contracts {
join: calendar {
type: cross
relationship: many_to_one
}
}
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"]
}
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.
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: