Skip to Content

Capstone: Attribution

Learn SQL from Scratch

You’re almost done with Learn SQL from Scratch. Well done! It’s time to combine everything that you learned into a final capstone.

You’ve chosen the First- and Last-Touch Attribution project. You’ll have about two weeks to complete a set of SQL analyses and a presentation summarizing your findings. Both your code and presentation will be reviewed by a Codecademy coach.

Let’s get started!

CoolTShirts, an innovative apparel shop, is running a bunch of marketing campaigns. In this capstone, you’ll be helping them answer these questions about their campaigns:

1. Get familiar with the company.

  • How many campaigns and sources does CoolTShirts use and how are they related? Be sure to explain the difference between utm_campaign and utm_source.
  • What pages are on their website?

2. What is the user journey?

  • How many first touches is each campaign responsible for?
  • How many last touches is each campaign responsible for?
  • How many visitors make a purchase?
  • How many last touches on the purchase page is each campaign responsible for?
  • What is the typical user journey?

3. Optimize the campaign budget

  • CoolTShirts can re-invest in 5 campaigns. Which should they pick and why?

If you need a refresher on first- and last-touch attribution, review the lesson.

Then, go to this project, which will guide you through the queries you need to answer the above questions. Make sure to save your queries and results — you’ll be using them in your presentation.

Finally, create a set of slides using Google Drive, Microsoft Powerpoint, or some other presentation software. Your presentation should answer all of the questions listed above. Each answer should include a text explanation and data to support your claim. The queries used to find that data should be included in a separate .sql file. We recommend that you use the template linked here. This template contains examples of the types of slides you’ll want to use. Your completed presentation will be approximately 15 slides long.

Your work will be reviewed by a Codecademy coach against this rubric. Make sure to check your work before submitting! You can find submission instructions in the next step in the Learn SQL from Scratch dashboard.

Ready to Learn More?

Find the course that's right for you! Explore our catalog or get a recommendation.