You’ve probably ran into a post or two over the last couple of years about using pivot tables to analyze your PPC data. Some of those posts may have been kind enough to include instructions for how to calculate a weighted average position. This is important because once you start aggregating your data in a pivot table you can’t average your Average Position or you will get a skewed perspective.
How to Calculate a Weighted Average Position in Excel
Regardless of the report you need to analyze, you will add an “AvgPos X Impressions” column. This column will be used to create a ‘Calculated Formula’ in your pivot table.
Once you’ve got your pivot table going, you will need to create calculated metric by navigating here: PivotTable Tools > Options > Formulas > Calculated Field.
Now name your new ‘Weighed Avg Pos” metric and use the following formula:
Now you will have an accurate representation of average position regardless of the level of detail in your pivot table.
How to Calculate a Weighted Average Position in Tableau
For those of you fortunate enough to have Tableau for analyzing your PPC data, calculating a weighted average position is just as easy as in Excel. Navigate to the ‘Calculated Field’ tool by right-clicking in the ‘Measures’ area and clicking ‘Create Calculated Field…”
Again name your new metric ‘Weighted Avg Pos’ and use the following Tableau formula:
There, I’ve written it down. Now I can quit bothering my co-workers and just reference this post in the future. Those of you with better memories may not need this reference, but for the rest of you feel free to bookmark this page.