In this blog post, I want to summarize the new releases from the Google tools, that we use daily in datadice. Therefore I want to give an overview of the new features of BigQuery, Looker Studio, Google Analytics and Google Tag Manager. Furthermore, I will focus on the releases that I consider to be the most important ones and I will also name some other changes that were made.
If you want to take a closer look, here you can find the Release Notes from BigQuery, Looker Studio, Google Analytics & Google Tag Manager.
BigQuery
Better limits for connected sheets
It is possible to connect a BigQuery table with a Google Sheet, which is called “Connected Sheets”. This connection has limits that got an update now:
- Pivot table limit from 30.000 to 50.000 rows
- Data extracts limit from 25.000 to 50.000 rows
To start you go to the desired table in BigQuery and click on Export > Explore with Sheets.
A new Google Sheet opens with all the data of the table (when the data is not too big). From this GSheet table, you can extract data or create a pivot table with the mentioned new limits.
New array and struct operator
BigQuery can handle array and struct datatypes in the stored tables.
Arrays:
Before it was just possible to access an item in an array with OFFSET and ORDINAL. Now it is possible to just use the index, which is the same as using OFFSET.
An easy example:
planet_array = [‘Mercury’, ‘Venus’, ‘Earth’, ‘Mars’]
planet_array[ORDINAL(1)] -> ‘Mercury’
planet_array[OFFSET(1)] -> ‘Venus’
planet_array[1] -> ‘Venus’
The big problem is, when you use in our example planet_array[7], we access an item outside of the array, BigQuery throws an error and the entire query fails. To bypass this you can use SAFE_OFFSET or SAFE_ORDINAL, then no error is thrown and the corresponding value is NULL. So far I would still highly recommend using either of the two SAFE functions.
Struct:
It is the same as for arrays. You can access a struct element just by the index which is the same as using OFFSET.
An easy example:
planet_struct = (‘Mercury’, 1, 4879.4)
planet_struct[ORDINAL(1)] -> ‘Mercury’
planet_struct[OFFSET(1)] -> 1
planet_struct[1] -> 1
Dataform
Small improvements
Dataform has some new features, especially on the security side:
At first, you can restrict remote access to repositories on organizational level. On the project level, it is possible as well. Further information can be found here.
Second, you can delegate service accounts for single workflow configurations. When there is no service account attached, it takes the service account assigned to Dataform/repository. In the Workflow Configurations section in Dataform, you need to click on the three dots of the selected workflow to change the assigned Service Account.
Looker Studio
Pro: See the underlying data of a chart
A new feature for the pro version allows you to take a deeper look into the data of a chart or table.
In the edit mode, you can right-click on a chart and select “Show underlying data”. Afterwards, you get an overview of all values of the dimensions and metrics used in the chart.
The visualization depends on whether a breakdown dimension was used or not:
Without a breakdown dimension: data is shown in a table
With a breakdown dimension: data is shown in a pivot table
Google Analytics
New Checkout Journey report
In your GA4 reports, under Monetisation > Checkout Journey is a new report available. It has the same structure as the “User Purchase Journey” report we covered in this blog post.
The focus of the “User Purchase Journey” report is the complete User Journey, from the beginning of a session to the purchase.
The “Checkout Journey” report starts with the beginning of the checkout till the purchase. Based on your defined checkout funnel you see how many users leave the checkout in certain steps.
GA4 recognizes the checkout steps when the GTM and the Data Layer are correctly implemented. Here you can find, how to structure the checkout events in the Data Layer.
New BigQuery export
There is a new export available to send data from your GA4 property to BigQuery. The new export just needs to be activated, when you have already a working event data export.
This data sends additional meta information from the users on your website. The data is especially interesting when you track additional user information on the event level.
In your GTM GA4 event tags you can add user information:
And this information is not part of the usual BigQuery event export. These values can be found in the new user export.
Google Tag Manager
The new Google Tag
Google changed the GA4 Configuration tag. The migration to the new tag did Google itself. Which means you still have a working tracking setup.
Next to the name, there are two minor changes to the new tag type.
In the configuration settings section, you can add certain meta information for your tracking. You have two possibilities to add this information:
- With the new Configuration settings variable you can add key-value pairs that you can reuse in every tag you connect with the variable
- In the tag itself you can you can also add the key-value pairs (values in the tag will override settings made in the connected variable)
A list of all predefined fields you can find here.
Google Cloud Retail Tag
There is a new tag available to get e.g. user data for your Google Cloud Retail.
To set it up, you need an API key, which you can create in your Google Cloud. A guide on how to create the key can be found here.
Then you see the section “User Events Data”. Under “User Events Data Source” you can select Data Layer to get the necessary information from there. This works when the Data Layer is structured for ecommerce tracking for GA4. Another choice is to select certain predefined variables to get this data.
After that, you can set further parameters to overwrite the default settings. An example would be the visitorID.
Further information on how to set up the tag and how the user data transformation works can be found here.
Further Links
This post is part of the Google Data Analytics series from datadice and explains to you every month the newest features in BigQuery, Data Studio, Google Analytics and Google Tag Manager.
Follow us on LinkedIn for insights into our daily work and important updates on BigQuery, Data Studio, and marketing analytics.
Subscribe to our YouTube channel for discussions on DWH, BigQuery, Looker Studio, and Google Tag Manager.
If you are interested in learning BigQuery from scratch, get access to our free BigQuery Course
Elevate your skills with Google Data Studio and BigQuery by enrolling in our Udemy course.
Need help setting up a modern, cost-efficient data warehouse or analytical dashboard? Email us at hello@datadice.io to schedule a call.
