Lesson 107: ARPPU calculations not quite the same?

Hey everyone!

In lesson 107, there is this helpful nuance that if we adjust the where clause to included refunded customers in an ARPU calculation (as opposed to the AND clause in a LEFT JOIN) that you would get ARPPU.

I dbl checked that and found a small discrepancy. I just wanted to make sure my thinking here is right.

I updated the select lines to show tables and it looks like this claim would be true exccept there are more than 2 values for status. The second query shows a slight difference from the first becacuse there are “trial” and “free” users, I think.

Am I getting this right?

SELECT
  -- SUM(amount) / COUNT(DISTINCT(u.id)) AS ARPPU
  *
FROM users u
LEFT JOIN purchases p
  ON u.id = p.user_id
    AND refunded = FALSE
WHERE
  status = 'customer'

-- arppu 58.5

SELECT
  -- SUM(amount) / COUNT(DISTINCT(u.id)) AS ARPPU
  *
FROM users u
LEFT JOIN purchases p
  ON u.id = p.user_id
WHERE
  refunded = FALSE

-- arppu 56.8

REPLIES

makaroni4

over 1 year Reply

Hi @coachpacman

You’re absolutely correct, the ARPPU is lower in the 2nd query because it counts all users who had purchases, but no longer are customers (free) + users in their trials.

You can actually see this if you aggregate by user statuses:

SELECT
  status,
  COUNT(DISTINCT(user_id)) AS user_count
FROM users u
LEFT JOIN purchases p
  ON u.id = p.user_id
WHERE
  refunded = FALSE
GROUP BY 1

Linking a couple of other questions related to ARPU calculation:

Hope that helps

coachpacman

over 1 year Reply

Thanks for confirming! Hope this doesn’t come off as nit picky. Talking through the nuances gives me confidence that I’m learning the material. Appreciate it!

Lesson 107: ARPPU calculations not quite the same?

REPLIES

WRITE A REPLY