Getting Away From Our PPC Campaigns This Summer

Having just finished marching in three parades and playing Taps at Memorial Day observances to honor our deceased veterans, I find myself looking forward to the more relaxed days of summer, and wondering how in the heck I can get away from my PPC campaigns this summer.

Wouldn’t it be nice to take the whole summer away from PPC campaign management? (iStockphoto used under license.)

It’s a pipe dream, I know, because PPC campaigns know no rest and take no vacations. They have a remarkable power to command some slice of our attention 365 days a year, 7 days a week, and 24 hours a day. No matter where we are in this world, our PPC campaigns are always just a tempting user login ID and password away.

Of course, there is no way that any of us can get away from campaigns all summer long, but certainly we’d all like to spend more time out-of-doors and out-of-reach of search query reports, A/B ad tests and so on. My personal goal for this summer is to grab back at least some of the time that PPC campaigns have been chiseling away from my personal time over the years.

So, for this month, I’d like to offer a few ideas for stealing back a few minutes here and there from repetitive everyday campaign management tasks. Next month, I’ll start into the multi-part series on optimizing for Bing Ads that I mentioned in last month’s column on Enhanced Campaigns.

Saving Time A Few Clicks At A Time

It’s amazing how many repetitive tasks we do on a regular basis and how many times we repeat the same keystrokes over and over again as we download reports from Analytics, AdWords, and Bing Ads and pull them into Excel for analysis.

Before we begin analyzing data, we usually spend a few minutes conditioning it – moving columns around, reformatting column headers and widths, and setting formatting for percentages and currencies, adjusting number of decimal places, and so on. For any given report, this may take only a few minutes, but over the course of time, this wasted time adds up.

There are at least two ways we can begin to claw that wasted time back in the repetitive reporting processes we do.

A good first step is to take time to pre-format our reports before we download them instead of accepting the default data column order and having to reformat things in Excel later. Both Bing Ads and AdWords allow us to specify the data column order, so a little mindfulness before creating our reports can eliminate a lot of reformatting keystrokes later on.

Second, once we get our data into Excel, we can automate repetitive reformatting actions such as standardizing data formats for currencies, dates, and percentages, or widening columns, adding bolded header text, freezing the top row of data for easier scrolling and so on.

Instead of manually formatting our data one column at a time, we can record Excel macros that can reformat a column or an entire worksheet in a single keystroke. Even without a lick of programming skill, we are able to create some reasonably powerful macros to automatic our repetitive steps. The macro recording function simply records the keystrokes you make and lets you save that sequence for future use.

Excel macros are also great for saving time on infrequently performed tasks which involve multiple steps you can never remember. A good example is cleaning up search query lists that come from Google Analytics or other Web analytics tool.

Keyword data from web logs or analytics often contain special characters that bog you down.

When you bring these Google Analytics (GA) lists into Excel, the raw search queries contain all sorts of special characters  (+ – [ ] ( ) “ ‘ / \ , . ) that don’t sort well or which display as #NAME?  because the keywords have + signs embedded in them which Excel, by default, adds an equal sign to and interprets as a function.

The manual solution is to do “Search and Replace” 12-15 times to clean out the special characters. Alternatively, we can record a macro as we are cleaning the list, so that the next time we need to do this, we can steal back time by accomplishing the same thing in one or two keystrokes.

To record your macro, go to the developer tab, and simply click Record Macro.

Excel will present you with a pop-up to specify the name of your time-saving macro, the hot-key combination you want to assign to it, and where the macro will be stored. When you are done formatting your file, click Stop Recording

When you are done formatting your file, click Stop Recording.

Recording a macro is very easy to do, but you have to be very precise with your keystrokes when creating it, because the macro can only do exactly what you’ve trained it to do. Later, when you want to play the macro back, you can either type the hot-key (CTL+Q, for example) or pull down a menu of your existing macros.

Once you’ve created a few macros like this, you’ll be hooked on them and want to explore them in more detail than this overly simplified introduction. For a good discussion on working with macros, check out this (or any article) by my Search Engine Land colleague, Anne Cushing, entitled “How to Use Macros You Find Online in 6 Easy Steps.”

Time Saving Hack For Google Analytics

As I was going through this example, I remembered another time-saving hack we all can use within Google Analytics. It’s not a new one, but it is still relatively unknown to many PPC campaign managers who don’t spend a ton of time in GA.

As you may know, many reports in GA exceed the 500 maximum limit for the number of data rows you can display or download. That means if you have a data set of 5,000 Keywords  you want to work with, you’d have to download 10 files and combine them before you can even get started. Painful.

Google Analytics reports limit you to 500 rows per report.

Fortunately, there’s a way around this limitation by modifying the URL string in your browser bar.  Take a look at your browser bar, and change this:

Change this:  …google.com/analytics/…/explorer-table.rowCount%3D500
To this:         …google.com/analytics/…/explorer-table.rowCount%3D5000

Now, you can grab all your keyword data in one report, like this:

By hacking the GA URL in your browser, you can add more rows to your Analytics reports.

That one time-saving hack can save you a lot of time – especially helpful when you are on deadline.

And, in case some of you already have downloaded a bunch of GA files and need to merge them together, get a tool called Monkey Merge. Instead of opening up each file and then copying/pasting the data into one master file, Monkey Merge will do that for  you automatically.  This tool is really old (2005), really cheap ($16 ) and comes in really handy when you need it.

Conclusion

Summertime isn’t the only time of year to be thinking about saving time and working more efficiently, but the nicer weather does tend to give us all a little more incentive.  I’ve only scratched the surface of mundane repetitive tasks we do all the time. Now’s a good time to look at everything we  do and all the reports we create regularly for ourselves and our clients. Are they all still needed? Is anyone really looking at them? Are there newer, better ways to get them done?

If you’ve got a few favorite time saving tips/tricks and cheats you’d like to share with fellow readers, please share them in the comments below. Happy summer to all. See you at the beach!