I haven't installed any major gameplay-changing mods, myself, but have installed a whole ton of tweak and cosmetic mods.
(1) The natural faces, above, is great - people look waaay better. I got this in a pack along wtih a large assortment of eyes.
(2)
BTMod, which expands the area of and reduces the font for your skills, equipment, maps, journal, etc.
(3)
Call Steed & Saddlebags because it sucks when you forget where you parked.
(4)
The color map mod which is really just prettier.
(5)
The Distant Terrain mod, which makes distant ground look more like ground and less like soup. Seriously, everyone should get this mod if nothing else; there's zero performance hit for a huge benefit.
None of these change the gameplay at all except maybe Call Steed. Most of them are purely cosmetic and do nothing but make an already-gorgeous game look even prettier.
-O