In his post, Niedzielan gave these concise instructions, so I'll just give a quote:
Extract to Documents\My Games\GalCiv3\Mods using WinRAR, 7Zip, or other extracting tool.
Make sure mods are enabled in Options.
To enable mods for your game then go to your options menu, toggle it on, then restart your game.
Alternatively you can go to Documents\My Games\GalCiv3\Prefs.ini and edit "EnableMods=0" to "EnableMods=1".
Minor civilizations are weaker major civilizations
This is a simple mod that slightly boosts the minor civilizations already in the game and makes them into major nations. They are still weaker than the actual major civilizations. This mod is good for big maps where you want a bit more civilizations on the map.
This mod changes the terraforming improvements into no longer being colony unique, but also reduce what the initial terraforming builds allow. This allows for more paced terraforming, but also for huge planets eventually.
Bigger Huge Map
This mod changes the huge map to be a bit bigger as I thought the gigantic map size was too big and the former huge size too small. Thus I went ahead and increased the size of the huge map from 200 hexes to 356. For comparison a gigantic map sits at 512 hexes. In addition I increased the ability to zoom out with your camera as well on that mapsize.
All Starbases Can Mine
This mod makes the mining ring and its upgrades into generic starbase modules. In addition the final modules for the mining ring no longer preclude each other.
Longer Age of Expansion
This mod makes advancing past the Age of Expansion require 30 techs. Always felt like the Age of Expansion went by too quickly.
Mass Effect Races
Exactly what it sounds like. Includes 9 races. Read more in its own thread.