FORUM Marketing Analytics Where to use "AND refunded = FALSE" on ARPU and ARPPU?

Where to use "AND refunded = FALSE" on ARPU and ARPPU?

I got confused in the lessons 105-107, about how to filter refunded = FALSE in WHERE and when to use in the LEFT JOIN .... ON.

In which metric should I use it in WHERE: ARPU or ARPPU?

Could you clarify it to me, please?


Hi 👋

Sure thing, it all comes from the definition. Let’s look at both.

Users in ARPU

🔍 Average Revenue Per User.

Here “per user” means all users – every single signup, a person who create an account.

In SQL terms, all users means all records from the users table.

Users in ARPPU

🔍 Average Revenue Per Paying User.

By definition, we don’t need all users here. We’re narrowing down our user segment to only users who pay aka “customers”. Hence, we’ll filter our all non-customer users.

Revenue in ARPU and ARPPU

Now let’s look at the revenue part in both metrics. To arrive at the Average Revenue, we need to divide all revenue by the number of all users (ARPU) or by the number of customers (ARPPU).

In SQL terms, all revenue does not ⚠ mean all records from the purchases table. The reason is refunds – some of the purchases are refunded, so we won’t count them (otherwise we might optimize our decisions towards getting more refunds 💣).

Hence, we do need the refunded = FALSE filter in queries for both ARPU and ARPPU.

I hope that helps, ping me if I can clarify it more. 👋