Grouping data is an excellent tool in Google Data Studio. What are the primary benefits of grouping data? First and foremost, it generates valuable new insights (a few examples will follow below). Second, it can provide you with cleaner data. Consider the Facebook data in Google Analytics. The following sources can be found in the source/medium reports:
- m.facebook.com / referral
- facebook.com / referral
- l.facebook.com / referral
- UTM tags with Facebook, and so on.
Wouldn’t it be nice to group these sources under a single heading? Google Data Studio can combine all of the different Facebook sources into a single larger source. In the following example, we will demonstrate how to accomplish this.
Using the CASE WHEN statement to group data
Users of Google Data Studio can group data using the “CASE WHEN” statement. The following formula should be used:
If REGEXP MATCH(dimension x, “matching text”) THEN “preferred text” ELSE “other rows.”
The CASE WHEN formula works as follows:
When “dimension x” matches the “matching text,” the text is transformed to “preferred text.” Change all other texts to “other rows.” Examples are provided below.
Example of a CASE WHEN statement: Facebook
It is possible to group multiple Facebook sources. To accomplish this, we use the preceding CASE WHEN statement.
When we parse the formula, we get the following: when Source/medium matches anything with “Facebook,” transform the “Source/medium” to “Facebook.”
The.* symbol is an integral part of the formula; these symbols are Regex codes. The.* combination is used to match any character that comes before or after the text.
Is it necessary to keep the remaining sources in the table? The dimension after “ELSE” is then used.
It is also possible to combine several different texts into a new category. In the following example, we’re grouping various football products into a single category.
Another excellent example is pet products. In this example, we’ll call the “Dog products” and the other products “Other products.”
A Google Data Studio filter can remove the “Other products” from the table. This can be accomplished as follows: Exclude -> Example of a dog category -> Equal to (=) -> Other products.
