Mostly textures/visual stuff. Dawn of Skyrim/Skyrim Radioactive adds a bunch of stuff to towns' exteriors and interiors, respectively. So far it looks good, but I haven't played a lot yet, so I donno.
Cloaks of Skyrim/Winter is Coming/Immersive armor adds stuff to wear for your character and the NPCs.
Interesting NPCs adds tons of characters to the world with full voice acting, and I think some random quests.
Alternate start so I don't have to see the intro to this game ever again.
A bunch of little gameplay things. Path of Shadows makes stealth more complicated, but I haven't had a chance to try it out yet.
The most important mod, though, is a bear summon that plays Misirlou on the lute. Very lore-friendly.