I use dozens of mods. It's why I played DAO at least twenty times, while I played DA2 only 4 times.
Most of them are cosmetic mods, giving you new skins, colours and hairstyles, armours and weapons. I like to make new morphs, replacing NPCs etc..And then there are few 'fixes' I couldn't play without - like removing glittering spell effects, weapon trails and ground circles, hidden interface during screenshots, shutting up the nug when Leli sings, making Shale's steps quieter, daytime camp, placing swords and daggers at hips...
And then I have mods for Zev - Zevran ASAP, Zevring, ZDF, Zevran Romance Scenes, Zevran Open Wedding... 
I do have a few 'cheats', like adding a points via console, and Add any item deluxe, but I use those only when I create a character just for one screenshot and want them to wear a specific armour in it.
I don't have any mod that changes the game. Well, currently.
I had a playthrogh where I played as Zevran, where I played a child, etc.. It's a lot of fun. 