Average of an Average of an Average

JustinB
JustinB Member
in Charting

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.

image.png

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:


image.png
image.png

Please help!

Answers

  • GrantSmith
    GrantSmith Coach

    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!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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"
  • JacobFolsom
    JacobFolsom Domo Employee

    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.

    image.png


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

    image.png


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

  • JacobFolsom
    JacobFolsom Domo Employee

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

    https://www.youtube.com/watch?v=BlCs8G_hTVE


    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"

Categories