While there are some improvements on graphics (different UI images, different background images), gameplay/AI (making most independents unit unavailable for recruitment, so that AI armies can only recruit better independents), most mods add a nation.
Some add an alternate version of an existing nation (New Faith for Ulm or different era for Machaka), some add existing nations from different games (Skaven, Night Elves), some add new nations from myths the vanilla game doesn't cover (my Kalevala mod, still under construction, and lots of other stuff) and some are made up by the author (Alugra, city of superheroes; Amos' various mods, e.g. Travelers).
Conceptual Balance strives for making spells and magic items and pretender choices and perhaps even some nations/units that are weak in vanilla more in line with more powerful choices. The changes are mostly small, but there are so many of them that it can be hard to learn to play in it.
I'm composing a mod that adds a bunch of magic sites, but it's the first of its kind. We only got ability to add scale effects and recruitable units/commanders (outside of capital-only sites) in the last patch, but those lazy betatersters didn't test that the commands actually work so the mod can't reach its final form before we get another patch.
And as was mentioned, there's maps. Besides looking different, many mapmakers add little something into them. Something like few fully equipped Cyclops with their construct bogyguards guarding a victory point province.
:evil