A campaign report can show clicks by channel and still miss the path a person took before converting. Window functions help fill in that gap without collapsing every customer into a single summary row. I find them especially useful when marketing questions start to overlap with product behavior.
Keep each touchpoint visible
Imagine an events table with a user ID, event timestamp, campaign source, and event name. A function such as ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) gives each user’s events a sequence, so you can inspect what happened first and what followed. That is often a better starting point than assuming the last recorded source explains the whole journey.
Use LAG() when you need the previous event or campaign source alongside the current row. For example, it can help identify whether a product signup came after an email click or a paid search visit. Keep the underlying event rows in view while checking the logic; a sequence is only as meaningful as the tracking and identity rules behind it.
Compare behavior over time
A running count can show how activity accumulates during a journey, while a partitioned rank can select the most recent event for each user. In SQL, the important choice is the window definition: which rows belong together, how they are ordered, and whether the frame includes the current row. Small changes to those choices can change the result.
For a time-based marketing question, order by the event timestamp and decide how ties should be handled. If two events share a timestamp, add a stable tie-breaker such as an event ID where one is available. That makes the query’s ordering explicit instead of leaving the sequence to chance.
Make the decision clearer
Window functions are useful for exploring acquisition journeys, but they do not establish that one touchpoint caused a conversion. Attribution rules, missing events, consent settings, and cross-device identity can all affect the path you see. State those limits before using the output to compare channels.
I use window functions when the next decision depends on order: which source preceded signup, whether users returned after a campaign, or where an event sequence changes. Start with one concrete question, write down the partition and sort rules, then verify a few user journeys by hand. That gives the query a purpose beyond producing another table.