Some of these mod packs and lists seem ridiculous. I mean, 400-500 fucking mods (!)? Imagine all the garbage and bloat in there. If you were only allowed to install 10 mods (5 gameplay and 5 quest mods) which would they be? What are your ten most essential mods that you just can't live without? I'm pretty sure I'd do something like this for my first complete playthrough since I disliked vanilla but don't wanna go nuts about it. I like the controlled edginess of certain modders but there are plenty out there who are talentless and a bit ill in the head. I remember playing an eerie quest mod for Oblivion years ago and the main questgiver was disfigured. He told this creepy story about being abducted by a Dark Seducer as a kid and kept as a plaything for her as she tortured him for days. I don't think you'd ever see something like that from Bethesda.
The thing is that 90% of the work is done by 10% of the mods in any given pack, and then a huge amount of it ends up being superfluous or relatively minor in terms of what is added. If you had to ask me for five gameplay mods (discounting QOL stuff like SkyUI) it would probably look like:
Morrowloot
Ordinator
Apocalypse, or your spell pack of choice
Ultimate Combat, or your combat overhaul of choice
JK's Skyrim, or (you'll never guess it) your city/town overhaul of choice
I don't have much of an opinion on quest mods. Vigilant is very, very good and I didn't mind Helgen Reborn when I played it. I'm sure there's something to make the College of Winterhold and the Dark Brotherhood less shitty, since the community likes those factions. Oh, I guess Beyond Skyrim: Bruma would count.
I went on a rant a while back that most of the graphics mod you are told are essential are anything but, and often actually look worse (can look at my old posts for examples) but some sort of collective delusion exists in the modding community where bethesda art = BAD, modder art = GOOD. The texture work in the special edition, is largely, very good. Many big retexture packs merely look different by way of color scheme and so on, but arent actually higher quality texture work despite ostensibly often being higher resolution. Good luck trying to explain to a mod addict that higher resolution doesnt automatically make for a better visual, its like trying to explain apples and oranges to a dog. So, i think you should omit most big retexture packs everyone tells you to get, even if only for the fact most of them dont retain the vanilla style/presentation. However, one exception would be some of the cathedral project mods, I'm thinking specifically their grass/landscape mod because it manages to disguise the ground LOD transitions very very well, better than any mod ive ever seen.
Static Mesh Improvement Mod and ruin clutters improved are the only two visual mods i consider "essential". They're simply that good.
I really like to get autistic and go all out with 4K textures and stuff, but you're largely right, and I tend to find that the best texture packs are the ones that stick to the vanilla aesthetic and are therefore less likely to clash with things. However it's also likely that I'm pretty much blind to what vanilla Skyrim really looks like, because I haven't played genuinely vanilla Skyrim for more than 30 minutes since 2011. SMIM is essential for visuals, and I still think the best texture pack for armour and weapons is Amidianborn's from what, 2013?
Weather mods are very overrated, in fact, possibly the most overrated. Vanilla SE weathers are quite good, and some mods, including ones i like, change the contrast and color for the worse. As far as visuals go, the biggest improvements are ALWAYS the result of an enb, and in fact this is one of the dishonest things the community does is they will compare their modded with enb setups to vanilla without enb and be like see how much better this 80gb of shit looks? and actually its the 400kb enb that is doing 90% of the work.
I always end up installing a weather mod but you're absolutely correct that I wouldn't even be able to tell you what they do. Some of them have really bizarre aesthetic choices too, like Vivid Weather, which ends up turning the entire worldspace orange as soon as you get a hint of sunset. An ENB is absolutely essential for playing Skyrim if you ask me, mainly because the vanilla colour balance is very brown and aggressively bland.