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

  • GrantSmith
    GrantSmith Indiana 🔴

    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.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    or a window function in data set views.

  • DataJake
    DataJake

    domo

    💎

    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


  • JustinB
    JustinB ⚪️
    edited May 14

    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).

  • DataJake
    DataJake

    domo

    💎

    @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.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    edited May 17

    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