While I love the traditional Star Trek and Star Wars mods, I've played plenty of those in other strategy games. The Star Trek one for Distant Worlds is particularly good.
That being said, I'm looking forward to mods in setting that I've always wanted to play a grand strategy game in with no chance to thus far. So my top contenders would be Mass Effect, Warhammer 40K, EVE Online. The idea of controlling the Turian Hierarchy, Imperium of Man, or the Gallente Federation on a macro scale really excites me.