Luca Blight is always my go-to answer for this question. I think part of that is that he went out like a bloody star. I'll never forget his last stand:
"It took hundreds to kill me but I killed humans by the thousands!!!! Look at me!!!! I am sublime!!!! I am the true face of evil!!!!"
One villain I never really got the appeal of was Vaas from Far Cry 3. He was just a minor warlord who didn't really stand out to me.
Really, the storytelling in video games usually has to be so basic that it's hard to mold a great villain from scratch. You look at absolutely amazing villains, like Johan from Monster, and it's obvious how hard it would be to incorporate that much development in an interactive medium.