Multiple Nested Case Statements Help

Reply
White Belt

Multiple Nested Case Statements Help

I'm trying to nest multiple conditional clauses together and having trouble. Based on the campaign_name, I want a specific calculation completed and value output of 'Good' or 'Bad.' 

 

This formula works properly when only specifying one campaign_name: 

CASE WHEN
SUM(case when `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then 1
else 0
end)
/
COUNT(`contact_id`)

<= 0.07

THEN 'Good'
ELSE 'Bad'
END

 

When I add a second conditional statement, it appears as if the second statement overwrites the first:

 

CASE WHEN

SUM(case when `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then 1
else 0
end)
/
COUNT(`contact_id`)

<= 0.07

THEN 'Good'

WHEN
SUM(case when `campaign_name` != 'VALUE1' AND `Abandon_Time` > 15 then 1
else 0
end)
/
COUNT(`contact_id`)

<= 0.05

THEN 'Good'
ELSE 'Bad'
END

 


Accepted Solutions
White Belt

Re: Multiple Nested Case Statements Help

Attached


All Replies
Brown Belt

Re: Multiple Nested Case Statements Help

I don't have time at the moment to create a test dataset but let me see if I can get you further along.

 

A full case statement is made up of

 

(CASE

WHEN X THEN Y

ELSE Z

END)

 

you have multiple END statements, which would end a case statemement, but you only have one actual "CASE" function listed. You might have to try to utilize additional case statements or remove the extra END statements.

White Belt

Re: Multiple Nested Case Statements Help

Thank you! I could be missing something but believe I have two additional CASE statements. I color-coded below:

 

CASE WHEN

SUM(case when `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then 1
else 0
end)
/
COUNT(`contact_id`)

<= 0.07

THEN 'Good'

WHEN
SUM(case when `campaign_name` != 'VALUE1' AND `Abandon_Time` > 15 then 1
else 0
end)
/
COUNT(`contact_id`)

<= 0.05

THEN 'Good'
ELSE 'Bad'
END

Black Belt

Re: Multiple Nested Case Statements Help

While looking at this case statement, I do have a few questions.  

 

Are you wanting to use the same denominator for each campaign?  It looks like you are counting the number of times there was an abandon time greater then 15 for campaign "VALUE1" and dividing it by the total count of contact_id's in your dataset.  In other words, lets say you have a total count of 100 contact ids.  Only 50 of which were related to the value1 campaign.  If 4 show an abandon time of >15 then would you expect your result to be 4/50 or .08 ('Bad') or 4/100 or .04 ('Good')?

 

I would also want to understand why you aren't using COUNT(DISTINCT `contact_id`)

 

 

 

Apart from that, I think that your issue with the original case statement has to do with the ELSE 0 parts of your nested case statements.  This means that both of your nested case statements are able to be evaluated regardless of campaign name.  

 

Maybe something like this would work?

CASE WHEN
(COUNT(CASE 
WHEN `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then `contact_id`
end)
/
COUNT(`contact_id`))
<= 0.07
THEN 'Good'

WHEN
(COUNT(CASE
WHEN `campaign_name` != 'VALUE1' AND `Abandon_Time` > 15 then `contact_id`
end)
/
COUNT(`contact_id`))
<= 0.05
THEN 'Good'
ELSE 'Bad'
END

 

I think a sample dataset would help if this doesn't work.

 

Thanks


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

Re: Multiple Nested Case Statements Help

@ST_-Superman-_  Thank you for the help!! You are correct in that I'm intending to only calculate against the contact ids related to the value1 campaign; so the result would be 4/50. And I didn't use count distinct because every contact id should already be discinct. 

 

I tried the formula you suggested, and the results appear to provide the same output as mine. For whatever reason, the result defaults to 'Good' no matter what value I set in the comparison values of 0.05/0.07. 

 

If I remove half of the statement and use just the below, it seemingly applies the calculation to the specified campaign. All other campaigns default to 'Good' though which may be part of the problem. 

 

CASE WHEN
(COUNT(CASE 
WHEN `campaign_name` = 'VALUE1' AND `Abandon_Time` > 15 then `contact_id`
end)
/
COUNT(`contact_id`))
<= 0.05

THEN 'Good'
ELSE 'Bad'
END

I added a sample sheet and changed 'VALUE1' to 'Customer1', 'Customer 2', and 'Customer 3'. 

Thanks again!

White Belt

Re: Multiple Nested Case Statements Help

Attached

Black Belt

Re: Multiple Nested Case Statements Help

Give this a shot:

case when 
	(COUNT(case when `Abandon_Time`>15 then `contact_id` end) / COUNT(`contact_id`))<.05 then 'Good'
    when
    (COUNT(case when `Abandon_Time`>15 then `contact_id` end) / COUNT(`contact_id`))<.07 and `campaign_name`='Customer1' then 'Good'
    else 'Bad'
    end

 

I used this for the "Abandon Rate"

(COUNT(case when `Abandon_Time`>15 then `contact_id` end) / COUNT(`contact_id`))

when you show this in a table and use the campaign_name field as a column, then it will separate out all of the data by campaign name for you.  Then I just used the performance calculation to classify each abandon rate as good or bad.1.png

 

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

Re: Multiple Nested Case Statements Help

Thanks again @ST_-Superman-_ . Judging by the formula working in the sample set provided, it would appear that my actual data set must be the problem. The same type of problem is still occurring - the first comparison clause is controlling 'Customer1' as well and modifying the clause that filters on campaign_name makes no change. I think I'll need to look at changing my data feed..

Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!