How to Calculate ARPU

Here, I’ll introduce how to calculate ARPU (Average Revenue Per User) using actual data.

In this example, we’ll use payment data from a subscription business where each row represents one payment transaction, including Customer_ID, Payment_Date, Payment _Amount, and other information. (You can download the data 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 will introduce 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 your device is not connected to the internet, please proceed to the “Calculate ARPU with UI” section.

Calculate ARPU 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:

Calculate ARPU by month

The code to calculate ARPU will be generated. Check the results and click the “Run as Step” button.

The step is added, and ARPU has been calculated.

Calculate ARPU with UI

Here I’ll explain how to calculate ARPU using the UI.

ARPU can be calculated with the following steps:

Aggregate MRR (Monthly Recurring Revenue) and customer count by month
Calculate ARPU by dividing MRR by customer count

Aggregate MRR (Monthly Recurring Revenue) and customer count by month

To calculate ARPU, you need information about MRR (Monthly Recurring Revenue) or ARR (Annual Recurring Revenue).

Generally, ARPU is the average monthly revenue obtained from one customer, calculated based on MRR. However, in some cases, ARPU may refer to the average annual revenue obtained from one customer, calculated using ARR.

In this example, we’ll calculate ARPU as the average monthly revenue obtained from one customer.

First, select “Aggregate (Summarize)” from the “Payment_Date” column header menu.

When the summarize dialog appears, select “Payment_Date” for the group and choose “Month” with truncation for the date unit. When calculating annual ARPU, select “Year” with truncation.

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. When calculating annual ARPU, change the column name to “ARR”.

Now you have configured the settings to aggregate MRR (or ARR).

Next, aggregate the customer count information needed for ARPU calculation.

In the summarize dialog, select “Customer_ID” for the value and “Unique Count” as the aggregation function.

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

You have now aggregated the MRR (or ARR) and customer count needed for ARPU calculation.

Calculate ARPU by dividing MRR by customer count

Finally, to calculate ARPU, select “Create Calculation”, “Standard” from the MRR (or ARR) column header menu.

When the create calculation dialog opens, enter MRR / `Total Customers` in the calculation editor. When calculating annual ARPU, the formula would be ARR / `Total Customers`.

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

You have successfully calculated ARPU.