There is a thread on the forums here that will show you the path to the mod folder if you run XP, or the different path if you run Vista, please check it out as it is very complete.
But as a quick substitute:
Basically, you run the game, click on show mod path, and it will create a mod folder in the applications data area for XP and the AppData area for Vista. They are hidden, so if you do a search for either it will locate it for you. Then just open stardock games\sins of a solar empire\ and you will see the mod folder, empty for now of course. My suggestion, make a shortcut and put it somewhere easily found. Then unzip your new mod, and open it, inside should be a folder of the same name, but inside that is the gameinfo and other folders the mod uses.
So, copy the folder and the gameinfo folder, lets say, mod1.zip has >mod1>gameinfo, etc...
But all we want is the name folder and the gameinfo, etc inside it. Not both the name folders, if i am as clear as mud.
just mod1/gameinfo, etc...
Copy that to the mod folder.
Start your game and select options
At the bottom select mods
your new mod should be in the list to the right, if not, then its not where we think it is. But if it is!! Yay!!
Select enable mod with your mod highlighted.
When it loads it will pop up a new picture and the music will resume and that tells us its loaded.
Then just go back to the main menu,
select new game
and make your options, fast research, unlock or lock teams, etc
then press start and awaaaaaayyyyyy we go!!! 
Have fun and keep that mod folder shortcut handy, you will probably want to try a few to see which ones you like!!
Take care,
-Teal
