Creating a Beastmode that meets Revenue Requirements

EMart

Hi!

We're looking to build a beastmode that meets two requirements to then print a result. For example if a video generated more than $1K in total revenue and more than $100 during month-12, then "repost". See current beastmode below:

Background: The dataset tracks the lifespan of revenue per video title, in a 12-month period. Revenue is trended where if we posted a video in May 2022 and we're able to track how much that video generated the first month, including day 1 through day 30, the second month would include revenue from day 31 through day 60, etc.

Current beastmode:

CASE
WHEN
(MONTH(date) = MONTH(NOW()) AND YEAR(date) = YEAR(NOW()) AND sum(revenue) = 0) THEN 'Repost'
WHEN
(MONTH(date) = MONTH(NOW()) AND YEAR(date) = YEAR(NOW()) AND sum( revenue) < 100) THEN 'Takedown or Repost'
WHEN
(MONTH(date) = MONTH(NOW()) AND YEAR(date) = YEAR(NOW()) AND sum(revenue) < 100 AND sum(revenue) <1000) THEN 'Takedown'
ELSE 'Other'
END

Thank you!

GrantSmith

CASE
WHEN SUM(revenue) > 1000
  AND SUM(CASE WHEN (`date` - DAYOFMONTH(`date`) + 1) = STR_TO_DATE(CONCAT(YEAR(NOW())-1,'-',MONTH(NOW()),'-01', '%Y-%m-%d') THEN 
          `revenue` END)
WHEN . . .
END

You can try something like this to check if date is equal to 12 months ago and has 100 or more in revenue and the overall revenue is greater than 1000

GrantSmith

CASE
WHEN SUM(revenue) > 1000
  AND SUM(CASE WHEN (`date` - DAYOFMONTH(`date`) + 1) = STR_TO_DATE(CONCAT(YEAR(NOW())-1,'-',MONTH(NOW()),'-01', '%Y-%m-%d') THEN 
          `revenue` END)
WHEN . . .
END

You can try something like this to check if date is equal to 12 months ago and has 100 or more in revenue and the overall revenue is greater than 1000

EMart

Thank you @GrantSmith! I will actually be testing this beastmode today! :)

EMart

Hi @GrantSmith, inspired by the beastmode you shared, any feedback on my beastmode?

Basically trying to say when the overall general revenue is greater than 1000 and month 12 revenue is less than 100 then 'XYZ'

CASE

WHEN revenue> 1000
AND (DATEDIFF(date,published_date) >329 AND DATEDIFF(date,published_date) <=359) < 100 THEN 'XYZ'
ELSE 'other'

END