On windows XP (Vista has a different user folder location), you'd simply unpack the zip file the mod comes in to C:\Documents and Settings\your username\Local Settings\Application Data\Ironclad Games\Sins of a Solar Empire\Mods, keeping the folder structure so that the mod has its own folder in that location. Then start the game up, go to the mods page in the options, and enable it.
