Product Analytics

144. Signup rate per device type

I got the right answer (and same SQL results) for the exercise even though I counted event action ‘Signup’ instead of DISTINCT pageview_id.

As a rule of thumb, I understand that it’s preferable to count unique identifiers for conversion rates. But are there any risks to using my method below?

My query is below for reference:

SELECT p.device_type, 100.0* COUNT(action) / COUNT(DISTINCT visitor_id) AS cvr
FROM web_analytics.pageviews p
LEFT JOIN
web_analytics.events e
ON p.pageview_id = e.pageview_id
AND e.action = 'Signup'
WHERE url LIKE '%/books/%'
GROUP BY 1
ORDER BY 2 DESC

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

Hey everyone,

I’m working on lesson 97, Left Join 101, and I tried counting rows for a query with inner join vs left join and got the same results. My understanding is that inner joins would return results with only matches, and left join will include rows with null values.

I would expect left joins, keeping all things else equal, would return greater results. Am I missing something?

SELECT COUNT(user_id)
FROM books b
LEFT JOIN books_users u
  ON u.book_id = b.id

-- returns  1667

SELECT COUNT(user_id)
FROM books b
INNER JOIN books_users u
  ON u.book_id = b.id

-- returns  1667

Hello,

with reference to chapter “181. Counting app releases per month” & query - “How many releases Bindle had in February, 2018?” - I’m unable to understand on how’s the following solution different from the one proposed in chapter & why isn’t this correct?

SELECT COUNT( distinct app_version)
FROM adjust.callbacks
where to_char(created_at, 'yyyy-mm') = '2018-02'

Thanks, Tushar

I’m in lesson 98 “Grouping and counting with LEFT JOIN”, but I noticed something, when doing the following query:

SELECT
  name,
  COUNT(user_id)
FROM books b
LEFT JOIN books_users u
  ON u.book_id = b.id
GROUP BY 1
ORDER BY 2 ASC

You actually get some books with 0 values, because nobody has started reading them. But then for avoiding duplicated values from same users we add the DISTINCT, however, all books now again have at least a value of 1, I’m not sure why this happens.

SELECT
  name,
  COUNT(DISTINCT(user_id))
FROM books b
LEFT JOIN books_users u
  ON u.book_id = b.id
GROUP BY 1
ORDER BY 2 DESC

Hi, I’m doing the exercise “Identifying the most popular book in the catalogue”, I actually arrived to the same answers, altough with a little different query, I want to know if my result as pure lucky or is just another way to solve it. My query was:

SELECT
  name,
  COUNT(user_id) AS number_users
FROM books_users bu
INNER JOIN books b
  ON bu.book_id = b.id
GROUP BY 1
ORDER BY 2 DESC, 1 ASC

An the solution was the following:

SELECT
  name,
  COUNT(DISTINCT(bu.user_id))
FROM books_users bu
INNER JOIN books b
  ON bu.book_id = b.id
GROUP BY 1
ORDER BY 2 DESC, name ASC

Also, I want to know what’s the “general rule” to do inner joins, is there a difference which table I use first and which I join?

Thanks!!

In this code where we setup to capture retention, i know we join the table to itself and specify that the left side table has to be a signup action but we never specify what kind of action the right side (joined table) has to be.. doesnt this mean that technically we could be joining a signup action to itself? If thats the case, shouldnt we specify that the joined table’s action can’t be a signup one?

WITH user_activity AS (
  SELECT
    u.user_id,
    u.created_at::date AS signup_date,
    e.created_at::date AS activity_date,
    COUNT(*) AS events_counts
  FROM mobile_analytics.events u
  LEFT JOIN mobile_analytics.events e
    ON e.user_id = u.user_id  
  WHERE
    u.action = 'signup'  
  GROUP BY 1, 2, 3
  ORDER BY signup_date ASC, user_id ASC
)

SELECT 
  100.0 * COUNT(DISTINCT(CASE WHEN activity_date = '2018-02-08' THEN user_id END)) / COUNT(DISTINCT(user_id)) AS D7_retention_rate
FROM user_activity
WHERE 
  signup_date = '2018-02-01'