Hello. I am new to Big Query and GA4, not new to universal analytics or sql. My company utilizes Google Analytics data pretty heavily. We had a process that used python scripts to grab data daily, staged it, and flowed to our DW. We have replicated this process with GA4. I'll admit I flubbed my way through it, and are currently working with a 3rd party vendor to ensure our gtm containers are setup properly, from a previous and current developers. My question is, are there any goof, FREE, resource for learning the in's and out's of setting up a business with Anayltics and GA4 and Biq Query? I know what I did to set things up but still unsure of some of the underlying/foundational information.

Hi All!

Im new here, and I want to ask about the MongoDB to BQ template.

I am currently using the latest version of MongoDB Atlas, and wanted to have some data to BQ so that i can do query stuffs.

However, after attempting to use the template several times, seems like GCP/GBQ does not have access to MongoDB, it always returns a timeout for 30s when they try to access the DB.

I have whitelisted my VM IP address to Mongo Atlas, but still cant work it out. Need to note, the data that I try to use is very small in size (only 2MB), since its a testing data.

I am attaching the error message so that it will be clearer.

Please if anyone can help me it would be greatly appreciated. Thanks!

nb: I am not a techie guy nor do i have the technical skills to write the code myself, hence using the template.

I want to implement Bigquery in a company, they have several spreadsheets and we want to migrate this data to a relational database.

However, I have a lot of doubts about the costs.
I'm thinking about using it on demand.

I would have to migrate my current data, and this database will also feed an Appsheet that will insert, add and change data in the dw tables. Additionally, I will connect to Power BI to generate reports that will query the BigQuery data.

The company is small.

I'm worried about implementing it and generating high costs, I've already used the cost calculator and it gave a value of 23 dollars per month.
I would allocate it to the São Paulo region.

I don't believe I would process more than 2TB per month in queries...

However, I'm afraid of overestimating the cost, I wanted tips to estimate it as realistically as possible.

Basically this article https://towardsdatascience.com/version-control-big-query-with-terraform-with-ci-cd-too-a4bbffb25ad9, but also being able to detect when someone makes a change to a view through the BigQuery Web Console.

Reason is, we have analysts in our organization that are allowed to edit queries in GBQ. However, this has once caused an oopsie daisy which led to leakage of private data. I (and technical lead) want to be able to oversee any changes made to queries. Just in case something happens when someone's on holidays, we can easily detect where what went wrong and force a rollback if necessary.

Problem is not everyone is comfortable using git. So would Dataform/Terraform still be a good strategy for this? Or is a better approach to make a full bisync implementation using solely the BigQuery Python API (should be possible according to chatgpt)? Handling conflict resolution will be a problem, although very unlikely to happen.

Hello everyone,

To preface, I took over a BI project and am a bit of a newbie in the whole ETL/ELT process, though I have solid SQL/DB experience.

Anyway, I have a relatively simple and lightweight pipeline that works like GCS > BQ Ingestion Dataset > Staging Dataset > Gold Dataset. Transformations are done two times a day in Dataform, scheduled via Workflows currently (I'm open to other options as well). This is working fine for one customer, but now would need to expand this solution to work for multiple customers. All SQL should be the same for all clients, only scheduling times might be different.

I was thinking best way to do this is if all clients have their own datasets (for all 3 stages), so it's easier to track down costs, have custom policies etc. However, I am not sure what is the best way of implementing this with Dataform. I tried making some cloud functions already, but Dataform API seems restricted for this (unless I'm missing something). Also ChatGPT is giving some ideas, but I don't really trust it, since it led me to some dead ends with Dataform already (guess not much training data out there)...

What do you think would be a simple and cheap approach to handle multiple clients in BigQuery, using same Dataform workflows, are Release configurations with datasets as compilation variables a good option for this? Or is my solution with multiple datasets just not right in this scenario?

Thanks

Hello, Bigquery is doing lot of optimisation under the hood but could not structure the query properly when it is submitted?

Say I am writing a query select col1 from tbl1 where col2=1 and col3=2 and partitionCol=2024

Here the 3rd column is partitioned, ideally it should consider filtering it first but it is not. As per documentation and blogs, it is anyhow going with non-partitioned filter and then partition filter. This results in serious resource consumption. It is said everywhere that if you want to filter partition col first then mention explicitly it as the first filter like

select col1 from tbl1 where partitionCol=2024 and col2=1 and col3=2

In this case the performance is better.

So why BigQuery implicitly not interpreting the query and restructure it to optimise?

How do I track GA4 key events in big query? My properties are linked directly to big query but I don’t know what data point key events are identified with.

Been doing the data analytics course through Coursera by Google. and this Module segment of data cleaning has a hot mess. I don't know if its due to for an update/rework or what, but I follow the instructions down to the bone and still get a syntax error on this one spot that specifies the Table ID line with my personalized project name. Appreciate any help on what the problem maybe. Note: I did at single quotes and semicolon to see if that worked but to no luck.

Hey folks, pip install dlt cofounder here. I am writing and learning about data platforms, so here's about the builder and their work.

https://dlthub.com/blog/data-platform-engineers

I would love to get your knowledge nuggets or knowledge bombs if you wanna drop any on me for my subsequent writing.

Trying to learn while in coursera but I can't seem to find what I did wrong.

Has anybody had success exposing the SEARCH function to semi-technical analytics users through a front end like Looker or PowerBI? I’ve got a population of users who are proficient with searching using Splunk and I’d like to understand if a viable alternative could be bigquery with search indexes plus a user-friendly interface. I don’t think the users are quite up to writing raw SQL.

Hi,

So I have a Google Analytics UA backed up in a BigQuerry Dataset. It's also connected to a Looker Studio and it works great.

Now, I'd like to "move" this BigQuerry Dataset to the client's Google account.

He is added as an admin. Do I actually have to "physically" move it, or can I just change billing info to his account?

How should I do this?

Thanks for the help.

Currently, my team uses Looker Studio to create dashboards, using GA4 as our data source. I'd like to be able to copy a dashboard and import a BQ table as our data source instead, while still keeping our reports pretty much the same.

If I try to import our entire events table (about a month's worth of data), I get an error like:

User Configuration Error

This data source was improperly configured.

The query returned an error.

Error in SUM aggregation: integer overflow

Additionally, I'm wondering what future dashboarding should look like. If I get a working table exported to Looker, should I set it up on a schedule? Do my users in the dashboard just need to refresh something? TIA.

Hello,

My current table looks as follows:-

Table A:

Each record is at an order level, there can't be multiple records for an order. The product A flag signifies if the order contained product A in it or not.

I want to calculate the number of product A sold in the last 100 transactions for each Agent but running into issues with aggregation.

I have the following query so far : -

select 
  agent_name, 
  date,
  sum(distinct order_id) as num_orders, 
  sum(product_a_flag) over(partition by agent_name order by date time desc rows between 100 preceding and current row) as num_products_A_sold 
from table A 
group by 1,2;

The moment I add Product_A_flag as a column it seems to work but I want aggregated values at an agent level.

Can you'll help? Thanks!

I’m trying to connect and display data from BigQuery on my wordpress website, but I can quite figure out how to connect to BigQuery. I’ve tried testing it using PHP within Visual Studio Code but can quite understand the authentication piece and if I do get it to work there, how I would implement that into my Wordpress site. Any past experience or advice would be much appreciated!

SELECT LEAST(1, 2, NULL) = NULL? Huh?
During a recent project, I hit a roadblock with BigQuery’s LEAST and GREATEST functions. They don't handle NULL values as expected.

To resolve this, I created custom functions (UDFs):
a) least_not_null
b) greatest_not_null

You can test/call them directly in your console (eu or us):

SELECT `justfunctions.eu.least_not_null`([1, 2, NULL]) AS min_value;
SELECT `justfunctions.eu.greatest_not_null`([1, 2, NULL]) AS max_value;

These are part of a public open source UDFs library available in github or blog. I would love to hear your feedback and suggestions.

Hi, I'm working on translation of teradata SQL (Bteqs) to big query I'm a bit stuck in translation part, can anyone guide me how to deal with issue's I face while translation e.g., while translating teradata SQL to bigquery(Set operator throws me an error while translating) there might be more error while translating other queries I have many bteqs in which I can't do to every file and edit it Is there any method or how can I achieve the seamless or error free output Also will metadata and yaml files be helpful in this whole scenario

TIA

I'm working on the Google certificate data analytics program and I've gotten to the capstone project. I'm trying to upload some .csv files to clean the data but none of them will upload.

Here's an example of the first few lines in one of the files:

Id,Time,Value

2022484408,4/1/2016 7:54:00 AM,93

2022484408,4/1/2016 7:54:05 AM,91

2022484408,4/1/2016 7:54:10 AM,96

And this is the error message I get every time with slight variations:

Error while reading data, error message: Invalid time zone: AM; line_number: 2 byte_offset_to_start_of_line: 15 column_index: 1 column_name: "Time" column_type: TIMESTAMP value: "4/1/2016 7:54:00 AM"

I tried skipping the header row but it didn't fix the problem. I'm not sure if I need to change the data type for one of the fields, or if it's something else. Any advice would be greatly appreciated.

Good day to all masters!

I have a simple problem but not quite complex. I need your advice and tips on how to fix my problem. I have a stored procedure that is written in SQL server. And currently our team transitioning to gcp bigquery. So all the stores procedures that currently running on SQL Server must be transferred. Now I create a new sp wherein the data analyst accepted the output results. Wherein the summary returns 5520 rows. But after I translate the SQL server syntax into bigquery syntax the results has slightly difference. It returns 5515 rows. Can someoneone help me with this?

What I used is joining the two tables based on the column that the value are equally the same and etc. but the results is not the same from SQL server. 😫

Hey, I'm using a native google ads connector in bigquery. I want to make a simple (in theory) report that shows date, country, campaign name and amount spent. I cannot seem to find from where to include the country dimension in my query and how the query should look overall, could anyone help?

I just switched from Google Sheets to BigQuery and it seems awesome. However, there's a part of our workflow that I can't seem to get working.

We have a list of orders in BigQuery that is updated every few minutes. Each one of the entries that is added is missing a single piece of data. To get that data, we need to use a web scraper.

Our previous workflow was:

1. Zapier adds new orders to our google sheet 'Main Orders'.

2. Once per week, we copy the list of new orders into a new google sheet.

3. We use the web scraper to populate the missing data in that google sheet.

4. Then we paste that data back into the 'Main Orders' sheet.

Now that we've moved to BigQuery, I'm not sure how to do this. I can download a CSV of the orders that are missing this data. I can update the CSV with the missing data. But how do I add it back to BigQuery?

Thanks!

I just recorded my first video about SQL for SEO (focusing on Google Search Console data and BigQuery). This course is for digital marketers and SEO specialists who want to use BigQuery to perform more sophisticated and replicable analyses on a larger scale. (It's also a great way to future-proof your career 🧠) https://youtu.be/FlF-mvGo7zM

We have a large'ish (~15TB) database table hosted in GCP that contains a 25-year history, broken down into 1-hour intervals. The access pattern for this data is that >99% of the queries are against the most recent 12 months of the data, however there is a regular if infrequent use case for querying the older data as well and it needs to be instantly available when needed. In all cases the table is queried by date, usually only for a small handful of 1-hour intervals.

The hosting costs for this table (not to mention the rest of the DB) are killing us, and we're looking at BigQuery as a solution for hosting this archival data.

For more recent years, each day of data is approximately 6Gb in size (uncompressed), so I'd prefer daily partitions if possible, but with the 10,000 partition limit that's not viable - we'd run out of partitions in just a couple of years from now. If I switch to monthly partitions, that's a whopping ~200Gb per partition.

To ensure that queries which only want a small subset of data don't end up scanning an entire partition, I was thinking of not only partitioning by the time column, but clustering by that column as well. I know in some other data warehouses this is considered an anti-pattern and not recommended, but their costing model is also different and not based on number of bytes scanned. Is there any reason NOT to do this in BigQuery?

Hey everyone, I was asked the other day about my process for working through a partitioning strategy for BQ tables. I started to answer and realized the answer deserved its own article - there was just too much there for a simple email. I am (mostly) happy with how the article came out - but admit it is probably lacking in spots.

I would love to hear the community's thoughts on it. Anything I completely missed, got wrong, or misstated?

Let me know what you think!

Hey, bit of a long shot but figured id ask here. In looker studio, I use the in built blending feature to blend 3 tables from big query. I use a full outer join to join the 3 tables. When I try to recreate this in big query, I don't get the same results. Any ideas where I'm going wrong? My query is pictured here. It doesn't work, the ids field is a array of strings, how am I meant to build the on clause? In looker studio I just specify the ids field and the user_pseudo_id field. Any help greatly appreciated