For the longest time, the only mod I used was Gedemon's YNAMP. Although I don't often use it, the TSL maps are quite excellent. Mostly, I played on the Ludicrous map until the latest update seemed to fuss with it slightly. Now I play on the giant.
Its only in the past few weeks I have really started checking out other mods. Some highly positive mentions go to....
Civitas City-States. Very well done and pretty well balanced. Also, I have not been disappointed in other civilizations from the Civitas group.
Gedemo has offered up a number City-States and obscure civs that are quite enjoyable. Their civilopedia entries are quite extensive and the leader dialogue fits the civ pretty well.
ADLER gives us "The Glory of Rome" which offers up Caesar and his Rome, as well as the Eastern Roman Empire (Byzantine) under Constantine. Also from ADLER are mods with JFK and Hitler, as well as a couple fantasy based mods, such as Atlantis. All of these mods are quite over the top OP, but fun as heck and very well done. ADLER even warns they are more for sandbox play and should be played at the highest difficulty levels.
CutesyGoddess also offers some civs from a fantasy (or gaming?) genre that is anime in style. While anime itself is not normally my thing, these mods were also well done and rather fun to play against.
While the mods from ADLER and CutesyGoddess are indeed OP and will generally dominate any vanilla civ, it does even out when playing them in the same game. Just something to keep in mind. It was a hoot watching Atlantis destroy Hitler's Germany.
Last, but by no means least, is the Steel and Thunder units mod. Its the continuation of MOAR units by Deliverator23. For me, its become a must have. I like seeing gatling guns and riflemen and other transition type units or completely unique units.
These are just some that come to the top of my mind right now, but there are many others. Other modders I should mention would include Merrick, ChimpanG, JFD, Sukritact, Seelingcat, Sailor Cat and a few others.
Not every mod will meet every player's tastes, but I absolutely admire the folks who have put the work in to make such additional content for us all to enjoy. So far, none of the mods I have tried have conflicted with each other which makes things even sweeter.
What does bother me is reading some of the more unkind comments on the Steam site where the mods are uplaoded. Some people can get quite nasty if they don't like a mod, or the mod didn't work as expected for them. They act as if they were paying for the content and the modder owed them immediate answers or fixes.
These modders have skills I don't have and do work I am unwilling to. I don't think I could ever complain about a mod I got for free.
To those whiners and complainers, I quote from Monty Python's "Holy Grail".....
"I blow my nose at you!"