Connecting Looker Studio to Views in BigQuery

Подписаться 2,6 тыс.

Просмотров 4,7 тыс.

50% 1

Stockton teaches you how to connect Looker Studio to BigQuery using views.
👇 Grab The Conversion Rate Cheat Sheet 👇
datastudio.vip/youtube
👇Start Exploring Your Marketing Teams Data👇
mediauthentic.com/youtube
Learn On Your Own!
datastudio.vip/learn
0:00 - Intro
0:30 - Grab The Conversion Rate CheatSheet
1:33 - Rebuilding GA4 Report in BigQuery
6:50 - Using Sub Queries
12:15 - Comparing BigQuery to GA4
15:27 - Saving the Query as a View
17:47 - Connecting to Looker Studio
19:55 - Wrap Up
Here's the query used:
SELECT
event_date,
(select value.string_value from unnest(event_params) where key = "page_location") as page_location,
event_name,
count( distinct user_pseudo_id) as users,
count( distinct concat(user_pseudo_id, (select value.int_value from unnest(event_params) where key = "ga_session_id"))) as sessions
FROM
`PROJECTID.DATASETID.events_*`
WHERE
_table_suffix between "20230216" and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
GROUP BY
event_date,
event_name,
page_location
ORDER BY
sessions desc

Опубликовано:

16 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 22

@Vision-Labs Год назад

ATTN:!!!! Quick correction, in the video I talk about how we connect to a "View" so it doesn't re-query the entire table. I misspoke! Guess I was a little tired when I hit record 😅🤣! A view does re-query the entire dataset. Use a scheduled query instead to not re-query the entire table every time Looker Studio reloads. Let me know in the comments if you want a video on scheduled queries 👇👇👇

@getyousome321 Год назад

Scheduled Queries please 😎

@albertstanulis5278 Год назад

Scheduled queries would be great bit also a video on queries to optimise looker studio quering speed, notoriously slow looker is 😅

@mendyzeiger3624 Год назад

Great Lesson! Thank you! Yes, I agree with Albert below. A video on how to optimize would be GREATLY appreciated.

@Vision-Labs Год назад

Check out this new video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-g1qcu3fJCCg.html see if you like it :)

@mendyzeiger3624 Год назад

@@Vision-Labs Like? I LOVE IT! THANK YOU!

@yeti402 Год назад

Appreciate the time & effort that went into creating this video, but it's impractical unless you want a 1 table dashboard with only 1 pre-defined time range.

@Vision-Labs Год назад

Hey 👋 Yes, this was more of an example as opposed to a workflow. Definitely need to have a date column to partition and filter by in looker studio for a long term solution. -JJ

@albertstanulis5278 Год назад

Great video, but from practical point of view I hope you can comment on date range, simply because Looker advantage is to have freedom using salect date filters and compare date filters. Your view has only 1 weeks date range.

@Vision-Labs Год назад

Hey Albert, thanks for checking out the video. I think you're comment here is a great idea for another video on BigQuery and Looker Studio. Something like, "How to get a dynamic date range in BigQuery and Looker Studio". Thoughts on that?

@albertstanulis5278 Год назад

@@Vision-Labs yes, agreed, also important to explain in detail the benefits why you built that table innBigQuery and not queried from Looker.

@mendyzeiger3624 Год назад

Yes Albert! Any help on how to make more dynamic DataSets in BigQuery will be a great help. I created a lousy little report in Looker Studio yesterday, and within an hour I hit the quota 😞. I'm not too keen on spending money on 3rd party products to get my data out of BQ if I don't have to 🙂

@mohammedshabeeb4046 10 месяцев назад

Yes, ive been searching for somrthing like for a very long time. it would help make my visualisations more dynamic. I've been trying to find a way to get the graph to give total sales vs previous periods sales months wise view with datewise data points. By default, the graph shows current months sales vs previous months sales (I have the default value of date range controller set to yesterday). When the user selects lets say a date range from 18 jan 2023 to march 25 2023, i want the graph to show from beginning of jan to end of march. I hope ive made my scenario clear, but ive been looking for something like that for so long and havent found it yet. @@Vision-Labs

@bobyhartanto4314 4 месяца назад

I want to asked, in regards to best practice, which is better : 1.Save the sql script into view, and connecting to looker studio, or 2.Save the sql script into table(with scheduling.) and connecting to looker studio ?

@Vision-Labs 4 месяца назад

It depends on your use case. For the minimum cost. Usually a table. For more flexibility a view

@avishaiz Год назад

Great videos! To get the unter day table I need to set bq in ga4 as streaming? Will it show me the current data? Without any delay

@Vision-Labs Год назад

Yes, You will need both Streaming & Daily turned on! - JJ

@tkachenko.andrey Год назад

Thanks for the video! Very interesting tricks and useful tips, thanks for this content! Its really rare. So many questions! :) What key do you prefer to use for blending data, is Session ID? For example Country by days, page location, source etc. or better to make one table with all of this parameters? P.S. And the biggest questions, and where no answers about Quotas with using Queries when you had 3M+ events per day and where you can check it.

@Vision-Labs Год назад

Thanks for the comment :) Session ID is usually what we will blend data on. Then we will usually create tables for each type of scope that we're looking at. For example, we'll create 1 table that is all the session stuff. Session source, medium, campaign, purchases, pageviews, adds to cart, purchase value, etc. But everything is aggregated at the session id level. We'll create another table that is at the page level and have all the individual page stuff. Basically you have to create multiple tables and have multiple data sources for your reports. Typically our "standard" reports have 6-10 data sources. All of them are Different BigQuery tables. As for the cost of querying in BigQuery. There is a query that you can use directly inside of BigQuery to determine the cost of this. We'll likely do a video for this very soon because not many people know about it. Let me know if that helps. - Stockton

@tkachenko.andrey Год назад

@@Vision-Labs Thanks a lot for the answer, it helps to understand how to work and manage data inside BigQuery. New meta is hard to configure, all new features, there are too many things that need to be settled up correctly :)

@feloniousmonk321 5 месяцев назад

It would be really helpful if you explained succinctly the point of View and why people use it. Not finding it that clear.

@Vision-Labs 5 месяцев назад

There are a lot of pros and cons to views. Basically a view is a “saved query” that runs when called upon A table is that saved outcome. Hope that helps! -JJ Ps if you are interested we talk about this inside of The Lab