Beast Mode for "Views in the first 30 days"

EMart

Hi there,

My team wants to create a beast mode that looks at total views in the first 30 days per asset since the video was published. For example, a video was published on March 1, 2024 and we want to know how many total views did it generate through March 30, 2024. We created a couple of sample beast modes that are coming through as valid but are not providing accurate data. Can someone take a look at these two beast modes below and potentially suggest some workarounds? Thank you in advance!

BM1:

SUM(
CASE
WHEN (date <= ADDDATE(published_date,interval 30 day) AND date >= published_date) AND DATEDIFF(CURRENT_DATE(),published_date) > 30 THEN viewsWHEN date > ADDDATE(published_date,interval 30 day) THEN 0 END
)

BM2:

CASE

when DATEDIFF(date,published_date) >0 and DATEDIFF(date,published_date) <=29 then '01 Month'

ELSE '>13 Months'

end

ArborRose

Something like this?

Summing views in the first 30 days

SUM(

  CASE

    WHEN date <= ADDDATE(published_date, INTERVAL 30 DAY)

       AND date >= published_date

       AND DATEDIFF(CURRENT_DATE(), published_date) > 0 THEN views

  ELSE 0

  END

)

This checks if the date is within the 30-day window and after the publication date. The condition DATEDIFF(CURRENT_DATE(), published_date) > 30 was removed since it doesn't impact the summing logic for views within 30 days.

Categorizing views within 30 days

SUM(

   CASE

      WHEN DATEDIFF(date, published_date) >= 0

         AND DATEDIFF(date, published_date) <= 29 THEN views

   ELSE 0

   END

)

A simplified version to directly check if the date difference from the publication date is within the first 30 days and sums the views.

ArborRose

Something like this?

Summing views in the first 30 days

SUM(

  CASE

    WHEN date <= ADDDATE(published_date, INTERVAL 30 DAY)

       AND date >= published_date

       AND DATEDIFF(CURRENT_DATE(), published_date) > 0 THEN views

  ELSE 0

  END

)

This checks if the date is within the 30-day window and after the publication date. The condition DATEDIFF(CURRENT_DATE(), published_date) > 30 was removed since it doesn't impact the summing logic for views within 30 days.

Categorizing views within 30 days

SUM(

   CASE

      WHEN DATEDIFF(date, published_date) >= 0

         AND DATEDIFF(date, published_date) <= 29 THEN views

   ELSE 0

   END

)

A simplified version to directly check if the date difference from the publication date is within the first 30 days and sums the views.