FORUM SQL Habit Lesson 104. Calculating ARPU

Lesson 104. Calculating ARPU

Can someone explain why we use the first code where the refunded = false is in the join statement and not in the where condition?

It seems like the lesson is saying that we use it in the join and not where because if we use it in the where we exclude the free users since they never made a purchase but isnt this what we want? I thought ARPU was total revenue / total paying customers..

If I use the code where the refunded = false is in the join statement and count distinct id from the users table then im counting every user who has a utm_campaign.

Also, why do we say utm_campaign has to be null.. shouldnt ARPU just look at total revenue/paying customers regardless of whether or not they came from our marketing?


Hi @jy, these are really great questions! 🚀

Let’s start with utm_campaign confusion. I think I jumped to fast into calculating ARPU for a specific cohort of users – only “paid” users (users who came from a marketing campaign). That’s why there was a filter utm_campaign IS NOT NULL. Of course, ARPU or any other metric could be calculated for any user cohort:

  • all user base
  • users from a specific country
  • users acquired via a certain channel, etc

I’ve already adjusted the lesson to make sure we start with all users cohort and go to the paid marketing users cohort only in the end of the lesson.

Thank you for the contribution ☀

Let’s move on to the ARPU definition. Indeed, some sources define ARPU as the revenue per paying customer. IMO it’s a bit misleading since Unit Economics also defines ARPPUAverage Revenue Per Paying User, which is exactly the ARPU for paying customers.

As you can see, ARPU and ARPPU are quite different and it’s important to distinguish between them. You’d use ARPU to compare marketing channels/campaigns, for example. ARPPU is useful to understand how customers are engaging with your product (maybe your app/website offer additional services or products and ARPPU helps to understand if customers are actually buying them).

💡 ARPPU is discussed in lessons 105-106.