Tom N.’s Post

The third book that published on Google Play Books for free. Explain Key Errors and Duplicate Rows in fetched Google Universal Analytics reports API: A Comprehensive Guide to Troubleshooting and Data Management https://lnkd.in/e6h-bFkF #Analytics_Reports_API #GA3 #Key_Errors #KeyErrors #Free_Book #PostgreSQL

The GSC Bulk Export gets you data about anonymized queries. Clicks, impressions and all the missing rows from Google Search Console. ❓ A good exercise to do is measuring what's the degree of anonymization. You can run the code below on the "searchdata_url_impression" table! (This is also simple and common SQL, ideal for learning!) Some considerations: 👉 Only search results, not Discover etc. 👉 Replace table_name with your table and possibly, your project name too (Copy ID) 👉 Anonymization for search is different from Discover's! 💡 This percentage is for informative purposes, you can't do anything about it! Mind you, the column names may be different if your data team (or whoever) renamed the columns. Always check how's the table first! ⬇ If you want to get my free BigQuery handbook, join my newsletter in bio!

GA4 data in BigQuery is quite hard to understand at first. But you need to understand how it works for large projects. 💡 Storing data is caring or you'll have many headaches The BigQuery export is not sampled and it's a nice way to get your data somewhere without much effort. You can say it's like the GSC Bulk Export. The most confusing thing is the tables you get: 👉 events_: this table contains your event data. In SEO you often use this table here. 👉 events_intraday_: like the one above but for streaming data. I never use it! 👉 pseudonymous_users_: your table with users data. This table appears if you don't have the User ID integration active. 👉 users_: if you have a User ID integration active. E.g. users can log in to your website and you can assign them an ID. GA assigns a pseudo identifier to each user to track how they behave across different sessions and devices. The users_ table refers to a particular type of ID, called user ID. There are many ways to assign an identifier to a user, one of them is the user ID, for example if they log in. In most cases, you are absolutely fine with the 1st and 3rd tables. This isn't always the case so be sure to understand what you want to do first. In SEO, you often need the events_ table alone, just saying. ❗ The other annoying detail is the presence of nested data. Let's take the events_ table. You get your events data in a nested format, so you need to use the UNNEST function in BigQuery. In plain English, you need to flatten the data to make sense out of it. But nested data also offer a benefit in terms of query performance! GA4 is annoying, I know it, but it's important to know what it offers with BigQuery. You simply can't ignore this feature. 📥 If you want to learn more about Analytics for SEO, subscribe to my newsletter in bio! P.S. Article and code in the comments ⬇

Know All About The QUERY Function In Google Sheets Now, in this blog, you will know all about the QUERY function in Google Sheets. Knowing all about the Query function in Google Sheets will help you retrieve data from a large dataset. It helps you to visualize the specific range of data from Google Sheets, which you have mentioned in your Query function. As a result, you don’t need to go through the hassle of finding your data from a large dataset. This function will help you to write SQL-like queries using the Google API Query Language. The function sends a request to the Google Sheets API using the Google Sheets Query, and it will receive filtered data as a result. Seems Interesting!!! 👍 then read more.👇 https://lnkd.in/gbgu2FmX

Update from Google BigQuery's toolbox! Introducing the GAP_FILL function, a powerful way to address missing data in time series analysis. GAP_FILL allows for seamless data interpolation, making it easier to handle time series with gaps by filling them based on specified criteria. You can use it with an OVER clause, defining your time period and the value column to ensure continuous data flow for your analyses. For example, if your dataset tracks daily sales but is missing data for some days, GAP_FILL can help. This fills in the missing dates, assuming `0` sales for days without data, thus ensuring your dataset is complete, each date within the range is accounted for, and it's ready for deeper analysis. Prior to this, we would create an array of all date values and joined it with the rest of the analysis using LEFT JOIN to be able to take care of the gaps. Here’s the link to the official documentation: https://lnkd.in/dRByZUbR

I've been curious about the origin of the term #VertiPaq since I heard it for the first time about 4 years ago. I asked Google Gemini and it guessed its origin without any explicit reference; just based on its characteristics! Although I think the second part is related to the compression rather than package, I have to say that I am really impressed by the way it inferred that "verti" is the short form for "vertical" and linked it to the word "columnar"! Here's our conversation: Me: Where does the term "vertipaq" come from? Gemini: The term "VertiPaq" is a combination of two words: * Vertical: This refers to the columnar storage format used by VertiPaq, which is optimized for analytical queries. Instead of storing data in rows, as traditional databases do, VertiPaq stores data in columns, allowing for faster access to specific data points. * Paq: This part is likely a shortened form of "package" or "packet," suggesting a self-contained unit of data. In essence, "VertiPaq" means a self-contained package of data stored in a vertical (columnar) format. This format is particularly efficient for data warehousing and business intelligence applications, where rapid analysis of large datasets is essential. Me: Provide a reference. Gemini: Unfortunately, I couldn't find a specific, publicly accessible reference that explicitly states the origin of the term "VertiPaq". It's likely that the term was coined internally by Microsoft, the company that developed the VertiPaq engine. However, we can infer the meaning and context from its use in relation to Microsoft's Power BI and Analysis Services Tabular products. These products utilize the VertiPaq engine for efficient data storage and query performance.

Exciting news for BigQuery users: the new Table Explorer feature is here in preview! It seems like many might have missed this update, but in my opinion, it's a game-changer for getting started with BigQuery. Now, marketing professionals can easily dive into data analysis without needing to know SQL. While it's still in preview, the potential is promising. Check out the article linked in the comments for more details.

𝗛𝗮𝘃𝗲 𝘆𝗼𝘂 𝗲𝘃𝗲𝗿 𝘁𝗵𝗼𝘂𝗴𝗵𝘁 𝗮𝗯𝗼𝘂𝘁 𝘄𝗵𝗮𝘁 𝗵𝗮𝗽𝗽𝗲𝗻𝘀 𝘄𝗵𝗲𝗻 𝘆𝗼𝘂 𝗿𝘂𝗻 𝗮 𝗾𝘂𝗲𝗿𝘆 𝗶𝗻 𝗕𝗶𝗴𝗤𝘂𝗲𝗿𝘆? 🤔 Let’s look at it step by step with an example query: 𝗦𝗘𝗟𝗘𝗖𝗧    𝗖𝗢𝗨𝗡𝗧(*)  𝗙𝗥𝗢𝗠    𝗽𝗿𝗼𝗷𝗲𝗰𝘁.𝗱𝗮𝘁𝗮𝘀𝗲𝘁.𝘁𝗮𝗯𝗹𝗲  𝗪𝗛𝗘𝗥𝗘    𝗱𝗮𝘁𝗲 > '𝟮𝟬𝟮𝟯-𝟬𝟭-𝟬𝟭' 𝗦𝘁𝗲𝗽 𝗯𝘆 𝗦𝘁𝗲𝗽 𝗘𝘅𝗲𝗰𝘂𝘁𝗶𝗼𝗻 𝗶𝗻 𝗕𝗶𝗴𝗤𝘂𝗲𝗿𝘆:  𝟭. 𝗦𝘂𝗯𝗺𝗶𝘁𝘁𝗶𝗻𝗴 𝘁𝗵𝗲 𝗤𝘂𝗲𝗿𝘆 You start by sending the query through the BigQuery interface or API, and then the process begins. 𝟮.𝗥𝗼𝗼𝘁 𝗡𝗼𝗱𝗲 𝗣𝗿𝗼𝗰𝗲𝘀𝘀𝗶𝗻𝗴 The 𝗗𝗿𝗲𝗺𝗲𝗹 𝗾𝘂𝗲𝗿𝘆 𝗲𝗻𝗴𝗶𝗻𝗲 (Google'𝘀 𝘀𝘆𝘀𝘁𝗲𝗺 𝗳𝗼𝗿 𝗽𝗿𝗼𝗰𝗲𝘀𝘀𝗶𝗻𝗴 𝗾𝘂𝗲𝗿𝗶𝗲𝘀) gets your query and checks the metadata. This helps it understand the table's structure, like its partitions and data types. 𝟯.𝗧𝗮𝘀𝗸 𝗗𝗶𝘀𝘁𝗿𝗶𝗯𝘂𝘁𝗶𝗼𝗻 The root node breaks the query into smaller tasks. T𝗵𝗲𝘀𝗲 𝘁𝗮𝘀𝗸𝘀 𝗴𝗼 𝘁𝗼 𝗶𝗻𝘁𝗲𝗿𝗺𝗲𝗱𝗶𝗮𝘁𝗲 𝗻𝗼𝗱𝗲𝘀, 𝗼𝗿 𝗺𝗶𝘅𝗲𝗿𝘀, 𝘄𝗵𝗶𝗰𝗵 𝗮𝗿𝗲 𝘀𝗽𝗿𝗲𝗮𝗱 𝗼𝘂𝘁 𝗮𝗰𝗿𝗼𝘀𝘀 𝘁𝗵𝗲 𝗻𝗲𝘁𝘄𝗼𝗿𝗸. This helps balance the load and keeps everything running well. 𝟰.𝗤𝘂𝗲𝗿𝘆 𝗢𝗽𝘁𝗶𝗺𝗶𝘇𝗮𝘁𝗶𝗼𝗻 The mixers then optimize the query by selecting which data partitions to read. This way, only the necessary parts are scanned, saving time and resources. 𝟱.𝗗𝗮𝘁𝗮 𝗥𝗲𝘁𝗿𝗶𝗲𝘃𝗮𝗹 𝗮𝗻𝗱 𝗖𝗼𝗺𝗽𝘂𝘁𝗮𝘁𝗶𝗼𝗻 Next, the leaf nodes at the bottom of the Dremel tree read the data from 𝗖𝗼𝗹𝗼𝘀𝘀𝘂𝘀, Google'𝘀 𝘀𝘁𝗼𝗿𝗮𝗴𝗲 𝘀𝘆𝘀𝘁𝗲𝗺. They also apply any filters (like date > '2023-01-01') and perform calculations, such as COUNT(*). 𝟲.𝗔𝗴𝗴𝗿𝗲𝗴𝗮𝘁𝗶𝗼𝗻 𝗼𝗳 𝗥𝗲𝘀𝘂𝗹𝘁𝘀 After that, the intermediate nodes collect results from the leaf nodes, combine them, and send everything back up. 𝟳.𝗙𝗶𝗻𝗮𝗹 𝗢𝘂𝘁𝗽𝘂𝘁 Finally, the root node puts together all the results and sends the final answer back to you. for more details, checkout the article in comment box!

New blog (in comments): The complexity paradox: why your spreadsheet is complex and BigQuery is simple Ever noticed how a “quick fix” spreadsheet can spiral into a maze of formulas, while tools like BigQuery offer true simplicity over time? This is the Complexity Paradox: familiar solutions may feel easy now but often turn fragile as data grows, while advanced tools bring lasting stability. 📝 Spreadsheets: A Hidden Maze Spreadsheets are familiar and quick, but as data scales, they become a web of dependencies and manual fixes, relying heavily on you to keep them intact. ⚙️ BigQuery: Scalable Simplicity Though BigQuery can feel like a leap, it makes data management scalable and straightforward. With tools like Google’s Data Transfer Service, SQL, and Dataform, you gain structured, reliable data management that grows with you. 📈 Embrace the Paradox Choosing the right complexity upfront brings simplicity later. Measurelab can guide your journey from quick fixes to scalable, lasting solutions. #Measurelab #DataTransformation #BigQuery #AnalyticsEngineering

Handling High Cardinality Dimensions in GA4 Using BigQuery: A Guide to Optimized Reporting 📊 High-cardinality dimensions, like user IDs or page URLs, bring unique challenges in Google Analytics 4 (GA4). Working with a high volume of unique values can create inflated datasets and skewed metrics and often lead to slower queries and higher costs in BigQuery. But with a few strategic techniques, you can overcome these challenges to ensure efficient, accurate reporting! Here’s how 👇 1. Aggregating Key Metrics to Reduce High Cardinality Noise High cardinality means dimensions have many unique values, which can make event-level data incredibly dense. Tip: Use aggregation to reduce noise and summarize data at broader levels, such as by daily pageviews instead of individual page URLs or unique user actions. 2. Clustering by Key High-Cardinality Dimensions Clustering can speed up query performance by grouping data, making retrieval of relevant data much faster. Tip: For GA4 data in BigQuery, cluster by high-cardinality fields such as user_pseudo_id or event_name. 3. Sampling Large Datasets for Rapid Analysis In cases where high-cardinality data is too large to analyze quickly, sampling can give you fast insights without querying the entire dataset. Tip: Use LIMIT and RAND() functions to sample portions of your dataset randomly. 5. Transforming High Cardinality Dimensions for Consistency Small inconsistencies (like case sensitivity or minor variations) can inflate high-cardinality dimensions, adding redundant entries to your dataset. Tip: Clean up high-cardinality fields by applying transformations—such as LOWER() to standardize case or using REGEXP_REPLACE() to strip unnecessary parameters from URLs. Final Thoughts Handling high-cardinality dimensions can be challenging, but with the right SQL techniques, you can tame even the largest datasets and extract meaningful insights quickly and efficiently. Aggregation, clustering, and transformations are powerful tools for optimizing high-cardinality data and ensuring GA4 reports stay responsive and accurate. When used correctly, BigQuery transforms GA4’s high-cardinality data from a challenge into an opportunity to uncover deeper insights about your audience, all without sacrificing performance. #DigitalAnalytics #BigQuery #GA4 #HighCardinality #DataOptimization #SQL #DataEngineering #WebAnalytics