AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
2.7K members online now
2.7K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Problem with CASE statement

Follower ✭ ✭ ✭
# 1
Follower ✭ ✭ ✭

I am experiencing a problem with the CASE statment in the Data Studio function.

 

Background: I have data from a number of countries, which are reported with the dimensions "ga:country" and "ga:continent"

 

I need to handle Japan individually as a country, while handling all other countries as only belonging to a certain continent only. To be able to chart or tabulate that, I have created a function "

CASE WHEN ga:country  = 'Japan' THEN ga:country ELSE ga:continent END

This computed field parses fine, but when I try to use it, I get either "Japan" or "     " (empty string) as a result. it appears that ga:continent used in this context simply evaluates to "all countries except Japan" (that's what the figures show at least), but I expected the case of "not japan" to be treated exactly as if I had only used "ga:continent" in the first place.

 

It might be the case that this is a limitation of how the "CASE function is implemented, but from a usage point of view it is an unnatural limitation which makes it impossible to handle the data in a useful manner. Please improve.

Problem with CASE statement

[ Edited ]
Follower ✭ ✭ ✭
# 2
Follower ✭ ✭ ✭

Hi Kjell,

 

You're exactly right about it being a limitation of the CASE function. The root of the problem is the the CASE function can only return boolean, text, or number values -- not metric fields like we're asking it to do here. (See "Notes" section at the very bottom of the article)

 

I hope they fix this soon because this would be easy enough to do with even the query() function in Sheets, but in the meantime, one manual work around I think would work:

 

1) Manually pull a custom report in analytics like so: 

Primary dimension: Continent

Secondary dimension: Country Code

Metrics: {{metrics you need in report}}

Advanced Filter: Exclude > Country Code > {{Japan's country code}} 

 

2) Export to Google Sheet (All results will be on Sheet1 by default)

 

3) Create 2nd tab (Sheet2) and import all unique continents from Column A of Sheet1 into Column A of Sheet2 via UNIQUE('Sheet1!A:A)

 

4) In Column B of Sheet 2, write the formula join("|",filter('Sheet1!B2:B{eod},'Sheet1!A2:A{eod}=A2)). This will result in something like this: 

Sheet2, A2: North America / Sheet2, B2: 23423|235235|235235

 

5) Create your new case function with all of the data from Sheet2

 

CASE WHEN REGEXP_MATCH(Country ISO Code, "23423|235235|235235") THEN "North America".....[repeat for every continent].... WHEN REGEXP_MATCH(Country ISO Code, "{Japan's Code}") THEN "Japan" ELSE "Uncategorized" END. 

 

6) If new countries come in as 'Uncategorized' you can always find out their names / continents and add them to existing CASE function

 

 

Hope this helps!!