While I think there is a skew to MP & simple gaming action over storytelling for longivity and popularity, if the story is good combined with a good game then I think it can still help. Red Dead Redemption and Assassin's Creed 2 come to mind here. While neither will trouble Shakespear they are both helped by a combination of strong character, compelling setting and a narrative that hooks most people pretty well.
I think that, the usual arguments abouts games as art, etc aside, most people do play games as games - i.e. the emphasis is on the gameplay aspects, and particularly in the West I do think a lot of people see longivity in MP and would rather play the same map over and over again vs people in a pure gameplay environment than, for example, replay a story driven game a few times.
That said, there are plenty of people who will replay a game to re-experience something the same way they'll rewatch a movie. For example, I still think the opening chapter of Half Life 2 is one of the best in an SP campaign ever, and I'll still on occasion boot it up and replay it just to realise how effective it is (and I guess to lament how many SP campaigns have come since and failed to even come close to it in skillful setup).
So as ever it's shades of grey vs black and white. I do think story can result in longivity in sales, just not quite as often as MP and DLC and a big brand franchise currently, particularly in the Western markets.
I personally prefer story/narrative driven games, so I hope they stick around.
Try to be reasonable... its easier than you think...
