Percentages can be used to gain valuable insight into the affects a portion has on a total. They are heavily relied upon to determine and evaluate KPIs for an organization and guide operations to improve your overall business performance.
In this example, we’re dividing our cancelled subscriptions over our total subscriptions to calculate our churn rate. Churn rate is a common metric used by SaaS companies to determine how effectively they are retaining their customers.
First we will need to create a dataset to calculate the number of customers who have not cancelled. To do this we will count the number of subscriptions where the payment start date is not null and the cancelled date is null.
Dataset 2 will get a count of subscriptions that have cancelled. To achieve this we will count the number of subscriptions where both the payment start date and the cancelled date is null.
After you’ve created your two datasets, make sure to use a Cross Join.
In the Data Pipeline, click +Add Step, then choose Add Column from the dropdown menu. Set a title for your column, then enter the percentage formula, which is as follows:
100.0 * "Column1" / "Column2"
To round your percentage to two decimal points, you can use the SQLite round() function. Your formula will look like this:
round( 100.0 * "Column1" / Column2", 2)
Where 2 is the number of decimal places.
If your column values are integers and you would like your results to have decimal precision, be sure to multiply by 100.0 to convert the column over to a float data type. This will convert the top value to a decimal before the division step, and will ensure your results are in decimal form.
Our example looks as follows:
Click Ok, and your percentages will be calculated.