Formula Columns – RevOS Documentation & Support

Sometimes you need to create a new KPI or scoring criteria (also known as feature) in your model, for that purpose you can just use a formula to calculate it based on the data from other columns in your model.

This can be useful if you want to compute a derived metrics, for example from data in your CRM and Product Analytics and/or transform the existing value to make it more usable for scoring.

To create a new formula column you can click on + sign on the right-top of your table and select Formula

After that a enter the name of your column and then a formula editor pane will open:

Formula language

As a formula expression you can use any ANSI SQL (and BigQuery specific) functions and expressions. For example simple mathematical expressions like Calculating Customer Lifetime Value (CLTV)

average_monthly_revenue * customer_lifespan_months

This computes the CLTV by multiplying the average monthly revenue per customer by the average customer lifespan in months.

Or a conditional statement like, Calculating Monthly Recurring Revenue (MRR) from Subscription Amounts

subscription_amount /
CASE
	WHEN subscription_term = 'Annual' THEN 12
	WHEN subscription_term = 'Quarterly' THEN 3
ELSE 1
END

see more examples in the examples section below and see full list of all available functions here.

Data Type

It’s important to understand that formula will be applied to each row in your table and result of the evaluation of that expression will be transformed to the data type you specify, so for example above where we compute a MRR (Monthly Recurrent Revenue) based on ARR (Annual Recurrent Revenue) the result is a Number and Number have to be selected in the underlying Data Type.

If Data Type does not match the output of the function then you will see the error. Try to select a different Data Type in this case.

Usage Examples

Let’s go though some samples of the formulas

Calculating Churn Rate Percentage

(customers_lost / NULLIF(customers_at_start, 0)) * 100

This calculates the churn rate as a percentage by dividing the number of customers lost by the number of customers at the start of the period, using NULLIF to prevent division by zero.

Calculating Revenue Growth Rate Percentage

((revenue_current_period - revenue_previous_period) / NULLIF(revenue_previous_period, 0)) * 100

This formula computes the revenue growth rate between two periods, expressing it as a percentage.

Same as above if you want to round it to 1 digit after the comma you can improved by using ROUND function as following:

ROUND(((revenue_current_period - revenue_previous_period) / NULLIF(revenue_previous_period, 0)) * 100,1)

Calculating Sales Cycle Length in Days

DATE_DIFF(deal_closed_date, deal_created_date, DAY)

This calculates the length of the sales cycle for each deal by finding the difference in days between when the deal was created and when it was closed.

Limitations

There are following limitations that for formulas as of today:

A formula can not reference to another formula column from the model. If you want to reference it, just copy the formula expression into the new formula columns. We are working on removing this limitation in the future.

Formula can not reference external tables. If you want to join data from multiple tables please use ‘Enrichment’ functionality/

Formulas can not use aggregate functions. As formula evaluation is done on row-by-row basis no aggregation can be done.