No matter the genre, the best villains are the ones who have realistic motivations for what they do. We may not, from our external vantage point, agree with those motivations or the goals they engender, but we can at least understand them.
Some villains are simply villains because they like doing evil things. Psychotics are rarely memorable because they are usually so one-dimensional. Of course, there are exceptions (like the Joker or Hannibal Lector), but they’re compelling because of their well-crafted personalities, not their evil-for-its-own-sake motives.
Many villains are simply greedy or power-hungry. They desire wealth or dominance for its own sake. Such villains could be interesting characters, but their primary motivation is too simplistic to become really top-notch villains. Emperor Palpatine was a much more interesting character in Empire Strikes Back and Return of the Jedi because we still weren’t sure about his ultimate motives. Mystery adds to interest. The Star Wars prequels really cut him off at the knees as a character (on top of all their other flaws), because he was reduced to simply wanting to be in power for the sake of being in power, no matter how interesting and convoluted the plot was that brought him there. Why did he want to become Emperor? Who knows? He just wanted to. A decent motivation would have given him (and, indeed, all of the prequels) a lot more “oomph”.
The best villains, I think, are the ones who call themselves heroes. Think of the absolute best villains from literature, movies, and comics. Michael Corleone doesn’t really like having to do the horrible things he does; in fact much of his interest as a character is derived from his inner struggle against the things that his family honor and tradition forces him to do. Magneto considers himself a hero, and so do many others, because he’s fighting against oppression of his fellow mutants (real or imagined). He’s willing to become exactly that thing which he fears and hates most, and a lot of his own interest as a character derives from his inability to see that truth. He’s a hero in his own mind for advocating mutant supremacy, and considers others villains for advocating non-mutant supremacy.
Darth Vader fits into that last category. Regardless of what most of us think of the Star Wars prequel films, it did establish him as a hero-in-his-own-mind; he went over to the Dark Side in order to save his wife and unborn child. He wanted to end the Clone Wars and bring back peace to the galaxy. In his own mind, those were noble motives that could make up for the horrible actions that were needed to bring them about.
Adolf Hitler (at the risk of invoking Godwin’s Law) considered himself a hero as well, as did millions of Germans in the 1920’s and 1930’s. The salvation of Germany was enough of a motive that any action in furtherance of that goal could be forgiven. In an alternate universe where the Axis won World War II, its leaders would sleep soundly not because they were chortling about all the misery they had caused, but rather because they knew in their hearts that what they did was necessary in the furtherance of the greater good. At least as they perceived that good.
In RPGs this holds true as well. The goblin king who raids the village just to enjoy the wails of the women and the burning of the huts is bleh. The goblin king who raids the village to steal the cattle and collect any silver that might be squirreled away is at least understandable. The goblin king who exhorts his followers to wipe out the humans before they can do the same to the goblins, as a pre-emptive strike against what is considered an implacable foe that has spawned generations of bad blood, becomes almost sympathetic.