Maybe I didn't do enough researh, but most of the mods for Morrowind seemed to be improvements upon the same game or additions. This doesn't excite me that much.
Counter-Strike is basically a completely different game than Half-Life. Same engine, but that's it.
Most of the mods for NWN are the same, they completely change the game. It's also super easy to use, as even an eternal n00b like myself was able to put together a decent dungeon.
I agree that the original campaign should always be the focus of a game, but I don't think it's the only thing that should be judged.