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.