As others have mentioned, the VaLVE games have had some of the best storytelling. HL (G-man), HL2 (We don’t got to Ravenholm anymore), HL2EP2 (Vance!), Portal (mm, cake), Portal 2 (Cave Johnson).
As others have noted, I think part of VaLVE’s success is casting the protagonist to be a silent “everyman” or “everywoman.” (also in this class goes The Stanley Parable). Granted, Chell and Dr. Freeman are white “everypeople,” but it seems a large part of their appeal is they don’t voice thoughts that might privilege one particular worldview. Indeed, Blue Shift and Opposing Forces explicitly shift the eyes of the protagonist to the supporting cast and the enemy, respectively.
When you write for a book or screen, you write for that character. But, when you write for a game, you need to write for a player who will inhabit that character. I think that’s a fundamental difference, and in some ways a more complex audience demand.
As noted, VaLVE tries to bridge the gap from player-to-protagonist by going the “silent lead” route. Some better-written RPGs (Morrowind, not Oblivion or Skyrim), use a captivating ensemble cast to tell the story, reducing the player to an interchangeable cipher.
Of course, some good game writing goes the Hollywood route, casting the lead as a full-bodied character, which draws the obvious comparisons to cinema (Mass Effect series, BioShock series).
Yeah, well some people like movies just for the special effects.
I love good gameplay. Shadow of Mordor has sweet gameplay. But, aside from a cameo or 2, the story is not all that special.
On the other hand, the combat mechanics in every Mass Effect and every BioShock is underwhelming and boring, yet the worlds they build and the stories they tell are wonderous.
The entire intro of BioShock: Infinite from the rowboat to the baseball is an utter classic for me. I can envision art critics dissecting it the same way people dissect the beach scene from JAWS.