To calculate ARPU we need to divide revenues from a cohort of users (all users or users from a certain marketing campaign, for example) by the number of these users. It’s a very simple query with a
LEFT JOIN but there are a couple of very important nuances we need to get. I need all your attention right now Ready? Let’s go
Here’s a query that calculates ARPU:
SELECT SUM(amount) / COUNT(DISTINCT(u.id)) AS ARPU FROM users u LEFT JOIN purchases p ON u.id = p.user_id AND refunded = FALSE
First nuance: count unique users
DISTINCT to count...