For me, it's not really about having a great story. I want a good story at the least, but I'm not overtly demanding. What matters to me is the storytelling, how the story is conveyed to the player and how immersed you become in the story, and this is where stories in games shine. When done right at least.
Heavy Rain is a pinnacle for story-based games in my opinion, not because the story is good (it's good, but nowhere near great), but because the story is still so powerful, and the game is built to suck you in, and actually make you feel the story. And the game does that so incredibly well, that I can't think of any other story I have ever read, watched, heard or played that can compare on the overall experience. A book can be exciting, and you can get sucked in and not want to put it down, but you don't actually feel what the protagonist feels. And that's why I play story driven games.
Heavy Rain is kind of a niche though, since very few games actually let you shape the story and do it well too. The Mass Effect games are another high point, because the way the story is conveyed with the conversation system and how your decisions carry on from game to game is absolutely brilliant, but Mass Effect is a franchise of two sides. On one hand you have the story, the universe, the characters which are all great (some of my all time favorites in gaming in fact), and you have the action that (in Mass Effect 2 at least) is great and there are some great set pieces. But the two rarely cross paths, as the action and story are kept as mostly seperate entities, unlike Heavy Rain where the two fold together very well.
I could keep talking about immersion into storylines, but my point is that you don't necessarily need a great story to feel like the story is great, because the immersion can heighten the experience beyond that point. Games like Heavy Rain and the Mass Effect series do a great job with that (and to a lesser extent games like Uncharted 2: Among Thieves, Splinter Cell: Conviction, Batman: Arkham Asylum and others), where as other games do less with the immersion, but focus more on telling a great story, like MGS4.
All the games mentioned for the different categories are of course subjective, and your preferences can be wildy different from mine, so the specific games won't necessarily hold up for you just because they did for me.
