GA4 real-time report: Using BigQuery and Looker Studio

This post is here to document what I’ve learnt about creating a GA4 real-time report. You need to use Google BigQuery to process data from Google Analytics so it can be delivered near real-time into Looker Studio.

Why do I need a GA4 real-time report?

It’s a simple reason.

I needed to see advertising traffic going to a University website in real-time. This was for A level results day, a key day in the Higher Education calendar.

The GA4 problem

With Universal Analytics (GA3) it was possible to pull new data into Looker Studio every 15minutes.

However, Google has limited GA4 to show updates every 24-36 hours – often only finishing processing updates 48 hours later. Usually this doesn’t cause too much trouble. But as the peak focus of results day lasts a few hours, it’s important to have data that’s near real-time.

This is so we can improve the advertising campaign to make sure people are able to get through to the help line to discuss their results.

You see, the theory goes that as a marketer our goal is to make people’s experience on the website better. Which results in more people visiting the website, which results in more data so we better understand how to make the user experience better.

This is a very simple diagram of the improvement loop.

BigQuery as a possible solution

I’d read that Google BigQuery had the ability to receive GA4 data as it happens (i.e. real-time). However, I had never used it and my SQL is very limited.

This article documents what I learnt over a couple of weeks to build a series of databoards that could be used by my fellow marketers.

Data flow through the systems

I’ve created a few diagrams to record my understanding of how the data flows from the website through each platform to get to the marketer.

System overview

Let’s start with the overview diagram. This shows the data flow from each system.

• So we start with Google Analytics, which sends data into Google BigQuery.
• BigQuery stores the data in a table.
• Looker Studio accesses the data from BigQuery and visualises the data.

A more detailed diagram for the data model

This diagram explains a little more about what’s happening inside each platform in the system.

I’ll write a brief explanation after the diagram.

1. We start with people visiting the website.
2. Google Analytics measures what people do on the website. However, it’s all anonymous so the website owner can’t personally identify anyone. Only see the aggregate.
3. Google Analytics sends this data to BigQuery.
4. Big Query receives all of the data and saves it in a table.
5. We then create a “view” of the data that only includes the fields I’m interested in. This helps to limit how much data is being handled.
6. A scheduled query processes the “view” on a set schedule so it’s updated. This again limits how many resources are being used, and so means the dashboards in Looker Studio will be quicker.
7. Looker Studio takes the snapshot from the scheduled query as a data source.
8. I build the data boards (a visualisation of the data) in Looker Studio using the data source.
9. My marketing colleagues use the data boards to review and analyse the results
10. The marketers then write reports or make decisions for how to improve the website.

Efficiently connect Looker Studio to BigQuery using scheduled queries

I have to say a big thank you to Stockton from Vision Labs (video below) because without his video’s help it would have taken me far longer to do all the SQL in BigQuery for Google Analytics.

Alternative to GA4 for real-time analytics

I’ve used Google Analytics for a long time, however more and more I’m convinced that an alternative analytics platform would serve me better. There are a number of reasons:

Mainly, I very rarely look in GA4. It’s UX is a mess and I don’t enjoy using it. The simple alternative here is to connect GA4 to Looker Studio and build reports and dashboards. This is my current workflow as it’s so much easier to find the data you want and display it in a way colleagues can understand.

But then the question comes. If GA4 is only collecting the data, do I really need it? Could another platform be better suited, something that’s more privacy focused and where I can better control what’s being collected.

The popular choices are privacy focused platforms such as Plausible and Fathom. However, I’m not a fan. Both are designed for small websites and users. Also, from what I can tell it’s difficult to get the data out to visualise in another tool.

TinyBird to the rescue?

My gut tells me something like TinyBird is the solution. It positions itself as data infrastructure for software teams, and it’s used by some big companies for interesting data uses. For example:

• User-facing analytics
• Real-time analytics

I tested TinyBird last year following their real-time tutorial but didn’t get through it enough to get something working. One day I’ll pester one of the devs and figure it out.