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.
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:
Small Business Dad 