SkyrimRE is awesome, it adds alot of shit to the game including changed talents and abilites, new racials, AI upgrades and a bunch of other neat features.
Skyrim 2k Textures is also one. It upgrades the textures but it requires a bit of a horse of a pc. I ended using the lite version it's still better than the original.
So.. So many... I personally enjoy the fletching mod, smelting (allows you to melt down equipment you find into bars), Theres ALOT of awesome armour and weapon mods along with player made quests and dungeons. I personally find most of my fun by turning a entire village into animals and watch them go about their business using console commands.