My favorites for a while now have been the following:

Snaking Mod

Let's you build logging camps and piers even if you don't start next to a forest or river by snaking your cities towards those features.  Makes city planning a little more intuitive and not wholly dependent on the city's starting tile.

River City Bridge

Adds a new bridge building that you can build in your cities to span rivers and build on the other side of them.  Again this makes city building a little more interesting.

Champion Bonanza

Adds a lot of champions to the game.  I found that after a few playthroughs I was seeing the same champions again and again and this grew boring very quickly especially since I found a lot of the low level vanilla champions (for Empires at least) to be lackluster.  This mod not only adds a fair amount of champions but it also adds a few really cool quests to get some really unique champions.

Black Market Bazaar

I started using this recently because I was getting tired of seeing the same old items from loot drops and wasn't keen on paying $5 for the Loot DLC (I did end up buying it eventually but for half price during the recent sale).  The mod adds quite a few new items to the game.

At some point I'd also like to try some AI mods.  I would also love to get some new quests (already have the Quest DLC), so if anyone can recommend any, I'd be grateful.

Thank you for the replies! It seems as the mod to start off with is the Children of Storm. Then I'll add Necromancy Revamped,  Black Market Bazaar and Demons and Wizards.

I would like any mod the adds more monsters, armor, weapons, items and spells. Did I miss any? Does anyone know if the ai mod makes the undead from the DLC harder?

Children of Storm offers NEW. It feels lke another new game. Imho just MORE of the same is not interesting. Better try NEW experience!

Yes, just today I upgraded my AI mod (Patchwork mod). Undeads are stronger there. They grow huge and prosperous cities, quickly expand and build massive armies, spreading all over the lands.