Excellent achievements encourage you to think about the game differently, and show off parts of the experience that the expected way to play doesn't offer. Think about "pacifist" achievements in shooters, "Irony" in Bioshock, "Master of All Things" in HD Remix (win a ranked match with every character), "Flawless Solo" in Rock Band 2, etc.
Good achievements are the 500 or so points that I expect to get out of finishing a game on the default settings, being relatively good at it but not hunting down every single collectible. It's good to have a record of "I finished this game" on my profile, and it's fun to watch my score go up.
Bad achievements have an incentive for unhealthy amounts of time played, or counterproductive behavior, or are generally impossible. Guitar Hero 3 is a great offender in this regard, with its achievements for playing with a standard controller, playing with the sound off, playing perfectly with a fellow expert fake guitarist, etc.