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!

Hi Anatoli!

I have a question about the funnel analysis query in lesson 129, namely this one:

SELECT
  COUNT(h.visitor_id) AS homepage_pvs,
  COUNT(b.visitor_id) AS book_page_pvs
FROM web_analytics.pageviews h
LEFT JOIN web_analytics.pageviews b
  ON h.visitor_id = b.visitor_id
    AND b.url LIKE '%/books/%'
    AND (
      b.referer_url = 'https://www.bindle.com/'
      OR b.referer_url LIKE 'https://www.bindle.com/?%'
    )  
    AND b.created_at BETWEEN h.created_at AND h.created_at + '30 minutes'::interval
WHERE
  h.url = 'https://www.bindle.com/'
  OR h.url LIKE 'https://www.bindle.com/?%'

My question is: Is the

AND (
  b.referer_url = 'https://www.bindle.com/'
  OR b.referer_url LIKE 'https://www.bindle.com/?%'
)

bit necessary, given that we filter using WHERE further down? Is there a difference? They seem to give the same results.

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'

Hi, im curious to know if this solves the same problem? The answer is correct but i want to make sure my SQL is correct as well in terms of what we’re looking at.

with joined as ( 
SELECT
    a.creative_name,
    a.label,
    a.activity_kind,
    a.adid,
    b.event_name
FROM adjust.callbacks a
LEFT JOIN adjust.callbacks b
ON a.adid = b.adid
and b.event_name = 'signup'
WHERE a.tracker = 'gxel3d1'
AND a.activity_kind = 'click'
), numbers as ( 
SELECT
    creative_name,
    count(distinct(case when event_name = 'signup' then adid end)) as signups,
    count(distinct(adid)) as total
FROM joined
GROUP BY 1
)
SELECT
    *,
    100.0*signups/total
from numbers
order by 2 desc

In the following code from Lesson 109.. how come we dont need to group by utm_campaign at the end when we are calculating CPA? is it because we joined the users to the row by utm_campaign?

WITH spend_per_campaign AS (
  SELECT
    utm_campaign,
    SUM(amount) AS total_spend
  FROM marketing_spends
  GROUP BY 1  
), users_per_campaign AS (
  SELECT
    utm_campaign,
    COUNT(*) AS users_count
  FROM users
  WHERE 
    utm_campaign IS NOT NULL
  GROUP BY 1
)

SELECT
  s.utm_campaign,
  users_count,
  total_spend / users_count AS CPA
FROM spend_per_campaign s
INNER JOIN users_per_campaign u
  ON s.utm_campaign= u.utm_campaign

Does this look correct for the homework at the bottom of lesson 108 where it asks to add country to the CTE?

I chose inner join instead of left join but i dont think it would matter if i did left join?

WITH customers AS (
  SELECT
    a.user_id,
    b.country,
    MIN(a.created_at) AS first_purchased_at
  FROM purchases a
  inner join users b
  on a.user_id = b.id
  WHERE
    refunded = FALSE
  GROUP BY 1,2
  ORDER BY 3 DESC
)		

SELECT *
FROM customers

Is it possible to get the organic search query in web analytics when someone visits the page from Google search? As far as I know - not (even GA doesnt show it), but maybe you have a hack :P

How to distinguish between direct visits, referral visits and clicks from organic search when tracking with a custom pixel? In the course, the main info on campaigns comes from UTMs from the URL, but you would also wanna analyze organic traffic, that does not usually have tracking parameters in a link.

How to connect conversion data from a CRM (Hubspot) and web analytics (pageviews)?

How does fingerprinting work? Can it connect user data if the user e.g. opened an email on a new device?

How to start doing web analytics with a custom pixel?

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?

I have a table with 3 columns: day of month, client_uk, flag_login (Y/N). My task is to group this table over by month to determine what flag_login should be for each month for every client. The client could be login a few time per month, for me, it doesn’t matter and I should determine whether the fact of client login at least 1 time per month. If it’s yes flag_login should Y, in opposite case ‘N’.

I don’t know how to solve it, because my window for window function should be client_uk and month simultaneously.

Thanks for the help in advance!