First- and Last-Touch Attribution
Lesson 1 of 1
  1. 1
    Introduction
    Think of your favorite website: how did you find it? Did you use a search engine? Or click on an ad? Or follow a link in a blog post? Web developers, marketers, and data analysts use that informat…
    Start
  2. 2
    First Touch Example
    Imagine June. She wants to buy a new t-shirt for her mother, who is visiting from out of town. She reads about CoolTShirts.com in a Buzzfeed article, and clicks a link to their landing page. June …
    Start
  3. 3
    Last Touch Example
    Two days later, CoolTShirts.com runs an ad on June’s Facebook page. June remembers how much she wanted that Ninja Turtles t-shirt, and follows the ad back to CoolTShirts.com. She now has the fol…
    Start
  4. 4
    First versus Last
    If you want to increase sales at CoolTShirts.com, would you count on buzzfeed or increase facebook ads? The real question is: should June’s purchase be attributed to buzzfeed or to facebook? Ther…
    Start
  5. 5
    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 c…
    Start
  6. 6
    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 = …
    Start
  7. 7
    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 bel…
    Start
  8. 8
    Review
    You can now wield SQL to find where, when, and how users are visiting a website. Well done! Here’s a summary of what you learned: - UTM parameters are a way of tracking visits to a website. Dev…
    Start

What you'll create

Portfolio projects that showcase your new skills