The best villains are often the ones that can actually perform their dastardly evil plans and seem half-way competent. This includes mastering the double-cross and manipulation of allies for the inevitable backstab. Kefka's the only one that clearly succeeds in this regard- how many villains can you honestly say have succeeded at becoming a god AND destroying the world as we know it?
*SPOILER ALERT for multiple games*
Ner'zhul AKA the Lich King from Warcraft III and it's expansion has done this as well, but hasn't completely succeeded yet. He was the Orc leader in Warcraft II's expansion, and cursed to become a bodiless entity by demons after he destroyed the orcish homeworld. You face his undead minions throughout nearly the entire original game, and especially in the Human campaign, I was thinking, 'Someone's gotta be a sick, sick guy to make all THIS.' And then he manipulated Arthas into becoming evil. From then on, he uses Arthas as a sort of puppet figure, killing most of the elves and summoning Archimonde (and saving the world to make sure HE could conquer it), all while trying to get Arthas to Icecrown so he could finally get a new body.
Another example of this is from another Blizzard game (they seem to make great villains, somehow). Diablo from his namesake series. The entire first game was him manipulating your hero so he could get a stronger body and damn your soul for all eternity. The second game was you chasing him into Hell to try to stop (and fail at) him from releasing his brothers from imprisonment. Sure, you killed him at the end of Diablo II and Baal was the main baddy in the expansion, but can you honestly say that he might have counted on that?
Kerrigan, of course, must be mentioned. I don't think I need to say any more other than this- in the span of one campaign in the Brood War, she manages to get the Dark Templar Matriarch offed, kill General Duke, kill Fenix AGAIN (possibly for good), betray Raynor and Mengsk, and hand the UED their ass (causing their military leader to kill himself).
Other great villains aren't necessarily great, they might even be cowards, but they manage to perform deeds that you don't expect that give you motivation to finish the job. Can anyone tell me that they expected Aerith to die that suddenly (without looking at a strategy guide) from Sephiroth's blade? Or did you honestly expect Atlas to be playing you like a pansy from the very beginning, or even Ryan to make you lose yourself? And yea, Guilty Spark was an annoyance, but he managed to kill the invincible Sgt. Johnson, and he survived a Flood infection.
*END MASSIVE SPOILERS*