How to create DoD variance formula

jvsering

My data set has 'Total Actions' by advertiser, by goal name, by day. I would like to be able to create an alert when 'Total Actions' drop to 0 on a particular day (where it previously had >= 1). Of if there is a variance of 90% or more.

As I understand it, we can create alerts on a card but only based on the Summary number. I want to be able to create an alert for multiple lines on this dataset.

For example:

3 days ago, Total Actions for a client was 1000 but yesterday it was 0.

I was thinking that I could add a column via ETL with the below but I'm getting a syntax error. I'm not sure this is the best way to go about it either.

SUM(Total Actions)
WHERE DATE(Date) = DATE_SUB(CURDATE(), INTERVAL 3 DAY);

What would be the best way to go about this?

david_cunningham

Does your dataset always have data for each day? In other words, if on a particular day, the value is zero, will it show up as a row with the date, and a value of zero, or is the row for that date simply not in the dataset?

If the former, here are some details on how you could achieve this. Note, this also assumes that the data is already aggregated up by day and group. If not, you'll need to do that aggregation first. If you don't have data for each day, and you want to compare to the last 3 days, and not the last 3 values, you'll need to combine your data to a date dimensions table.

For instance, say you have this example dataset.

First thing is to use the Rank & Window Tile to create a lag function

From there we can create a function called "alert_triggered" that will evaluate if the value changed by > 90% or is equal to 0. We set the output as either 1 or 0 (true or false). I assumed since you said variance that you meant a 90% swing in either direction, which is why it's wrapped in an ABS() function.

case

  when ABS((value-3_days_ago)/3_days_ago) > .9

    or value = 0

  then 1

  else 0

end

From there you can use the alert_triggered column in your Summary Number. You would then set up your alert to trigger when Summary Number > 0

david_cunningham

Does your dataset always have data for each day? In other words, if on a particular day, the value is zero, will it show up as a row with the date, and a value of zero, or is the row for that date simply not in the dataset?

If the former, here are some details on how you could achieve this. Note, this also assumes that the data is already aggregated up by day and group. If not, you'll need to do that aggregation first. If you don't have data for each day, and you want to compare to the last 3 days, and not the last 3 values, you'll need to combine your data to a date dimensions table.

For instance, say you have this example dataset.

First thing is to use the Rank & Window Tile to create a lag function

From there we can create a function called "alert_triggered" that will evaluate if the value changed by > 90% or is equal to 0. We set the output as either 1 or 0 (true or false). I assumed since you said variance that you meant a 90% swing in either direction, which is why it's wrapped in an ABS() function.

case

  when ABS((value-3_days_ago)/3_days_ago) > .9

    or value = 0

  then 1

  else 0

end

From there you can use the alert_triggered column in your Summary Number. You would then set up your alert to trigger when Summary Number > 0