Skip to main content

🔒 GA4 | tutorials

How to build your own GA4 attribution model comparison tool in BigQuery and Looker Studio

This tutorial teaches how to prepare GA4 export data input for attribution modeling, create different models using SQL, combine all data in one table, and create a model comparison tool in Looker Studio.

Marketing attribution can be compared to solving a mystery, with multiple clues that lead to a conclusion. But just like in a mystery, not all the clues are equally important, and some may even be red herrings that distract from the truth.

Or maybe there may not be an absolute truth to uncover, as different attribution models can lead to different conclusions.

To be able to compare your (custom) attribution models, this tutorial will help you understand how to build your own rule based attribution models, using SQL and the GA4 export data in BigQuery, and how to create an interactive model comparison tool using Looker Studio.

Half the money I spend on advertising is wasted, the trouble is I don't know which half.

This famous quote highlights the challenge of measuring the impact of marketing efforts. The quote is commonly attributed (pun intended) to John Wanamaker, a successful merchant and marketing pioneer in the late 19th and early 20th centuries. However, the exact origin of the quote is unclear and it is possible that Wanamaker may have never actually said those exact words.

What is marketing attribution?

Let's take a step back. What is digital marketing attribution? Well, put simply, it's the process of identifying which marketing channels or touchpoints were responsible for driving a particular conversion or action on your website or app. In other words, it's a way to measure the effectiveness of your marketing efforts and determine which channels are contributing to your overall success.

Now, in the context of GA4 there are two main types of attribution models:

  • single channel
  • multi channel

Single channel models assign 100% of the credit for a conversion to a single touchpoint, usually the last click before the conversion. This means that if a user clicks on an online ad and then converts, the credit for the conversion is attributed solely to that ad.

Examples of single channel attribution models:

  • First click
  • Last click
  • Last non-direct click

Multi channel models, on the other hand, take into account all of the touchpoints that a user interacted with on their path to conversion. These models assign credit to each touchpoint based on a predetermined set of rules or algorithms.

For example, a multi touch attribution model might assign 40% of the credit to the first touchpoint, 30% to the last touchpoint, and split the remaining 30% between any touchpoints in between.

Examples of multi channel attribution models:

  • Linear
  • Position-based
  • Time decay
  • Data-driven
In this tutorial we will focus on - rule-based - single and multi channel attribution models. That means the data driven attribution model is out of scope for this tutorial, as it uses machine learning algorithms to attribute conversion credits to different touchpoints and can not be replicated using only SQL in BigQuery. If you're comfortable with Python as a programming language, this approach by Krisjan Oldekamp is highly recommended.

Attribution models in GA4

In Google Analytics 4 you will find most of the attribution models mentioned above. By default (if there is enough data available) it will use the data-driven model, but you can also switch to other models.

This will only impact the events data in GA4 reports. The user scoped traffic_source fields and the session level event parameters like source and medium in the BigQuery export will remain unaffected.

Pros and cons of different attribution models

First click attribution model
Credits the first touchpoint encountered by the customer in their journey.

  • Pros: simple, helps identify new customers, incentivizes marketers to focus on building brand awareness
  • Cons: doesn't give credit to touchpoints that come later in the journey and may have played a crucial role in conversion

Last click attribution model
Credits the last touchpoint encountered by the customer before converting.

  • Pros: simple, easy to implement and measure, gives credit to touchpoint most directly responsible for conversion
  • Cons: ignores other touchpoints that may have contributed to the conversion

Last non-direct click attribution model
Credits the last touchpoint that is not direct traffic.

  • Pros: gives credit to touchpoints that may have contributed to the conversion but weren't the direct cause of the customer coming to the website
  • Cons: may still ignore touchpoints that came before the last non-direct click

Linear attribution model
Distributes credit equally across all touchpoints encountered by the customer on their journey.

  • Pros: gives credit to all touchpoints in the journey, can help identify patterns in the customer journey
  • Cons: may not accurately reflect the importance of certain touchpoints in the journey

Time decay attribution model
Gives more credit to touchpoints that are closer in time to the conversion.

  • Pros: acknowledges the fact that touchpoints closer to the conversion are often more important, can help optimize marketing efforts in the short term
  • Cons: may not give enough credit to touchpoints that came earlier in the journey but still played an important role

Position-based attribution model
Gives more credit to touchpoints that are at the beginning and end of the customer journey, and less credit to those in the middle.

  • Pros: acknowledges the importance of touchpoints that initiate and close the customer journey, while still giving some credit to touchpoints in the middle
  • Cons: may not accurately reflect the importance of certain touchpoints in the journey, particularly if there are multiple touchpoints at the beginning or end of the journey.


It's important to note that, while digital marketing attribution can be incredibly useful in understanding the impact of your marketing efforts, it does have its limitations.

Attribution models are only as good as the data they are based on, and there are often gaps in the data or inaccuracies in tracking that can lead to incorrect attributions. Additionally, there are many factors outside of your marketing efforts that can influence a user's decision to convert.

That's why the industry slowly moves towards incrementality. It's a different approach that looks at the impact of a particular marketing activity compared to a control group that didn't receive that activity. This can give you a much clearer idea of whether a particular activity is actually driving incremental value or not.

Use cases

That being said, there are still many use cases where digital marketing attribution can be valuable. For example, it can help you identify which channels are driving the most revenue, which campaigns are most effective at driving conversions, and which touchpoints are most valuable in the customer journey. This information can help you optimize your marketing strategies and allocate your budget more effectively.

In this tutorial you will learn how to:

  • prepare your GA4 export data input for different attribution models
  • create different attribution models using SQL
  • combine all data in one table, that will feed the model comparison tool
  • create a model comparison tool in Looker Studio