We just learned how to attribute a user’s first and last touches. What if we want to attribute the first and last touches for ALL users? This is where SQL comes in handy — with one query we can find all first- or last-touch attributions (the first and last versions are nearly identical). We can save this query to run it later, or modify it for a subset of users. Let’s learn the query…
In order to get first-touch attributions, we need to find the first time that a user interacted with our website. We do this by using a
GROUP BY. Let’s call this table
SELECT user_id, MIN(timestamp) AS 'first_touch_at' FROM page_visits GROUP BY user_id;
This tells us the first time that each user visited our site, but does not tell us how they got to our site — the query results have no UTM parameters! We’ll see how do get those in the next exercise.
Find all last touches. Your query will look similar to the
first_touch query above.
Make sure June’s last touch at
08:13:01 is in the data.
WHERE clause for
user_id = 10069 to your existing query.