Excel tools to help with Pay Per Click

Day to day working in Pay Per Click, a substantial amount of time is spent on analyzing data and creating reports to communicate to clients what PPC advertising is doing for their business.  It is not enough to optimize your account and automate your reports out of Adwords, however.  Each client has a different set of goals, different ways of processing information and different levels of interest in reading reports.

The more reporting I have prepared for clients the more tricks in Excel I have mastered, yet there are many I have yet to learn.  This week I sifted through some articles on Excel tools for PPC and tried to add a few new tricks to keep up my sleeve for future reports.

Some of the Excel tricks I learned were simple shortcuts to memorize while others were a bit more complicated.  Don’t overlook the more simple shortcuts, however, because these are the tricks I find myself most giddy about!

 “Ctrl”+”Shift”+”5”:  I know how easy and simple this is but I’m obsessed with it.  This shortcut is used to quickly format cost per click, total cost, cost per conversion and other monetary figures.  Simply select the row or column you want to format and hold down  “Ctrl”+”Shift”+”5” to turn the numbers within the pink columns into the numbers within the blue columns without clicking through all the formatting options.  Call me crazy, but this is my favorite.

“Alt”+ “=”:  This shortcut will quickly add up all the cells above within the same column.  Instead of having to write out “=SUM(A1:A50)” you can get the sum of clicks, impressions, conversions etc. easily and more quickly than have to type out the formula.

“Ctrl”+”Shift”+”!”:  This shortcut will apply formatting for numbers.  It will format with two decimal places, minus signs and thousand separators.  This will make your impression columns much easier to digest without having to go through the Format Cells dialog box.

“=IFERROR:” The IFERROR (If Error) function is helpful if you have implemented a formula and you get an error message such as “#DIV/0!”  In this case, like me, you tried to divide by zero and got an ugly, urgent-looking error message.  If you use “=IFERROR”, however, you can tell excel exactly what phrase or value you would like to show up in place of the error message.

“=VLOOKUP”:   This is a formula that I have long heard about but only today (while researching this blog post) took the time to learn.  Thank goodness I did, too!  “=VLOOKUP” can be used to compare data month-over-month, compare data from an old account structure to a new structure, etc to easily see what has changed in your account.  In my first few attempts trying to learn this tool, I struggled with writing the formula.   The formula is “=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])”.  A quick tip is to make sure you create a Table that contains the data you are trying to compare against before you complete this formula rather than trying to select the data range.  For example, I struggled because I tried entering =VLOOKUP(D3,January!D:I,6,FALSE) instead of creating a table an entering in =VLOOKUP(D3,Table1,6,FALSE) which gave me issues.

Pivot Tables: By far Pivot Tables are my favorite tool within Excel.  They allow you to summarize any amount of data in order to see a “big picture” view of what is happening within your account.  It also allows you to filter data so you can easily look at individual campaigns, adgroups, locations, devices, etc.  I learned how to use Pivot Tables by watching YouTube.com tutorials and following along step by step.  If you are an Excel newbie or aren’t familiar with Pivot Tables, I suggest you put this at the top of your “to-learn” list.

Don’t forget that Excel offers many different ways to create visuals too.  Creating helpful charts, graphs and other visual elements to add to my reports for my clients is something I have personally put on my to-do list.  What is an Excel tool you are trying to master?  Or better yet, what do you recommend I learn next?

Tags: , ,

  • Wilde George

    It’s really a useless conversation to go into the specifics of to fix any

    of these particular elements in PPC or landing pages etc…there are so

    many ins and outs to online marketing nowadays the most important thing

    you can do as a business owner is start working with a company that really

    knows what they are doing, has solid communication, and is able to work

    with you to make sure key metrics are being measured and more customers are

    being sold your products at the end of the day. Honeestly this stuff

    changes everyday, I tried to keep up with it for a while, but i ended up

    working with John over at results driven marketing after one of my friends

    referred me to have them setup some remarketing for my shopify store. Real

    smart dude, if its any help to anyone else call them at 325-446-1507 since

    they helped out my biz a lot and I’m always willing to pass along a

    referral to someone who takes pride in what they do.

  • John Carter

    One of the best Google Adwords tips I ever got, from a guy named Simon, was to include a solid mix of the three (four including modified) match types, with the correct proportions of broad, phrase, and exact within each ad group. Most people that get started on PPC don’t think to do things that way. If you want to talk to Simon, you can call him at 219-733-4687.