For me, most of the mods I use are little quality of life things. I like to get mods that improve inventory handling, for instance.
Kind of a funny (but not really funny) mod story is that I realized as I was getting ready to play Megaquarium for the first time that it had Steam Workshop support. I wasn't too excited to start yet another tutorial, so I wasted time by looking through the mods. I found one that seemed interesting, that added new items for the gift shop, and subscribed to it, but I didn't activate it when I finally started the game.
Well, I played about 5 campaigns before I realized that the game automatically activates any mods that you subscribe to and that I had been using that mod the whole time. I had been thinking that the economy was a little easy, but hadn't realized that I had a bunch of things to sell that actually weren't in the vanilla game.
Alternate Start - Live Another Life it adds all sorts of alternative starts for your hero, I think this is an interesting feature. At a minimum, I like it when you can add something of your own, and not follow the standard
For Bethesda games (games which I’ve modded the most), I enjoy mods that let you skip the intro, such as the one mentioned by @stormluisss . I especially like the one in Skyrim where you can choose a totally different starting location and start with different items/weapons.
Other mods that I look for the most are small quality of life changes that add to the overall immersion. Some examples will be matching your walk/run speed with NPCs, automatically lowering your gun when it’s drawn in FO4, rain stopping from going through roof surfaces and clear glass windows to look outside in Oblivion. Small tweaks like this make the world more believable and helps enhance my immersion with my character.
I do also enjoy graphical mods usually, though not total overhauls. I like the ones that just enhance the vanilla graphics, such as running the textures through an AI upscaler so it preserves the original artistic vision but makes them a little more crisp and HD. On top of that I also enjoy sound mods for small things like enhancing footsteps to be louder and give more oomph, ambience and reverberation, and better randomized NPC quips. Overall, I enjoy any mods that try to enhance the original vision of the game without fully replacing it, anything that helps to add to the immersion of being in these fantasy worlds.