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

We can easily modify the first-touch attribution query to get last-touch attribution: use MAX(timestamp) instead of MIN(timestamp).

For reference, the first-touch attribution query is shown below.

WITH first_touch AS ( SELECT user_id, MIN(timestamp) AS 'first_touch_at' FROM page_visits GROUP BY user_id) SELECT ft.user_id, ft.first_touch_at, pv.utm_source FROM first_touch AS 'ft' JOIN page_visits AS 'pv' ON ft.user_id = pv.user_id AND ft.first_touch_at = pv.timestamp;

Now that you’ve seen how it works, it’s time to practice!

Instructions

1.

Using the query above as a guide, write the LAST-touch attribution query and run it.

2.

Make sure June’s last touch at 08:13:01 is still there!

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

Folder Icon

Sign up to start coding

Already have an account?