Your favorite game's "awesome story" merely goes through the motions when portraying conflict
The protagonist mulls over destroying the food supply of an entire town to gain some strategic advantage. The team pipes in: "Are we really doing this?", Alice asks; "I guess there is no other way," Bob sighs, and that's that. Once the deed is done the town mayor's elite guard chases the team and shouts: "You will pay for this!". The chase sequence is over. Total casualties: twenty people, and seventy thousand more in a month or so. The incident is brought up exactly once later in the game, where Alice notes that "we maybe overdid it blowing up that food supply". The game is full of this kind of stuff, and is hailed as "exciting" and "eventful".
Your favorite game's "awesome story" is carried by an episodic plot
This is a flaw so old and so pervasive that Aristotle complained about it: just one thing after the other. Oh no, we've got to hit the road! Oh no, the chariot broke. Need to get spare parts. Oh no, the nearby village is full of killer robots... Oh no, the killer robot repellent stocks are in the next village over... Oh no, the people of the next village over are starving and hostile... Oh no, all the emergency food rations have been claimed by bandits, and the bandit leader refuses to negotiate on account of the roadblock to the southeast, etc, etc, etc...
Now of course this is less of a problem if the audience is at least forced to concede "wow, that was some experience dealing with the chariot breakage", "wow, that was some experience getting the spare parts", "wow, that was some experience dealing with the killer robots". But in practice stories are often built this way in a futile effort to achieve a magic gestalt effect where a sequence of forgettable episodes is somehow more than the sum of its parts.
Your favorite game's "awesome story" is one of those pieces of 'environmental storytelling'
Imagine a person who claims that in terms of pure gameplay mechanics, walking simulators are generally superior to soulslikes. They explain that it's exactly the fact that walking simulators do not involve strategic decision making, hair-trigger reaction times, or skill with controller input, that makes them typically such a master class in mechanical design. Because you see, these things are all crutches, and the superior philosophy is for the game mechanics to engage with the player without relying on these crutches, as the typical walking simulator does.
This is what it sounds like to me when someone extols the virtues of the "amazing story" in a game where none of the characters have friends, families, conversations, goals, fears, or first names. At that point you're way past "less is more", you're practicing narrative homeopathy. I'll grant maybe the game is a compelling piece of art, and that's something different.
Your favorite game's "awesome story" robs the player of a basic sense of agency
It is generally not awesome for the player character to join a cult, agree to assassinate their boss's boss, cheat on their life partner, pick a side in a major power struggle, voluntarily inject themselves with an experimental nano-fluid, etc, without the player's consent.
Your favorite game's "awesome story" is a 5-hour affair fit into 50 hours
Half a book page's worth of plot. 4 sidequests, 10 errands, 80 points of interest, 3 broken bridges, 2 days of real time. Half a book page's worth of plot. Repeat.
Your favorite game's "awesome story" falls apart the moment you try to put yourself in any character's shoes and consider their supposed motives and means
There isn't a dull moment: backup plans are revealed, friendships are made and ruined, alliances are brokered and broken, bold gambits are attempted and thwarted. But wait, didn't Alice swear to destroy her father's company? So why did she agree to call in a favor with that elite mercenary unit last mission, when we decided to run a crucial errand that helped stabilize the same company? And where were these mercenaries back in mission 1 the moment things went south and we were surrounded by 30 armed bad guys? Also, isn't this the third time already that Eve's changed her allegiance? At this point the Nutella conspiracy that she is orchestrating goes, what, four levels deep, and she has been able to act perfectly and maintain the deception for each level so far until revealing the next?... "We will bypass the front security using this special security-bypasser that I have assembled for this mission", says Qarxas the alien; this useful contraption has never been brought up before, and will never be brought up again. See also: mind control, parallel universes, get-out-of-death-free cards and time travel. Of this, H. G. Wells famously said: "If anything is possible, nothing is interesting".
Your favorite game's "awesome story" at its core has, let's be tactful and say a pathological fixation on things as opposed to people
The story's central conflict is fundamentally and entirely about the nuke and the facility and the energy field and the virus and the organization and the protocol etc etc. The people are set pieces; at best they get to momentarily be people while caught up in all the above, at worst not even that.
For some reason sequels are extra eager to walk into this trap, thinking the energy field and the virus are what made the original so compelling, so this time let's have the story revolve around 3 energy fields and 8 viruses. Actually what made the original so compelling was the distraught scientist who worked herself half to death on a vaccine and got all the players to root for her because hey this is just like that time they pulled 3 all nighters in a row on that project. Unfortunately the sequel kills her two minutes into the intro, so as to establish that virus #6 is not fucking around and everyone is in really serious danger this time.
Your favorite game's "awesome story" is just a bunch of jerks speaking in riddles over and over
Come, friend; it's time that all questions be finally answered, and all mice go back to their holes, and the mighty be brought low. Or were we ever friends at all? Are you going to surrender to these doubts or push through, like a mother pushes through when she gives the gift of life? Can we break free of the past? Can we forge a future? Have you stopped to consider whether we should? What price are you willing to pay to make that happen? Can you tell the difference between good and evil? Truth and fabrication? Competent prose and whatever the hell this is?
#notallgames
Seriously though, I know you said "for the most part," but I just want to emphasize that there are absolutely I'd describe as downright literary, where the entire point is to tell a compelling story and explore some heady themes. One recent one I played like this was Pentiment, which explores some really interesting history and has a lot to say about religion, community, fallibility, family, etc...
And, I mean, lots of other people have already mentioned Disco Elysium and I could write an essay about it but anyone who hasn't played it should just watch this Jacob Geller video instead.