Multiple Case Statements
I am having a bit of trouble stringing together multiple case statements...
The end goal of my attempted beast mode is to calculate Average Weekly Cost Per Lead (CPL is Cost divided by # Leads). The wrench in this whole scenario is that I have to do this based off the broadcast calendar. Broadcast Calendar is just a standardized calendar utilized in media buying/advertising world. Depending on the year (I am doing this for 2019 and 2020), each month will either have 4 or 5 weeks. I hope this makes sense
Below is the beast mode I built. Here is some context on what some of this stuff means
- Media Month is just the numerical value for each month (eg: 1 is January, 2 is Feb, 3 is March and so on).
- Media Year is the year
- Metric is just which metric I am telling the beast mode to get data from.
((sum(case when `MediaMonth` in ('1','2','4','6','7','9','10','11','12') and `MediaYear` = '2020' and `Metric` = 'Net Cost' then `Value` end)
/ sum(Case when `MediaMonth` in ('1','2','4','6','7','9','10','11','12') and `MediaYear` = '2020' and `Level` = 3 and `Metric` = 'Leads' then `Value` end))
I was only successful in building this for the year 2020 for the months that only have 4 weeks in it. In the beast mode I have above, it's basically saying if the media month criteria is met for all the dimensions, then divide by 4 (those months have 4 weeks). This will get me my average weekly cost per lead. What I am missing are statements that will:
- Account for the months that have 5 weeks in 2020 (Months 3,5,8)
- Account for the same criteria (months with 4 and 5 weeks) for 2019
Anyone have any insight?