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 there a way of doing view-through attribution (when the person saw but not clicked on an ad)?

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?