Skill Path - Data Science - Marketing Attribution - Query doubt

Hi guys. I was just doing a project from skill path and have a doubt. Why is there a difference between this:

WITH last_touch AS(

SELECT user_id, MAX(timestamp) AS last_touch_at

FROM page_visits

WHERE page_name = ‘4 - purchase’

GROUP BY 1)

SELECT pv.utm_campaign,pv.utm_source, COUNT(lt.user_id)

FROM last_touch ‘lt’

JOIN page_visits ‘pv’

ON lt.last_touch_at = pv.timestamp

GROUP BY 1, 2;

and this:

WITH last_touch AS(

SELECT user_id, MAX(timestamp) AS last_touch_at

FROM page_visits

GROUP BY 1)

SELECT pv.utm_campaign,pv.utm_source, COUNT(lt.user_id)

FROM last_touch ‘lt’

JOIN page_visits ‘pv’

ON lt.last_touch_at = pv.timestamp

WHERE pv.page_name = ‘4 - purchase’

GROUP BY 1, 2;

It returns different results and I’m wondering why.

1 Like

Hi,

In your second query you have a WHERE statement to only show ‘4 - purchase’ while in the first one you are not

Hope this helps!

I’m wondering the same, it’s so strange! My best guess it that filtering it at an earlier stage cleaned out a few values that showed up later, but I don’t understand why. Null values should be viped out in both cases… weird!

Hi! I do believe that the reason is because there are three user_id (29180, 76466, 94852) whose last touch IS NOT on page 4 - purchase.

In your first example, those users end up in your set of results, but with a timestamp which is NOT their last touch, because by including WHERE you have forced the WITH clause to use the timestamp corresponding to 4 - purchase even though that is not the last touch of that user_id.

In the second example, those users are excluded /filtered out from the set of results because their last touch is not on page 4 - purchase, and therefore not selected by the last part of your query.

I would say that the first example leads to a wrong result, as it does not return what you are looking for: last touches on the purchase page.

You can find more info in slides 9 to 11 from my presentation of the CoolTShirts project here.

1 Like