Achievements (out of game) remind me of the good old days of highscores in arcades. That's why I love them. Keep in mind Oblivion was a launch window game, just about all the achievements back then were just for completing the story or whatever. They were some new thing devs had dealt with before. But now, as we've seen with Fallout 3 bethesda is ready to do some more creative achievements and I hope that trend continues with Skyrim.
In my opinion about half the games achievements should be story/quest related. With the rest being silly, fun, and some that are actual
achievements. As in challenging.
Of course trophies, and steam achievements will be there too. I just used achievements as my example because I play on X-box 360.
Edit* Another great thing about achievements is they make you do things you might have never tried before. Such as:
Completing Dark Carnival while carrying Gnome Chompsky all the way to the end in L4D2.
Performing a assassination on a elite to survive a fall that would have been fatal (campaign) in Halo Reach
Doing a 500 foot long wheelie on a motorcycle in GTA IV. etc.
All things you might not have tried otherwise. I know I would have tried the first two even if there wasn't a achievement for it ^_^