We can easily modify the first-touch attribution query to get last-touch attribution: use
MAX(timestamp) instead of
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!
Using the query above as a guide, write the LAST-touch attribution query and run it.
Make sure June’s last touch at
08:13:01 is still there!
WHERE clause for
user_id = 10069 to your existing query.