30 Day Interval

cmarino

Hello! I am looking to create a beastmode that sums video views and revenue separately in the 30-days after the asset is published. While the beastmode is validating and looks to be calculating accurately, the QA is very off. In the screenshot example, I manually filtered the time frame for 30-days after the published date and video starts and revenue are significantly off from what the beast modes are reporting. Below is the language I'm using, does anyone know what the issue may be?

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

MarkSnodgrass

If you feel that your beast mode is correct, the first thing I would check would be your date range filter settings. Check to see which field is being used in the date range as this can often skew results if it is basing it off a date field that you weren't expecting it to.

cmarino

Hi Mark, thanks for your response! The challenge is we are looking to pull average 30-day interval performance across over 100 assets within different timeframes. Does the beastmode not force the timeframe filter to act as no more than 30 days? Do you have a recommendation to achieve that?

DataMaven

I recommend adding all of the fields which are used in your beast mode into your table and scan through to see where the issue is. You can also take out the second WHEN statement. It should not have an impact, but may make things easier to review.

Have you checked what Mark suggested? That is something that has come up countless times for clients I have worked with.