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.
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!
Hi, Stockton. What about the first new table? You didn't create a schedule for that one. Will it work properly considering that the second scheduled query relies on the first table, which is not scheduled?
Yes! Views + scheduled queries work together View will run with every scheduled query. Depending on what you need you might want to append tables instead of using views. - JJ
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
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.
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
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
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?
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.
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, 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.
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.
Wow wow, man I feel so lucky I found your channel this is exactly what I was looking for, thank you!
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!
Hi, Stockton. What about the first new table? You didn't create a schedule for that one. Will it work properly considering that the second scheduled query relies on the first table, which is not scheduled?
Yes!
Views + scheduled queries work together
View will run with every scheduled query.
Depending on what you need you might want to append tables instead of using views.
- JJ
omg nice and ez. Thx!🎉
Glad we able to help!
great video mate!
Thank you Mike!
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
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.
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
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
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, 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
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.
@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,
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.