I'm just going to quickly talk about one game where I think the writing was both good, and contributed to the game in a very positive way. Paper Mario!
Oh dear. I have gushed about this game before, I think already in this very forum--it is my favorite. You're spot on about how writing gives the game character. Perhaps my favorite thing about it is how it maximizes the universe' (Mushroom Kingdom and it's inhabitants') potential for that character, in a way that Nintendo has apparently forgotten the importance of (R.I.P. Intelligent Systems, I miss thee).
Bowser is the brutal, malicious, and literally draconian entity that he was originally supposed to be--capturing a force of dread, and the ultimate goal of battling it. While still having a head full of lead, and his dialogue having several moments about that, he's actually a villain! Think about the last mario game with a truly villainous antagonist. With Bowser now gone kart-racing, being the butt-monkey of the series, or even teaming up with Mario on his quest for the lulz, that's something that's been completely lost. In contrast, in the first ten minutes of the game, he steals the hopes and dreams of everyone, uses it to kill the protagonist, gloats about it, then kicks his lifeless body out of a window at altitude x000 feet. Seriously. Think about that.
There's so much thought gone into exactly how each character and their dialogue is supposed to make you feel. It gives depth.
Whew. Back on topic.
So, wrapping that to point, writing serves to give a game depth however it can, just like any other facet of the experience. Ideally, they balance the other mechanics to give them more strength, like how (in Paper Mario also, although mostly it's sequel) I wanted to see the tattle text for each enemy because I enjoyed what Goombella had to say. Tangentially this taught me tactical information as well, sometimes without me even noticing, even though that's the primary use of the skill. It reinforced the message to do with the combat, with interesting dialogue.
Or how in the Metroid Prime trilogy, I was compelled to do that with the logbook, craving the scan information of each enemy to learn more about the biological diversity of Tallon IV, and just possessing that wealth of knowledge.
A less similar example would be how the notes in Thief 2, scattered everywhere in the sprawling levels, often can lead to extras, secrets, loot, what have you, or just establish setting. Digging through trashbins taught me about characters and setting, but was equally effective at provoking me to explore more of the game, acting on potential leads or even just mundane curiosity.
I think that game devs. often miss out on tapping into that potential, for writing to make dynamics more compelling, and it's definitely a sign of doing it right if the player doesn't even realize how much motivation it gives them to play and learn more--again, unconscious.