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.

Vacation from PPC Campaigns

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.

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's Record Macro function is found on the Developer Tab

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.

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.

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!

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Channel: Strategy | Paid Search Column

Sponsored


About The Author: is President and founder of Find Me Faster a search engine marketing firm based in Nashua, NH. He is a member of SEMNE (Search Engine Marketing New England), and SEMPO, the Search Engine Marketing Professionals Organization as a member and contributing courseware developer for the SEMPO Institute. Matt writes occasionally on internet, search engines and technology topics for IMedia, The NH Business Review and other publications.

Connect with the author via: Email | Twitter | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • Larry Kim

    Matt – are you a veteran?

  • Matt Van Wagner

    No, Larry, but I like to do my part to honor those who have served and have been playing trumpet (bugle) for American Legion ceremonies for the past 40 years. I also serve on the board of Building Dreams for Marines, (www.buildingdreamsformarines.com) a non-profit here in NH that assists NH based Marine veterans who have physically-limiting conditions by providing life-enhancing modifications to their homes. We’ll add ramps, widen doorways, redo bath and living spaces – whatever it takes to help them live more comfortably in their homes.

  • Barb Young

    Hi Matt – great post. Thanks for re-introducing me to macros!
    I’ll share a tip that saves us time every day in Google Analytics using the “Shortcuts” feature. One of our lead gen clients has unique ‘confirmation’ urls that we analyze almost daily based on the associated Campaign and City.

    Here are our time saving steps for this and other frequent Content reports 1: Content>All Pages, 2: Apply a filter for “confirm”, 3: Select Advanced Segment for “Paid Search Traffic”, 4: Select Pivot Table icon, 5: Set “Secondary Dimension” as Campaign, 6: Set “Pivot by” as City, 7: Set “Pivot Metrics” as Unique Pageviews.

    To eliminate the need to constantly recreate these steps, we then click the “Shortcut” button above the metric graph and save the report as “PPC Leads Pivot Table”.

    Now, all we have to do when we want to run the report is to select the report from our Shortcuts in the “My Stuff” area and we have our instant report with segment and filter intact. Shortcuts are also huge time savers for our Ecommerce client reporting.

    Last thought: Custom Reports – can’t live without them and save us a bundle of time and effort every month when we need quick access to complex combinations of filtered data.

  • Matt Van Wagner

    Excellent tips, Barb, thanks! Definitely great way save keystrokes and save time.

  • http://twitter.com/sammmer Sam Mazaheri

    Great post Matt. I haven’t used macros nearly as much as I should…

  • Matt Van Wagner

    Thank you, Sam. They are exacting to create, but once you get your macro game on, you can do most recording on the first try, and there are so many ways to use them. Good luck!

  • Eivind Otnes

    The beach sounds tempting! Anyone that know of any good resources for learning how to create macros?

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide