Learn
First- and Last-Touch Attribution
The Attribution Query I

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.

Instructions

1.

Find all last touches. Your query will look similar to the first_touch query above.

2.

Make sure June’s last touch at 08:13:01 is in the data.

Add a WHERE clause for user_id = 10069 to your existing query.

Folder Icon

Sign up to start coding

Already have an account?