Multiple Case Statements

user32470

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!

jaeW_at_Onyx

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 4-4-5 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/retail-calendar-in-sql.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.