Shawn Jonghyuck Jung shawn-jung
markrittman
/ multi_channel_attribution.sql
Created
February 8, 2020 22:56
Multi-Channel Attribution Standard SQL query for Google BigQuery sourcing session data from Segment and attributed value from operational system
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH session_attributiON AS | |
| (SELECT *, | |
| CASE WHEN session_id = LAST_VALUE(session_id) | |
| OVER (partitiON by user_id order by session_start_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) THEN 1 | |
| ELSE 0 | |
| END AS LAST_click_attrib_pct, | |
| CASE WHEN session_id = FIRST_VALUE(session_id) | |
| OVER (partitiON by user_id order by session_start_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) THEN 1 | |
| ELSE 0 | |
| END AS first_click_attrib_pct, |