Paid Search Marketing

PPC VLOOKUP: How to Use VLOOKUP for Paid-Per-Click Marketing

By Tom Demers December 30, 2010 Posted In: Paid Search Marketing Comments: 4

VLOOKUP is one of a number of handy Excel functions for PPC. For grizzled PPC campaign managers this function is likely very familiar, but as a former philosophy major who fulfilled his math requirement with a class in logic (taught by a philosophy professor) I think it's helpful for non-Excel ninjas who are new to PPC to understand:

  1. How the Excel VLOOKUP function works
  2. How it can be applied to pay-per-click management

As a result in this article we’ll walk through some specific applications and resources offering more information on using the VLOOKUP function for PPC.

How to Do a VLOOKUP

Before we dig into the applications of the VLOOKUP function for PPC I want to go over how to actually use the VLOOKUP function. Here are a couple of videos that should help you to learn the function:

And another example of how to use the function:

Now you may not actually have a reference table and of course we don’t have the color codes or state fees outlined above within our paid search campaigns, but either of those videos should give you a strong foundation for the syntax in the VLOOKUP function and how to format it properly. From there, we can apply the function within various paid search efforts.

How Might We Use the VLOOKUP Formula in PPC?

The best use of the VLOOKUP function for PPC is in comparing data from multiple date ranges. There have been some great posts outlining creative applications of VLOOKUP both right here on the WordStream blog and by our friend Chad Summerhill:

  • Comparing PPC Month Over Month – Amy Hoffman from the team at Hanapin wrote up a great, in-depth post on how to compare PPC data month-over-month. This is a fantastic means of figuring out “what’s changed” in your accounts to help you identify opportunities or troubleshoot issues within a campaign. As Amy outlines in her post, you can leverage this methodology to help you compare all kinds of metrics – from Click-Through Rates to – to diagnose dips or surges.
  • Calculating Peer Comparisons in PPC Data – Here Chad outlines a similar methodology (and even offers up a handy spreadsheet) for comparing data using the VLOOKUP formula, though in this case he’s walking you through how to analyze your data against it’s peers to help surface areas of your account that are the most in need of your attention. This sort of context and workflow prioritization is critical to successful PPC campaign management amidst the sea of data available in paid search accounts.
  • Optimizing Your Paid Search Account Structure – Here Josh Dreller outlines another interesting application for the VLOOKUP function in developing strong account structure.

The ability to do in-depth data comparisons within your paid search campaigns is crucial, and the VLOOKUP formula helps make this possible within Excel, so it’s an important function to be familiar with.

Want More Excel Goodness?

If you’re still jonesing for some more Excel data-munging, we made a great addition to the WordStream Quality Score Toolkit not too long ago that offers a ton of actionable insight into your accounts’ Quality Scores. Get it here if you missed it the first time around. Looking to create impressive charts to showcase PPC with Excel? Try out this tutorial on making bubble charts on Excel.

AdWords Performance Grader




If you enjoyed this post, please consider leaving a comment.

Comments

Thursday December 30, 2010

PPCOptimization (not verified) Said:

Great info. Your link to Optimizing Your Paid Search Account Structure is broken though.

Thursday December 30, 2010

Elisa Gabbert Said:

Sorry about that -- it's fixed!

Sunday January 02, 2011

Stag Do Ideas (not verified) Said:

Really great video (esp. liked the second one). I can't believe I've got by without vlookup until now - it's superb and I can immediately think of uses right away. Thanks for pointing it out in such a helpful way.

Monday May 09, 2011

PTC Sites (not verified) Said:

Easy to understand tutorial, thumbs up

Leave a Comment

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.
 
Free Keyword Tool

Get thousands of relevent keyword suggestions - more,
faster, free!

Free Keyword Niche Finder

Discover profitable pockets of keywords for your
business.

Free Negative Keyword Suggestion Tool

Identify wasted spend before it happens and increase
your paid search ROI.

Contact Us | Company | Support | Site Map | Trademarks | Privacy Policy © 2007-2014 WordStream, Inc. All rights reserved.