Hey everyone, been lurking for a bit as I learn the ropes on the CI/CD side. My team is now asking me to help pull some marketing data together, specifically around attribution. They want a multi-touch model visualized in Looker Studio, and I'm trying to piece it together.
I found some high-level guides, but they skip the messy parts. I'm hoping someone can walk through a practical setup. Our stack uses Google Analytics 4 data, and we've got some custom event data piped into BigQuery. The goal is a simple linear attribution model to start.
What I'm stuck on is the SQL logic for the BigQuery data source. How do you actually structure the query to allocate credit across touchpoints for a conversion? My attempt keeps double-counting sessions. Also, how do you handle different conversion types?
Here's my broken query so far:
```sql
SELECT
user_pseudo_id,
event_name,
-- This is where I'm lost
COUNTIF(event_name = 'purchase') OVER(PARTITION BY user_pseudo_id) as conversions
FROM
`my-project.analytics_events.events_*`
WHERE
event_name IN ('session_start', 'view_item', 'add_to_cart', 'purchase')
```
What's the common pitfall here? Is it better to build the model logic in the SQL layer, or try to use Looker Studio's built-in features? Our site traffic is around 500k sessions/month, SaaS business model. Any concrete examples would be super helpful!
Learning by breaking
Great question. The double-counting is the classic hurdle. You're aggregating at the event level, but you need to assign credit at the session or user journey level first.
Your query needs to define the conversion window and touchpoint sequence. A common approach is to use a session number or timestamp to build the path before splitting credit. For different conversion types, you could add a `WHERE` clause for your specific conversion event, or use a CASE statement to assign different weights in your linear split.
Could you share a bit more about your session definition? Are you using GA4's session ID or building your own from event timestamps? That might be the root of the double-count.
Everyone jumps to the linear model because it's the default in every blog post, but they never mention the foundational flaw. You're thinking about credit allocation before you've even correctly defined a touchpoint. GA4's data model is a mess of events and parameters, not a clean session log. Your query is counting conversions per user, not per conversion path, which is why you're double counting.
The real pitfall is trying to force this logic in SQL at all when the underlying data is unstable. GA4 session definitions change, custom events can be missed or duplicated, and then you're building a business metric on quicksand. Your team asked for a multi-touch model, but did they ask for the maintenance burden and the constant data validation it will require?
Before you write another line of SQL, you need to lock down what a 'session' and a 'touchpoint' actually are in your specific BigQuery export, because it's probably not what you think. Are you using ga_session_id? That can reset mid-user journey. Are you stitching user_pseudo_id across devices? You can't. So your attribution model is already broken, you just haven't visualized it yet.
Skeptic by default
User760 gets to the heart of it - everyone's racing to split the pie when they haven't even agreed on the ingredients. The "foundational flaw" isn't just the logic, it's the cost. You're about to spend a dozen expensive BigQuery compute hours to build a fragile metric on shaky definitions. Your data engineer's time, your analytics platform cost, your validation cycles - all for a linear model your marketing team will argue with next quarter.
The real math isn't in the attribution SQL, it's in the TCO of maintaining this homemade solution versus using a pre-built model from a vendor. You're right to be cynical about the instability. Most teams burn $50k a year in engineering and cloud costs to avoid a $20k SaaS subscription, then call it optimization.
pay for what you use, not what you reserve