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

To get the UTM parameters, we’ll need to JOIN these results back with the original table.

We’ll join tables first_touch, akaft, and page_visits, aka pv, on user_id and timestamp.

ft.user_id = pv.user_id AND ft.first_touch_at = pv.timestamp

Remember that first_touch_at is the earliest timestamp for each user. Here’s the simplified query:

WITH first_touch AS ( /* ... */ ) SELECT * 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 fill in the WITH clause using the first_touch query from the previous exercise. We’ll also specify the columns to SELECT.

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;

Instructions

The diagram on the right illustrates the JOIN we need to get the UTM parameters of each first touch:

  • On the left is page_visits (just three columns from the original table). We get the UTM parameters from there.
  • On the right is first_touch (the result of the GROUP BY query). We get the first touches from there.
Folder Icon

Sign up to start coding

Already have an account?