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 first_touch:

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.