Better late than never: after a very long wait, Google finally reworked the parameters functionality in Data Studio!
Up until not long ago, setting parameters on a report was cumbersome and had very few use cases: we could only add parameters using custom queries, and to change their values, the user would have to either rely on a report editor, or access the report through a different URL. Neither of those made for a very user friendly experience.
Finally, on August 20, Google overhauled the entire feature, adding more flexibility and functionality. Now, several of the limitations we found when building reports can now be better managed with parameters and a little cleverness in setting them up.
There’s a lot we can do with them, and here’s just a few of the most common use cases:
- Dynamically setting a dimension or metric for a graph;
- Changing colors for metrics below or above a certain threshold;
- Using parameters in a calculation;
- And a lot more!
Despite their power, it really pays to be thoughtful when setting up any extra parameters and giving the users added control and flexibility. Any added element, especially one that is as customizable as custom parameters, increase the complexity and make for a steeper learning curve in dashboards.
When using parameters in calculations or as free text fields, we should be even more careful to ensure that all users are seeing accurate data.
As the list above shows, there are several use cases for this overhauled feature, and we can’t go into each of them in detail in this article. Instead, we’ll focus on the first one: dynamically setting dimensions and metrics.
Setup with Custom Queries
The most straightforward way to set custom parameters is with a custom query pulling data directly from a BigQuery table or view.
Let’s assume that we want to use a single time series chart to display user data from Google Analytics. There are three important metrics our client wants to track: the absolute numbers of users, compared to new users or engaged users on a daily basis.
Our custom query would look something like this:
SELECT date, users, CASE WHEN @user_type = ‘New Users’ THEN new_users WHEN @user_type = ‘Engaged Users’ THEN engaged_users END AS user_type FROM `pp-examples.datamart.users`
When adding the custom query as a data source, we simply click on “Add Parameter” and most of the fields should be populated already. We only need to set the input type, display name, and the possible values.
From then, it’s simple to create a drop-down list and use the parameter as the filter dimension, just as if we were setting up filters. Now anyone visiting the dashboard can compare these metrics.
But what if we don’t want to use a custom query? We can also save some money by using a data source that’s already included in the dashboard and setting a custom parameter.
Parameters can be added to already existing data sources similarly to adding a field. We can set their IDs, display names, specify a data type and the possible values. The interface looks a little different from adding them through custom queries, but everything is fairly self explanatory.
With our parameter now in place, it would be natural to mimic the query in a custom field, after all, Data Studio also supports CASE WHEN statements. Sadly, that’s not supported.
But there is a workaround by using nested REGEX_REPLACEs. Here, our custom field would look something like this:
CAST( REGEXP_REPLACE( REGEXP_REPLACE( CONCAT( user_type, ";", new_users, ";", engaged_users ), "^(New Users);(.*);(.*)", "\\2" ), "^(Engaged Users);(.*);(.*)", "\\3" ) AS NUMBER )
This looks confusing at first, but is in itself quite simple: the first field in our concatenation is the user_type parameter. Its value will either be “New Users” or “Engaged Users”. The second and third fields are the respective numbers of users.
Now, when our parameter takes the value “New Users”, the REGEX will replace everything with the value in the second match in the parenthesis, that is, the number of “New Users”. The same applies for “Engaged Users”.
Finally, we cast the result as a NUMBER data type, as we want to use this field as a metric, and Data Studio would not allow operations otherwise.
Either method used would give us the same result, and our dashboard users can now enjoy increased flexibility. Here is an example:
Though there would have been a way to solve similar problems before this updated feature, they would have relied on reshaping our tables or views. The main advantage of using parameters is that we don’t need to transform the data, we’re dynamically using different columns in our calculations and graphs. On top of that, we don’t need to know any SQL, reducing the technical barrier to entry.
If reading this post gave you a few ideas for a dashboard but you’re not sure how to set it up, don’t hesitate to contact us, we’d love to help you out!