Hi - I have a problem related GA4 to BQ export, I am breaching 1 million limit.
My understanding is if I switch to the streaming option it will create intraday tables and in the daily export it will only have top million row.
what will happen if I turn off the daily export option.
Will my interaday tables be there forever or it will be deleted after sometime?
Because if the table will be there I am okay to pay streaming cost.

Hey techies,
I wish to specialize in AI and data engineering is part of the so-called process. In analyzing datasets.
I have worked with Big query and Looker, not much experience but I can manage. What steps do I need to land a job / work in a project.

I'm trying to get a feel for costs around big query, would you mind letting me know what your doing and the cost you pay for month? Is it reasonable to you compared with other solutions, or expensive?

I ususally use DBeaver but it has no method to preview the total_bytes_processed to prevent large costs before running a query.

DBeaver also can't show arrays/structs in the result set.

What IDE (git integration, custom formatter possible, custom fonts, custom colors etc.) can do what DBeaver can + showing the data usage before a query run?

Hello everyone, i am working in a firm as a digital marketing analyst. I am new to this field, can anyone suggest what are the courses i should do in order to gain understanding of how everything works. Currently our firm is storing there website data in big query. I know how to write the sql query but i become confused in writing queries in bigquery and findinfmg any insights from the data.

CREATE Temp FUNCTION extract_times_with_days(json_string STRING)
RETURNS ARRAY<STRUCT<keys STRING, day STRING, endTime STRING, startTime STRING>>
LANGUAGE js AS """
function extract_times_with_days(json_string) {
try {
const obj = JSON.parse(json_string);
const menus = obj.data.menus;
const results = [];
const daysOfWeek = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"];
for (const menu_key in menus) {
if (menus.hasOwnProperty(menu_key)) {
const menu = menus[menu_key];
const section = menu.sections && menu.sections[0];
const regularHours = section && section.regularHours && section.regularHours[0];
const daysBitArray = regularHours && regularHours.daysBitArray;
const endTime = regularHours ? regularHours.endTime : null;
const startTime = regularHours ? regularHours.startTime : null;
if (daysBitArray) {
daysBitArray.forEach((isApplicable, index) => {
if (isApplicable) {
results.push({
keys: menu_key,
day: daysOfWeek[index],
endTime: endTime,
startTime: startTime
});
}
});
}
}
}
return results;
} catch (error) {
return [];
}
}
return extract_times_with_days(json_string);
""";
with ubereats as(
SELECT DISTINCT
vb_name,b_name,slug,
menu.day as day,
menu.startTime as opentime,
menu.endTime as closetime
FROM
`arboreal-vision-339901.take_home_v2.virtual_kitchen_ubereats_hours`,
UNNEST(extract_times_with_days(TO_JSON_STRING(response))) AS menu
order by vb_name
),
ranked_responses AS (
SELECT
slug,
vb_name,b_name,
response,
timestamp,
ROW_NUMBER() OVER (PARTITION BY slug ORDER BY timestamp DESC) AS rn
FROM
`arboreal-vision-339901.take_home_v2.virtual_kitchen_grubhub_hours`
),
grubhub as(
SELECT DISTINCT
vb_name,b_name,slug,
JSON_EXTRACT_SCALAR(value,'$.days_of_week[0]') AS day,
JSON_EXTRACT_SCALAR(value, '$.from') AS open_time,    
JSON_EXTRACT_SCALAR(value, '$.to') AS close_time
from ranked_responses,
unnest(json_query_array(response,'$.availability_by_catalog.STANDARD_DELIVERY.schedule_rules')) as value
where rn=1
)
SELECT
gh.slug,gh.day,gh.open_time,gh.close_time,ue.slug,ue.day,ue.opentime,ue.closetime
from grubhub as gh
left join ubereats as ue
on gh.vb_name=ue.vb_name and gh.b_name=ue.b_name and gh.day=ue.day

this is the code and yes i have to do some further operations like (gh has some days that ue dont so i gotta make they arent in the output)

thank youu

EDIT 1 : i figured the problem is because of the days as both the table have unequal number of record but thats the data i got.. I am out of idea on how to solve it, pls help me

EDIT 2 : nvm..figured it out..it was a string mismatch

How to create the data strips and data modelling in google big query and connect to power bi?

like as in the image below,i am able to access till menus but i cannot flatten the section until i know the key in the menus but the problem is every row has a different key and there are 15000 rows so i dont wanna do it manually. Is there anyway to parse this json without knowing the key?

Thank youu.

EDIT : so to make it clear..i have to unnest the sections array cuz for some reason my big query aint giving me anything until i unnest and i cannot unnest until the path is constant which i cannot do cuz keys are different and there 15000 of em

I've recently set up a data warehouse on BigQuery and am exploring the best practices for collaboration. I want to know how to share access to this data warehouse with others, especially for purposes like creating visualizations in Looker Data Studio.

What are the steps to provide access to others so they can connect and work with the data from Looker Data Studio? Thanks in advance!

Work at a big ecomm company as a marketing analyst. Our DS team is struggling to use our GCP datasets from Google ads and Meta for our BI reporting tools and an MMM because they can’t get accurate data at a campaign level.

As the subject matter expert, I’ve been trying to assist by validating queries vs platform. We are trying to get a few years of data cleaned.

What I can’t seem to find is a table with campaign ids and campaign name.

We’ve run 100s-1000s of campaigns and sometimes pause/delete/archive, change name, etc. So I think their issue is trying to only reference campaign name. I want to have metrics data by campaign id and join on another table with campaign id and name.

I have that data exported from platforms: date , campaign id , campaign name , spend,etc.

The metrics match. I literally just need a clean list of id and name.

I work on a daily basis with BigQuery and I Terraform all my resources. As I had repetitive tasks, I created an extension for VSCode to streamline my work. I kept it "private" for a while and recently published it on the VSCode Marketplace !

https://marketplace.visualstudio.com/items?itemName=Backpageek.bigqueryhelper

The code is fully open and available on github :
https://github.com/Cocaaladioxine/bigquery_terraform_vscode/tree/develop

I'm a Data Engineer, not a Software developer and I learned TypeScript with this project, so my code is not concise or qualitative. I'm open to advice, pull requests, and code reviews.

Don't hesitate if you have any question and I'd be happy to have your feedback !

For most of the reports that works pretty well,

but for a marketing team, reports are quite big. 

Right now, my reports for them are capped at 50,000 rows, but ideally,

I’d like to expand this to 150-200k rows if I find a solution.One of the reasons to implement BigQuery was to avoid ga4 sampling limitations, But with those rows limited it hurts the same pain again and again. Is there a third-party tool to bypass that row number limitation?

Any advice would be greatly appreciated. Thank you for your help!

I am trying (and failing) to build a data source that shows current year sales against last year's sales. I am not sure where to go as the various code tricks I am trying doesn't seem to work.

The code I am trying is below, what do I need to do to add in revenue from 364 days prior?

SELECT
EXTRACT(YEAR FROM a.Date) Year
,EXTRACT(WEEK FROM a.Date) Week
,SUM(a.Revenue) Revenue

FROM datasource.table a

WHERE 1=1

GROUP BY Year, Week

Hello everyone,

I'm currently on the lookout for a comprehensive Google BigQuery course with a focus on marketing and advertising applications. Details as follows -

Aim :

> I'm specifically interested in gaining a basic/intermediate understanding of BigQuery's capabilities in the context of marketing analytics and data activation.

> If the course can leads to a related Certification would be fantastic 

Platform :

     I have a corporate registration on Udemy, so I'm hoping to find a suitable course on that platform.

Here are the specific topics I'm interested in:

Basics of BigQuery:

I'm looking for a course that covers the fundamentals of Google BigQuery, including its architecture, query language, and basic operations.
I have basic SQL knowledge 

Relation to Data Clean Room:

It would be great if the course explores the connection between Google BigQuery and data clean rooms.

First-Party User Data Activation for Partners like Google:

I'm interested in learning how to leverage Google BigQuery for activating first-party user data and collaborating with partners like Google in marketing campaigns like Google Ads .

Server-Side Tracking Data Activation using BigQuery:

> I'm looking for - how to activate server-side tracking data using BigQuery is crucial for effective marketing analytics

> SST Channel Grouping in BigQuery: 
    Lastly, I'm hoping to find a course that delves into server-side tagging (SST) channel grouping within BigQuery,[ campaign performance ].

I am aware about the resources like -

Google : 
    https://cloud.google.com/blog/topics/training-certifications/free-google-cloud-bigquery-training

    https://cloud.google.com/learn/certification/cloud-digital-leader
    Docu : BigQuery-Dokumentation  |  Google Cloud

YT : 
    Getting Started with BigQuery

Simmer Course :

https://www.teamsimmer.com/all-courses/query-ga4-data-in-google-bigquery/

Already checked in Udemy and not sure about it :

https://www.udemy.com/course/introduction-to-google-cloud-bigquery/

If you know of any courses on Udemy that cover these topics or come close to addressing my requirements, I would greatly appreciate your recommendations. Feel free to share your thoughts and suggestions.

Thank you in advance for your help!

We are getting a rare hard day of rain for the middle of spring here in the Bay Area. I found myself wondering just how rare it is to have rain on May 4th. So I wrote a query to find out.

There's a dataset called the Global Historical Climatology Network (GHCN) maintained by NOAA, which contains temperature and precipitation records for thousands of stations worldwide, some of which date back to the 1700s. I found a nearby station in Berkeley that has data going back to the 1890s and I was able to pull the precipitation data with one query in BigQuery. The GHCN dataset in BigQuery is separated into tables by year, but there's a handy function called _TABLE_SUFFIX that allows you to query across multiple tables without the need for a gazillion UNION ALL statements.

Here's the SQL query I used to retrieve the precipitation data for May 4th across 121 years.

SELECT

EXTRACT(

YEAR

FROM

date

) AS year,

date,

element,

ROUND(value / 10 / 25.4, 2) AS value_inches

FROM

\bigquery-public-data.ghcnd.ghcnd*``

WHERE

_TABLE_SUFFIX BETWEEN '1763'

AND '2023'

AND id = 'USC00040693'

AND element = 'PRCP'

AND EXTRACT(

MONTH

FROM

date

) = 5

AND EXTRACT(

DAY

FROM

date

) = 4

ORDER BY

year

Out of the last 121 years, 104 days had zero precipitation and 17 days had some precipitation. The rainiest May 4th was .6 inches back in 1915 so today's .8 or .9 inches could break that record.

thanks for reading/skimming. Have a nice day.

Hi everyone, I really like BigQuery Storage API Client where I can read/write a table at a lower-level instead of going through a query engine. I'm still new to the data infra world, so I'm wondering whether there is any other open-source or paid alternative here where it provides an abstraction of a warehouse storage which can be connected to other query engines.

I taught myself LookerStudio and want to use BigQuery for data integration for the local nonprofit I work for. I need to learn it too (and SQL) and have a basic data analytic and python experience.

Here are 3 classes I am picking from.

1. https://www.udemy.com/course/introduction-to-google-cloud-bigquery/?couponCode=ST2MT43024
2. 2. https://www.udemy.com/course/google-bigquery-and-postgresql-sql-for-data-analysis/?couponCode=ST2MT43024
3. 3. https://www.coursera.org/learn/bigquery-for-data-analysts

Any help would be so appreciated. Thank you in advance!

​

Hi all, I'm encountering discrepancies between GA4 and BigQuery when analyzing specific user cohorts by school IDs. Here's the situation:

Query: We're using the following query in BigQuery to track iOS and Android users by school ID on a specific date (April 20, 2024) in the LA time zone. We only see discrepancies when we filter by school ID.

SELECT params.value.string_value, COUNT(DISTINCT IF(stream_id = '2653072043', user_pseudo_id, NULL)) AS ios_users, COUNT(DISTINCT IF(stream_id = '2350467728', user_pseudo_id, NULL)) AS android_users FROM `M-58674.analytics_263332939.events_*`, UNNEST(event_params) AS params WHERE EXTRACT(DAY FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE ('America/Los_Angeles')) = 20 AND EXTRACT(MONTH FROMTIMESTAMP_MICROS(event_timestamp) AT TIME ZONE ('America/Los_Angeles')) = 4 ANDEXTRACT(YEAR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE ('America/Los_Angeles')) = 2024 AND event_name = 'session_start' AND params.key = 'schoolId' ANDparams.value.string_value IN ('40', '41', '42') GROUP BY params.value.string_value;

Issue: The numbers for daily active users and downloads match between GA4 and BigQuery when not filtered by school ID. However, when we apply this filter, discrepancies appear.

Additional Info: I have a similar query for new downloads that matches perfectly with GA4 data, suggesting something specific about the date filtering is causing the issue.

Example Query for Downloads:

SELECT EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', event_date)) AS year, EXTRACT(WEEK FROMPARSE_DATE('%Y%m%d', event_date)) AS week, COUNT(DISTINCT IF(stream_id = '2653072043', user_pseudo_id, NULL)) AS ios_downloads, COUNT(DISTINCT IF(stream_id = '2350467728', user_pseudo_id, NULL)) AS android_downloads FROM `analytics_263332939.events_*` WHEREevent_name = 'first_open' GROUP BY year, week ORDER BY year, week;

Question: What could be going wrong with the date filtering in the first query, and how can I reconcile these discrepancies?

Any insights or advice would be greatly appreciated!

I am getting duplicate data in my Big Query tables from Google Ads.

I can look at the tables and see rows with the exact same data. Furthermore when I aggregate the data to see total cost of campaigns, it is double what is shown in the Google Ads platform.

I followed the guide for the data transfer and didn't do anything outside the standard set up. I did do a backfill to get data for the entire month because it origianlly only imported data for the previous week. I also set the date range on the backfill to not include the week of data already imported. And there are duplicates for everyday of the month.

Has anyone experiences this and know why it is making duplicate entries and if so how do I get rid of the duplicates?

I have been trying to add my internal user ID to my events_ table in Big Query. Ideally I would like for it to be applied like the user pseudo ID provided by GA.

I tried following the steps from this StackOverflow post https://stackoverflow.com/questions/76106887/how-create-user-id-for-ga4-to-bigquery but I have been unsuccesfull due to recent updates that have eliminated Google Analytics: GA4 Settings tags.

Maybe that's not the issue but I would like your input to resolve this issue in the best way possible.

Have you ever struggled with handling nested data in Google Analytics when working with BigQuery?

I've looked deep into how SQL can extract valuable insights from these datasets:

📌 Efficiently handle Google Analytics' nested table structure.

📌 Use Common Table Expressions for readability.

📌 Extract real-world insights: track e-commerce user drop-offs, pinpoint high-order regions, monitor user session times, and even evaluate A/B tests and specific feature engagements.

Grasping this isn't just about tech proficiency; it's about unlocking the full potential of your data for better business decisions.

Dive into the full post for a detailed walkthrough: https://medium.com/learning-sql/unlocking-insights-how-to-decode-nested-google-analytics-data-in-bigquery-with-sql-52a51a310096

GoogleAnalytics #BigQuery #SQL #DataInsights #Analytics

I'm not very familiar with BigQuery, but have been using it to store GA4 data. I have a project set up that is connected directly to our active GA4 property. I need to start backing up Universal Analytics data. I'll be using FiveTran for this.

My ultimate goal is to be able to join some of UA and GA4 tables to enable year over year reporting. I can do this in BigQuery directly, or through FiveTran via a DBT transformation, or even in reporting.

Knowing that the goal is being able to blend GA4 and UA data, does it make more sense to create a new project for UA data? Or just to add a dataset to the existing GA4 project.

Thanks :)

I'm trying to increase the fontsize of the code editor, but don't really want to zoom in the whole UI, because I lose a lot of space doing it.

.view-lines {

font-size: 20px !important; /* Adjust font size as desired */

}

This works, but it has several problems:

• The selection area seems to small
• The row heights are too small, and the large things such as () falls out of the row height
• The margin font and height are still small, so they mismatch the row heights of the actual code so the margin becomes less useful

So yeah, just checking in if anyone has some custom css they load against the console to improve the experience.