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 (
SUM(amount) AS total_spend
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
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.
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!