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
New grouping functions
Grouping is such an essential and old mechanism in SQL, that it is always a surprise, that even for this topic, there can be improvements.
3 new functionalities are available:
- GROUP BY GROUPING SETS
- GROUP BY CUBE
- GROUP BY ROLLUP
And there is the GROUPING operator to show if the specified value is grouped.
This is our reference table:
GROUP BY GROUPING SETS:
Query:
SELECT
person_first_name,
shop,
SUM(revenue) AS sum_revenue,
GROUPING(person_first_name) person_first_name_is_grouped,
GROUPING(shop) AS shop_is_grouped
FROM orders
GROUP BY GROUPING SETS (person_first_name, shop)
ORDER BY person_first_name, shop
Result table:
Behaviour: It groups all the single combinations together. Either person_first_name IS NULL or shop IS NULL and it sums the revenue per single entry. E.g. The Online Shop gets the revenue from Michael, Nina, and George (50 + 100 + 60 = 210)
GROUP BY CUBE:
Query:
SELECT
person_first_name,
shop,
SUM(revenue) AS sum_revenue,
GROUPING(person_first_name) person_first_name_is_grouped,
GROUPING(shop) AS shop_is_grouped
FROM orders
GROUP BY CUBE (person_first_name, shop)
ORDER BY person_first_name, shop
Result table:
Behaviour: It groups every combination. A possible value for every cell is the value itself and NULL and then it groups the combinations.
E.g. In the line where product_first_name and shop is NULL, all lines will be counted for sum_revenue.
GROUP BY ROLLUP:
Query:
SELECT
person_first_name,
shop,
SUM(revenue) AS sum_revenue,
GROUPING(person_first_name) person_first_name_is_grouped,
GROUPING(shop) AS shop_is_grouped
FROM orders
GROUP BY ROLLUP (person_first_name, shop)
ORDER BY person_first_name, shop
Result table:
Behaviour: It groups every combination for the first set (person_first_name). A possible value for every cell is the value itself and NULL. Then it groups the combinations and sums up the revenue.
The difference to GROUP BY CUBE is, that you do not see the sum_revenue of just Local and Online Shop
These new features can be super helpful for some special use cases, but you need to get a feeling for it, especially with a bigger set of values it gets more and more complicated. The documentation you can find here.
Improved data masking
If there are security concerns for some people for certain values inside a table you can add rules on how to “mask” the data. An overview can be found here. Now there are some more functionalities available, on how to mask the data.
Here is a list of functions you can use:
- String functions: REGEXP_REPLACE, CONCAT
- Hash functions: FARM_FINGERPRINT, MD5, SHA1, SHA256, SHA512
- Conversion functions: CAST
With these functions, you can create your own data masking routine. There is a guide with further information.
Apache Hive and Snowflake migration
Google has a migration guide for some tools. If you want to change from another service to BigQuery, you can take these tips.
There are additional guides available for Apache Hive and Snowflake now. You can find them here. This means, that if you consider switching your DWH setup from one of these tools to BigQuery, you have a lot of information available there.
Dataform
File Search
A small but handy improvement.
There is an input field in the workspace where you can search for files. After adding the search string, you get a list of all found files or directories and you can select them by clicking enter.
Workforce identity federation
You can now use the service of an external identity provider to authenticate the users to access the Dataform repository and workspaces
Further information can be found here.
Looker Studio
Upgrade to Looker Studio Pro
There is now an easy way to update to Looker Studio Pro. You can click on the Homepage on the settings button.
Then under “Pro subscription” you can enter the necessary information to get the pro version.
Important: You have to choose a GCP Project to handle the payment process. The GCP project will make the payment.
Table Drilling and Tooltips (No Pro)
This is a useful feature to add more user interaction to your dashboard.
It was already possible to add drill-down levels to your dashboard, the new feature works a bit differently but has the same kind of idea.
Now you can add new drill-down features to every dimension of a chart with some tooltips and add links with further information to the data.
You start with it by selecting a dimension of a chart, clicking in the setup tab on the pen symbol of the dimension, and choosing “add drill action”.
Then you see the following menu on the right side:
This menu contains two different parts:
- Drill Down: You can add fields to drill down for the selected dimension
- Drill Through Links: Adding static links to give the user more context to the data
After I added some fields and a link to the chart dimension, you see the following when you hover over the dimension in the chart.
And when you click on the Drill actions field, this is coming:
Here you can do the following:
- Filter for the selected value (in this case: Hoodie)
- Drill down to one of the other dimensions (product_sku or product_name)
- Click on links in the list
Due to this new feature, the user can change every dimension in the chart for different drill-down levels and can get more information about the data.
Dimension control (No Pro)
This is a new control for the dashboard users to change the primary dimensions of the connected charts.
At first, you have to add the new control to the dashboard:
Then you can select between which Dimensions the dashboard user can choose:
Then the user can select which dimensions they want for the charts on that page. For sure you can also group charts and the control together as usual.
It is a nice new feature, with two small problems, which will be fixed at some time hopefully
- The name of the changed dimension in the charts and control is the default column name and can not be changed
- In a table with multiple columns, you can just change the first column with the control
In combination with the new drill-down feature, the viewer can get a big impact on the dimensions shown in the dashboard.
Auto Refresh (Pro)
There was always a problem with the update of the data in the dashboard when you showed it on a monitor permanently without any interaction. It was just possible to update the data on the dashboard with a browser extension.
Now you can set an automatic refresh in the dashboard settings. The quickest refresh is every 5 minutes, which could be for some use cases still too slow.
Important: This is just updating the data in the charts, NOT the data in the data sources. If the dashboard update is quicker than the data source update, it shows sometimes cached data.
Google Analytics
Item-scoped parameters in BQ export
For your GA4 property, you can add custom parameters on the item level as well. There was one problem, in the BigQuery export were these values not available.
This is fixed now! Per Item, you get these parameters as well. Be careful with your query then, because you have to unnest the items record and then the parameters record per item again!
Calculated metrics
In GA4 you can create custom dimensions and custom metrics. Custom metrics you can use for new aggregated KPIs for products, users, events, …
Now it is possible to create calculated metrics. With calculated metrics, you can combine different metrics into a new one. You can use predefined metrics and custom metrics for the calculation. You can find this setting in the admin section of your property under “Custom definitions” > Tab “Calculated metrics” > Button “Create calculated metrics”.
Deleting attribution models
Google announced in April this year that some attribution models will be deleted. You can find further information here. This deletion happened now and the models:
- First-click
- Linear
- Time decay
- Position-based
are not available anymore or will be deleted in the upcoming days. Just the two models last-click and the data-driven model remains.
Google Tag Manager
Export debugging sessions
If you need support by debugging your tag-firing behavior in GTM, you can now export a GTM preview session to send it to another person.
To export a session
- start a debug session
- perform the needed steps on the website
- click in the debug window on the three dots menu and on “Export session” and you get a JSON with the debug information on your local machine
To import a session
- you go to the Tag Assistant
- Click on the three dots menu and “Import session”
- Then you have a read-only view of the debug session
In the import debug session you can click on every event and check the tags, variables, Data Layer, consent, and error situation.
Proxy server routing
There is the possibility of routing your server-side traffic through a proxy server now. You just need to add a new environment variable to the server. Implementation guidelines can be found here.
Important: It just works when you deploy the server on Cloud Run, not on App Engine!
Upcoming datadice blog posts for this month
- Waterfall Charts in Looker Studio - Coming Soon
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.
Check out our LinkedIn account, to get insights into our daily working life and get important updates about BigQuery, Data Studio and marketing analytics
We also started with our own YouTube channel. We talk about important DWH, BigQuery, Data Studio and many more topics. Check out the channel here.
If you want to learn more about how to use Google Data Studio and take it to the next level in combination with BigQuery, check our Udemy course here.
If you are looking for help to set up a modern and cost-efficient data warehouse or analytical dashboards, send us an email to hello@datadice.io and we will schedule a call.
.jpeg)
