BigQuery is here to stay as THE MAJOR WAY of analyzing GA4 data
Ok, look.
If you’re like most marketing / marketing analytics pros, you’ve already realized that we’re now in a (very exciting, but challenging) transition period:
→ from the old world – where GA4 UI was our main reporting tool
→ to the new world – where GA4 is more of a data capturing tool – and we do more & more analysis using raw data (e.g. raw GA4 data in BigQuery)
And whether your goal is to earn more in your day job, or land better freelancing / consulting clients…
…skills like SQL, BigQuery & basic ETL / data engineering are no longer optional.
But there is one, big, hairy, audacious… problem
Working with GA4 data in BigQuery is NOT easy.
My bet is: you’ve already tried it.
And oh my.
Real-life footage of me trying to write my first UNNEST function in February 2023
It’s really not simple – I’ve literally seen Senior Data Engineers go ‘WTF!?’ after seeing GA4 tables in BQ for the first time..
And whether it was while you were trying to…
→ sessionize your data
→ properly access traffic sources
→ deal with the nested format of the tables
→ understand the impact of the Consent Mode implementation
→ or solve another common GA4-related problem
…chances are – you got stuck.
And while you know that learning this whole “BigQuery topic” is important – you can’t seem to be able to find time to really dive deeper into it & catch up with the industry.
(after all, nobody’s gonna manage all your other projects / client work for you…)
But the good news is:
I created a resource where my main goal is to take you…
→ from where you currently are in your GA4 <> BigQuery journey – even if that means “stuck AF”
→ to where you’re comfortable while querying GA4 data in BigQuery – using those advanced queries too!
…in the shortest time possible.
Introducing:Library of 30+ copy-pasteable SQL queries for GA4 data in BigQuery
A complete (and constantly growing) repository of ready-to-use queries for making the most out of GA4 data in BigQuery:
→ from the most basics ones – like basic table flattening…
→ to intermediate ones – like traffic acquisition, user acquisition, landing page performance, and more…
→ to more advanced ones – like like attribution model comparisons, user_id <> user_pseudo_id stitching, fixing Google Ads misattribution, and more…
All queries available in the library are:
Aligned with the industry standards – and regularly updated
I went through most (if not all) resources on the market to make sure that it’s the most practical & usable asset out there.
Queries are updated whenever Google makes changes to table schema
Intentionally written in a beginner-friendly, copy-pasteable way
So that you don’t need to become a full-time data analyst to make the most out of GA4 data in BigQuery.
Each query can be copied directly from the library, and used in BigQuery in 2.792 seconds (tested with a sprinters’ timer…)
With video explanations attached to each query
So that – whenever you want – you can understand how the query was created, and how you can adjust it to your needs.
(Btw, there’s over 3.5 hours of videos available inside the library!)
Full list of queries available in the library
Let’s address THE question – what queries can you find in the library?
As of Jan 14th, this the complete list (it will be growing over time):
1. Basic, “warmup” queries:
● total number of users
● total number of users per date
● total number of new website users per date
● and more!
2. Default reports from GA4 interface:
● basic table flattening
● user acquisition report
● traffic acquisition report
● tech performance report
● page performance report
● landing page performance report
3. Daily / weekly / monthly summaries:
● daily website performance summary
● weekly / monthly / quarterly website performance summary
4. E-commerce:
● daily e-commerce summary
● weekly / monthly / quarterly e-commerce summary
● product performance report
● e-commerce funnel performance
● products frequently bought together
● most common product sets
● [NEW – added November 2024] product category vs traffic source
5. Advanced queries:
● fixing Google Ads misattribution
● multi-touch attribution query
● attribution model comparison
● cohort retention
● connecting GA4 tables with CRM data – basic user_id <> user_pseudo_id stitching
● [NEW – added December 2024] advanced user_id <> user_pseudo_id matching (using merged_id)
Oh, and the bonuses
Here are additional bonuses waiting for you once you get access to the library:
BONUS #1: Intro to GA4 <> BigQuery
● how to set up Google BigQuery
● how to set up GA4 <> BigQuery connection
● how to make sense of the complex format of GA4 tables in BigQuery
BONUS #2: BigQuery cost management
● BigQuery pricing structure
● how to set up monthly budgets to make sure you control your costs
● how to optimize your queries & processes to minimize your cost
BONUS #3: Intro to simple data pipelines
● intro to creating automated queries in BigQuery
● intro to visualizing your BigQuery tables in Looker Studio
● intro to Dataform – coming soon!
BONUS #4: Lifetime access to a dedicated Slack community
● ask questions, network with fellow professionals & stay on top of the industry – getting access to the library gives you a lifetime access to our dedicated Slack community
Okay, so how much does it cost?
Good question – and that’s something that, quite honestly, I thought long and hard about.
After all, if you provide web / marketing analytics services, you could easily charge at least $2000-5000+ for implementing reports using some of those more advanced queries (custom attribution models, connecting GA4 data w/ other data sources etc.).
So $600-800 would be a very fair starting point for this library.
I mean, I charge *at least* $250 / hour for consulting jobs that require those skills.
But the idea was to create a resource available to anyone – not just US-based marketing analytics consultants
So I decided, for now at least, to go with the price which makes it available to pretty much anyone – regardless of their location & job seniority.
So I stuck with…
…$2* per query – which is kind of ridiculous when you consider that some of them are 130+ line SQL behemoths returning things like custom multi-touch attribution models, super-advanced way of user_id <> user_pseudo_id stitching & other cool stuff.
*$2.33 to be precise ($67 / 30)
I mean, even if – hypothetically – you’d only use only one advanced query from the library, $67 would still be… a steal considering how much time (and expertise) it takes to write them – and there’s 30 of them.
Tier #1
$127 $67
Library of 30+ SQL queries
● Library of 30+ copy-pasteable SQL queries for GA4 data in BigQuery – divided into 5 categories:
– basic, warmup queries
– default reports from the GA4 interface
– daily / weekly / monthly summaries
– e-commerce queries
– advanced queries
● 3 bonus tutorials
– intro to GA4 <> BigQuery
– BigQuery cost management
– intro to simple data pipelines
● Dedicated Slack community
● Lifetime access to all resources – list of queries is growing over time + all existing queries are regularly reviewed & updated
Tier #2
$247
Library of 30+ SQL queries
+ 3 done-for-you queries
● Library of 30+ copy-pasteable SQL queries for GA4 data in BigQuery – divided into 5 categories:
– basic, warmup queries
– default reports from the GA4 interface
– daily / weekly / monthly summaries
– e-commerce queries
– advanced queries
● 3 bonus tutorials
– intro to GA4 <> BigQuery
– BigQuery cost management
– intro to simple data pipelines
● Dedicated Slack community
● Lifetime access to all resources – list of queries is growing over time + all existing queries are regularly reviewed & updated
● 3 done-for-you queries – you tell me about your needs, and I write queries for you
Tier #3
$497
Library of 30+ SQL queries
+ 3 done-for-you queries
+ 1:1 60 min coaching / consulting call
● Library of 30+ copy-pasteable SQL queries for GA4 data in BigQuery – divided into 5 categories:
– basic, warmup queries
– default reports from the GA4 interface
– daily / weekly / monthly summaries
– e-commerce queries
– advanced queries
● 3 bonus tutorials
– intro to GA4 <> BigQuery
– BigQuery cost management
– intro to simple data pipelines
● Dedicated Slack community
● Lifetime access to all resources – list of queries is growing over time + all existing queries are regularly reviewed & updated
● 3 done-for-you queries – you tell me about your needs, and I write queries for you
● 1:1 60 min coaching / consulting call – where we solve all your marketing analytics-related problems & challenges
What people are saying about the library
Bragging ain’t cool – but people really seem to like the library!
Here are some messages from over 83 people who already got access to the library…
*one of the first ones was Fred Pike (5x CXL Google Analytics instructor) – and below is his heart-warming testimonial*
About the author:
Oh, hello.
I just realized that I haven’t introduced myself yet.
So there we go – I’m Adam, and for the last 10 years I went through a journey of being a professional BJJ athlete, owning a data & marketing agency, and now working on sharing skills I developed along the way as usable, ready-to-use digital products – like this one.
Since 2018 I’ve been heavily focused on mastering SQL & data skills in general – and since early 2023 I’ve been focused on GA4 data in BigQuery.
I also had a chance to speak at a bunch of cool data & marketing conferences – below I’m sharing some proof pics hoping that you’ll think I’m a little bit cool as well!
The library of queries is my way of helping you take the efficient way – and to access advanced insights without spending months / years:
i) learning SQL
ii) and then learning how to apply it to raw GA4 data in BigQuery
$29
Monthly Plan
Capitalize on low hanging fruit to identify a ballpark value added activity to beta test, leverage agile.
$59
Agency plan
Iterative approaches to corporate strategy foster collaborative thinking, capitalize on low hanging.
$59
Agency plan
Iterative approaches to corporate strategy foster collaborative thinking, capitalize on low hanging.
Frequently Asked Queries (I mean, questions…)
Here are some answers to questions people frequently ask:
A library of 30+ copy-pasteable SQL queries for GA4 data in BigQuery.
As of now, the library has been released to 4 cohorts (83+ people in total).
The enrollment to the 5th cohort will start on January 19th.
The price of the library will start from $97.
100%. I’m reviewing the queries regularly to make sure that they’re aligned with the industry standards & with most recent structure of GA4 tables in BigQuery.
All intermediate-to-advanced queries have video explanations attached to them to help you understand how they were created & how you can adjust them to your needs.
BUT if you’ll have any followup questions, you’ll get access to a dedicated Slack community (where I’m available almost daily) – and you can ask your questions there.
For now the library lives in Notion – in the future it might be moved to another platform (T.B.D.).
I’ve spent A LOT of time making sure that you’ll absolutely love it!
But if for any reason you don’t, reach out to me at: hello@ga4bigquerylibrary.com within 7 days after your purchase, let me know what you didn’t like, and I’ll refund you the full amount.
Still have some questions I didn’t answer?
Reach out to hello@ga4bigquerylibrary.com or connect with me on LinkedIn!
Soo... are you ready to join?
Look – at this point you have 2 choices:
Choice 1: figure it all out by yourself
I know you’re smart, and I think you can definitely do it – I’ve done it, so you can do it too.
BUT it will cost you time, energy, money (e.g. for running inefficient queries) & A TON of frustration – been there, I know.
Choice 2: take the efficient way, get access to the library…
…and for $2.33 per query, find yourself in the world where you have a direct access to the library of expert-curated, copy-pasteable, ready-to-use SQL queries for GA4 data in BigQuery.
The choice is – obviously – yours, although I…
-Adam