Average of an Average of an Average

Hello,

I have a project that is using an interesting methodology to score social media ads/campaigns/accounts. They first score each ad, then they average the ad level scores to get the campaign score, then they average the campaign scores to get the account scores.

In the example above I score the ad based on the impressions:

case when impressions < 20 then 1

when impressions < 40 then 2

when impressions < 60 then 3

when impressions < 90 then 4 else 5 end

This gives me my ad level score. Then I need to average the ad scores to get the campaign score, and average the campaign score to get the account score.

I would also need to use the above to create cards that are rolled up at the campaign and account level:


Please help!

Answers

  • Hi @JustinB

    You'd need to use an dataflow to pre-aggregate your data. You can do this in Magic ETL with the group by tile.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • or a window function in data set views.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • I might recommend using a Pivot table with the Hierarchy for Campaign and Ad set on the Rows and set the Aggregation to Average on your Ad Score beastmode.


    When you collapse at the Campaign level, this summary becomes more clear


    Jacob Folsom
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • JustinB
    JustinB Member
    edited May 2021

    Can you show me your calculation? My pivot table will not allow me to aggregate my beast mode? My calculation is also far more complex (it creates a score using many metrics).

  • @JustinB ,

    case when impressions < 20 then 1

    when impressions < 40 then 2

    when impressions < 60 then 3

    when impressions < 90 then 4 else 5 end


    If you need to use SUM(Impressions) then it will no longer allow you to average. So that impressions number would need to be rolled up to the granularity needed for the scoring model. Otherwise, it will simply take the row level impressions.

    I tried this with data stored at daily level rolled up to Ad, and it doesn't work properly.

    Jacob Folsom
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    edited May 2021

    Hey @JustinB

    Did a little tutorial for you on YouTube.


    If you're in the Domo-dojo instance, here's a link to the Dataflow

    https://domo-dojo.domo.com/datacenter/dataflows/53/graph


    Link to DataCrew post

    https://datacrew.circle.so/c/articles-and-knowledge-base/tutorial-buildaverageofanaverage

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"