CBM is by far the most common at least in this and associated communities, currently in version 1.92. It rebalances units, items, and spells to make more strategies playable, and does some nation balancing.
CBM at this point includes Worthy Heroes, Endgame Diversity Mod (EDM), and UWGIM (UnderWater Gameplay Improvement Mod) within it.
Mod Nations are probably the next most common. They add a new nation to the game. The existing mod nations are variously balanced - some being grossly overpowered (see just about anything by Amos), some being underpowered (usually a sign of a nation still in early developement), and some being about right. The most frequently used mod nations are probably the Warhammer nations, but there's a number of other nations that are also considered fairly balanced. Most of these are included in the Mod Compatibility project, available here:
http://z7.invisionfree.com/Dom3mods/...?showtopic=317
There's a dom3 community at Something Awful which uses some mods ultimately derived from CBM and its bundled mods. Awesome Gods takes CBM's pretender rebalancing, and cranks the awesome up to 11. Its probably not balanced anymore, but the gods are definitely more awesome. Awesome Endgame started with an early version of EDM, and tacked on more changes to endgame content. Awesome Heroes seems to be just starting, and is fiddling with Worthy Heroes. Generally these mods eschew balance in favor of crazy.
Site-focused mods generally broke with the last dom3 patch.
Finally, there's a number of mods which do things like change basic gameplay. SemiRand and Chaos make the map crazier, and are generally intended for single-player play, but might see use in MP. Randomocalypse and Cataclysm are relatively new but have seen a decent amount of MP play already. The first builds a random nation for each player, the second gives every player a generic and weak range of national units, but has abundant sites with powerful mages and troops - so what you find in your travels is what you have.
I think that covers much of hte diversity of what mods do.