Multi-Touch Attribution
Google, Uber
Problem Statement
Marketing teams use Multi-Touch Attribution (MTA) to understand which channels drive sales. In a Linear Attribution model, every interaction a user had before converting receives an equal share of the credit.
Write a query to calculate the attributed credit for every interaction that contributed to a conversion.
Rules:
- An interaction is only credited if its interaction_time is strictly less than the conversion_time.
- If a user has 3 interactions before converting, each interaction_id receives 1/3 (0.333...) credit.
- Output columns: interaction_id, channel, attributed_credit.
- attributed_credit should be calculated as 1.0 divided by the total number of valid touchpoints for that specific user.
- Result must be sorted by interaction_id ascending.
Tests your understanding of
Window Functions, Join Logic and Linear Attribution
Input Tables
interactions
| interaction_id(INTEGER) | user_id(INTEGER) | channel(VARCHAR) | interaction_time(TIMESTAMP) |
|---|---|---|---|
| 1 | 101 | Search | 2024-01-01 10:00:00 |
| 2 | 101 | Social | 2024-01-01 11:00:00 |
| 3 | 101 | 2024-01-01 15:00:00 | |
| 4 | 102 | Search | 2024-01-01 12:00:00 |
| 5 | 102 | Direct | 2024-01-01 13:00:00 |
conversions
| user_id(INTEGER) | conversion_time(TIMESTAMP) |
|---|---|
| 101 | 2024-01-01 14:00:00 |
| 102 | 2024-01-01 16:00:00 |
Expected Output
| interaction_id(INTEGER) | channel(VARCHAR) | attributed_credit(DECIMAL) |
|---|---|---|
| 1 | Search | 0.5 |
| 2 | Social | 0.5 |
| 4 | Search | 0.5 |
| 5 | Direct | 0.5 |
Tags
HardWindow FunctionsJoin LogicLinear Attribution
40-50 min
31%