The Best Charts for Visualizing Website Trends and Metrics in LookerStudio
Overwhelmed by data visualization? Don’t fear, we’ve got you covered with this ultimate guide to everything Looker Studio charts!
How to build your GA4 Realtime Report with Looker Studio & BigQuery with pics
Introduction You want to be able to view your GA4 data in real-time, but you’re having a hard time making up from down with the current GA4 real time report. You have come to the right place! We’re going to explore everything necessary to have a beautiful real time Looker Studio report using the GA4 export data inside of BigQuery. GA4 has a report that will show you “real time” data, but it’s scoped to the last 30 minutes and has a number of fatal flaws that make it less than useful. It basically serves as a glorified hit counter and does nothing to tie in performance or session-based data. We’re going to solve that! Is Real-Time GA4 Data For You? This is definitely a question that we’re still asking ourselves. If you know that you absolutely need and want to be able to view your site data in real-time, definitely let us know by sending an email to hello at visionlabs.com Building the report that we’re going to go over in this report isn’t free. You will have some very small costs associated even with just turning on the “real-time” connection between GA4 and BigQuery, as well storing your BigQuery data, and running the queries. Everything is pretty small, except for the queries and the time to build the report. Depending on the usage of the report, the query cost could grow pretty quickly. More on that later. We’ve identified a few use cases where it does make sense to invest the time and resources for a real-time GA4 report using BigQuery: If anything were to go wrong with your website and a few hours of the problem going undetected is a huge problem, then a real-time GA4 report is just the thing you need to stay frosty! What’s Wrong With The Built In GA4 Reports? Oh boy oh boy! I’m going to tell you! The obvious problem is that the standard reports and explorations take 24-48 hours to populate. Google uses this time to process the data in order to display it properly. What all happens is a bit of mystery, but we know that in some cases they will model and predict the data. Under the reports tab there is a page at the top title “realtime” which shows some activity that has happened over the last 30 mins. This report shows: With all of these cards, what they don’t show is anything related to a session (session source/medium/campaign/landing page) and actual performance tied to those session based dimensions. Additionally, even once the data is processed there are some big drawbacks to the reports and explorations that are currently offered: How Does BigQuery Solve The Problem? Linking your GA4 property to BigQuery gives you a database full of the raw event-level data of your website. Using SQL it’s possible to isolate the exact dimensions and metrics that are valuable to the business, sessionize events, clean up all the issues with page location and landing page, and even build our own data models for any other application. What we’re going to explore is how to use the “intraday” streaming data (that’s the real-time data stored in BigQuery) to build a Looker Studio Report. How Much Does This Cost To Use? There are 3 main costs that you’re going to incur while building this type of report. The first is the cost of your time and energy to build the queries, and plug those into a Looker Studio report. The second is the cost of streaming data from GA4 to BigQuery. The third is the cost of running the queries from Looker Studio. In other words, actually using and interacting with the report. Let’s break down the costs in more detail: Keep in mind that you have 1TB free of queries every month and each TB after that is billed at $5 per TB. So it is still relatively inexpensive to run. We’ve also provided a query usage monitor for you to keep track of how your usage is trending. Building The Session Summary Query Let’s now take a look at how to build a query that can read the streaming, real-time events coming in from GA4 to BigQuery. This assumes that you already have this connection made between your GA4 property and Google Cloud. Goals for the query Here are the main objectives that we’ll be aiming for as we build out this query: Step 1. Build An Unnested Events Table In step 1 we’re going to use a Common Table Expression (CTE) to build a flat table containing all of the fields we want to use in our report. Later we’re going to use an analytic function to extract the “first value” of these fields as dimensions so first we have to make a flat table containing all the fields we want as step 1. Then in step 2 we can find the first value for each field. Extract Session ID One of the most essential parts that make this whole thing work properly. If we want to be able to see session data (Landing Page, Source, Medium, Campaign, Content, Term, Country, and Device) then we need to know what ID we’re going to group on and use to unify all the events pertaining to the same session. In BigQuery, the session ID is part of the event_params on every event. The ID represents a timestamp for when the visitor’s session started. We can access the session ID for every event by doing a scalar sub query on the nested event_params field. The query looks like this: There’s one problem with this… what if 2 people land on the website at the exact same time? To account for this (rare) occasion, we’re going to concatenate the session ID and the user pseudo ID together to form a more perfect ID that represents a session. The revised query looks like this: Format The Event Timestamp For Your Timezone This query is going to be responsible for grouping
Big Query, GA4, & Looker Studio; Solutions Are In The Works.
The unadulterated #truth of my plans & strategies for GA4 + Big Query while I continue to test things on a few of my owned websites.