How would you implement a last-touch attribution model for user signups?
Tests your ability to translate marketing concepts into warehouse SQL. A strong answer covers UTM/pageview events, sessionized tables, and a windowed join for the last touch within 30 days of signup.
What's really being asked
The interviewer wants to see if you can turn a vague business concept into an auditable SQL data model. Marketing attribution is fundamentally a modeling problem, not a data volume problem. They are checking whether you understand event tracking schemas, warehouse fact tables, and time-bound windowing logic, and whether you default to owning the logic in code rather than outsourcing it to a black box.
The full answer
Four things in order. First, event tracking: you need anonymous and identified pageview or session events that capture UTM source, medium, campaign, timestamp, and a user identifier that eventually resolves to a signed-up user. Second, storage: land this in a cloud warehouse like Snowflake or BigQuery as a sessionized fact table, not just raw JSON logs. Third, the lookback window: write SQL that joins touches to conversions with a predicate like touch timestamp greater than or equal to conversion timestamp minus 30 days and less than conversion timestamp. Fourth, last-touch logic: within that filtered set, pick the single most recent touch per user using a window function like row number partitioned by user id ordered by touch timestamp descending, keeping only row one, and assign the full conversion credit to that channel.
The mistakes people make
Three red flags come up often. One, suggesting Google Analytics or a BI tool as the implementation instead of warehouse SQL; this misses the point that the model must be transparent and extensible. Two, forgetting the lookback window entirely and just taking the global last touch, which might credit a visit from six months ago. Three, storing only attributed outcomes without the underlying touch grain, which makes the model impossible to debug or switch to first touch or linear later.
What usually comes next
The interviewer might ask how you handle anonymous users before they create an account, which tests your understanding of identity resolution or device stitching. They might ask how the model changes for multi-touch attribution, which should lead to allocating fractional points across touches. They might also ask about performance at scale, which should lead to partitioning on date and clustering on user id.
A concrete example
Imagine a user visits from a Facebook ad on January 1, a Google branded search on January 15, and signs up on January 20 after typing the URL directly. With a 30-day lookback, both paid touches are eligible. The last-touch model assigns one full point to the January 15 Google search. If the lookback were 3 days, only the direct visit would count, showing why the window length changes the story.
Interview question
When implementing a last-touch attribution model in your warehouse, which approach correctly assigns credit for a signup?
- a.Select touches falling within 30 days before the signup, then use a window function to keep only the latest touch per user.Correct
- b.Store only the attributed channel in the outcomes table and discard the underlying touch-level records.
- c.Rely on a BI tool or Google Analytics to compute attribution instead of building the logic in SQL.
- d.Rank all user touches by timestamp and keep the most recent one, ignoring how long before the signup it occurred.
Why? this is the answer
Option A is correct because it applies the 30-day lookback window and uses a window function to isolate the final eligible touch per user. Option D is tempting but wrong because ignoring the lookback window could credit a touch from months before the signup, which violates the model's intent.
Just read this? Test yourself on what you have been reading.
Read the original → getdbt.com
- #growth
- #sql
- #data-modeling
- #attribution
- #experimentation
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on growth — each one lists the topics its interview covers.
See open roles