Sephiroth is memorable, because every time he (or jenova pieces being controlled by him) shows up, someone dies, or something big happens. Just look at every scene he shows up in.
1. He slaughters his way through the shinra headquarters, and kills the dictator of the planet in his own office. And then he escapes.
2. We get a flashback where he destroys clouds home town, murdering clouds mom and tifa's dad.
3. He kills a giant snake that you just spend 30 minutes avoiding, and then strung it up as a testament to his power.
4. He casually breaks into the military ship where the president and his entire staff are located, kills the guards, and breaks out jenova
5. He either kills or gravely wounds tseng, who is not seen again for the entirety of the game. He then manipulated cloud into sacrificing cait sith, and then takes over clouds body, forcing him to try and kill aerith.
6. He kills aerith.
7. He releases multiple city destroying kaiju from an ice berg, who then proceed to wreak absolute chaos and mayhem on the planet, and who are a potentially planet destroying force unto themselves. He then summons a meteor capable of destroying the planet, put cloud into a coma for a large chunk of the game, removing both him and tifa, and creates an impenetrable shield around his new base in the north pole.
8. He has a larger then life final battle, culminating in a psychic duel.
Sephiroth isn't a great character because of his design, or music, which are fairly generic by anime standards. Nor is he a great character because of his power. There are multiple final fantasy characters who are stronger then sephiroth, even in his own game. The reason he is a great villain is because of the way he is portrayed and written. He's a game changer, and every single time he shows up in the entire story, the status quo changes. Every. Single. Time. This is why he's more interesting then Kefka, or ultimecia, or whoever else. This is why his appearances in other media, outside of the original game, have been bad. They've watered down his character into someone less intimidating then he originally was. Sephiroth was fucking terrifying in the original game, because you never knew what he was capable of doing when he found you. People remember him killing aerith, but that wasn't the last, or most damaging, thing he did in that game. Sephiroth is a masterclass villain.