I've never really got into mods, but I have to wonder if after 7 yeras they've actually released any gameplay/quest mods which make Skyrim a game worth playing?
Quest mods? Vigilant is pretty good. I'd argue that it's more interesting than anything in the vanilla game in terms of player choice and consequence, but it also has specific good and bad endings, meaning that any player choice ultimately comes down to doing the "right" thing. Artifacts - The Breton Paladin introduces an extremely high quality set of armour and weapons for Crusader-type characters, and also requires the player to go through a sort of "budget Knights of the Nine" experience. I mainly recommend it because it helps to patch up the lack of Temple content in the game, and also because it is throughly superior to Bethesda's own Creation Club content. Others would suggest Project AHO, which I thought was OK but didn't blow me away. It's still arguably up to the quality of the main game (faint praise). I haven't checled out any of Trainwiz's work, such as The Wheels of Lull, and I doubt I'll ever get around to them at this point, but people seem to consider them to be high quality.
I've also seen some mods that attempt to fix the underlying problems with the Faction questlines, but often they're too numerous and prominent for any modder to properly address. This is probably the main reason that I advocate the "GTA with Swords" position. The game's base content is so stupid and pointless, that there's more fun to be gained by radiant quests/dungeon diving and essentially performing a freeform roleplay, than there is from following Bethesda's structured questlines. One of my main problems with quest mods is that so often they feel completely disconnected from the base game's world, that you need to make a conscious decision to go out in search of them. There's always a nagging voice at the back of my head saying, "You haven't really accomplished anything by completing this" and it's pretty much true. The base game won't react to the decisions or accomplishments made in mods, only in base game content, which all sucks. I even have that problem with the DLC, which I've never been able to bring myself to finish.