How to Calculate Payback Period

Here, I’ll introduce how to calculate the payback period using actual data.

We’ll calculate the payback period using two datasets.

The first dataset represents subscription business payment information where each row represents one payment transaction, Payment_Date, Customer_ID, Payment _Amount, and other information. (You can download the data from this page)

The second dataset contains business cost information for a particular month. The columns include manufacturing costs, selling and administrative expenses, new customer acquisition costs, and other information. (The data can be downloaded from this page)

In Exploratory, there are two methods for creating metrics:

AI Prompt: Creating metrics using natural language processing functionality
UI Menu: Creating metrics by processing data through menus accessible from the UI

This note introduces both methods.

“AI Prompt” is only available for users with paid licenses such as Business Plan or Personal Plan, or users who are currently trialing those plans.

Also, AI Prompt is only available when your device is connected to the internet.

If you are not using the above plans or are using a device that is not connected to the internet, please proceed to the “Calculate Payback Period with UI” section.

Calculate Payback Period with AI Prompt

This section introduces how to create metrics using AI Prompt. (For details about AI Prompt, please see here.

Click the “AI Wrangling” button. When the AI Prompt dialog appears, enter a prompt like the one below and run it:

Aggregate MRR, number of customers, number of new customers by month

This will generate code to aggregate MRR, number of customers, and number of new customers by month. Check the results and click the “Run as Step” button.

The step is added, and we’ve calculated the basic metrics by month.

Next, click the “AI Wrangling” button again to join the cost data.

When you want to perform operations that reference other data frames, such as joins, you can specify data frames in your project by entering “$”.

Since we want to join with the cost data frame, enter text like the following and execute:

Join with cost data

With this operation, the cost data is joined to the payment data, and we now have all the data needed to calculate the customer acquisition cost (CAC), ARPU, and gross margin.

Finally, calculate the payback period from the prepared data.

Click the “AI Wrangling” button. When the AI Prompt dialog appears, enter a prompt like the one below and run it:

Calculate Payback Period

This will generate code to calculate the payback period from CAC (Customer Acquisition Cost), ARPU (Average Revenue Per User), and gross profit margin. Check the results and click the “Run as Step” button.

The step is added, and we’ve calculated the payback period.

Calculate Payback Period with UI

Here I’ll introduce how to calculate the payback period using the UI.

Data for Calculating Payback Period

To calculate the payback period, we need to calculate CAC (Customer Acquisition Cost), ARPU, and gross profit margin.

Customer acquisition cost can be calculated as follows:

\[\begin{aligned} \text{CAC (Customer Acquisition Cost)} = \frac{\text{Total Cost of Acquiring New Customers}}{\text{Number of New Customers}} \end{aligned}\]

Also, ARPU (Average Revenue Per User) can be calculated as follows:

\[\begin{aligned} \text{ARPU (Average Revenue Per User)} = \frac{\text{MRR}}{\text{Total Number of Users}} \end{aligned}\]

Furthermore, gross profit margin can be calculated as follows:

\[\begin{aligned} \text{Gross Margin} = \frac{\text{Revenue} - \text{Cost of Goods Sold}}{\text{Revenue}} \end{aligned}\]

Therefore, we’ll first create a table like the one below from the two datasets mentioned earlier, which will allow us to calculate CAC (Customer Acquisition Cost), ARPU, and gross profit margin.

Creating this table will provide all the information needed to calculate the payback period.

Aggregating MRR, Number of Customers, and Number of New Customers

MRR can be calculated by aggregating the total “Payment_Amount” by month, and the number of customers can be calculated by aggregating the number of unique customer IDs by month.

On the other hand, the number of new customers cannot be calculated just by aggregating the payment data. So how do we calculate it? If the payment data has information about the number of payments, we can calculate the number of new customers by aggregating the unique number of customers with a payment count of 1.

To calculate the payment count information in the payment data, select “Create Window Calculation”, “Dense Rank (No gaps between ranks)” and “Ascending” from the column header menu of “Payment_Date”.

We do this to rank the payment dates in chronological order for each customer, which allows us to calculate which payment number it is.

We select “Dense” as the ranking method to ensure that when there are payments on the same day due to upgrades, etc., they are not treated as separate payments.

Since we want to calculate the ranking for each customer, when the window calculation dialog opens, select “Customer_ID” for the Group By and click the preview button.

Finally, change the column name to “Payment_Count” and click the “Run” button.

Now we’ve added payment count information to the payment data.

Next, we’ll aggregate MRR (Monthly Recurring Revenue).

MRR is the monthly total of the amounts paid by each user, so it can be easily calculated by simply calculating the monthly payment amount.

Select “Aggregate (Summarize)” from the column header menu of “Payment_Date”.

When the aggregate dialog appears, select “Payment_Date” for the group and choose “Month” with truncation for the date unit.

Next, select “Payment_Amount” for the value and SUM as the aggregation function.

Finally, change the column name to MRR and click the preview button.

Next, we’ll aggregate the number of customers.

In the aggregate dialog, select “Customer_ID” for the value and “Unique_Count” for the aggregation function.

Next, click the edit icon to change the new column name to “Total Customers” and click the “OK” button.

Finally, we’ll aggregate the number of new customers based on the payment count.

Select “Customer_ID” for the value and “Conditional Unique (COUNT_UNIQUE_IF)” for the aggregation function.

When the “Configure Column” dialog appears to set the condition, enter “Payment_Count” for the column, “Is” for the operator, and “1” for the value.

This setting allows us to aggregate the “unique count” of “Customer_ID” where the “Payment_Count” is 1.

Next, enter “New Customers” for the new column name and apply.

Click the preview button to confirm that we can aggregate the “Number of New Customers”, then click the “Run” button.

We’ve successfully aggregated MRR, number of customers, and number of new customers.

Joining Cost Data

Next, we’ll join the cost information to the data we just aggregated.

Select “Join (Add Columns)” from the column header menu of “Payment_Date”.

When the join dialog opens, select the cost data for “Target Data Frame”, select “Date” for the key column, and click the “Run” button.

Now we’ve created the data needed to calculate the payback period.

Calculating Payback Period

Finally, we’ll calculate the payback period.

First, to calculate CAC (Customer Acquisition Cost), select “Create Calculation” and “Standard” from the column header menu of “Customer_Acquisition_Cost”.

When the create calculation dialog opens, enter Customer_Acquisition_Cost / `New Customers` in the calculation editor.

Next, confirm that “Create New Column” is checked, set the column name to “CAC”, set “After This Column” to “Last Column”, and click the “Run” button.

We’ve calculated CAC.

Next, we’ll calculate ARPU. Select “Create Calculation” and “Standard” from the column header menu of MRR.

When the create calculation dialog opens, enter MRR / Number of Customers in the calculation editor.

Confirm that “Create New Column” is checked, set the column name to “ARPU”, change “After This Column” to the last column, and click the “Run” button.

We’ve calculated ARPU.

We’ll calculate the gross profit margin this time. Select “Create Calculation” and “Standard” from the column header menu of “MRR”.

When the create calculation dialog opens, enter (MRR - Manufacturing_Cost) / MRR in the calculation editor.

Confirm that “Create New Column” is checked, set the column name to “Gross Profit Margin”, change “After This Column” to the last column, and click the “Run” button.

We’ve calculated the gross profit margin.

Now we have all the information needed to calculate the payback period.

Select “Create Calculation” and “Standard” from the column header menu of “CAC”.

When the create calculation dialog opens, enter CAC / (ARPU * Gross Profit Margin) in the calculation editor.

Next, confirm that “Create New Column” is checked, set the column name to “Payback Period”, change “After This Column” to the last column, and click the “Run” button.

Now we’ve calculated the payback period.