Exercise lesson 130

Hello, I’m struggling to understand the formula used in the exercise to calculate the churn rate:

SELECT
  100 - 100 * COUNT(DISTINCT(b.visitor_id)) / COUNT(DISTINCT(h.visitor_id))::float AS churn_rate
FROM web_analytics.pageviews h

I don’t get the 100-….why cannot we leave it as:

100 * COUNT(DISTINCT(b.visitor_id)) / COUNT(DISTINCT(h.visitor_id))::float

Many thanks!

makaroni4

almost 5 years Reply

@Ale.1, that’s a great question, it’s super important to distinguish conversion rate from the churn rate

Example: 10 users visited the checkout page, 2 of them purchased. The conversion rate (in that case, conversion to purchase) is 20%.

The same example: 10 users visited the checkout page, 2 of them purchased. It means that 8 didn’t, or they churned. Churn rate is 80%.

As you can see, churn rate and conversion rate should add up to 100%.

That’s why in the lesson we calculate the conversion rate (visitors who went to a book page from the homepage):

100 * COUNT(DISTINCT(b.visitor_id)) / COUNT(DISTINCT(h.visitor_id))::float

and then subtracting it from 100 to get a churn rate (visitors who didn’t go to a book page).

That’s it

Ale.1

almost 5 years Reply

Alright, pretty clear now! :)

Thank you for the fast response.