I think it all comes down to the game. No, good story isn't a requirement for selling well, but I think looking at the best selling games of all time undermines the success of games centered on story. Games built around story can still do really well, and although they don't do Mario Kart Wii numbers, games like Fallout 3, God of War III and Batman: Arkham Asylum have done really well this generation.
It all depends on the game, and how it is marketed. I replayed Arkham Asylum a few months ago, and it's still a wicked awesome campaign to play through. So much in fact, that of the all the SP campaigns I have access to right now, I can see myself replaying this game the most. For now anyway.
But the reason why B:AA succeeds so well at being a good replayable single player game, is because it mixes great gameplay with great story telling. A game like Uncharted 2 relies more heavily on its story telling than its gameplay to provide a great SP, and so once you've played it through once, you've seen pretty much all there is to see, because the game is so linear (Naughty Dog have of course added longevity to the game with multiplayer), but B:AA leaves the gameplay more open to the player. You feel like you are the god damn Batman, and it's fun beating up these thugs, messing with them with the stealth play and trying out new ways of getting the job done.
I think games like B:AA are the way to go if you want to have longevity in your singleplayer campaign. It's got the right mix of great, open gameplay and great story telling, and that structure I think is what has earned the game all the fans it's got.
A game like Enslaved is very focused on delivering a great story, and guides the player too much. It's like Uncharted 2, in that once you've gone through the game, you've seen all there is to see, and if you come back to replay it, you'll be replaying the exact same, very straight forward game. You can't make the experience your own, because the player is not connected enough to the experience. Enslaved is still a good game, but it's just so extremely linear, that it's hard to feel like you as the player, are having an impact on the events unfolding before you. Uncharted 2 was certainly a better example of how you can draw the player into a campaign like this, not to mention that it is way more polished.
EDIT: Just to mention it, Heavy Rain is another game that seeks to be more replayable and draw the player in, though it does by intertwining the story and gameplay in a new way (even if it resembles old adventure games a lot in structure), and has exceeded expectations wildly. It's a model that needs to be polished, but it's one I think is really, really exciting, and I want to see it thrive alongside games of the Arkham Asylum structure. 