Grouping data is a great tool in Google Data Studio. The biggest advantages of grouping data? Firstly the most obvious one: it creates valuable new insights (a few examples will follow below). Secondly it can give you cleaner data. Think about for example Facebook data in Google Analytics. In the source / medium reports you can see the following sources:
- m.facebook.com / referral
- facebook.com / referral
- l.facebook.com / referral
- UTM tags with facebook
Would’t it be nice to group these sources into one umbrella category? Google Data Studio can transform all the different Facebook sources into one bigger source. We will show you how to do this in the next example.
Grouping data by using the CASE WHEN statement
Google Data Studio users can group data with the “CASE WHEN” statement. The following formula needs to be used:
CASE WHEN REGEXP_MATCH(dimension x, “matching text“) THEN “preferred text” ELSE “other rows” END
The CASE WHEN formula basically works like this:
When “dimension x” matches the “matching text” then transform the text to “preferred text”. Transform all other texts to “other rows”. Examples are given below.
The CASE WHEN statement: Facebook example
As mentioned earlier in this blog it’s possible to group multiple Facebook sources. We’re using the above CASE WHEN statement to make this possible.
When we parse the formula it says this: when Source/medium matches anything with “facebook” then transform the “Source/medium” to “Facebook”. The .* is an important piece of text in the formula, these symbols are Regex codes. The .* combo is used to match any charcacter before or after the text.
Do you need to keep the rest of the sources in the table? Then use the dimension after “ELSE”.
It’s also possible to group different different texts into one new category. In the next example we’re grouping different football products into one football category.
Another great example with pet products. In this example we’re grouping the “Dog products”, the other products we’re going to call “Other products”. It’s possible to remove the “Other products” from the table with a Google Data Studio filter.
This can be done like this: Exclude -> Dog category example -> Equal to (=) -> Other products.