How would you implement a last-touch attribution model for user signups?

WHAT THIS TESTS: 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.

A GOOD ANSWER COVERS: 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.

COMMON WRONG ANSWERS: 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.

LIKELY FOLLOW-UPS: 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.

ONE 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.

Source: getdbt.com