I'm also relatively new to mods but having taken the plunge, I'm hooked.
The ones I have tried:
Ascension (I am not one for really nasty battles but Ascension adds some additional roleplay elements which I find essential now that I've played it). I generally install Tougher Illasera, Tougher Gromnir and Tougher Yaga-Shura, and leave the rest. I didn't enjoy Tougher Demogorgon.
Angelo NPC (because I dislike Anomen) - my review is here
Edwin Romance (also because I dislike Anomen - a woman needs more choice!)
Romantic Encounters (just don't install the Edwin component if you have the Edwin romance installed)
Unfinished Business (haven't tried all of it, but the parts I've tried were good)
Assassinations (THE mod if you want to kill people instead of, or as well as, helping orphans and finding lost gongs)
Iylos NPC (a non-romanceable male monk for ToB only. He's an interesting character, and he kicks arse. So if you ever want to try an SoA only NPC, you can replace them with this guy once you reach ToB. Sorted. You can download him from Spellhold Studios).
There are still many more I intend to try - trying Turnabout for the first time on my current game, once I reach that far. I'm very wary of bugs, but most of my mods have behaved very well so far. I think the key is not to install too many at once, and do a clean install between each playthrough.