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

Calculated Field Conditional Count ignoring WHEN condition

[ Edited ]
Follower ✭ ☆ ☆
# 1
Follower ✭ ☆ ☆

Hi, 

 

I tried to create 3 calculated metrics in DataStudio to count likes, comments and shares from the Google Analytics "Social Actions" metric by checking the value of "Social Action" (the dimension) and summing up.

 

1) Count of Comments with following formula:

CASE WHEN Social Action = 'Comment' THEN 1 ELSE 0 END

Aggregation > SUM

 

2) Count of Likes with following formula:

CASE WHEN Social Action = 'Like' THEN 1 ELSE 0 END

Aggregation > SUM

 

3) Count of Shares with following formula:

CASE WHEN Social Action = 'Share' THEN 1 ELSE 0 END

Aggregation > SUM

 

Even though I believe SUM is the right aggregation, I actually tried both, with "COUNT" as an aggregating criterion as well, but in both cases the result is just 1 or 0 (basically telling me that there was at least 1 Social Action satisfying my formula).

 

Just as a helper, here is the screenshot of 2 tables. Above the one with GA out-of-the-box metrics, below the one with my calculated metrics. I would expect to see the same figures, but I don't. Is this a bug or am I doing something wrong?

 

2016-07-29 11_33_06-My First Report.png

 

Cheers and thanks
Andrea

 

 

IMPORTANT NOTE: the "best answer" that I marked is actually still a workaround, so for Data Studio this should still be an open limitation and frankly, from the number of threads that I have seen asking the same question, it should have a fairly high priority.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author rapowder
December 2016

Re: Calculated Field Conditional Count ignoring WHEN condition

Follower ✭ ✭ ✭
# 9
Follower ✭ ✭ ✭

I had this same problem with my social tracking (only I was use custom events vs. the native GA social tracking) and ended up having to create individual goals in the GA interface for each social action to track them individually as their own metric.

 

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.

 

For whatever reason, the data transformations as of now only go:

 

 Metric > New Dimension (e.g. CASE WHEN 'Social Actions' >0 THEN 'Sessions With Social Interaction'

vs.

Dimension > New Metric (e.g. CASE WHEN REGEXP_MATCH(Social Action, "Share") THEN sum(Social Actions)

 

The return is only capable of handling static values whereas we're asking it to return a sum of a metric field dependent upon the value of a dimension value.. The one thing (which blows my mind) it specifically rules out in the syntax reference. 

 

I'm hoping this is something that they fix soon considering this would be easy enough to do in a Google Sheet with a sumif() or query() to pivot function.

 

As far as a fix goes, if you use Google Tag Manager, it's really simple to solve. You just have to make an event (something like Category: Social, Action: Share), create a trigger to fire when 'socialAction' contains 'share', and then from there just set up a new goal in GA referencing the event you just made in GTM. 

 

If you don't have GTM, same process, just requires manual hardcoding of the event and trigger on the site.

 

This was the before: 

Screen Shot 2016-10-07 at 7.09.23 PM.png

 

 Vs. Now: 

 

Screen Shot 2016-10-07 at 7.02.16 PM.png

 

 

 

View solution in original post

Re: Calculated Field Conditional Count ignoring WHEN condition

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭
Hi Andreas,

You can use multiple "when condition" in one case. You can do use one formula for all social actions or to split it:

1st Approach:
sum (case when social action = 'comment' then 1 when social action = 'like' then 1 when social action ='share' then 1 End)

2nd Approach:
case condition for each social action. let's say: sum (case when social action = 'comment' then 1 else 0 End)

You can do same for all other social actions.

Let me know, if this works.

Thanks

Abhishek

Re: Calculated Field Conditional Count ignoring WHEN condition

Follower ✭ ☆ ☆
# 3
Follower ✭ ☆ ☆

Hi Abhishek,

 

actually adding the SUM() in the formula is the same as setting it in the Aggregation and gives the same result.

 

Here is if you specify it in the formula like you suggest (result is that in the aggregation column it shows "Auto" and you cannot change it)

 

sum-in-formula.png

 

Here is if you do it like I did, then you can chose how you want to aggregate the results (which is actually a bit cleaner because you can chose to count, countdistinct, average, etc.)

 

sum-in-aggregation.png

 

My guess is that it's some kind of limitation, but I am happy to hear about other ideas.

 

 

Re: Calculated Field Conditional Count ignoring WHEN condition

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭
Hello Andreas,

"actually adding the SUM() in the formula is the same as setting it in the Aggregation and gives the same result."
You are right but defining an aggregation itself in the formula is much cleaner. I preferred my own calculations anyway.

To be honest, count and count unique doesn't make any sense because if you use count unique, you will then get only 1 and with count: the total number of rows incl. 1 and 0's. So, to have a proper calculation I would rather go with sum. But if you say that it is kind of limitation then I don't know. I am also very new to Data Studio (from Last Monday, I started working on it Smiley Wink ) and just learning by doing.

I am not sure if you can share your dataset with me, if so then I might help you.

Maybe, Google guys can answer this better if there is any set of limitation or so.

Thank you

Best
Abhishek

Re: Calculated Field Conditional Count ignoring WHEN condition

Follower ✭ ☆ ☆
# 5
Follower ✭ ☆ ☆
Hi Abhishek,

you are right about the COUNT / COUNTUNIQUE. I actually tried it just to see what kind of result that was throwing.

I hope Google guys can answer, I have seen a similar use case about somebody that was trying to count events of a given category and it was also talking about some limitations.

Cheers

Re: Calculated Field Conditional Count ignoring WHEN condition

Follower ✭ ✭ ☆
# 6
Follower ✭ ✭ ☆
Hi Andreas - I am running into the same issue on a calculation for summing GA events, I can only get 1 as the result even though there are 1000's of events. Did you find a workaround?

Calculated Field Conditional Count ignoring WHEN condition

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hi Brendan , 

 

Could please elaborate more precisely your issue with screenshot? Maybe I can help with that.

 

Thanks

Abhishek

 

Re: Calculated Field Conditional Count ignoring WHEN condition

Follower ✭ ✭ ☆
# 8
Follower ✭ ✭ ☆

Hi Abhisek, 

When I run the attached calculation, it always returns '1' same as what Andreas experienced. I was hoping it would return a count of how many GA events occurred with action "ViewerStartSuccess". Ga is reporting many 1000's of events. 

I am not able to change aggregation from Auto. 

Thanks!

 

Calculation - google data studio.png

Marked as Best Answer.
Solution
Accepted by topic author rapowder
December 2016

Re: Calculated Field Conditional Count ignoring WHEN condition

Follower ✭ ✭ ✭
# 9
Follower ✭ ✭ ✭

I had this same problem with my social tracking (only I was use custom events vs. the native GA social tracking) and ended up having to create individual goals in the GA interface for each social action to track them individually as their own metric.

 

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.

 

For whatever reason, the data transformations as of now only go:

 

 Metric > New Dimension (e.g. CASE WHEN 'Social Actions' >0 THEN 'Sessions With Social Interaction'

vs.

Dimension > New Metric (e.g. CASE WHEN REGEXP_MATCH(Social Action, "Share") THEN sum(Social Actions)

 

The return is only capable of handling static values whereas we're asking it to return a sum of a metric field dependent upon the value of a dimension value.. The one thing (which blows my mind) it specifically rules out in the syntax reference. 

 

I'm hoping this is something that they fix soon considering this would be easy enough to do in a Google Sheet with a sumif() or query() to pivot function.

 

As far as a fix goes, if you use Google Tag Manager, it's really simple to solve. You just have to make an event (something like Category: Social, Action: Share), create a trigger to fire when 'socialAction' contains 'share', and then from there just set up a new goal in GA referencing the event you just made in GTM. 

 

If you don't have GTM, same process, just requires manual hardcoding of the event and trigger on the site.

 

This was the before: 

Screen Shot 2016-10-07 at 7.09.23 PM.png

 

 Vs. Now: 

 

Screen Shot 2016-10-07 at 7.02.16 PM.png

 

 

 

Calculated Field Conditional Count ignoring WHEN condition

Visitor ✭ ✭ ✭
# 10
Visitor ✭ ✭ ✭

Hi Brendan, as Lindsey said (below) the only workaround is to either create custom metrics (1 for each action that you want to track) and fire them along with your event hit (in GTM or your code), or create goals attached to your events (but there you don't have real numbers because if somebody fires multiple times the same event in the same session it will only count as 1 goal completion).

 

As for the aggregation => you have specified the aggregation in your formula (SUM), so only you don't need to specify it in the aggregation column. Basically it would be the same as removing the SUM from the formula and setting it in the aggregation column.