I dont care about singleplayer at all. Singleplayer is for a week, maybe two. Multiplayer (good multiplayer) for 6-10 years. By the way, the best singleplayer happens when you control Mr.47

I prefer Mr. 44.
But whether or not the campaign is fun or not determines the replay value.
You can only experience the story once, but you can experience good gameplay a thousand times.
Some games have great campaigns, some are repetitive,some are just plain terrible, and some have Ninja Vision.
[Press (B) for Ninja Vision = lol]
The usual suspects for a lasting campaign would be:
Difficulty - A challenging game can keep you coming back for more like a terrible song that sticks in your head like a needle.
"Epicness" - Not only how awesome it is the first time, but how awesome it manages to stay for the next 10 times.
Teh Lulz - Halo would be a good example, where anything can happen. You could snipe that guy... Or you could accidentally hit an explosive that sends a traffic cone flying at your face with lethal speed. (C-Owned!)
Keeping It Fresh - a lot of games use different methods for this, like changing story-lines, changing environments, ect. Left 4 Dead is a good example, where zombies, weapons, and types of enemies are spawned randomly albeit with a filter-type thing that determines how well you're doing, and how tough the game should be on you.
That's my idea of it anyway. Give me a cat.