As I said over at Osbon's, the Achievements I enjoy most are the ones that reward lucky, odd, and crazy things. Crackdown has an Achievement for using the harpoon gun to pin three or four people to the same car. It also has an Achievement for leaping from the top of the tallest skyscraper and falling (harmlessly) into the bay below. Those aren't just accomplishments. Those are memorable moments. Trophies and Achievements can help preserve such fond memories.
Completing a level or chapter is often not a memorable moment. Every adventure game I play has Achievements for completing each level, but I don't remember many of those times. Still, having Achievements for the completion of levels is good, because it helps friends keep track of how far you are in a game and inspires friendly conversation.
There are ordinary moments like these that are worth commemorating (rewarding effort can be done in better ways). But, overall, Achievements should celebrate extraordinary moments. Those are the moments worth remembering. Those are the moments that gamers are most inclined to share, thereby promoting the game and aiding its longevity, among many other benefits.