ARPPU= total revenue/ # of paying users

 

 
Finding ARPPU by month is the most used time frame amongst businesses. This metric is a good indicator to track the profitability of your company and effectiveness of sales and marketing activities.
 
You can also calculate your ARPPU by the number of contracts or accounts, depending on how your business operates.
 
Notes: If your business offers contracts or subscription on a yearly basis, make sure to check out ARPPU by year template.
 
For this template, we will be calculalting monthly ARPPU using the database we created for the fake B2B SaaS startup, Awesome Company.

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

Sample MySQL database

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

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

• payment amount
• payment dates
• refund status
• user

How to calculate the Monthly 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-%m') as 'Month'
	from payment p
	where DATE_FORMAT(created_at, '%Y') = '2022' and p.is_refund = 0
	group by DATE_FORMAT(p.created_at, '%Y-%m')
	order by DATE_FORMAT(created_at, '%Y-%m')
), Pu as(
	select count(DISTINCT user_id) as 'PU', DATE_FORMAT(created_at, '%Y-%m') as 'Month'
	from payment
	group by DATE_FORMAT(created_at, '%Y-%m')
	order by DATE_FORMAT(created_at, '%Y-%m') 
)
SELECT
	Payment.Month,
	Payment.AR as 'Annual Revenue',
	Pu.PU as 'Paying User',
	ROUND(Payment.AR / Pu.PU) as 'ARPPU'
FROM Payment JOIN Pu ON Payment.Month = Pu.Month;

 
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.
• 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.