You are getting the unadulterated #truth about the plans & strategies I am hoping to achieve for all of our clients 🙂 while I am testing it out on this website.
If you want to be on the email list for future updates, just download the Cheat Sheet (even if you don’t want it, it’s currently the only way to get onto the email list)
How I am currently strategizing the our clients GA4, Big Query & Looker Studio set ups on May 20, 2022.
Big Query (WTF?)
If you weren’t aware, GA4 has a direct link to Big Query. Read Googles Update
BigQuery is Google’s data warehouse solution. It’s basically a glorified complicated Google Sheet.
You can store huge amounts of data for a few bucks a month.
And now you can store GA4 -> Big Query for FREE99. This was previosly only available to those with deep pockets & on GA360.
Now anyone can do this and start storing thousands of rows of data in seconds.
For reference, I just took a little Peakarooo 👀 at our client accounts and the largest client dataset we have is just short of 500,000,000 rows.
You read that right, just shy of half a billion.
Here are a few Real World Big Query Examples we have running :
- Stick & Churn for a SaaS platform
- We take Google Analytics, SaaS Database, & Payment plans to calculate stick, churn, refund rates, cohorts, and acquisition profitability.
- Speed Optimization for UA
- We port Universal analytics to Big Query daily and reconstruct the data models we need for a client getting 70k+ sessions per day.
- GA4 Event Based Funnels
- Since GA4 to Big Query’s attribution doesnt match the GA4 platform we cant utilize that attribution, but you can build a freaking fast GDS report which looks at App & site interactions.
- Facebook Ads + Google Ads + Stripe + Google Analytics Master Workflow
- If you want your data fast & adjustable, you need to have it stored in your own database. We have huge attribution project with GA4, GA, Stripe, and a bunch of Joining.
Google Analytics + Looker Studio Problems
There have been a few limitations with the Google Analytics & Looker Studio Connections if you have been building any complexity of Dashboards recently.
Looker Studio Speed Loading Times
The more data you have in your Google analytics, the more time it takes to load. A few seconds becomes 10 seconds which turns into 20 seconds and pretty soon your client is sending you a slack message that “Your dashboard is SLOW and not useful”
Big Query allows you to to load thousands of hits in miliseconds.
The problem is that it becomes more complicated. You now need to manage SQL scheduled queries, tables, & nested queries to do so.
I don’t know about you… But I am not a developer and do not want to become one anytime soon.
Count me in #TeamMarketer
Data Sampling
You might run into data sampling if you have a larger volume of events, users, transactions, etc. But with Big Query you will not run into that problem.
Accessing Parameters
As of writing this article GA4 does not show Parameters to GDS so you are not able to create any advanced reports unless you turn those parameters into dimensions.
I do not know if this will change. But It really sucks at the moment.
But in Big Query things are already complicated so getting parameters is just an extra line in a qurey.
It’s Now Complicated
For the majority of our clients we now need to do the following just to create Looker Studio Dashboard.
- Figure out what data to collect
- Collect that information with Google Tag Manager
(<– I did this live for this website)or tell client to collect it in their database - Configure that information in GA4 or their database
- Send the information into Big Query
- Unnest GA4
- Join that information to something else
- Visualize it.
- When something breaks… figure out where it stopped working.
Yep that is it! That is all we need to do (Sarcasm)*
But Ive got a few processes we are working on to solve some of these problems!
Solution #1: Documentation
Seems dumb while I type this, but GA4 is going to require us to document much more thoroughly. Since everything is customizable we need to know what we were thinking in 3 months when we forget what we were doing.
Sneak Peak 🙂
Solution #2 – Big Ass Queries
So in Big Query it is remarkably cheap to query things.
- 1TB free each month
$5/TB after that
So my plan is to create a giant query to un nest every single event_name & parameters in a single query which runs daily.
It has been working, but still need to flesh out a few edge cases with Avery (our developer)
Then we are going to either
Option #1 connect directly to Looker Studio to aggregate those metrics/dimension
or
Option #2 run a custom query of that flat table from GDS, which in theory should allow us to utilize BI Engine more appropriately.
Solution #3 – Build GDS Monitoring Dashboards
We already do this for Facebook CAPI ~ we write an event to Big Query for every transaction so we know what data Facebook is receiving.
But I want to do this for more steps. Like if the number of events dip below a certain point, if its not writing to Big Query we know its not being collected in Tag manager.
Big query will notify us if a Query stops running,
But we need to have a a central client monitoring dashboard with all client KPIs on the page
Did I miss something?
Let me know by sending me an email at “jj at lookerstudio dot vip” and ill update this post with your goodies.
