How to combine string fields in Google Data Studio
Have you ever needed to combine string fields in Google Data Studio? I tried to do it the other day and it was more difficult than expected.
In this post I'll show a method that solves how to combine different string fields into one field. This method is from Harry Bryars whom you can follow on Twitter or Medium. He was kind enough to show me this method!
Looking at the hypothetical Data Set
So in this hypothetical data set we have a list of tourist names in Column A and then the country they visited in each year, listed as five seperate columns. You'll notice that many cells are blank as some tourists did not travel to countries in some of the years. or in some cases no countries.
The First attempt at Combining the strings
So the first attempt at combining the strings seems like an obvious solution.
Use the CONCAT function to combine all the strings together, as shown below.
Here is the actual CONACT formula if you want to try it yourself.
CONCAT(Country 2015,", ", Country 2016,", ", Country 2017,", ", Country 2018,", ", Country 2019)And below is what the formula displays in our Data Studio report. You'll notice that if all the fields (columns) contain a country then the formula works as intended and combines the countries into a list.
However if one of the columns has no county listed and is null then the formula doesn't work.
Second attempt at combining the strings
This is the second version for the formula from Harry Bryars that worked!
So it uses multiple CASE statements to insert a "_" when the column value is null, and otherwise lists the country.
Below is how the formula show look.
Here is the actual formula if you'd like to try replicate this with your own data source.
CONCAT
(CASE WHEN Country 2015 IS NULL THEN "_" ELSE Country 2015 END,", ",
CASE WHEN Country 2016 IS NULL THEN "_" ELSE Country 2016 END,", ",
CASE WHEN Country 2017 IS NULL THEN "_" ELSE Country 2017 END,", ",
CASE WHEN Country 2018 IS NULL THEN "_" ELSE Country 2018 END,", ",
CASE WHEN Country 2019 IS NULL THEN "_" ELSE Country 2019 END,", "
)Below in the final column you can see that we have the formula working close to how we would like it to.
Below is the actual Google Data Studio report if you'd like to take a look.
My Other Blog posts
If you enjoyed this post then you might like my other blog posts.
How to map Growth in Google Analytics sessions by Country in Data Studio
Google Data Studio has always had the ability to add in a comparison column to tables, but not to map out a change in values on a map. Ideally I would want to be able to see which countries have seen the highest rates of growth over a period of time. This is a method that worked for me and hopefully it will be useful for you too!
How to use DATE_DIFF in Google Data Studio to work out Sessions per Day
So one thing I've wanted to do for a while is to determine how many sessions each blog post has had since they were published. Since the URLs of my blog posts contain the date they were published, I tried to work this out using regex and some other methods. It got a bit messy and wasn't very accurate.
Change Bubble Size and Colour in a Google Data Studio Map using Parameters
In this post I'll show how you can change Bubble Size and Colour in a Google Map in a Data Studio report. This method using two parameters to let you switch between which metrics are displayed on the map.
Google Data Studio Template: Google Ads (Blue World Report)
I wanted to share a Google Data Studio report template for Google Ads data. This template is available for download if you think it'll be useful for you.
Google Data Studio Template: Search Console Countries and Queries
So in this post I just wanted to share a Google Data Studio report template that lets you look at the connections between countries and search queries.
Make a Long-term Growth Dashboard for Google Analytics Data using Data Studio
In this post I'll show you how to make a Growth Dashboard for Google Analytics using Google Data Studio.
Comparing COVID-19 cases in the United States and Europe
In this dashboard I wanted to show the differences between the number of COVID-19 cases in the United States and Europe. Thanks to Johnny Miller from Unequal Scenes for the suggestion to take a look at the data.
How to make an Interactive List of People in Google Data Studio
So in this post I'll show you how to make what I've called an Interactive List of People in Google Data Studio.
Use a Constant Value to make a Goal dashboard in Google Data Studio
So in this post I'll show you how to create a Constant Value in Google Data Studio, and give you a practical example of how you can use a constant value to create a Goal dashboard.