# Average of an Average of an Average

Member

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:

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

**Did this solve your problem? Accept it as a solution!**
• 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"
• 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.

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

• Domo Employee

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"
• 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