There are so many great mods for Civ4 it can definately be a little overwhelming.
Here is a very brief rundown:
If you are at all interested in Sci fi strategy games, definately check out Planetfall or Dune Wars. Fantasy, download Fall From Heaven. If you want an expansion pack, check out Legends of Revolution. If you want a historical Simulator try Rhyse and Fall of Civilization (RFC), if you want the biggest baddest thing out there and have a good computer, try Rise of Mankind: A New Dawn. If you want a prettier game with new art, but no game changes, Check out BAT.
For a longer version in more detail:
I really apreciate everyone who suggested Legends of Revolution. I just released a new update for it today as well, version 0.9.9 (wanted to do a final 1.0 release, but being the perfectionist I am that must wait until I compile a decent soundtrack to replace the crappy music in the Modern era, I don't understand it, all the other era music is great but the modern era really dropped the ball, and there was such great music over the last century to choose from). Legends of Revolution is built around the RevolutionDCM core, which I have been putting most of my work into over the past few months (the reason why there wasn't an update since March for any LoR fans that were curious). The RevolutionDCM core is the core for most of the "expansion" type mods such as Rise of Mankind, Quote Capita, Diversica, and CCV. RevolutionDCM adds in all the new game concepts like Revolutions, barbarian civs, start as minors and such people really enjoy.
For what it's worth I'd suggest trying one of three mods utilizing the RevolutionDCM core if you are looking for an expansion mod (depending what you are looking for), keeping stability and bugginess in mind would be Legends of Revolution, Rise of Mankind: A new dawn, and the straight RevolutionDCM mod itself (I also have never tried Mamba's Diversica mod, so it may be really good too, I just don't know, I will say I haven't seen lots of bug reports in his thread, which is generally a good thing).
Legends of Revolution has about the same content added as BtS did to vanilla civ4 so it's not too big and many people find it to be "just right". One of the best things about it is it's 5 star stability rating; it's performance and stability is unparalleled when compared to the other "big mods". If you can play standard BtS on a huge map with no problems, you can play LoR on one as well (but of course if your comp has problems with huge map in the unmodded game it would only be compounded by the added content in LoR).
For many people looking for mods they want alot more, not just something that plays like a mere expansion pack you could see Firaxis making. For them I'd suggest trying Rise of Mankind: A New Dawn. RoM: AND is simply The mega mod available, no other mod will give you the content it has with so many added features and options it would be impossible to count or describe them all (you play from the stone age until a far off transhuman era with 100s of new added units and techs, as well as there being dozens of new game options to change overall gameplay to your liking, such as 1UPT ). It is a bit demanding on computer resources though and I'd give it a 4 star performance rating as there are some bugs and you really should have a pretty high end comp if you play it on bigger maps; but it is definitely playable and you can tell Afforess put a lot of effort into it's stability before he left.
Now as for simply playing the RevolutionDCM core itself. Well there are alot of people who simply want an unchanged game for the most part (base BtS does have alot of content in it already). RevolutionDCM is base BtS, but you have the option of turning on Revolutions, or trying out Barbarian Civ, everything is toggleable. It's simply a great and stable game core using BUG and Better AI with some new optional game features to spice things up and customize the BtS experience how you like it.
As far as unaltered Gameplay mods out there, there is of course the BUG mod, which almost everyone on Civfanatics seems to use (why wouldn't you?). You can also download Fuyu's Better BUG AI mod to get the BUG mod with a better, smarter AI.
Lemon Merchant's BAT mod is also great for an unaltered experience as it simply takes the improved interface of the BUG mod and throws in all the beautiful ethnic unit and city art from Varietas Delectat.
The other thing to remember is that there are also incredible Total conversion mods out there. For Sci Fi fans there is Dune Wars and Planetfall, which are just incredibly well developed new games in their own right. For fantasy of course there is Fall From Heaven and all it's mod mods. I haven't played much of these though to be honest, but you can tell they are awesome. If you're at all a fan of Sci fi, definitely check out Planetfall and Dune Wars, you will be blown away with what some of the modders out there have been capable of doing with the Civ4 engine (you almost wol't even recognize it).
)
