Google Analytics 4 + BigQuery: Tutorial of Linking Google BigQuery + GA4
HTML-код
- Опубликовано: 14 ноя 2022
- One of the big benefits of GA4 is supposedly the free integration with Google BigQuery. This video explains Google BigQuery and the GA4 Export Schema. We also walk through how to create a Google Cloud project for BigQuery and how to integrate it with a GA4 property.
Have questions or comments? Leave them in the comments. I'm always up for learning more...and for new video ideas.
There's typically a new video every week or two about GA4 or Google Tag Manager or Looker Studio or sometimes Google Ads or SEO. If you want to follow along with what I'm learning (trying to learn) you can subscribe at the link below:
ruclips.net/user/rootbranchd...
🔗 Links to 📖📖📖 BLOGS: These are the most popular blogs on the Root and Branch site from RUclips visitors
✭ Do These 10 Steps to Install AND Configure GA4 Properly: www.rootandbranchgroup.com/ga...
✭ Events in GA4 and How it Compares to UA: www.rootandbranchgroup.com/ev...
✭ GA4 Reports Explained: Standard Reports, Explorations, Custom Reports: www.rootandbranchgroup.com/ga...
✭ Google Analytics 4 vs. Universal Analytics: www.rootandbranchgroup.com/go...
✭ A Practical Guide to Event Parameters: www.rootandbranchgroup.com/ga...
✭ GA4 Problems (And What You Can Do About Them): www.rootandbranchgroup.com/ga...
✭ Google Analytics 4 Link Click Tracking: www.rootandbranchgroup.com/go...
✭How to Install Google Tag Manager (GTM) on Wordpress: www.rootandbranchgroup.com/ho...
✭And Some SEO! How to Edit robots.txt on Wordpress!
www.rootandbranchgroup.com/ed...
🔗 Links to 📹📹📹 VIDEO TUTORIALS: These are most popular GA4 instructional videos as measured by views or watch time
📺 How to Set Up GA4 in 5 Minutes With GTM: • Set Up GA4 (in 5 Minut...
📺 How to Properly Configure Your GA4 Property: • 10 Steps to Configure ...
📺 6 Key Differences Between GA4 and UA: • Google Analytics 4 (GA... 🔑🔑🔑🔑🔑🔑
📺 The Truth About GA4 Event Parameters: • The Truth About GA4 Ev...
📺 GA4 Hits, Events, Sessions, and Users Explained: • Google Analytics Event...
📺 How to set up GA4 FORM SUBMISSION tracking with a thank you page: • How to Set Up a GA4 Fo... 🙏
📺 How to set up BUTTON CLICK tracking with GA4 and Google Tag Manager: • Set Up Page Timer Trac...
📺 How to set up PAGE TIMER tracking with GA4 and Google Tag Manager: • Set Up Page Timer Trac... ⏲
📺 How to Set Up LINK CLICK Tracking with GA4 and Google Tag Manager: • Button Click Tracking ... 🖯
📺 How to Set Up SCROLL DEPTH Tracking (25%, 50%, 75%, 90%) with GA4 and Google Tag Manager: • How to Set Up GA4 Link... 🖯
📺 How to Build a Link Click Tracking Report Using GA4 data and Looker Studio: • Google Data Studio GA4... 📊📊📊
📺 Campaign Tracking in GA4 to See UTM Tagged Traffic: • Campaign Tracking GA4 ...
📺 GA4 Conversion Tracking Compared to UA: • GA4 Conversion Trackin...
📺 Registering Custom Dimensions in GA4 Explained: • GA4 Custom Dimensions ... -
📺 How to Link GA4 and BigQuery: • Google Analytics 4 + B...
📺 Users in GA4 Explained vs. UA Users: • GA4 Users Explained: N...
📺 Creating and Using Audiences in GA4: • How to Create GA4 Audi...
📺 3 Practical Ways to Use the Path Exploration Report: • GA4 Path Exploration: ...
📺 How to Set Up Site Search in GA4 and UA: • Video
📺 Video review of the Source/ Medium report in GA4: • GA4 Reports: How to Re...
📺 All about the GA4 Certification: • Google Analytics 4 Cer... 🏆
Follow Root & Branch on LinkedIn: / root-branch
Or
Connect with me (Hey there!)
/ zackduncan
About Root & Branch: www.rootandbranchgroup.com/ab...
Thanks for coming by!
- - - - - - - - - - - - - - - - - - - - - -
Google Analytics can be hard, but there is someone whose yoke is easy and whose burden is light.
Rest easy. Mathew 11: 28 - 30
Meeting the Real Jesus - Tim Keller: • Meeting the Real Jesus...
Excellent vid! Thanks for taking the time to help the community.
Thanks for the kind words, Ebenezer. I sincerely appreciate it and I'm glad that you found it to be a helpful start with the GA4 / BigQuery connection.
Good luck and Godspeed to you in all things digital analytics in 2023!
Great job buddy! It helped me to understand and create the BigQuery connection.. Hopefully I will be able to use it in Looker Studio and fix my GA4 dashboards! :)
Eric! I'm glad to hear.
Thank you for the kind words and the positive report on how it helped. I wish you all the best with those Looker Studio dashboards.
Good luck and Godspeed!
Zack
Just set this up, thanks for the informative video! Hopefully data starts to pull through this week.
That's great to hear, Ryan. Glad it was useful for you!
If it works as you're hoping and you feel like sharing some of your next steps with others, let me know if you'd want to talk about doing some kind of guest blog on rootandbranchgroup.com. No pressure, but I think it would be cool to get a few mini case studies / examples from folks who are starting to use this tool. Would help us all learn.
Wish you all the best either way. Cheers!
Thank you that helped to start my journey into BQ
You’re quite welcome. Thanks for taking a minute to share your feedback….and best wishes on the next steps of your journey!
This is such a useful video, thank you!
Very glad to hear it was worth your while, David. Thanks for the kind words! 😎
thank you so much! Very useful video!
You're very welcome, Yenlik. I'm glad it was helpful.
Hi Root&Branch team,
I would like to mention that your video was great.
With Universal analytics being deprecated and GA 4 taking over. I am eagerly awaiting your informational content on how to go about it using Google Big Query in order to automate the reporting.
Thank you for the kind words and feedback. I'm glad you found this video useful.
I'm hoping we'll have another BigQuery specific video within the next month or so. There are also some other channels here that do a lot more with BigQuery that you might want to poke around and look for. Wishing you all the very best!
I thought this was a fantastic introduction on how to make the connection between Google Analytics and Google Big Query. Certainly gives me confidence to get my own integration started right away. Thanks for putting it together.
Thank you, Garrett. 🙂 I am glad to hear it was worthwhile for you.
By the way, feel free to let me know if you find BigQuery to be valuable for you / your business. I’m very much still in the learning phase on this end and hoping to learn from others. Would love to have some real people down the line who might want to share their experiences (wins / frustrations / tips / lessons learned / business cases / etc) and be a guest author / instructor on rootandbranchgroup.com.
Keep it under advisement. 😎
Good luck and Godspeed!
Zack
Great Video! Thank you
Appreciate the feedback, Ateeq. 🙏 You are most welcome!
Really valuable your content bro, hope you keep going!
Henrique, thanks so much for the kind words! 🙏
Glad you found some of it useful.
Excellent Video
Thank you, Conor. I appreciate the encouraging words!
Awesome video! Thanks for helping me get the BigQuery connection setup so easily. Is there currently any way to backfill the GA4 data into BigQuery from before we setup the connection?
Hi Gage, I've never done this myself but I found this article which may be useful for you: www.optimizesmart.com/how-to-backfill-ga4-data-in-bigquery/
By the way, if this works for you and you want to share your experience with others (myself included), you can be a guest blog author on rootandbranchgroup.com. No pressure, but keep me posted if that happens to sound interesting down the road.
Good luck and Godspeed!
Great vid. Would love to see more real world data crunching and analysis. Incidentally, is that something you might know how to acquire? In other words, I also am trying to learn/re-learn some of this stuff. But even after putting up a website and connecting this stuff it feels a bit pointless since I'm not getting the kind of traffic I need to really create a lot of data. I don't suppose Google or idk..somebody..has come up with some fake data that we could add to GA4 to actually play with the dashboard etc..? Would that be a thing? Otherwise, how do you actually learn GA, BigQuery, etc...?
Thanks Tyson. I appreciate the feedback. And I'm with you, I'd love to get some hands on experience with actually *using* the GA4 data with BigQuery. It's on the future to do list!
BigQuery has a sandox where you might be able to do some of this. I'm honestly not sure how it works, but I found this link that might be useful: cloud.google.com/blog/products/data-analytics/query-without-a-credit-card-introducing-bigquery-sandbox
Another place to get some practice in with GA4 is the GA4 Demo Account for the Google Merchandise Store. I'm not sure if you can do anything BigQuery related (doubt it), but it's still interesting to see how Google has set up their own property. I made a video about that if you're interested: ruclips.net/video/sTsVoVo_mZE/видео.html
Wish you all the best! I hope both of us get some more hands on experience with actual analysis in 2023
TL;DR:
In this video, the author explains how to link Google Analytics 4 to BigQuery using APIs. He shows the process step by step, including enabling APIs and services, creating credentials, and selecting a role for the service account. The author also demonstrates how to access the raw data and run a query using the SQL workspace. Overall, the video provides a brief introduction to using BigQuery with GA4.
-- Summary by chrome extension: Ask The Video.
I didn’t know there was chrome extension for this. Thanks for the excellent summary.
Thanks for the video with great instructions, btw, you have mentioned you are learning bigquery…can you share how you are learning any resources you find helpful…cheers and thanks sharing 🙏 🙏
Hi there, Dick.
I'm sorry to say I haven't made a ton of progress (and that's an understatement). Life has gotten in the way of those aspirations.
I have learned enough to become very interested in this tool (analyticscanvas.com/), which I think can make BigQuery more usable for those of us who aren't going to be javascript experts. I don't know enough yet to give a full recommendation, but I think it might be worth exploring for those who have a serious need for BigQuery, are willing to pay for tools, and also need help with extracting data.
Best wishes.
Zack
thanks a lot!
You’re welcome! Best wishes to you. 👍
Great video as always.
One question please, is there a way to send more than 1M events from standard GA4 property to bigQuery ?
Thank you.
Hi Karim,
I appreciate the feedback and am glad you found it helpful. I actually don't have personal experience with this situation, but here's what Google says at support.google.com/analytics/answer/9823238?hl=en:
"BigQuery Export limits
Standard properties have a daily BigQuery Export limit of 1 million events. If your property consistently exceeds the export limit, the daily BigQuery export will be paused and previous days’ exports will not be reprocessed."
So it sounds like there's some downside and Google wants you to pay up for GA360. Let me know if you are able to find out anything else. I'd be curious if there's another option that might be out there.
Excellent Video. Are we able to maybe add additional field like insert-timestamp to keep track of when event are inserted in table? Sometimes there is latency issues and some events arrive late. So filtering on event_timestamp might not be the best way to go about it. Is there a way we can automatic custom field creation on event_intra everytime that table gets created and set the default value to current_timestamp?
Thanks for the kind words and the insightful question. I don't have any experience with this myself so I can't say for certain. But I found this article that might be worth checking out: www.optimizesmart.com/bigquery-ga4-schema-send-custom-ga4-data-to-bigquery/
Good luck and Godspeed to you!
Hi, a good explanation! I have 2 questions:
1) If I have correctly activated everything for 1 property and I need to add some more (from the same Google account), do I have just to set the connection on GA or do I have to do something also in BigQuery? In the second case, what should I do exactly?
2) How can I get past days' data (past in relation to the activation of the connection, GA4 has these stats)?
Thanks.
Hi there.
For question #1, if I understand correctly, you should be good to go after you link the new GA4 property to BigQuery. You can have multiple GA4 properties within the same BigQuery project. Each property will be its own dataset.
For question #2, the data is not retroactive. Here's an article that might be useful if you want to look into backfilling your data: www.optimizesmart.com/how-to-backfill-ga4-data-in-bigquery/
Best of luck.
Loved the video! I have a client that uses google ad manager (gam) instead of ga4. Is it possible to do the same linking? I'm trying but I can't see how to make it work :(
Hi there. I am not that familiar with GAM myself, but I believe it's a very different process to get your data into BigQuery. Here's a resource a found that might be helpful.
cloud.google.com/bigquery/docs/doubleclick-publisher-transfer
Best of luck! If you decide to move forward with it and can get it to work, let me know if you want to be a guest instructor here on this channel to demonstrate it. :)
Ps. Thanks for the kind words.
Hi, how it Google Big Query priced? It looks promising for my project where I want to connect differentdata sources (own DB, GA4, FB Ads and Google Ads)
The tool is "free" to use, but there can be a cost based on how much data you're using.
I found this resource below that you may want to check out. The first tebibyte (1.1 terabyes) each month is free, after which it's $6.25 per tebibyte.
cloud.google.com/bigquery/pricing
Hey friends, I am wondering if I can synchronize the data from a Google Ads account through Google analytics to BigQuery and then export it to backfill other Google Ads accounts, because the basic data of each new Google Ads account is blank. re-running ads is equivalent to starting from 0, which will be very troublesome. It would be great if there was something like the Facebook pixel that could realize data sharing, which means that I only need to run the data of one of the accounts. , other accounts can directly use this data
Hello there. I personally don't know if this can be done. But that probably says more about my own knowledge than about what may be technically possible.
Wish you all the best and hope someone can help you. Good luck! (Ps. If you figure it out and feel like sharing your expertise, let me know)
Hi, loving your video, but i have a problem to connect ga4 with big query, in the ga4 it says "you do not have sufficient acces to the selected google cloud project, permissions required... "
can you help me with these?
Hello there. This is frustrating and confusing, especially because (I assume) you created the project and there shouldn't be anyone else with higher access levels. I don't have personal experience with it myself, but I found this thread online that was started by someone who I believe was having the same issue as you.
This might be a good place to start. groups.google.com/g/gce-discussion/c/rO2ZUtjM6II?pli=1
I wish you all the best. Good luck!
Hey, will the Analytics table (the one we will be querying) show up until we have some data? Currently, I'm not seeing it, all I see is External Connections.
Hi Carmina, the data export is not retroactive. This could be (?) why you're not seeing the analytics table. Although if it doesn't show up in a day or two, I'd surmise that something might not have gone totally correctly during the setup process.
Related: if you want to try and backfill your data from dates prior to the initial setup, this blog might be useful. I haven't done that myself, but could be worthwhile.
www.optimizesmart.com/how-to-backfill-ga4-data-in-bigquery/
Good luck and Godspeed to you.
@@rooted-digital
Hi. Thanks for the tip. I now see the "analytics_..." table; under it, I see "events_...". So, basically, I can now see what you are demonstrating here. My next step is to be able to put together a query so I can pull page views.
Fingers cross!
can you explain me how data is coming from BQ to GA4 is if it is coming through internet is there any option with take the data through internal mode
If I understand correctly, you'd like to manually upload your data to BQ. I don't know how to do that myself, but I found this Google resource on loading data to BQ that might be worth checking out: cloud.google.com/bigquery/docs/loading-data
Best wishes!
thanks buddy @@rooted-digital
How can I extract data for a certain period and not only by single day?
This thread on Stack Overflow might be helpful: stackoverflow.com/questions/67747147/bigquery-time-range
Good luck!
Hi, its possible connection BIgQuery with GA4 Demo account?
Hi Elen,
Not that I'm aware of. There is, however, a free sandbox, for those who want to get some practice using BigQuery and decide if it's the right fit for them.
Link below if you're interested:
cloud.google.com/bigquery/docs/sandbox
If you find a better way to get at what you're looking for, please let me know. I know I have a lot to learn in this area.
Good luck!
Zack
@@rooted-digital Hi Zack, I found a public dataset of GA4 right now, but thank you, if you let know, ping me
I'm using these to link data from GA4 to Bigquery, but I missed data from one day. So how can I check the reason why?
Ty!
I'm not sure I understand what you mean by missing data from one day. And even if I did, unfortunately I don't think I'd be able to answer your question for you.
I did find this article that deals with why GA4 reports and Bigquery data don't exactly match. This might be a good place to start to understand the differences in data between these two places: www.ga4bigquery.com/why-your-bigquery-results-dont-exactly-match-with-the-google-analytics-reports/
Wish I could offer more assistance. Best of luck to you.
@@rooted-digital ty so much. I mean we have data for 7 days on GA, and while the Bigquery just has data for 6 days, there is one day that we don't have the data into bigquery
@@congly5708 I see. I haven't tried to address this myself, but this article on how to backfill GA4 data to BigQuery might help if you find you really need that extra day: www.optimizesmart.com/how-to-backfill-ga4-data-in-bigquery/
Best wishes. 🙂
I have done this but my analytics data don't show up at bigquery, how much time does it takes to i am able to see my data
Google says it should be within 24 hours that data is flowing.
support.google.com/analytics/answer/3416092?hl=en
Hopefully you are seeing it by now. Let me know if not and I'll see if there are any other resources I might be able to send your way.
@@rooted-digital Thank you so much, data started the came after a day
Glad to hear it!
By the way, are you writing your own queries to extract the data? Or using a tool / service?
I don’t know much about this (about sql or extracting the data for practical purposes in general) and am thinking through how I’m going to proceed.
18 mins into nothing, still can't figure out how the hell google analytics data can be retrieved or how to even use BigQuery which is the video's subject
Edit: thanks a lot for the channel's response to me, I still learned something other than the video's topic, which is not to let out your frustration at someone trying to help even if he didn't, sorry and thanks
Hi there. Sorry to hear this was not useful for you. Personally, I'd really like to be use BigQuery on a more practical level, but the process of setting it up and linking with GA4 was a big first hurdle for me. As you know after watching it, that process of set up and linking is the focus of this video. I can take a look at the video title and description to try and be clearer.
If there is anything else GA4 related (but not BigQuery, since I likely don't know) that you're stuck on, feel free to let me know. It's possible I was, or am, stuck myself and might have a resource or two to point you towards to try and get a little ROI on the time you've invested here. If not, no sweat. I suspect you might have a higher level of GA4 knowledge than me given what you're now hoping to do with BigQuery.
In any event, wish you all the best. And thanks for the feedback. Cheers!
@@rooted-digital Hi, I am really sorry that my frustration burst out into the comments like that, it's just, an awful experience, it's not your fault, and I thank you a ton for your tutorial and for your kind reply, really appreciated.
I spent hours and hours into google's documentation and couldn't understand literally anything, it's an incredibly confusing documentation, I was trying to retrieve page views from GA4 using google's api, should be a simple query, which api you ask? well if it's called GA4 then it must be Google Reporting Api v4 right? well guess what this is an api for Universal Analytics but we call it version 4 same as GA4 because...reasons ???
and the api you need is a different one, which you have to spend 4 more hours to figure out how to run a single query using it, cheers dear user we love you.
I have 0 experience with GA before this so I was frustrated at the lack of PROPER FULL tutorials on this, but in the end I got it working but only on their try this method portal in the documentation page, I still need to figure out how to build a proper https POST request using my linked oauth 2.0 key so I can plug that into my actual code. I am still searching how to get access tokens in oauth2.0 so if you can help me, will be really appreciated
Hey, thanks so much for the gracious response. That was really kind. I'd say you're forgiven, but since I've been in those same moments of confusion / anger / extreme frustration with GA4, I'd say I understand completely and there is nothing to forgive. Believe me, the wall next to my computer should have dents from all the times it feels like I've smashed my head against it.
Unfortunately, I don't think I can help you with your current issue with tokens in oauth2.0. Most of my GA4 work is more on tutorials for setting up certain kinds of event tracking and some basic similarities and differences vs. Universal Analytics. This particular video was my first foray into the wild world of BigQuery.
One place that *might* be useful to check is a tool called Analytics Canvas. It is designed to plug in to the GA4 and BigQuery integration and streamline things like building queries and extracting data. I don't know a lot about it, but I'm intrigued from the little I've seen. And I think I might be trying to learn more.
Here's the website link if you decide to check it out. A heads up if you do that: it's not free, although the first 30 days is free which would hopefully be enough time to figure out if it's useful for your purposes. analyticscanvas.com/
Wish you all the best going forward. Good luck and Godspeed to you!
Thanks for the video but your voice is too low, I can barely hear you.
Ahhh, sorry to hear it.
Making myself a sticky note (again!) to check my mic settings before recording. Thanks for the feedback.
🙏