I've been hearing this question around here lately, probably because one of last year's performance appraisal goals for the QA department was test automation. I think it went something like this...

Managers noticed testers were complaining about being too busy. So they gave Mercury QuickTest Pro licenses to us (most of us had little automation skills if any) and told us to start automating our tests because it would free up our time. Some of the managers offered many of the "reckless assumptions" in James Bach's classic Test Automation Snake Oil article; I think the plan fell flat.

Purchasing an application like QuickTest Pro can get most testers a handful of record-playback scripts that can provide superficial value. Anything beyond that requires a significant investment in learning, time, and creativity. For starters, one must select and understand an automation framework. But even after building the coolest automation framework in the world, one is still faced with the same damn question, "What should I automate?"

I have only been working with test automation seriously for about two years but along the way my small team and I have learned enough to throw a few suggestions out as far as what tests should be automated.

• Automate tests that cannot be performed by a human. An easy place to start is with performance testing from the user perspective. How long does a given user-triggered action take from the user’s perspective? This test cannot be performed manually (unless the tester is willing to do the same thing over and over with a stop watch and the time spans are at least several seconds). This type of test is not designed to find bugs. Instead, it is designed to collect performance information.
  
• Another practical way to use a test automation tool is to write scripts that simply get your AUT into the precondition state necessary to perform other tests? Again, this type of automated test is not designed to find bugs. It's not even really a test. It's more like a macro. Yet, a good tester can use it to their advantage.
• Sanity or Smoke Testing – If your AUT undergoes frequent builds with new bits or frequent builds with the same bits on different environments, automated tests can find configuration problems or regression problems. Building this type of automation library is more ambitious. And getting it to run unattended requires a seemly infinite amount of error handling. A good place to start is getting a few positive tests to navigate through some critical areas of the AUT, performing validations along the way.
• A final answer to the question of what to automate is "nothing". Don't automate anything. On smaller, in-house or custom apps that generally run on the same environment and seldom update, it would be silly to invest any significant effort into writing automated tests.
  

Bj Rollison made a great post on the kinds of tests he automates. I like this post because it reminds me of the test complexities of shrink-wrapped software and humbles my own test efforts, which often feel quite overwhelming.