The purpose of this post is to share how you can use BigQuery to create a first-touch attribution model for a Google Analytics 4 property that crosses multiple streams. I've delayed writing this post because it solves a very specific problem that not everyone will have, but I am publishing it now because I expect this …

This is a quick post to highlight how you can simplify your SQL using the WITH clause. I've used this technique a lot lately to prepare queries for clients that are easy to understand and work with (the queries, not necessarily the clients). Contents Benefits of using the WITH Clause Syntax of the WITH Clause …

When working with Google Analytics 4 data in BigQuery, it is common to analyze the first or most recent activity for a user. For example: How long has it been since each user has logged in? For each user, how much time has passed between their first and most recent sessions? The purpose of this quick post is to share …

The most difficult part of migrating from Universal Analytics to Google Analytics 4 is the reporting. GA4 does not have many of the same built-in reports that Marketers and Analysts have come to expect from previous versions of Google Analytics, and it can be difficult to replicate them. However, with a little …

When using Google Analytics 4 it can be surprisingly difficult to create a table that shows multiple different parameters of the same event as dimensions. As an example, let’s say that you have a news app, and your authors create content in three categories: “sports”, “politics”, or “fashion”. Every time a user shares …

As mentioned in my post on Using BigQuery and Data Studio with GA4, the Google Analytics data is stored as a JSON object in BigQuery (the same is true for Firebase Analytics data collected on a native app). Unfortunately this structure is not good for visualizing your data. The UNNEST operator allows you to run queries …