Creating a Beastmode that meets Revenue Requirements

EMart
EMart Member
in Beast Mode

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!

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 
    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • EMart
    EMart Member

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

  • EMart
    EMart Member

    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

Categories