Himanshu Sharma’s Post

Here is how you can calculate new and returning users in #GA4 BigQuery... Understand that ‘user type’ (new user or returning user) is not a default dimension in GA4 BigQuery Export data. So, you will have to calculate it manually. Before you can write a text prompt, you need to understand the logic/calculation behind the dimensions you want to retrieve. Similarly, the total number of new users and the total number of returning users are not default metrics. So, you will have to calculate them manually. Based on our understanding of the logic/calculation behind your dimension/metric, create a text prompt instructing 'GA4 BigQuery Composer' to create the corresponding SQL. . . Let us suppose you want to query the data which meets the following requirements: 1) You want to query data from a particular date range, say Oct 1st to Oct 30th, 2023. 2) You want your table to show only the total number of new and returning users. . . Follow the steps below: Step-1: Use the following text prompt in ‘GA4 BigQuery Composer’. Write SQL which meets the following requirements:    1) Show data for Oct 1st to Oct 30, 2023    2) Show only the 'user_type_calc' and 'users_calc' columns of the data table.   3) If the value of ‘ga_session_number’ event parameter for the event ‘session_start’ is 1, classify the user as a ‘New User’ and assign this value to 'user_type_calc' field. The 'ga_session_number' event parameter is an array of structures within the 'event_params' array of structures.   4) If the value of ‘ga_session_number’ event parameter for the event ‘session_start’ is greater than 1, classify the user as a ‘Returning User’ and assign this value to 'user_type_calc' field.   5) If neither of the above conditions are met, assign a 'null' value to 'user_type_calc' field.   6) Count the unique values of the 'user_pseudo_id' event parameter as values of the 'users_calc' field.   7) Show the values of the 'users_calc' column in descending order.   My table ID is: <Enter your table ID> Step-2: Copy the generated SQL code by clicking on the ‘Copy code’ button. Step-3: Paste the copied code in the SQL editor and then click on the ‘RUN’ button. You should now see the query result like the one below:

5 common-sense, beginner-friendly ways to avoid overspending on your BigQuery projects One of the most common questions I get from people wanting to dive into working with GA4 data in BigQuery is: “𝘖𝘬, 𝘣𝘶𝘵 𝘪𝘴𝘯’𝘵 𝘪𝘵 𝘦𝘹𝘱𝘦𝘯𝘴𝘪𝘷𝘦?” Short answer: generally speaking, it’s not. → with the default, on-demand model you get a free 1TB of querying each month  → once you exceed it, you'll pay $6.25 for each TB of queries → for most websites, that free TB/mo is sufficient However, for projects where exceeding that 1TB monthly is a real risk - there are some common-sense, beginner-friendly ways to cut your costs without sacrificing the quality your workflows. Let's look at some of them. *This list is as basic & common-sense as it gets - yet: i) I've seen many people NOT using such tips ii) from my experience, it covers 60-80% of use cases* #1 pay attention to each query’s size before you run it Basic & common-sense, I know. As promised. But still - the easiest way to not overspend is to simply track the costs your queries generate - and optimize where needed. How to optimize? Read below. #2 don’t use `SELECT *` for previewing the table Whenever you want to see how a table looks like: → use the preview tab → don’t run `SELECT *` (yes, many people do it, generating unnecessary costs) #3 LIMIT is an illusion Whenever you're about to run a heavy query, you might be tempted to use ‘LIMIT xxx’ function. After all, if you’re seeing only first xxx (e.g. first 100) results, the query should be lighter, right? Well... not really. Unfortunately, LIMIT function doesn’t change the size (or cost) of the query AT ALL. #4 use table suffixes when working on new queries My typical workflow when working on a new query is: → I create a new query based on data from a limited time period (day / month) to make it lighter & cheaper - and use table suffixes to make that limitation → whenever I’m happy with the query, I run it on `events_*` table to check if everything works correctly there as well → (optional) automate it if needed This way, I generate a fraction of the costs while experimenting with a new query - because, at first, I query only a subset of data - and query the whole table only once I know what I'm doing. #5 set up budgets & alerts to monitor your spendings If you want to keep your finger on the pulse, the best way of doing so is by simply monitoring your spendings... ...and setting up alerts for when you cross certain thresholds (e.g. 25%, 50%, 90% of your predetermined monthly budget). Simple, yet effective - like Apple's Screen Time reports telling you that last week you've spent an average of 13hrs daily on your phone... Now, if you’re an experienced data engineer reading this list, you’re probably like “OK, but where are the ACTUAL tips? I knew this stuff for ages!” I know. I promised super basic stuff, and it was super basic stuff. We’ll speak about more advanced query optimizations some other time.

BigQuery supports ANSI SQL join types. JOIN operations are performed on two items based on join conditions and join type. Items in the JOIN operation can be BigQuery tables, subqueries, WITH statements, or ARRAY's https://lnkd.in/e8aqt6xt

Learn BigQuery SQL techniques to extract only the most relevant elements of your ugliest URLs ✨ Zach Quinn shares techniques for extracting information from URLs in BigQuery in one of his latest articles. Article: https://lnkd.in/gS3rCAZS #sql #learningsql #bigquery

#GA4 #BigQuery Tip - How to automatically generate SQL to calculate total number of users, new users and returning users in last 7 days. Step-1: Navigate to the 'GA4 BigQuery Composer tool' (a customized ChatGPT). If you don't have access, Google it to find the link. . . Step-2: Enter the following prompt: write SQL to calculate total number of users, new users and returning users in last 7 days. . . Step-3: Enter your table ID when asked. It should look something like 'ga4.analytics_207482454.events_20231022'. . . Step-4: Composer will now automatically create the SQL code for you. Copy the code. . . Step-5: Navigate to the query editor of your BigQuery project and past the copied SQL code. . . Step-6: Check the size of your SQL code. Ideally, it should not be in GB or TB. If it is, consider reducing the size of the data you plan to extract. . . Step-7: If you don't see any error message for your SQL, click on the 'Run' button to execute the code. The query results will appear at the bottom window. . . Let's make our data extraction requirement a bit more advanced. Let's break down the data by first user source/medium. . . Step-1: Enter the following prompt in GA4 BigQuery Composer: Update the SQL to breakdown the data by first user source/medium. . . Step-2: Composer will now automatically create the SQL code for you. Copy paste this code to your query editor of your BigQuery project. . . Step-3: Check the size of your SQL query and any error message. If everything looks fine, then execute the code. . . Note(1): If you get an error message then copy-paste the error message into the Composer tool. It will re-write the SQL for you free of charge. Note(2): If you are unsatisfied with the result format (may you want to rename a particular column), it will re-write the SQL for you. For example, you can use the following prompt: make adjustment to this SQL so that 'source_medium' field name appears as 'first_user_source_medium'. Note(3): The more you use the composer tool, the more accurate and efficient it will get for you. Just don't start a new session (unless you see a tool update, as I often publish new tool versions).  . . Note(4): It is very likely that when you compare GA4 BigQuery data with the data from UI or data API, it will not exactly match. But it should be reasonably close. . . Note(5): Be as specific as possible when writing prompts. If you write vague prompts (like "calculate the number of users"), you will get vague output.

BigQuery Essentials: Building Data Structures with Struct, Array, and JSON for Optimal Performance Google BigQuery is designed for big data, and when you're working with massive datasets, you need more than just basic tables and rows. This is where Struct, Array, and JSON come into play. These structures allow you to group, nest, and repeat data, which can make your queries faster, more efficient, and easier to manage—especially when dealing with complex data relationships. In this article, we'll introduce you to these three core data structures, show you how they work, and explain why they’re essential for getting the best performance out of BigQuery. Whether you’re coming from a SQL Server background or just learning your way around BigQuery, understanding these tools will give you a significant boost. [Read More in my blog] https://lnkd.in/gpq-gcrY

🔍 Exciting news from BigQuery! 🔍 Gone are the days when BigQuery was merely an adhoc querying supermachine. Now, it's not just about exploring the unknown; it's about learning from experience and optimizing for the future with History-Based Optimizations. 💡 What are History-Based Optimizations? They leverage data from past query executions to apply additional optimizations, enhancing performance like never before. Picture this: your first query takes 60 seconds, but the second one? Only 30 seconds! And the improvements keep coming until there's nothing more to add. 📊 Check out this example: Execution count | Query slot time consumed 1 | 60 (Original execution) 2 | 30 (First optimization) 3 | 20 (Second optimization) 4 | 21 (No further optimizations) 5 | 19 (No further optimizations) 6 | 20 (No further optimizations) These optimizations are only applied when there's high confidence in performance improvement. And if an optimization doesn't make a significant difference, it's out for future queries. 🔧 Enabling this feature is simple: just include 'default_query_optimizer_options = adaptive=on' in your ALTER PROJECT statement. 🛑 Need to turn it off? Use 'default_query_optimizer_options = adaptive=off'. 🔍 Reviewing optimizations? Easy-peasy with SQL or API methods. Dive into the guide for all the details. Roles and permissions: - Ensure you have the necessary permissions to enable/disable this feature. - Reviewing optimizations requires specific roles. More details in the guide. Upgrade your querying game with BigQuery's History-Based Optimizations today! #BigQuery #DataOptimization #QueryPerformance

BigQuery SQL Injection Cheat Sheet

Get Started With GSC Queries In BigQuery #gsc #query #bigquery

#BigQuery has an interesting data structure concept, called “sharded tables”. Anyone who uses the #GA4 BigQuery export (or used the old #UniversalAnalytics 360 BigQuery export - RIP 😢✝) would have come across this. One can spot this feature by BigQuery collapsing tables (within the same dataset) that begin with the same names and just end with different suffixes: typically the suffix being a date in YYYYMMDD format. In GA4 for example, the event table takes the form of multiple daily tables named events_YYYYMMDD. Some interesting things to know about these sharded tables: 🔵 Let’s start with the basic stuff: a sharded table can be queried as one table, by using * in the place of the suffixes e.g. SELECT <something> FROM `projectname.datasetname.events_*`. A pseudocolumn called _TABLE_SUFFIX can be used in the WHERE clause to limit the amount of data read (to one or more shards). It is also possible to query a subset of table shards by leaving a longer prefix before the wildcard, e.g. SELECT <something> FROM `projectname.datasetname.events_202405*`. 🔴 You can simply create sharded tables by naming tables (under the same dataset) in a way that they have the same prefix, or in other words, the same initial string. BigQuery will (try to) recognize them as shards when you query them using the * wildcard. ⚪ The suffixes can be (and usually are) dates, but they don’t have to be: BigQuery will recognize and collapse table names starting with the same string. E.g. if you switch on streaming in GA4, you’ll also have events_intraday_YYYYMMDD tables which are also included in the query if the table events_* is queried. But you could use it in other smart ways e.g. have a different table for each region (as an alternative to using clustered tables, if you want to do this for cost efficiency reasons). One good use case of sharded tables (when it is preferable over partitioning) is when the data structure changes over time - e.g. the data from a source system may start having more columns or the data type will change. Using sharded tables allows for a seamless export even in these situations: the change in the exported data will not break the export process. (It will introduce issues in reading those tables - more on that in another post.) – Follow me for updates on #digitalmarketing #data #analytics, #BigQuery and #eprivacy, and hit the bell on my profile to receive updates.