• About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
  • About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
June 07, 2016

How to Calculate Average Position

Posted by Preston Smith

I’ve been asked more times than I can remember, “How do you calculate the real average position?” The good news is that everyone asking realizes that taking an average of an average will get you in trouble. The bad news is they still don’t know how to actually calculate the true average position. In this post, I’m going to run through the methodology and a dummy example in Excel.

Let’s start by looking at the dummy data.

avg pos pic 1

We have date, campaign, impressions, and average position. The end goal could be to calculate average position by date or by campaign.

The reason we can’t just take an average of average position, basically, is the differences in impression volume. We need to account for the impression volume by calculating a weighted average instead of an average of an average.

The first step is to multiply average position and impressions in the raw data, creating a metric I usually label “Avg Pos Index”. Fill this formula down for all rows.

avg pos pic 2

We can now make a pivot table off of 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.

avg pos pic 3

And that’s it! Now we can easily see the true average position by date or campaign just by changing the layout of the pivot table.

avg pos pic 4

A couple of parting thoughts:

  1. I ran through this example in Excel, but the same methodology will work in Tableau, SQL, or whatever tool you are using.
  2. PLA’s will wreck your average position calculation. You obviously don’t want to include them as PLA’s don’t have a position in the same sense as regular paid search ads, but they are typically still included in search reports that pull average position. Just make sure you remove PLA’s by filtering them out from your average position pivot/dashboard.

Interested in working with us? See our open analytics roles here.

analyticspaid searchreporting
Previous
Next

Latest White Papers

  • Shifting Plans for 2020 & Beyond
  • Game On: How Brands Can Log Into A Diverse Multi-Billion Dollar Industry
  • What CCPA Means For Brands
  • How Google is Improving Consumer Data Privacy
  • Ways to Prepare for the Cookieless Future
  • See all White Papers

Featured Posts

  • Ad Age Names PMG #1 Best Place to Work in 2021
  • Hindsight 2020 & Looking Ahead to 2021
  • Preparing for Streaming’s Growth & The Future of TV Buying
  • MediaPost Names PMG Independent Agency of the Year
  • PMG Client Portfolio Trends During Amazon Prime Day 2020

Categories

  • Consumer Insights
  • Content
  • Creative Design
  • Data Analytics
  • Development
  • Digital TV & Video
  • Ecommerce
  • Industry News
  • Local
  • Mobile
  • Paid Search
  • PMG Culture
  • Programmatic & Display
  • SEO
  • Social Media
  • Structured Data
Fort Worth

2845 West 7th Street
Fort Worth, TX 76107

Dallas

3102 Oak Lawn Avenue
Suite 650
Dallas, TX 75219

Austin

823 Congress Avenue
Suite 800
Austin, TX 78701

London

33 Broadwick Street
London
W1F 0DQ

New York

120 East 23rd Street
New York, NY 10010

Get in touch

(817) 420 9970
info@pmg.com

Subscribe to the PMG Newsletter
© 2021 PMG Worldwide, LLC, All Rights Reserved
  • Contact
  • Privacy Policy
 Tweet
 Share
 Tweet
 Share
 Tweet
 Share
 LinkedIn
We and our partners use cookies to personalize content, analyze traffic, and deliver ads. By using our website, you agree to the use of cookies as described in our Cookie Policy.