I'll add that achievements should be an addition to the game, not an afterthought. As people have said there's too many cases where developers just make their game then slap on some "Kill this boss" or "Complete this level" achievements because they feel obligated to do so. It's ok to have SOME of them, but at least keep the ratio of obligatory achievements and good, thoughtful achievements at a 1:1 ratio, at least.
The BEST achievements are ones where the devs put some thought into it, whether it be weird gimmicks you could do or outright challenges. HL2 and WoW have some fine examples, though obviously they also both have the obligatory "Complete Ravenholm" and "Reach Level 85" achievements. Trying to throw Dog's ball into the hoop with the Gravity Gun in HL2 is an example of a weird gimmick achievement, and "Keep off the sand" for a challenge.
In WoW, you had things like /love various critters for one achievement, or achievements to kill a boss while doing something weird and tricky or just outright stupid. Such as getting Svala Sorrowgrave to kill an abomination with her sacrifice. Abominations aren't even a part of the boss fight, you literally have to go and pull one of the nearby Abominations, weaken it to about 10% health without killing it, then cause Svala to start her Sacrifice attack, and lure the Abomination underneath the sword and let it sit there until it hits and kills it.
THOSE are the kinds of achievements that I love most. They basically either challenge you, whether it be platforming skills like stay off the sand, or a gear / tactic challenge like you see in WoW, and often make you think outside of the box.