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
 7.3K All Categories
 13 Getting Started in the Community
 134 Beastmode & Analytics
 1.8K Data Platform & Data Science
 52 Domo Everywhere
 2K Charting
 994 Ideas Exchange
 894 Connectors
 236 Workbench
 342 APIs
 76 Apps
 18 Governance & Productivity
 233 Use Cases & Best Practices
 49 News
 473 Onboarding
 570 日本支部