I dabbled with mods for certain games in the past and made note that they didn't help the game. On the contrary, they most generally made it worse inspite the good ideas, and created more frustration and hassle than was worth tolerating for what little they managed to achieve. So I stopped using them.
I think, if the game can't stand on its own feet, mods can't help. And it if can, mods aren't really needed (though I don't oppose having support for them).
I respectfully disagree with your subjective experience and opinion regarding mods.
YOU properly may not find worthwhile engagement, but, there's a whole population demographic judging by not only the creators, but active consumers downloading mods on sites like Nexus alone.
There's even a side industry of Youtubers that go out of their way to have "This week in mods for game XYZ" reviews.
I'm not saying my opinion is better than yours or yours better than mine.
I am, however, saying that if you don't want/like/care about mods, you don't have to use them if they're available. No one is forcing you.
A developer not offering mod support, because some lot might claim it does nothing to benefit the game, on the other hand, does force those who benefit from the modding community to sometimes make a choice.
For instance, Cyberpunk 2077 is going to be a hard force First Person Perspective game.
FPP gives many folks motion sickness, and for those it does, they benefit from using Third Person Perspective. If TPP isn't offered as an option, the modding community will provide.
Some of us are color blind, and the modding community offers solutions to adjust the game that are often not available in the options menu. Many other visual and audio adjustment options often not available in the default options menu are typical offerings in the modding community, like Depth of Field emphasis, Field of View adjustment, improved textures of those of us with higher end systems and even nerfed textures to accommodate those with systems just barely capable of running the game so they can actually play it instead of having to try getting a refund.
Not everyone has the taken for granted privilege of every optimal condition to satisfy game experience as intended.
On top of those extra accessibility features that are prevalent within the modding community, there's also those of us that get a little tired of the restrictive nature of variety that's often the case when ti comes to almost every game. There's only 10 costumes worn by NPCs in area XYZ and none of those costumes are player wearable? Well, mods will give you 100 more. Investing a whole week of playing Fashion Plate Barbie and Ken with the character YOU created might not do anything for you, but, for others, they'll spend that week and more on playing character dress up alone given wide enough variety and selection that's often the case with mods.
... and then there's bugs. Every game typically has them. They get fixed ... eventually ... well, usually, but, you know who jumps right on bugs and has bug fixes sometimes in the first days of a game launch? Modders.
Modders are by far the best, quickest resource and solution to early bug fixes than, say, having to wait weeks or months for the dev team to patch something that only seems to effect 10 out of every 1000 players.
This is only a tiny slice of how modding benefits those that subscribe to the community.
There's people that add whole quests, entire new lands and stories, animations, voice acting, and even DLC sized content.
For Skyrim, there's a whole game conversion mod called Enderall, that completely converts the game to a NEW and different game ...
Polygon Enderal Review
... but, it's understandable that doesn't interest some folks.
For many others, however, it does.