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

Nested If Statements --> CASE function?

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello!

 

I'm looking to pull an excel formula into Google Data Studio (ideally it'd be into Analytics but I believe that's impossible since the Calculated Metrics seem extremely limited in what can be handled). 

 

I'd like to, by campaign, ad group, etc., assign a # of points to CPL (a calculated metric in GA) based on how close it is to our target, a # of points to ROAS based on how close it is to our target, and add the two up. 

 

When the data is exported and CPL exists in  cell M3, this is what the CPL Points formula looks like in Excel (similar formula for ROAS):

 

=if(M3>=44.85,0,if(and(M3>=42.55,M3<=44.84),1,if(and(M3>=40.25,M3<=42.54),2,if(and(M3>=37.95,M3<=40.24),3,if(and(M3>=35.65,M3<=37.94),4,if(and(M3>=33.35,M3<=35.64),5,if(and(M3>=31.05,M3<=33.34),6,if(and(M3>=28.75,M3<=31.04),7,if(and(M3>=26.45,M3<=28.74),8,if(and(M3>=24.15,M3<=26.44),9,if(and(M3>=21.85,M3<=24.14),10,if(and(M3>=19.55,M3<=21.84),11,if(and(M3>=17.25,M3<=19.54),12,if(and(M3>=14.95,M3<=17.24),13,if(and(M3>=12.65,M3<=14.94),14,if(and(M3>=0.01,M3<=12.64),15,if(M3=0,0)))))))))))))))))

  

I thought I could handle this using a Custom Metric in the Data Studio using the CASE function; however, it appears as though the CASE function is based on text within a dimension, not another metric. Is there a better or other way to do this?

 

Nested If Statements --&gt; CASE function?

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

Hi Lisa! 

Have you found a solution to your problem? If so, do you mind sharing the solution?
(I'm currently facing the same problem)

Thanks! 
Chin

Nested If Statements --&gt; CASE function?

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

NARY_MAX(NARY_MIN(CEIL(15+(-M3+12,65)/2,3), 15),0) 

Nested If Statements --&gt; CASE function?

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

No, I didn't!