There is certainly no "best mod." All mods add something different and unique to the game. The "best" is just the one that matches your tastes the most.
Some "total conVersion" mods include the AST Mod, the Star Trek Mod, the Babylon 5 Mod, the Star Wars Mod II, and Adamant Mod. Proportions (as well as AIC) mod drastically alters the pace of the game, slowing everything down. The homeworld is very important. Pirates and Nomads mod allows you to play as a Pirate or Nomadic race, which both do not live with colonies, but through other means. Devnull adds a number of tweaks to some of the game's unbalanced items, as well as some new stuff, and is the home of the infamous Space Monsters. Highliner Mod makes it so that only really big "highliner" ships can travel between systems, and regular warships can not. This is done by making them use the "fighter" hull.
For a complete listing of mods, visit
http://www.malfador.com/se4mods.html