Using Excel for Pay Per Click

This week Matt Umbro (@Matt_Umbro) came up with yet another great question set titled “Using Excel for PPC.” The following is the transcribed Streamcap from the live chat:

Q1: For the more novice Excel users, explain why you would use VLOOKUP and CONCATENATE?

  • Concatenate: to create keyword mashups; vlookup: to pull data from one sheet into another within a workbook. – Melissa Mackey (@Mel66)
  • Concat = combining month and year so it graphs well eg. 2013-08 instead of 2013 and 8 in different fields. – Russel McAthy (@therustybear)
  • I Concatenate to join together values and then use VLOOKUP to pull an additional variable matching those values. – Brian Gaspar (@BGaspar)
  • Rather than using concatenate, I often just write something like =A1 & ” ” & B1. – Chris Haleua (@chrishaleua)
  • I’m the same. VLOOKUP doesn’t get much use around here. Might be something I should look at using more. – Michelle Morgan (@michellemsem)
  • Use VLOOKUP to build quickly with a set list of items. CONCATENATE you can use to build phrases. Just as easy to use. – Mark Jensen (@Just_Markus)
  • You don’t use CONCATENATE! You use ‘&’ because it does the same thing & is much quicker to type! – Tamsin Mehew (@TamsinMehew)
    • I do the same thing. “&” works pretty well and it’s easier to build spaces into. – Michelle Morgan
    • concatenate is to & what =SUM(1,2,3) is to =1+2+3. – Russell McAthy
  • VLOOKUP might be my favorite function but I also use INDEX MATCH pretty often since it is a little more flexible. – Chris Haleua
  • I use CONCATENATE to create dynamic URLs in bulk…VLOOKUP to compare PPC stats month-over-month. – Jonathan Levey (@jlevey)
  • vLookup to find Postion vs Expected CTR. I dont use concatenate no need. – Phil Pearce (@philpearce)
  • Not using VLOOKUP that much, hardly at all. but concatenate to mesh up keywords/ad text for analysis etc. – Christina Hall
  • I tried it a couple times and couldn’t get it to do what I wanted. Must have had the wrong expectations. – Michelle Morgan
  • Use vlookup all the time when pulling reports from different sources. – Alma Smith (@Alma_Smith)
  • I often combine VLOOKUP with ISNA to replace errors with blank values and keep the sheet looking clean. – Chris Haleua
  • I use to pair up metrics I want pull in using VLOOKUP function. – Nefer Lopez (@Nefer_L)
  • I tend to use sumifs more than vlookup – can handle multiple criteria w/o concatenation, but can’t cope with text. – Temsin Mehew
  • Huge Account. Concatenate Account, campaign Group. Vlookup to bring in bids, ads, etc to new sheet. Do this all the time. – Joseph Drury (@drurytheelder)

Q2: What are some tasks you wish you could easily complete in Excel? And does anyone have solutions for any of these tasks?

  • Sorting and filtering data beyond Pivot Tables. I use MS Access for this. – Melissa Mackey
  • Animated scatterplot charts (which is something they just released in Excel 2013 through power view). – Chris Haleua
  • Not a real specific way to say it, but easier ways to build graphs with data from multiple tabs. Gets irritating sometimes. – Michelle Morgan
  • I wish I could create scatter plot charts with more than 500 or so data points. – Mark Jensen
  • I want to learn more about graphs in general and all the features available. Any good resources? – Bethany Bey (@Bethany_Bey)
  • When I copy a pivot table, how do I make sure duplicate values aren’t assigned a blank cell? – Matt Umbro
    • =INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0″³ )) – Chris Haleua
  • I wish there was a way to setup scripts that call on an Excel doc, in addition to google docs. – Nefer Lopez
  • I would like to use Excel to analyze the SQR and identify words in the search query that are not in my PPC keyword. – Jonathan Levey
  • I wish Excel could auto turn data into Insight. – Phil Pearce

Q3: In your opinion, what is the most underutilized Excel feature? Why?

  • Macros – Robert Brady (@robert_brady)
  • Excel Solver! – Sam Owen (@SamOwenPPC)
  • Macros! underused because of the learning curve. Can be really useful to automate tasks or complete complex ones. – Ravi Sodha (@ravisodha)
  • Easy. Page breaks. Fitting to 1×1. Split/Freeze Frames. – Brian Gaspar
  • Hoalseek – if you’re doing forecasting of any kind and not using it then slap yourself with a fish. – Russell McAthy
  • It’s a simple feature, but =LEN is amazing for writing ad copy. – Matt Umbro
  • Search-and-replace. I use more than advanced functions. People underestimate what you can do with it. – Theresa Zook
  • Macros, because they can easily perform advanced tasks repeatedly. – Jonathan Levey
  • Scenario management for forecasting is great too. – Russell McAthy
  • Conditional formatting using colors. Great instant visual for orders of magnitude for cost, conversions, roas, etc. – Logan Durant (@THELoganDurant)
  • For some reason I haven’t used Chart Templates until just recently. Saved me a ton of time formatting. – Rick Galan (@RickGalan)

Q4: Explain how you use pivot tables for your day to day PPC management?

  • Where do I begin? Slicing and dicing data, filtering, calculated metrics e.g. total CPA or CPC, ad report analysis. – Melissa Mackey
  • OMG pivot tables 4 life! Seriously. No better way to do ad hoc analysis where you drill in and out, segment, etc. Reporting too. – Rick Galan
  • For summing things up when they’re too big for SUMIFS. – Tamsin Mehew
  • Ad test and LP analysis, monthly performance reporting, SQRs. Almost everything. – Bethany Bey
  • For Daily tracking can use filters in pivot to drill down by engine source and type, either by Campaign or overall. – Brian Gaspar
  • Pivot tables are great for easily aggregating lots of data into a digestible format. – Matt Umbro
  • Using pivot tables in combination with a Macro to generate week to week Performance Charts on Campaign Level. – Thomas Langel (@reklame)
  • Aside from what all others said, pivot tables are underrated for formatting as well. – Aaron Levy (@bigalittlea)
  • Fun pro tip – make multiple charts off one pivot by using GETPIVOTDATA to pull out just what you need. – Rick Galan
  • Ad copy tests, autoplacement reports, SQA work, looking at data in external lead systems, all kinds of good stuff. – Michelle Morgan
    • Adding in external info is a big one! They can bring multiple sets of info into one report. – Sean Quadlin (@SeanQuadlin)
      • Getting actionable insights off of external lead systems is a big part of the job. Difficult, but necessary and fun. – Michelle Morgan
  • Use Pivot Tables and Dimensions/Labels to do ad testing on a near daily basis. – Joseph Drury
  • Also not to mention the custom formula feature using Pivot fields to create more KPI’s to display. – Brian Gaspar
  • I use them for ad-hoc reports, and a lot when working w/product feeds. – Logan Durant
  • I use pivot tables to see if different ad groups are triggering the same search query. – Jonathan Levey
    • Smart, also good for PLA ad groups triggering the same query. – Matthew Umbro
  • Drill down and trouble shoot problem areas in accounts. What happened to my rockstar KW’s? – Nefer Lopez
  • I also use GETPIVOTDATA to make charts that automatically look back 14 days, and update dynamically when the pivot updates. Pivot tables provide DEPTH to data that is usually flat. – Rick Galan
  • Excel has 2 dimensions (rows & columns) for values by time or category. Sheets can get to a 3rd but pivot gets to the 4th. – Chris Haleua
  • If you’ve got data by the day then you can use Group Selection to show the data by the week or month. – Tamsin Mehew
  • Throw a slicer on your pivots to update the filter on multiple pivot tables at once! (sorry mac users – not for you). – Rick Galan

Q5: In what ways do you use Bing Ads Intelligence for Excel? What do you believe to be the most important feature? Why?

  • I love using the @BingAds Intelligence Add-In for Excel for keyword research. Still impressed they own – Chris Haleua
  • I use BAI a lot. It’s more robust than Google and way more accurate. Plus, HATE Keyword Planner. Keyword expansions, related keywords, volume estimates – all with filters & formatted nicely for Excel! – Melissa Mackey
  • I don’t always use the @BingAds Intelligence Tool, but when I do I gather keyword suggestions. – Jonathan Levey
  • I love BAI but haven’t been able to use it in a while. I liked to do keyword research with it. – Christina Hall (@Chrissie_Hall85)

Q6: If you use Excel for reporting, what types of formulas/features do you make use of? Why?

  • Use Web Query URLs from Marin. Easily Refreshable Charts, Tables and More! – Joseph Drury
  • =VLOOKUP =SUMIF =SUMIFS =LEFT = MID =LEN =COUNTA =MATCH =FIND – starter for 10. – Russell McAthy
  • Excel is how I make all of my visuals (charts, graphs, etc) for reports. – Michelle Morgan
  • It’s been said before, but I report using pivot tables all the time. Their formatting is all that I need. – Sean Quadlin
  • Even experts need to start looking deeper into Power View & Power Map. Both of these new Excel 2013 features are game changers. – Chris Haleua
  • Condition Formatting to showcase outlining performance, both positive and negative. – Brian Gaspar
  • In campaigns w phone tracking, add a field to calculate overall conversion rate accounting for phone calls & form conversions. – Timothy Jensen
  • Pivots. A lot. Already mentioned GETPIVOTDATA. Charts & Conditional formatting. More than 5 minutes to update = doing it wrong. – Rick Galan
  • =TRIM is a good one if you’re trying to cleanup white spaces and sort lists. – Nefer Lopez
  • I use the chart feature and these formulas: AVERAGE, SUM, and IF. – Jonathan Levey
  • If it’s on a report then using IFERROR is extra important. – Tamsin Mehew
  • I’ve found SUMIFS handy for pulling data across multiple sheets (date ranges etc. taken into account). – Timothy Jensen
  • =SUMIFS is also great at pulling data for ranges. eg date 1 in A1 date 2 in A2 and SUMIFS for data between the two. – Russell McAthy


More PPCChats

Don’t forget to stay tuned for the next #PPCChat on Tuesday at 12 noon Eastern, 9 am Pacific and 5pm in the UK. Same Chat time, same Chat channel.


Check out the PPCChat Twitter list to see and connect with all current and prior participants.

• Matt Umbro (@Matt_Umbro)
• Paul Kragthorpe (@PaulKragthorpe)
• Aaron Levy (@bigalittlea)
• Alma Smith (@Alma_Smith)
• Bethany Bey (@Bethany_Bey)
• Brian Gaspar (@BGaspar)
• Chris Haleua (@chrishaleua)
• Christina Hall (@Chrissie_Hall85)
• Jonathan Levey (@jlevey)
• Joseph Drury (@drurytheelder)
• Logan Durant (@THELoganDurant)
• Mark Jensen (@Just_Markus)
• Melissa Mackey (@Mel66)
• Michelle Morgan (@michellemsem)
• Nefer Lopez (@Nefer_L)
• Phil Pearce (@philpearce)
• Ravi Sodha (@ravisodha)
• Rick Galan (@RickGalan)
• Robert Brady (@robert_brady)
• Russel McAthy (@therustybear)
• Sam Owen (@SamOwenPPC)
• Sean Quadlin (@SeanQuadlin)
• Tamsin Mehew (@TamsinMehew)
• Thomas Langel (@reklame)

Sharing the Library of Streamcaps

This is a guest post by Paul Kragthorpe; WebRanking SEM Manager in Minneapolis, Minnesota, #PPCChat Streamcap Grabber, SEO Blog Author. Connect with me @PaulKragthorpe, and Google Plus.

Be Sociable, Share!

Tags: , , , , ,

4 Responses to Using Excel for Pay Per Click

  1. […] Using Excel for Pay Per Click, PPC Chat Streamcap […]

  2. […] week’s PPC Chat was one for the ages. The Using Excel for Pay Per Click chat had lots of people sharing tips, tricks, and frustrations about using the tool to help them in […]

  3. […] Last week, the #PPC Chat topic was “Using Excel for PPC”, and I’ve got to be honest, it was one of my favorite chats we’ve ever had. There were so many useful tips, tricks, and spreadsheets shared that it was definitely a worthwhile hour. The full stream cap can be found here: PPC Chat Streamcap. […]

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Tweets

I am speaking at SMX East was rated one of the Best PPC Blogs by Boost CTR