Multiple Case Statements
Hi Everyone,
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))
/
4)
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?
thanks!
Comments

Build a separate table, a date dimension, that has one row for every day since the dawn of time and then explicitly label the month and week number based on your requirements. Then JOIN that date table into all your dashboard datasets. It'll be MUCH easier than this CASE statement nightmare you're building for yourself!
What you're describing is not unique to Media. It's conceptually similar to a 445 calendar or a Retail calendar. There are many well documented blog posts about building a 445 calendar.
https://calogica.com/sql/dbt/2018/11/15/retailcalendarinsql.html
This article describes the problem and the sql solution reasonably well, you'll just have to refactor it for mySQL.
... i know it's sql intensive, but this will be much more supportable long term.
.... BUSINESS ANALYST QUESTIONS ...
are you trying to calculate TotalCost / TotalLeads?
or are you trying to calculate ( avgCPL month 1 + avgCPL month 2 + avgCPL month 3 ... ) / 12
I don't know if it's appropriate to take the average of an average... but if you're interested in the first one then you can ignore the convolution of weeks per month.
if you want to do avg of avg cpl by month, then you do have to consider the month the activity occurred in, but it doesn't sound like it should be impacted by number of weeks per month.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
 11.3K All Categories
 6 Private Company Board
 2 APAC User Group
 12 Welcome
 45 Domo News
 10.2K Using Domo
 2K Dataflows
 2.6K Card Building
 2.3K Ideas Exchange
 1.3K Connectors
 354 Workbench
 271 Domo Best Practices
 13 Domo Certification
 482 Domo Developer
 58 Domo Everywhere
 113 Apps
 734 New to Domo
 86 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 31 お知らせ
 65 Kowaza
 305 仲間に相談
 667 ひらめき共有