Let’s discuss details of marketing attribution query:
WITH users_pvs AS ( SELECT ROW_NUMBER() OVER(PARTITION BY p.visitor_id ORDER BY p.created_at ASC), u.id AS users_id, * FROM web_analytics.pageviews p INNER JOIN users u ON u.visitor_id = p.visitor_id WHERE user_id IS NULL ) SELECT users_id, url, SPLIT_PART(SPLIT_PART(url, 'utm_campaign=', 2), '&', 1), SPLIT_PART(SPLIT_PART(url, 'utm_source=', 2), '&', 1), SPLIT_PART(SPLIT_PART(url, 'utm_medium=', 2), '&', 1), SPLIT_PART(SPLIT_PART(url, 'utm_content=', 2), '&', 1),...
About SQL Habit
Hi, it’s Anatoli, the author of SQL Habit.
SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst and solve real-life challenges from Business, Marketing, and Product Management.
SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work.