Search

25 . 11 . 15

7 Top Excel Tips For PPC Analysts


Fear not non-PPCers, whilst this post is aimed at getting Paid Search specialists to work more effectively, there are still a few golden nuggets that I hope may be useful for everyone in Search and beyond!

Those of us lucky enough to work in PPC will know that proficiency in Excel can be an absolute life saver when it comes to working efficiently. However, we can all be found guilty sometimes of not using this marvellous tool to its full potential. The purpose of this blog post is to highlight 8 top tips that will hopefully turbo charge your PPC output.

Of course, these tips are a merely a flavour of what Excel has to offer for the seasoned PPC Analyst. There is a wealth of resource online and I encourage you to take the time to master this fabulous piece of software as it forms such a big part of our (professional) life!

Our first port of call is not really a tip as such but more of a state of mind…

…Introducing “the PPC claw”

Keep your left pinkie on Ctrl and spread out your hand. A huge array of shortcuts are now available to you (see below). Commit them to memory and you will be amazed by the amount of time you will save! Congratulations, this is now the position your hand will be in for the majority of your (working) life.

John Blog 1

Tip 1) Formulas For Case Sensitivity:

PPC best practice states that the first letter of each word in a piece of ad copy should be capitalised. This allows the text to “pop”. Rather Than Holding Shift Before You Type Every Word, the following formulas allow you to quickly change the case of any cell with text.

johnblog2

Tip 2) Converting GA Dates

As insightful as GA can be many of us prefer to download the data and open up a good old fashioned spreadsheet. If you’ve ever tried to download data from GA that is segmented by date you’ll have found this rather unfriendly formatting that isn’t instantly recognised as a date by Excel.

john3

Don’t worry! Here is a neat little formula to switch around your digits and get you back in line with the Gregorian calendar.

=DATE(LEFT(B8,4),MID(B8,5,2),RIGHT(B8,2))

john4

Tip 3) Max, Min, Maxif, Minif

Manipulating data with precision is a corner stone of PPC campaign management. Go big data or go home! The following formulas are great for getting quick snapshots of raw data or better preparing data to be later pulled into a table or graph.

=MAX & =MIN

Quite self-explanatory. This formula pulls the largest or smallest value in a data set. This is especially useful with dates if you want to pull your data into a table and you’d like your table to start with the latest date or oldest date.

john5

A variation on these formulas is =MAXIF & =MINIF

Again, these allow you to pull the largest or smallest value but this time within a specified condition.
Basically this is combining the MAX/MIN formulas with the =IF formula
The =IF checks to see if a value in the data meets the criteria e.g. <1, >1, =0 etc. Then it returns a value if true or false.

For example, if you want to pull largest date from the list above but only if the quantity is larger than 13.

john6

You must remember to hit Control, Shift & Enter with this formula as it is an array formula and won’t work otherwise.

Tip 4) Data Validation

This is particularly useful for reporting as it allows you to populate your data into a list. At face value you may ask why this is useful and ultimately it is more about presentation. However, should you start producing =sumif tables that are dependent on a =Vlookup with a variable lookup value (i.e. this list) then suddenly you are able to dynamically populate data tables from a drop down menu.

john7

john8

Tip 5) Plus And Minus Entire Months With Ease

If you’ve ever wanted a list of months to dynamically populate with formulas you’ll know that it’s not as easy as you’d think. With weeks it’s fine as there are always 7 days in a week. Always. If only this was the case with months and don’t forget leap years!
The formula =EDATE(A1,1) will add a month to your current cell (where A1 is the current date and the 1 is the number of months you’d like to add to that date). It works to minus dates as well!

John9

 Tip 6) Campaign building aids

If you’ve ever built an account from scratch you’ll know that you can end up with hundreds if not thousands of Ad Groups. This means hundreds if not thousands of ads to write copy for too! Of course, there will be points were you have cross over and copy and paste will become your two best friends.

One quick tip that will help ensure that you don’t duplicate keywords or Ads is the Remove Duplicates tool.

Simply select your data and you’re a click away from Excel removing any duplicate content.

Simple but effective.

I’m sure we’re all familiar with Control F when we need to find something in particular. However, it’s lesser known sister is Control H! This is the shortcut for Find and Replace. This is an excellent tool to edit existing ad copy where one uniform value across all the data needs to be changed from A to B.

Turning your Exact Match keywords into negatives or BMM keywords.

This is a huge time saver in campaign building and is wonderfully simple.

To make an Exact Match keyword negative use;

=”[“&A1&”]”

john11

To make an Exact Match keyword BMM requires a few more steps but is simple enough as well.

Using Find and Replace find a “space” (just hit the space bar) and replace with “ +” (space bar plus a “plus” sign)

john17

This will change the first two words

john12

After this you can use the formula =” +”&A1

john13

You’re done! From now one you can build your Exact Match keyword list with the knowledge that your other lists can be duplicated in minutes!

Tip 7) =LEN

Character count can be the bane of a PPC Analysts life at times. Capturing the essence of a Brand’s ethos, credo, image & promotion in a 25 Character headline is an art form in itself. Here is a quick formula to help you stay within the lines.

=LEN(A1)

john14

This formula counts the characters in a cell. Add conditional formatting for quick reference and you’re done. The conditional formatting is particularly good should clients want to change the ad copy themselves. The visual aid is helpful for those that haven’t committed the character limit rules to memory like us professionals.

In summary, I hope these tips are useful and aid your PPC expertise but I can’t stress enough how much more potential there is with Excel. Mastering this tool will make you a better PPC Analyst and, should you need any more encouragement, it is well known that “Excel Super Users” are paid more on average than their less savvy colleagues!

 

 


Comments