#15 - UI changes in BigQuery, some handy SQL tricks and other updates in August

Hi there,

First, a warm welcome to all new subscribers who joined since the last newsletter.

Lots of updates to cover! Let's dive in.

New articles on GA4BigQuery

Since the last newsletter we've added several handy SQL tricks:

• The MAX_BY function (this really makes life easier)
• Using named windows for better readability when using window functions
• The SAFE. prefix and other "safe" functions to avoid query errors and get NULL values instead
• A case made against using ordinal positions in ORDER BY

And an article about calculating acquisition columns for conversions (or any event record) using a last-(non-direct)-click approach, for situations where the built-in fields like session_traffic_source_last_click are not fit for purpose.

Now let's look at what happened in August. The BigQuery team added a few UI changes:

A new way of commenting and collaborating on scripts

Starting from mid-August, comments can be made in various places in the BigQuery UI such as Saved Queries, BigQuery Notebooks, data canvases etc. The way comments are handled and managed (e.g. using the status of open/resolved etc.) is eerily similar to the commenting feature in Google Workspace applications. It can be opened up from the right side menu banner, via (currently) the bottom icon, under version control.

A new "Reference" panel to show dataset information

A new UI feature was added, which collects most of the information we usually check on a table: the schema (columns and data types), the last updated date and the size, in the same place. This makes the development flow significantly faster; furthermore, ctrl+clicking the table name now brings us this pane as opposed to the dataset details. Otherwise it is accessible from the right side menu pane. Clicking the column names add these columns to the SQL query, saving us from some typing as well. Official documentation here.

Some BigQuery Data Transfers will become paid

Another noteworthy update is Google's plan to make some of the BigQuery Data Transfers—including, but not limited to: Facebook Ads, Salesforce, database syncs (e.g. Oracle, MySQL etc)—paid, once they reach General Availability status. Cost will be determined by the slot hour usage. I haven't yet figured out how to get the slot hour usage for e.g. the Facebook Ads transfer, though I don't think it'll be costly. (The Facebook transfer is not a good product, so hopefully the cost will remain negligible.) Other transfers e.g. Salesforce and database table copies might be expensive: there is a good discussion in the comments of this post. Watch out for the GA (mean: general availability) release dates.

New GA4Dataform release (v18) and knowledge nuggets

The Superform Lab guys released a new version of GA4Dataform, an easy-to-install, free(!) Dataform repository that pre-processes GA4 data. As they are planning to release more updates and features, they have started putting out some useful nuggets of content, including a guide for getting started as well as more techy documentation on tweaking the configuration files.

Many of the guys maintain their own blogs; check out a very advanced Dataform use case recently published, showing how to query a table and use the result in JavaScript to build other Dataform models.

Updated Simmer course on BigQuery, SQL and GA4

This is a September update, but it would be a disservice to not mention it: Simo Ahava and Johan van de Werken teamed up again to deliver an updated BigQuery course that covers SQL basics, advanced GA4 use cases, some integrations and automations. Premium GA4BigQuery subscribers can avail of a 15% discount on this course even after the September launch discount ends. Those who enroll on the Simmer course can avail of a "15% forever" discount on the annual GA4BigQuery subscription that applies to all future renewals as well.

BigQuery related content in August from the community

• A useful, quick piece of analysis (with BigQuery SQL code) was posted by Marco Giordano to understand which pages, and from which channels, are driving new visitors. Another post of his describes a page level aggregation of GA4 and GSC data to segment and understand pages from a SEO performance perspective, and he has also shared 5 more tips on what to analyze with these data sources.
• What is event_bundle_sequence_id in the GA4 export schema? Find out from Alex Ignatenko's post. He also listed out some common reasons why numbers in GA4 and BigQuery differ, and showed a basic SQL method to distinguish new and repeated purchases using GA4 data.
• Applying data policies (posted by Jaco Silvis) to restrict the display of certain sensitive (e.g. PII) fields, to ensure data security without severely limiting access to the table itself. Jaco also posted about the fact that we can now build ML models using the BigQuery UI. (Of course, whoever has built an actual ML model will know that this is not the bottleneck and it's still a big job to build a model, but it's still a nice direction.)

• A guide on Vector Index and Vector Searches by Hasan Mushtaq. Also, another guide on Flattening JSON using BigQuery's "Data Preparation" feature.
• Those interested in moving towards using the pipe syntax, Riccardo Malesani reported a good example on the ease of unioning multiple tables, adding/removing columns on the way, being much simpler than the old mechanism.
• Table snapshots: a cost-efficient way to store backups beyond the time travel window, by Matt Dixon.

Other relevant blog posts from the community

• A new Google Sheets add-on to audit GA4 properties: easy to use, providing a nice report spanning most aspects of the GA4 configuration and data.
• A new custom HTML tag to provide a better, more comprehensive and nuanced sense of engaged sessions than Google's original, rudimentary approach—LinkedIn post and code on GitHub. Also check out his GTM Attribution cookie too which got some new updates this month.
• Google Tag Manager now has better support for reading Google Analytics cookies for values.
• ChatGPT conversations now searchable on Google if you don't watch your sharing settings closely.
• You can use custom variables in Google Ads via GTM in a very similar way as we do in GA4, using the "Improved Google Marketing Tags using gtag" tag template instead of the native Google Ads conversion tracking template.
• Looker Studio dropped an update too: Query Result Variables now can have conditional formatting, now dynamic text can be even more dynamic and change colors based on data thresholds, just like scorecards. Read more on Query Result variables here.
• If you tend to look at Consent Mode signals in more depth: Google changed the way ad_user_data and ad_personalization is represented in the coded signal. Analytics Debugger created a "Consent Parser" — a lightweight ( ~900bytes) JavaScript library for encoding and decoding Google Consent Strings, and since also made available as a server-side GTM variable template.
• Updates have been made to this Attribution Insight Tag—a very useful tool to capture all sorts of UTM parameters and click IDs and storing them for better reporting or offline conversion processes.
• For those more advertising-minded: PMax search terms are now available from the Google Ads API.

• Google Cloud Conversatonal Analytics API now in preview, if you want to use AI to dig into your data in BigQuery.
• For those with a taste for statistical modelling, a custom GPT has been released to answer questions about MMM (Marketing Mix Modelling).

MeasureSummit early bird pricing until 16th September

As more and more speakers are introduced for MeasureSummit, it is apparent that there will be an excellent line-up on tracking and measurement, paid advertising, analytics deep-dives, as well as BigQuery. Attendance is free (you just have to sign up), but you can choose to get a discounted All-Access Pass to re-watch and download the presentations, and to get the transcripts and the slides. The price will increase after 16th September.

That's it for now. Thanks for reading and happy querying—and if I missed any key updates from August, feel free to add them in the comments!

Best regards,
Balazs