Weighted Average (average of average)

https://www.contextures.com/excel-pivot-table-calculated-field.html

 

 

You will need this for pivoting stats such as Avg. position in Google ads

 


 

  • Take Impressions
  • Get average position(s)so you can see how many times the ad was displayed (this number would have been used to work out the average)

 

  • Then calculate your own metrics ‘Avg. Position Index’

Do this by multiplying Impressions by Av. Position.

 

  • We can now make a pivot table from this data and create a calculated field for our true average position.

It’s simply the sum of the average position index divided by the sum of impressions.

https://www.pmg.com/blog/calculate-average-position/

To create a custom field/equation in a pivot table
– select a cell in the pivot table
– go to Analyze tab
– click fields, items & sets —> calculated field

 

 


 

Weight Average Quality Score

 

To do this with Av. Quality Score.

 

  • Download the Keyword’s impressions & Quality score
  • Create the index by multiplying Quality Score by Impressions.
  • Then Sum both the Quality Score & Impressions & divide them.
    – Sum of Quality Score INDEX/Sum of Impressions

 

This will give you a weighted average comparing all KWs.

 

See spreadsheet below for an example:

WeightAverage_Generic_DummyData

Advertisements