How to Use BigQuery to Build a Full Funnel Attribution Model

Last click attribution answers a question nobody asked. It tells you which touchpoint happened immediately before the conversion, which is roughly as useful as crediting a football goal entirely to whoever passed the ball last.

The alternative is to reconstruct the full path each customer took and distribute credit across it deliberately. GA4's interface offers a limited set of models. BigQuery lets you build your own, compare them against each other, and see how much your conclusions depend on the choice.

This is how to do it.

Why last click fails and what to do instead

Last click has one virtue: it is unambiguous. Every conversion has exactly one prior touchpoint, so credit assignment is trivial and nobody argues.

It is also systematically biased toward the bottom of the funnel. Branded search, retargeting, and direct traffic collect nearly all the credit, because those are the channels people use when they have already decided. The content that created the demand, the social post that started the consideration, the comparison article read three weeks earlier, all score zero.

Budget follows credit. So teams cut the channels that generate demand and pour money into the channels that harvest it, then wonder why harvesting gets more expensive every quarter.

Multi touch attribution fixes the accounting. It does not tell you the objective truth about causality, because no attribution model does, but it stops one arbitrary rule from silently governing your budget.

What data you need in BigQuery for attribution modeling

You need the GA4 export enabled, and you need enough history for your sales cycle. If your typical path from first touch to purchase runs six weeks, three months of data is a minimum and six is better.

From the export, four things matter:

• user_pseudo_id, which identifies the browser across sessions. If you also set a user_id at login, use it in preference, because it stitches across devices.
• event_timestamp, for ordering touchpoints.
• traffic_source fields and the session level source, medium, and campaign captured at the start of each session.
• The conversion event itself, with its value if you have one.

One caveat worth understanding upfront: user_pseudo_id is cookie based, so it resets when someone clears cookies or switches device. Paths reconstructed this way undercount cross device journeys. That is a real limitation, and it is still substantially better than last click.

How to structure your event data for attribution analysis

The goal is a table with one row per touchpoint, ordered per user, with the conversion marked.

Start by extracting session level source data. Each session's first event carries the traffic source that started it. Pull user_pseudo_id, the session start timestamp, source, medium, and campaign, and deduplicate to one row per session.

Then extract conversions: user_pseudo_id, timestamp, and value for each purchase or key event.

Then join them, keeping every session that occurred before each conversion within your lookback window. Ninety days is a common default. The result is a path table: one row per touchpoint per conversion, with a sequence position.

Two decisions matter here and they should be explicit. Lookback window, because a longer window credits more upper funnel activity. And whether direct traffic gets credit or inherits the previous source, which is the convention most platforms use and which meaningfully changes results.

Materialize this path table rather than recomputing it. Every model you build reads from it, and consistency across models is the entire point.

Building first touch and last touch as a baseline

Start with the two extremes, because they bracket everything else and they are simple to verify.

First touch assigns full credit to the earliest touchpoint in each path. Rank touchpoints per conversion by timestamp ascending and keep position one.

Last touch assigns full credit to the final touchpoint. Same ranking, descending.

Run both, group by channel, and compare. The difference between the two is the most informative single output of this whole exercise. Channels that score high on first touch and low on last touch are demand creators. Channels showing the reverse are demand harvesters. Any channel scoring low on both deserves scrutiny.

You now also have a sanity check: your last touch numbers should approximate what GA4 and your ad platforms report. If they do not, fix the path table before going further.

Building linear and time decay models

Linear splits credit evenly across every touchpoint in the path. Count the touchpoints per conversion, then assign each one a share of one divided by that count, multiplied by the conversion value if you are weighting by revenue.

Linear is naive by design, and that is useful. It is the model that assumes no knowledge, so it makes a good reference point.

Time decay weights touchpoints nearer the conversion more heavily, on the theory that recent influence matters more. Compute the time between each touchpoint and the conversion, then apply an exponential decay with a half life. A seven day half life means a touchpoint seven days before the conversion carries half the weight of one on the conversion day. Normalize the weights per path so each conversion still distributes exactly one unit of credit.

Half life is a business assumption, not a statistical finding. Short sales cycles justify a short half life. If your typical path runs three months, seven days will erase almost all upper funnel credit and you will have rebuilt last click with extra steps.

Position based is worth adding if you want a fourth: forty percent to first touch, forty percent to last, and the remaining twenty split among the middle. It encodes a common intuition that discovery and closing matter most.

How to compare models and what to look for

Put every model in one table: channel as rows, model as columns, credited conversions and credited revenue as values.

Then read it for disagreement, not for the answer.

Channels stable across all models are unambiguous. Whatever you were doing, keep doing it.

Channels that swing widely are where your budget decisions have been quietly hostage to a modelling convention. If paid social earns two percent of credit under last touch and eighteen percent under linear, then every prior decision about paid social was really a decision about attribution methodology.

Compute the ratio of first touch credit to last touch credit per channel. Above one means the channel starts journeys. Below one means it finishes them. You need both kinds, and last click reporting only ever shows you one.

Also look at path length distribution. If most conversions have a single touchpoint, multi touch attribution will not change much and you have learned something valuable cheaply.

How to connect attribution output to Looker Studio

Do not let the results live in a query nobody runs.

Write your model outputs to a summary table, scheduled daily. Keep it small: date, channel, campaign, model, credited conversions, credited revenue.

Connect that table to Looker Studio as a data source. Build a report with a model selector as a control, so the same charts can be viewed under any model. Watching the channel ranking rearrange as someone toggles between models is the single most persuasive way to explain attribution to a skeptical stakeholder.

Add your ad spend alongside, and you can compute credited return on ad spend per model, which turns the analysis into a budget conversation instead of a methodology one.

A closing caution

None of these models establish causality. They are accounting conventions applied consistently to observed paths. The value is not that one of them is correct. It is that seeing four of them side by side stops any single one from being invisible.

FAQ

Can you build a custom attribution model in BigQuery?

Yes, and that is the main reason to use it. With the GA4 export you have every session and every conversion at row level, so you can reconstruct user paths and apply any credit rule you choose. First touch, last touch, linear, time decay, and position based are all straightforward once you have built a clean path table to run them against.

How far back should an attribution lookback window go?

Match it to your sales cycle. Thirty days suits most ecommerce. Ninety days is a reasonable default for considered purchases. B2B with a six month cycle needs longer, which also means you need at least that much exported history. Longer windows credit more upper funnel activity, so state the window explicitly whenever you present results.

Why do my BigQuery attribution numbers differ from GA4?

GA4 applies its own data driven model, its own lookback window, and its own rules for direct traffic. Your BigQuery model uses whatever conventions you coded. Differences are expected. The useful check is that your last touch model approximates GA4's last click figures. If it does not, the path table has a problem worth fixing first.

What is the difference between time decay and linear attribution?

Linear splits credit equally across every touchpoint, assuming each mattered the same. Time decay weights touchpoints closer to the conversion more heavily, using a half life you choose. Linear is the neutral baseline. Time decay encodes an assumption that recency indicates influence, which suits short cycles and can badly understate early touchpoints in long ones.

Do I need user_id or is user_pseudo_id enough?

user_pseudo_id works and is available on every event, but it is cookie based, so it resets when someone clears cookies or moves to another device. That fragments paths and undercounts cross device journeys. If you can set user_id at login, use it in preference and fall back to user_pseudo_id, which materially improves path completeness for logged in audiences.