I'm deep into our own marketing data pipeline, moving away from third-party black boxes. We've got all our clickstream and conversion events landing in BigQuery daily. Now I need to start actually attributing conversions to the right touchpoints.
I'm thinking of starting with a simple last-click model just to get a baseline. My data is structured roughly like this:
- `user_id` (our internal ID)
- `session_id`
- `event_timestamp`
- `event_type` (e.g., 'page_view', 'campaign_click', 'purchase')
- `utm_source`, `utm_medium`, `utm_campaign`
- `conversion_value`
The goal is a straightforward query that, for each purchase, identifies the last marketing touchpoint (campaign click) before conversion. I know this ignores a lot, but I need a starting point I can build on.
Has anyone built this incrementally? My main questions:
* Any major pitfalls with sessionizing and stitching user journeys in raw SQL?
* For those who evolved past last-click, what was the next step? Time decay? Position-based?
* How are you handling cost data from platforms like Meta Ads? Are you joining on campaign names, or is there a better key?
I'm not looking for a perfect multi-touch model yet. Just a practical, maintainable first pass that's better than what we have (which is nothing). Performance and cost of the queries are also a concern—some of these pathing queries can get expensive.