ARPPU= total revenue/ # of paying users

 

 
Different from ARPU, this metric calculates the average revenue generated from active paying users in particular. Furthermore, the metric can help sales and marketing teams to plan out budgets for campaigns, while maximizing their profitability.
 
Notes: You can also calculate your ARPPU by the number of contracts or accounts, depending on how your business operates.
 
Even though most businesses find ARPPU per month, it is important to adjust your calculation depending on your subscription or pricing model.

For this template, we will be calculating yearly ARPPU using the database we created for the fake B2B SaaS startup, Awesome Company.

On Outcode, you can create a Yearly ARPPU Databoard in under 10 minutes.

Sample MySQL database

This is what Awesome Company's MySQL database looks like.

To track your Yearly ARPPU, you need the following data:

• payment amount
• payment dates
• refund status
• user

How to calculate the Yearly ARPPU in SQL

We need to bring data from 2 different tables. created_at and the is_refund data from the payment table and created_at and user_id from the user table.

You can easily copy and paste the query below to find your own. 
 
You can also create your own Databoard and share it with your team.

WITH Payment AS (
	select sum(p.amount) as 'AR', DATE_FORMAT(p.created_at, '%Y') as 'Year'
	from payment p
	where p.is_refund = 0
	group by DATE_FORMAT(p.created_at, '%Y')
	order by DATE_FORMAT(created_at, '%Y')
), Pu as(
	select count(DISTINCT user_id) as 'PU', DATE_FORMAT(created_at, '%Y') as 'Year'
	from payment
	group by DATE_FORMAT(created_at, '%Y')
	order by DATE_FORMAT(created_at, '%Y') 
)
SELECT
	Payment.Year,
	Payment.AR as 'Annual Revenue',
	Pu.PU as 'Paying User',
	ROUND(Payment.AR / Pu.PU) as 'ARPPU'
FROM Payment JOIN Pu ON Payment.Year = Pu.Year;

 
Do you have a better way to find ARPPU? Please reply below and inspire your fellow Outcoders!

Want to do more with your data?

• If you want to analyze your ARPPU more thoroughly, you can also break down your ARPPU by day or week.
• Try adding a table to view your ARPPU by product.
• Check out our templates and see how you can use your data here.

Share and automate

 
Outcode helps you to track measures in real-time with the Databoard.
 

• Share your Databoard or data table with other team members. Copy and paste the page URL anywhere you like.
• Get your real-time ARPPU sent via Slack or email with Outcode's quick automation feature.