#21 - New UI features and (loads of) other BigQuery updates in March

Hi Folks,

Apologies for the long break — life and work got very busy. Let's get back on track now and see what happened in March on the BigQuery front. April updates will follow soon.

Current online conferences

First, let's cover some time-sensitive news. As you are probably aware (perhaps from the email last week), MeasureSummit is on — just finished an information-packed first day. There was a wide variety of topics; as someone pointed out, there were a few talks that covered AI, but not everything was about AI. Still not too late to sign up and tune in for tomorrow's talks; and the ones already presented are still available on demand for the rest of the week. After the encore weekend, presentations will only be available with an All Access Pass.

The other "big" day is still ahead of us: next Tuesday is BigQuery Day with a very promising line-up. It looks to be heavy on FinOps and data engineering principles, but that all sounds very relevant for us BigQuery enthusiasts.

New on GA4BigQuery

A new premium article was released on the topic of mobile app tracking health check, which is best investigated using BigQuery.

Mobile App Tagging Health Check using BigQuery (Part I)

This article shows a way to test mobile app tagging using BigQuery and to quickly reveal tracking inconsistencies between the Android and iOS platforms. Part I shows how to reveal event-level discrepancies, and showcases the use of the WITH() expression that makes it easier to build complex columns.

GA4BigQueryBalazs Vajna

UI change: Pipelines and Connections in BigQuery Studio

Previously, there were various different ways to schedule jobs and pipelines: Data Transfer, Scheduled Queries, Cloud Run, Dataform etc. Now, they're all accessible under the same hood.

Now, creating a Data Transfer also starts differently than before. Previously, we used the "Create Transfer" button in its own dedicated section, whereas now we click "Create" and the data transfer is one of the options we can choose from.

Updates on Conversational Analytics

One of the most promising BigQuery features got further updates:

• Integration with Google Cloud Storage through ObjectRef functions. This means you can use natural language to query unstructured data like PDFs, audio files, images etc.
• Some BigQuery ML functions are now supported, e.g. AI.FORECAST, AI.DETECT_ANOMALIES, and AI.GENERATE, through prompts.
• BigQuery conversational analytics can now use BigQuery table partitioning, improving query performance when chatting with the agent.
• BigQuery jobs initiated by the conversational analytics agent are now labeled accordingly in BigQuery Job History (labels similar to {'ca-bq-job': 'true'}).
• The agent now suggests questions that are directly clickable.

And now, something I actually didn't notice before writing this newsletter: you can chat with the data from query results!

Other BigQuery updates

•  Custom organization policies to allow or deny specific operations across the whole organization. Really backend nerd stuff, but can come handy for large, more established organizations with strict IT standards.
• BigQuery Data Transfer Service remote MCP server to enable AI agents to create, manage, and run data transfers. Plus another MCP server for BigQuery migration tasks e.g. SQL translation.
• This is not a March update, but a new one in May, just in case you missed the last (out-of-course) newsletter: the Google Ads Data transfer (and the API in general) will restrict backfills to 37 months. While that's more than enough for most organizations, in case you need data from before that, you have only until 1st June to backfill data older than 37 months. (Tips to do it faster here and here.)

Community posts and updates around BigQuery

• A great webinar from the GA4Dataform guys on how to do attribution in BigQuery: the video is available on Youtube. Plus, also a nice use case article on how to use the GA4Dataform ga4_transactions table to find out which product categories get customers to buy again. And, another one about analyzing where people go before abandoning their cart. The last one in March (Jules was really on a roll) is about finding site search terms that convert well.
• An elaborate article on how to use BigQuery Graphs: the analysis and the visualization part.
• An example of using APPROX_COUNT_DISTINCT to get the distinct number of users in an enormous GA4 table. A similar trick from the same author using HLL_COUNT.INIT() to build distinct user count tables in an incremental manner.
• An elaborate guide to pre-process GA4 data using DBT. Plus, another, more "general purpose" article on DBT best practices with BigQuery, focusing on data architecture choices.
• "GA4-export-fixer": a kind-of alternative to GA4Dataform.
• An elaborate study to prevent BigQuery errors (and associated sunk cost) at scale.
• Cost optimization example by using the right data type.
• An article to unpack capacity commitments in BigQuery.
• A nice collection of BigQuery SQL code "tricks" to accelerate work.
• A tip on how to bake table expiration into the DDL (table creation code) to prevent creating permanent junk. A great idea I think.
• An article on how to catch silent data loss across hundreds of tables using the INFORMATION_SCHEMA and AI.DETECT_ANOMALIES
• An article on backfilling GA4 data using the Data Transfer.
• An example case (demo) of using AI.CLASSIFY in action (GitHub repo here).
• An AI model aimed at really understanding the GA4 data model and writing SQL queries.
• A Claude skills md file to help debug BigQuery SQL queries (GitHub link here).

Other relevant news from the community

• Email Campaign Measurement Toolkit freebie and an automated GA4 Audit Tool from the same team.
• Two nice GA4 tips from Stape.
• A guide on how to setup server-side GTM on Azure.
• Complete guide to build your AI assistant.
• A Shopify gotcha worth being aware of (by automatically switching off data collection).
• A video on Meta click-through attribution update worth being aware of.

Fun stuff

• Data Visualization from previous eras (do check this out, I think it's really cool).
• Kind of in the same spirit, how to use a spiral chart on GA4 and Search Console data.

I'm always amazed at the amount of BigQuery product and community updates each month; March did not disappoint either. It's a long list and you might get saturated while reading this — now as I was going through it all while writing it up, I re-discovered a lot of useful things, so it may be worth checking back on this in the future. If you have some ideas on how to "save" some of these goodies (in a way that they don't get buried in the sea of updates), let me know.

I hope you found this useful, I sure did on my own end and I don't regret collecting it all.

Best regards,
Balazs