Excel And PPC

This week Dallas Stevens (@DDP_PPC) hosts PPCChat in place of Matt Umbro (@Matt_Umbro) with a great question set titled “Excel And Pay-Per-Click.” The following is the transcribed Streamcap from the live chat:

Q1: Where does Excel provide the most value for you in PPC? 

  • For me, it Makes data mining so much more efficient and expidites tasks of repitition. – Dallas Stevens
  • Data analysis. I love exporting a huge data dump and having excel format everything in an easy to read view. Pivot tables baby! – Joe Martinez (@MilwaukeePPC)
  • Where doesn’t it? From ad writing to data analysis to building out multiple campaigns at once. – Theresa Zook (@I_Marketer)
  • In Adwords. If I’m doing something in Adwords, it will have an excel sheet attached. – Tanner Schroeder (@TannerSchroeder)
  • Analyzing the data, especially with large amounts. Easier to manage and compare. – Steve Seeley (@SteveSeeley)
  • On the production side of things: definitely bulk ad changes & keyword building. Formulas that are helpful when editor can’t. – Tim Halloran (@timmhalloran)
  • Excel gives me an ability to efficiently make bulk edits and pull up big and useful PPC data. – Andrey Kozlov
  • (I use Google Sheets) to manage a list of vendors to focus attention on a set of variables and bidding. Tons of IF statements. Google Sheets has a QUERY function I find far superior to VLOOKUP. I’ll use QUERY w/ IMPORTRANGE to rule the world. Really awesome! – David Cox (@dcoxdesigns)
  • I find Excel super useful for the same reason it’s useful in other data analysis: automating calculations for reporting w macros. – Mark Sullivan (@mpsulli)
  • Building models to create solutions for bid management, testing results, CPA reviews, it’s endless for analytical uses. – Rachel King (@rachelking237)
  • Allows me to sort/sift through data to pull out the actionable insights. – Robert Brady (@robert_brady)
  • Excel is where I do everything. Data analyses, buildouts, optimizations, notes-to-self about client industries, you name it. – Emma Franks (@akaEmmaLouise)
  • Pivot Tables. Also, ASAP Utilities is a great add-on. Well worth the money spent. – Brooke Townsend (@btownsend13)

Q2: Do you find it hard to use some Excel formulas for your PPC needs? Which ones?

  • I dont use VLOOKUP as often as other formulas in my day to day so I almost forget how to use it. – Dallas Stevens
  • The whole sorting thing with VLOOKUP always gets me. – Robert Brady
  • Only when excel decides to glitch out and not apply the formulas correctly. – Andrey Kozlov (@WhoIsAndrey)
  • Actually, no. The ones I use, I use often enough to be comfortable w/them. Complex ones, I set up in template spreadsheets. But I agree that Vlookups can be a nightmare. – Theresa Zook
  • Google Sheets again, It’s easier to link multiple spreadsheets than in Excel, plus I can “live share” my sheet w/ team. – David Cox
  • Vlookups can be the death of me. – Brooke Townsend
  • I struggled w/ “real” projections using forecast models in the pre-programmed What-IF & Forecasting functions. Better to build. – Rachel King
  • The bane of my existence – any formula or shortcut on Mac Excel vs Win. – Maria Corcoran
  • No. After 10 years of doing this, I’ve done everything with excel for adwords that I’ll ever need to do. – Steve Gibson (@stevegibsonppc)
  • VLOOKUPs are ones I still struggle with. But it also took me awhile to get CONCATENATE down so that’s how my excel skills are. – Joe Martinez
  • No. I am Excel Jesus. – Leo Sussan (@lsussan)
  • Just have to echo what’s already been said. Probs should but I rarely use VLOOKUP tbh. Also, custom formatting sometimes a fail. – Emma Franks
  • In all seriousness. I think nested if statements combined with vlookups are hell. It can get complicated fast. – Maria Corcoran
  • Only formula that consistently gets me a bit mixed up is INDEX/MATCH to avoid huge VLOOKUPs. I have to reteach myself each time. – Mark Sullivan

Q3: What Excel features do you utilize to make your PPC data more visual?

  • Heatmapping is a personal favorite (color scales) as well as data bars in some cases. – Dallas Stevens
  • I personally love conditional formatting because, colors. – Brooke Townsend
    • My number one visual by far. So easy to follow. – Joe Martinez
  • Conditional formatting to show performance heatmapping has been helpful. – Davis Baker (@davisbaker)
  • Conditional formatting is always helpful. – Timothy Jensen (@timothyjjensen)
  • Nothing. I’m not a visual person, for the most part. Occasionally use charts for long-term trending analysis. – Theresa Zook
  • Charts and Pivot tables, of course. – Andrey Kozlov
  • Pivot Charts…sadly, only on Excel for Windows. – Mark Sullivan
  • I used tables with Slicers, very helpful for people to find data and charts update at the same time you change your selection. – Andrea Cruz (@andreacruz92)
  • Shading via Conditional Formatting is an easy first step for visualizing a range of values. – Glenn Schmelzle
  • Graphs & sparklines, I love a good line graph. I prefer graphed and charted data side-by-side. Throw a heat map on & we’re set. – Rachel King
  • Heat maps, highlighting, and graphs. Gotta make the data pop! – Tanner Schroeder
  • My team prefers to see the numbers. visualization can skew the way the results actually are, IMO. Depends on the range used. – David Cox
  • I’m a big fan of charts when showing MoM, QoQ and YoY data. It’s funn showing clients the trends whether good or bad. – Garrett McGregor (@mcgregor212)
  • Pivot tables, heat mapping, cell styles, charts… plain white background behind charts. Anything to make data more digestible. – Emma Franks
  • Do you use visual features only for reporting purposes? – Dallas Stevens
    • Yes, 95% of the time. – Theresa Zook
    • I use it for analysis as well. Visual features can help sort the data and lead to actionable decisions. – Tanner Schroeder
    • Never, It is quite often easier to catch anomalies over time with trends or cond. formatting. – Maria Corcoran (@mariacorcoran)
    • No, I use data sets more often than visual data. Visual is for exec/client reporting primarily. I prefer raw data to pivot. – Rachel King
    • Not necessarily, visual features can be also great for daily pacing with Paid Search. – Andrey Kozlov
    • I like seeing my data with the visuals, helps me analyze the data better. – Dallas Stevens
    • I use it for my own sanity as well. Makes it easier to see what’s actionable for me. – Brooke Townsend
    • Nope. Reports should be meaningful to me before client sees. I do a lot more visualizing in preparatory middle/analysis steps. – Emma Franks

Q4: What was the last Excel formula you looked up and why?

  • VLOOKUP… I think I just need to use it once a day so I don’t forget how to use it. – Dallas Stevens
  • Vlookup because I don’t use it that often. – Theresa Zook
  • Concatenate formula – was combining large chunks of texts and data together. – Andrey Kozlov
  • Anytime my function doesn’t work I look it up. No shame, Iuse Google all the time. I bookmark the page i found the answer on. – David Cox
  • Was offset. A client wanted us to build onto a time-series table and update summaries with the latest week/mth/qtr’s data. – Glenn Schmelzle (@heyglenns)
  • Wanted to do more ad copy testing using excel to try and perfect each messaging. Started looking for different formulas to help. – Joe Martinez
  • I think it was IMPORTXML for some ranking table I was trying to get into Excel. That or INDEX/MATCH. – Mark Sullivan
  • Removing non-alpha numerical characters from a messy list of products. Turned into VBA by the end but couldve been a formula. – Tim Halloran
  • VLOOKUP! The only function I wish I remembered to use more often. Just easier for me to copy and paste and use other functions. – Emma Franks
  • Built a profitability model using VLOOKUP, nested IFS, & pivots off of a raw data file that updates hourly. BI wrote the SQL. – Rachel King
  • Formulas I should look up more often: MID and RIGHT for pulling out campaign from URLs. – Emma Franks

Q5: Have you created any custom PPC templates in Excel? If so, what are they used for?

  • I have a custom concatenate tool for attaching match types to keywords and building out accounts. – Dallas Stevens
  • I made a tool that analyzes our weekly sales activity. I have a HUGE formula w/tons of nested IF statements. I have multiple reports run off the base report. it has turned around our advertising ad spend to sales ratio. – David Cox
  • I have a folder of Excel templates. It has a little of everything. If I make it once, I create a template of it for the future. – Tanner Schroeder
  • We have a fill-in-the-blank media spend sheet, to help set budgets as we chg campaign-level settings. Factor’s in $ in reserve, etc. – Glenn Schmelzle
  • I spent over a week or so. we would look at the results then adjust as needed. it was actually fun. – David Cox
  • Yes! Forecast model, Device-level bid mngmt model, ROAS model, CPA by keyword by device model. Business-specific, ABM. – Rachel King
  • Lots of reporting templates (mostly Sheets, but some Excel). Most tasks are manual bc depends what answers I need from the data. – Emma Franks
  • Also have some basic templates for account structures and ad writting. – Dallas Stevens

Q6: What are some Excel features that you would like to learn more about?

  • Macros seem very handy but I have yet to explore that part of Excel. – Dallas Stevens
  • Definitely macros. Not sure why, but I just never bothered to learn them. – Theresa Zook
  • Scripts, macros. anything more advanced than functions. – David Cox
  • I would love to refresh my Solver knowledge, for future predictions based on multiple variables, long time without using it. – Andrea Cruz
  • I’ll echo Emma. More sheets you make, greater responsibility to keep them organized for reuse. – Glenn Schmelzle
  • VBA – very shallow knowledge of this programming lang. Only know what I needed to know to build a specific model. – Rachel King
  • Unconscious incompetence: I don’t know what I want to know about Excel but I know I want to know more helpful formulas. Honestly, all the cool things I’ve learned about Excel was from someone else once they explained their use case. – Tim Halloran
  • Fantastic guys! Lets hit on our last question to tie this all up! – Dallas Stevens
  • Any formula that would let me search for/see common attributes in a range (i.e. SQRs) without having to identify it myself. – Emma Franks

Q7: What resources have you used to expand your knowledge of Excel?

  • Mostly youtube videos and I did buy an Excel Bible to use as a reference for formulas. – Dallas Stevens
  • YouTube, PPC Blogs, Google Search. – Garrett McGregor
  • I search the internets for answers to my questions. – Theresa Zook
  • Is it cheating to say the Excel team at work. – Frances Donegan-Ryan (@FranceDR)
  • Google first. i am sure whatever question i have, someone else had it first. – David Cox
  • I usually reach out to my PPC team as well. If I can minimize the time to learn the better for me. – Garrett McGregor
  • Coursea.org to learn to program models; forums if I have a specific need; asking my BI manager! – Rachel King
  • I like youtube, because I can see step by step how to do things. – Andrea Cruz

PPCChat Sponsored by

CallRail LogoCallRail is a call tracking platform that brings enterprise-level call analytics to businesses and agencies. CallRail makes it easy to track which marketing sources and keywords make your phone ring. We provide call tracking, recording, and analytics for PPC, SEO, web, and offline marketing campaigns. With CallRail, you can create tracking phone numbers instantly, get reports in real time, and increase your advertising ROI by learning which campaigns and keywords deliver valuable phone leads.

CallRail

Resources

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.

Participants

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

• Matt Umbro (@Matt_Umbro)
• Dallas Stevens (@DDP_PPC)
• Paul Kragthorpe (@PaulKragthorpe)
• Andrea Cruz (@andreacruz92)
• Andrey Kozlov (@WhoIsAndrey)
• Brooke Townsend (@btownsend13)
• David Cox (@dcoxdesigns)
• Davis Baker (@davisbaker)
• Emma Franks (@akaEmmaLouise)
• Garrett McGregor (@mcgregor212)
• Glenn Schmelzle (@heyglenns)
• Joe Martinez (@MilwaukeePPC)
• Leo Sussan (@lsussan)
• Maria Corcoran (@mariacorcoran)
• Mark Sullivan (@mpsulli)
• Rachel King (@rachelking237)
• Robert Brady (@robert_brady)
• Steve Gibson (@stevegibsonppc)
• Steve Seeley (@SteveSeeley)
• Tanner Schroeder (@TannerSchroeder)
• Theresa Zook (@I_Marketer)
• Tim Halloran (@timmhalloran)
• Timothy Jensen (@timothyjjensen)
 

Streamcaps > Excel

This is a guest post by Paul Kragthorpe; works at WebRanking in Minneapolis, Minnesota.
Connect with Paul @PaulKragthorpe, and Google Plus.

Be Sociable, Share!

Tags: , , , , ,

Leave a Reply

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

Sponsored By

Recent Tweets



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