It definitely depends on the game. A game like Fallout 3 I have already played through twice and I'm starting my third character. With such a huge world and so much to explore, along with a pretty much infinite number of stat configurations you can definitely play it over and for me it gets better and better.
A game like Halo 3 or Gears 2 you can play over again to find a skull or a different collectible but you aren't playing it over again for the whole experience, just to find a certain item or to get an achievement (At least that's how I feel).
Lastly there's a game like COD4; I played through the campaign, loved the multiplayer but I've never thought of going back to the campaign. There's just nothing for me.
A game like Halo 3 or Gears 2 you can play over again to find a skull or a different collectible but you aren't playing it over again for the whole experience, just to find a certain item or to get an achievement (At least that's how I feel).
Lastly there's a game like COD4; I played through the campaign, loved the multiplayer but I've never thought of going back to the campaign. There's just nothing for me.