Building your Own GA4 Rules-Based Marketing Attribution Models using Google BigQuery and Looker
Google recently announced that first-click, linear, time-decay and positional marketing attribution was being removed from Google Analytics 4 and Google Ads in-favour of their data-driven attribution model.
Data-driven attribution offers, in-theory, a number of benefits compared to “rules-based” models such as first-click and linear attribution, including being able to scale to very large datasets, theoretical reduction in bias and the ability to automatically adjust attribution weights as your marketing landscape evolves.
And yet in-practice, having a marketing attribution model that changes the weighting it applies to email marketing interactions, for example, from year-to-year can make comparison reporting meaningless as you’ll never really know whether increased conversions attributed to that channel came from your increased spend or a change to Google’s model.
More importantly, as a “black box” attribution model you’re reliant on trust rather than an ability to inspect rules and code when it comes to weightings applied to each marketing channel, and its important to remember that the primary purpose of Google Analytics 4 for Google is to drive more spend on Google Ads campaigns.
However and as we covered in our blog Event-Based Analytics (and BigQuery Export) comes to Google Analytics 4; How Does It Work… and What’s the Catch?, Google Analytics 4 now allows you to export your event data in full-fidelity into Google BigQuery, opening-up the option to build your own first click, linear, time-decay and other rules-based marketing attribution models using logic you own and can change under your organisation’s control, not Google’s.
As an example of how you can do this, we built our own rules-based attribution models for GA4 that provided first-click, linear, time-decay and last-click marketing attribution along with first paid click, last paid click and last non-direct click models, attributing conversions across multiple purchase cycles for each converter and displaying the results in the Looker dashboard shown below.
The code for this blog can be downloaded in a couple of ways:
A Github gist containing a single Google BigQuery Standard SQL query that sources its event data from the BigQuery GA Sample Dataset and provides an attribution dataset that can be used as a data source for Looker Studio, Preset, Metabase or any other BigQuery-compatible BI tool, outputting one row per converter conversion cycle per attribution model with columns for each of the measures (conversions, conversion value etc)
A Looker block containing a single LookML view, model and LookML dashboard that uses the same underlying BigQuery SQL query but makes the attribution model output queryable interactively in a Looker explore, as shown below
This particular set of attribution models was designed for B2C-type businesses where a customer first opens an account, requests a trial or performs a once-only activity that you want to attribute value for, which is then followed by one or more purchase conversions each of which needs to be attributed using its own “conversion” or “purchase” cycle.
In the example code we use the add_payment_info GA4 event type as the once-only account opening conversion and the purchase event type as the repeating main conversion but these choices can be changed in the SQL code, and the purchase conversions we attribute the number and value of each conversion.
The Looker dashboard allows you to choose which attribution model is used to attribute conversions and conversion value, along with a drop-down selector for the dimension by which those conversions are visualized; the Looker explore provided by the Looker block provides access to a wider set of breakdown dimensions based on default GA4 event properties.
Finally, while we’ve provided the SQL and Looker code for this example of rules-based attribution using the BigQuery GA4 example data export dataset, you will of course need to adapt it to your own particular choice of attribution events, data export location and reporting needs.
The code is in a public Github repo and can be cloned, forked, reused or otherwise adapted for your own use, or we’d be happy to do that for you.
INTERESTED? FIND OUT MORE
Rittman Analytics is a boutique data analytics consultancy and a Google Cloud Platform partner with many years experience working with BigQuery, GA and Looker delivering marketing attribution and other modern data stack implementations.
If you’re looking for some help and assistance adapting this example code or to help build-out your analytics capabilities and data team using a modern, flexible and modular data stack, contact us now to organize a 100%-free, no-obligation call — we’d love to hear from you!
