Efficiently Connect Looker Studio to BigQuery using Scheduled Queries 

Thank you Mike!

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.

Glad we able to help!

Congrats to hear! Good luck on your hunt! -JJ

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.

HMMM, You might need to add a date filter at the top of the page and adjust the default date range -JJ

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 is basically like an "on demand" query. It allows you to connect & it runs the query when it is loaded. hope that helps - JJ

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

Totally possible! Just have to know the daily conversion rate! Which I believe is is a public table -JJ

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

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.