Beastmode to categorise product revenue by average monthly revenue

We have a daily metrics table (updated daily to Domo) which records revenue per product per date.

What we'd like to do is to calculate a rolling monthly average revenue per product as a beastmode, which is dynamic according to the date range selected by the end user in the card. This could then be grouped into custom tiers.

Tier examples for average monthly revenue could be as follows:

  • Diamond = greater than $1M
  • Platinum = $750k to $1M
  • Gold = $500k to $750k
  • Silver = $250k to $500k
  • Bronze = lower than $250k

Is it possible to build such a beastmode for a dynamic average monthly revenue. And then further categorise in the tiers.

Our aim is to understand how products might move across tiers over time.

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    You are going to want to watch @jaeW_at_Onyx video on rolling averages https://www.youtube.com/watch?v=cnc6gMKZ9R8

    You will need to ask your CSM to enable Windows Functions in Beast Modes if it is not already enabled in order to what he explains. Your tiers would just be a case statement built on top of the rolling average beast mode.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @Mark thanks for the shoutout; but also, you can't apply CASE statements AFTER aggregation. You'll have to use a Dataset View or ETL to pre-calculate avg monthly revenue, then use a CASE statement to assign tiers.

    https://youtu.be/Esnu1PSxRjM?t=621