Follow these quick steps to get mods working. First, make sure you have the latest game version, 1.05. The popular mods are all updated for it and won't work with earlier game versions.
- Launch the game, go to Options > Mods > Show Mod Path.
- Extract the mod to that exact path, make sure you preserve the folders. For example if the mod is called MyMod it should look like this: C:\Users\yourusername\AppData\Local\Ironclad Games\Sins of a Solar Empire\Mods\MyMod where MyMod contains whatever folders the mod needs (String, GameInfo, Texture, etc).
If you can't see AppData, go to your Windows Explorer settings and turn on "Show hidden files and folders".
Once the mod is properly extracted, it will show up in-game (same Options > Mods) with the folder name, so in the above example the folder MyMod would show up as MyMod in-game. Just click enable and you're done.