ARPU= total revenue/ # of users

 

 
Most businesses calculate the Average Revenue Per User per month. However, if your service offers an annual subscription or pricing model, it is better to find ARPU by year.
 

Notes: You can also calculate your ARPU by the number of contracts or accounts, depending on how your business operates.

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

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

Sample MySQL database

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

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

• payment amount
• payment dates
• refund status
• user
   

How to calculate the Yearly ARPU 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')
), Yau as (
  	select count(DISTINCT user_id) as 'YAU', DATE_FORMAT(created_at, '%Y') as 'YEAR'
	from login_history
	group by DATE_FORMAT(created_at, '%Y')
	order by DATE_FORMAT(created_at, '%Y')
)
SELECT
	Payment.YEAR,
	Payment.AR as 'Annual Revenue',
	Yau.YAU,
	ROUND(Payment.AR / Yau.YAU) as 'ARPU'
FROM Payment JOIN Yau ON Payment.YEAR = Yau.YEAR;

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

Want to do more with your data?

• If you want to analyze your ARPU more thoroughly, you can also break down your ARPU by day or week.
• Try adding a table to view your ARPU 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 ARPU sent via Slack or email with Outcode's quick automation feature.