#7 - AI-powered SQL code assistance coming to BigQuery Studio
A warm welcome to the 527 new subscribers who joined since the last newsletter!
The end of summer is approaching, and it's time for your monthly update on GA4 and BigQuery.
New articles on GA4BigQuery
Since the last newsletter we have added 3 new articles:
- Why (and how) to use GA4 event_timestamp with your local time zone instead of event_date in BigQuery
- How to set up Google 'Connected' Sheets to access GA4 BigQuery export data without learning SQL
- How (and why) to activate the GA4 user activity data export to BigQuery
SQL code assistance in 'BigQuery Studio'
In the last newsletter we already shared a couple of tools that make your life easier when querying GA4 event data in BigQuery. And of course we all fooled around with ChatGPT by now.
As expected, Google announced to add their own native SQL code assistance to BigQuery. This new feature - powered by an AI tool called Duet AI, is part of a set of new releases that aim to turn BigQuery into a complete data analytics 'studio':
(...) a single interface for data engineering, analytics, and predictive analysis to simplify end-to-end data workflows.
According to the documentation, BigQuery Studio will contain:
- A robust SQL editor that provides code completion, query validation, and estimation of bytes processed.
- Colab Enterprise Python notebooks. Notebooks provide one-click Python development runtimes, and built-in support for BigQuery DataFrames.
- Asset management and version history for code assets such as notebooks and saved queries, built on top of Dataform.
- Assistive code development in the SQL editor and in notebooks, built on top of Duet AI:
An AI collaborator integrated into BigQuery, Duet AI in BigQuery provides contextual code assistance for writing SQL and Python. It auto-suggests functions, code blocks, and fixes. With chat assistance, you can use natural language to get real-time guidance on performing specific tasks, reducing your need to search for documentation.
Buggy release: GA4 user data in the BigQuery export
For those of you who were desperately waiting to get their hands dirty with the new GA4 user data export tables, I have some fairly disappointing news. Numerous reports indicate that the daily export of (pseudonymous) users is incomplete and currently lacks actionable audience information. All in all a typical Google release. We'll continue to keep you updated on this topic.
Other BigQuery updates
- After you run a query in the query editor, in the
Charttab, you can see the visualization of your query results. You can zoom in or zoom out of the chart, download the chart as a PNG file, or toggle the legend visibility. In the Chart configuration pane, you can change the chart type (line or bar) and configure the measures and dimensions of the chart.
- Another AI feature: the
ML.GENERATE_TEXTfunction lets you perform natural language tasks on data that is stored in BigQuery. You can use it to rewrite or translate text, perform sentiment analysis, and so on (see tutorial).
Relevant blog posts and resources from the community
- Recreate page value in GA4 using BigQuery
- The trouble with users in GA4
- Instant BQML for GA4 properties (+ tutorial video)
- GA4 event and parameter cheat sheet
- GA4 BigQuery tips: guide to attribution
- Run Dataform queries for the latest GA4 daily table export
- Episode 153: Boosting GA4 with BigQuery (podcast with yours truly)
- How to show Google Analytics 4 transaction quantity with total revenue and transactions in a Looker Studio chart
- GA4 time travel - bridging UTC and local timezones
- Introduction to Natural Language Processing (NLP) and its business applications
- The traffic source challenge in GA4
- How to make sure your GA4 events tables do not expire
- Advanced time series forecasting using GA4 and BigQuery ML for ecommerce websites
- Custom GA4 channel groups with dbt
That's it for now. Thanks for reading and happy querying!