My favorites for a while now have been the following:
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.
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.