The problem is that good (for definitions of good involving "open", "non-linear", "varied", etc) gameplay is generally antithetical to a good plot, for reasons that should become readily apparent on some thought. Similarly, a good plot (for definitions of good involving "coherent", "well-fucking-written", "not fucking inane", "not phoned in", etc) is pretty opposed to anything beyond running in a little line, killing cannon fodder in a generic way, with a pre-written character (of course, most of those games have both shit gameplay and a shit plot (I can't think of a single game with a "good" plot at the moment...).
If you have gameplay in which the player defines their own character, the poor bastards responsible for writing the plot have a rather daunting challenge, one that they seem to respond to with "oh fuck, let's just make it so fucking generic it won't fit any character, so at least it will be equal". If the player is further given any real ability to influence events, then they have to write up contingencies for every possible combination of choices, and then the dev studio has to have more voice acting done, they might need more artwork (counting textures and models as artwork), or just break it up so that all the possible actions are independent of one another, and thus don't really have any impact at all. So it's pretty understandable why you get great games like The Elder Scrolls series (and by extension fallout 3), that have such absolute shit plots: it's just not feasible to make a decent plot that fits every character imaginable (or even every rough archetype within which the player will almost inevitably fall) or takes into full account the myriad of things they might do if given actual freedom to influence the plot. At least in those cases, the story is only shoved because "well, there has to be a point to all this somewhere...", and the real attraction is the world and gameplay.
Now, Bioware actually did a good job with Mass Effect's story, if you consider the silly-yet-still-full-of-itself plot and cliched-and-often-outright-awkward dialogue as brilliant satire instead of a steaming pile of shit drama (I honestly do not know their intent, and merely choose for the sake of how great the game was otherwise (even with the obnoxious, consoletastic bullshit like always carrying four weapons, even when you can't hit the broad side of a fucking barn with three of them, the horrifically bad inventory management, the hyper-linearity of most areas, and the occasionally unresponsive controls, specifically automatically snapping to cover when trying to run around a corner or along a barrier, and pretty bad recognition of when a melee attack is appropriate, not to mention grenades clipping through walls and flying off the map...) that it was satire), but that's more an exception of what constitutes "good" gameplay: you don't really get to create your own character, you get to play around with a face editor, and then be either a stick-up-your-ass paradigm of space-cop-itude, a violently psychotic, corrupt space-cop, or both; you do actually have some apparent power of the course of the plot (ignoring the negligible effect the order in which you perform missions has), though most aren't fully realized within the scope of the game
freeing or killing the rachni queen, saving or lolariously abandoning the assholes of the council, WHETHER OR NOT AERISthe whiny-little-[dummy] or the psycho-zealot-[dummy] DIES!?!! (that actually has an immediate effect, though seeing as how the whiny [dummy] is useless, dead weight (so much so that his name eludes me) it doesn't seem to be a big one)
and the gameplay is fairly repetitive over the course of a game (though still great, specifically on the highest difficulty, and it varies between runs based on the type of character you chose).
Now, for everyone complaining about sex and gore (I've not seen the trailers in question, as my internet is far too shitty to handle that, so I'll speak generally): if it's handle in a sane/appropriate manner, and backed up by strong gameplay, then I call it a positive inclusion. Not out of any inherent value in and of itself, but rather in that awkward and childish censorship is patently offensive, and shows a great deal of either cowardice (in bowing to the will of the kind of asshole who screams about it and starts lawsuits/bullshit legislation) and/or squeamishness central to the design process, neither of which are positive traits. It can also lead to hilarious fucked up things like what happens when you loot the armor off a raider in fallout 3, when the armor consists, say, of hotpants and a bra made of mininuke nosecones (actual armor in the game...), and the filthy raider is suddenly covered in a spotless white t-shirt and boxers, both of which are larger than the armor that was ostensibly over them... Now, when used as a central selling point, and coupled with terrible gameplay, then it's just a "LOOK AT ME LOOK AT ME, TITTIES AND BLOOD TITTIES AND BLOOD!!!!1!!" gag to try to sell it to frattards and twelve year olds.
<toad-edited for some of its inappropriateness>