Efficiently Connect Looker Studio to BigQuery using Scheduled Queries
Vložit
- čas přidán 28. 06. 2024
- In this video, Stockton teaches you how to connect Looker Studio to Big Query using views, Scheduled Queries, and partition tables. Another key tool to getting past GA4's new quotas.
👇 Grab The Conversion Rate Cheat Sheet 👇
datastudio.vip/youtube
👇Start Exploring Your Marketing Teams Data👇
mediauthentic.com/youtube
0:00 - Intro
0:32 - Get the Conversion Rate CheatSheet
1:10 - Hoping Into BigQuery
2:40 - Setting up Queries
6:08 - Creating A View
6:55 - Queriying the View
12:58 - Creating a Schedule
15:22 - Checking Your Work
19:25 - Connecting to Looker Studio
21:40 - Wrap Up
Learn On Your Own!
datastudio.vip/learn
Wow wow, man I feel so lucky I found your channel this is exactly what I was looking for, thank you!
Hi, if you guys take requests then kindly make separate courses on Looker Studio and Big Query. Random videos where we can get the best out of the two platforms will be an icing on the top (like this video). I crashed into your channel today and I really liked the way you guys explain everything. Keep up the good work.
Hey Ashok!
Thank you so much for the feedback! We will keep trying to do more "actionable" use cases accross multiple disciplins.
FYI, We do just that inside of BetterThanData.com if you wanted to consider joing in the future.
-JJ
Hey Ashok - if you're going to be doing bigquery/looker studio on a regular basis, I can't recommend a better program than the betterthandata site. These guys are TOP in this field.
great video mate!
Thank you Mike!
Thank you this video helped me tremendously. Just wanted to note though that because the data is partitioned by event_date, when you go to calculate unique users over a date range you're adding up day by day and are unable to filter out returning users over the range. I was losing my mind trying to figure out why totals weren't matching. All in all thanks for the help and keep up the good work!
omg nice and ez. Thx!🎉
Glad we able to help!
Excellent video, thank you. I love working with Bigquery, but in Latin America its use is not so common. The work marking here is very small. I want to continue learning and specializing and I would like to know if these tools have an attractive job market in Europe and North America
Congrats to hear! Good luck on your hunt!
-JJ
Thank you so much for the video!!!! I tried to apply the same to my data, however, when I chose "partition by transaction date", the report in looker studio report just grabbed the data for this year only, is there any way to resolve this? Thanks
HMMM, You might need to add a date filter at the top of the page and adjust the default date range
-JJ
THIS IS FANTASTIC STOCKTON! THANK YOU!
One question - At the end of the day, how many times are you hitting the BigQuery database once you create the initial view? Is the purpose of this view just for speed when running it in looker studio, or is it also intended to be a money saver because you're extracting less data on each run?
Every time the VIEW gets queried, it queries the entire dataset.
The view is being queried by the scheduled query at the set interval. In the video that's 12 hours. So the entire dataset is getting queried once every 12 hours.
The purpose of the view is prep the data before doing the aggregation in the scheduled query.
@@Vision-Labs Wouldnt it be more efficient to query JUST the last day and not the entire dataset and simply add that day (incremental) to the final table?
Technically you would want to grab the last 3-5 days (including yesterday) and add that to the final table. Even the last 5 days of data will be much more efficient as your database grows, it's just a little bit more involved for the average person.
@@Vision-Labs thanks for sharing this great video. Appending the last 3-5 days only would be the perfect solution, but how would you do that? I mean, as far as I understand you would want to schedule a query that runs every day APPENDING the data of, say, 6 days ago to the big table. Then another scheduled query only querying data of the last 5 days OVERWRITING the table. My point is, would the overwrite consider the partition (date) and overwrite only those 5 days? Or will it overwrite the entire table?
Or do you have any other option? Thanks again for your wonderful videos.
This is great, thank you! I'm trying to consider this on a larger scale, and I would like to ultimately create a situation where I can report on year-over-year data in Looker Studio, but I know I need to be cautious with costs. I was surprised when you switched the scheduled query from append to overwrite. Doesn't overwrite take more processing resources? Would append just add data from the new event_dates? And I know that GA4 may change data for up to 3+ days, so would need to re-write data for those dates? I was originally thinking I would need to set date ranges in my scheduled queries (_TABLE_SUFFIX) to handle this? I'd love to hear your thoughts. Thanks again!
Yes, overwrite technically does cost more.. but in the grand scheme of things, I think it's pretty insignificant.
As long as you're not querying the entire dataset for multiple charts, whenever you're changing the date range, etc. You should be fine.
The entire dataset is only getting queried a few times per day... which is incredibly cheap! And looker studio is then connected to a partioned scheduled query making things even better.
Also, setting it up to append would be a lot more work for not that much benefit. Because, as you mentioned, they can change data up to 5 days in the past. So building logic to delete the last 4 days and then append 5 days worth of data sounds complicated.
Thank you again for your videos and your response.
the view table you created first, is it taking data automatically by dates, because the partioned from the view has schedule to fetch it but from the view, but the view it self has only being queried once, when you created it. can you please clear this to me, thanks
The View is basically like an "on demand" query.
It allows you to connect & it runs the query when it is loaded.
hope that helps
- JJ
Hi! Would it be possible to use scheduled SQL queries directly on BigQuery to do the currency conversion?
Totally possible!
Just have to know the daily conversion rate!
Which I believe is is a public table
-JJ
@stockton - does the new user id stuff announced this week change this at all ??
Hey Mike!
JJ here :P
It doesn't affect things too much.
If you want user data you now have to join on that table as well :(
So that is what's up.
Talk soon,
- JJ
Hi, I have a question. After we prepare the daily data and save it into a table, when we try to generate a report from this newly created daily table, how do we create a monthly user #? Since we can not simply sum the daily user # up, is there a way to solve this issue?
Not really, this is one of the hardest parts of GA4 BigQuery as you will have to have a different table for daily, weekly, monthly.
- JJ
@@Vision-Labs I see. Thanks
Hi,
Can we export audience data to BigQuery ?
I now use BigQuery to connect my GA4 and my Looker Studio but it seems I can't export my audience data from GA4 to BigQuery, I want to make audience filter on my Looker Studio, but I don't have audience data in my BigQuery
This is really important for me 😢
Hey there,
You cannot export audience data to BigQuery. BigQuery is storing data at the event level, so you would need to recreate the audiences yourself by wrangling the event level data.