How to Calculate a Weighted Average Position in Excel & Tableau

by on April 15, 2011 in PPC Data Analysis

This post is just as much for me as it is for you.  I can’t tell you how many times I’ve bugged my coworkers asking them how to calculate a weighted average position when analyzing PPC reports with Excel or Tableau.  I guess that’s what happens when you let a former art major do math, but weighted average position is just one of those must-have PPC tools.

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.

PPC Weighted Average Position Excel Formula

PPC Weighted Average Position Excel Formula

 

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:

=’AvgPos*Impressions’ /Impressions

PPC Weighted Average Position Excel Calculated Field

PPC Weighted Average Position Excel Calculated Field

 

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:

sum([Avg Position]*[Impressions])/sum([Impressions])

PPC Weighted Average Position Tableau Formula

PPC Weighted Average Position 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.

Tags: ,

About Chad Summerhill

View all posts by Chad Summerhill →

4 Responses to “How to Calculate a Weighted Average Position in Excel & Tableau”

  1. Jc April 25, 2011 at 1:36 pm #

    “I guess that’s what happens when you let a former art major do math…”

    I’m sorry lol. Have you worked in display?

    Reply
  2. searchengineman August 16, 2012 at 3:53 pm #

    Thank you very much, I was trying to figure out how Google uses its formula
    to calculate (Average Position) – PS: You’re not the only former Art Major who
    landed in PPC! – The advantage is I can get beyond the 1st decimal..which is a bonus.

    Searchengineman

    Reply
  3. Aji October 8, 2012 at 7:23 am #

    man, thanks for this tip.. i’ve been looking around for the formula and found your blog. BIG thanks :)

    Reply
  4. Kevin James McAuley December 10, 2012 at 5:14 am #

    Hey Chad,

    Thanks for this helpful tutorial. Even though your pivot table says “Sum of WeightedAvgPos” once you have followed these steps it is all good?

    Thanks

    Reply

Leave a Reply