I love that you asked this question - I don’t think I ever really stopped to analyse what makes my favourite or most memorable characters that way.
I think for me:
1: Flaws
2: Story
3: Unexpected things happening
The pretty, happy, successful Sims come and go and I play a lot of them (maybe we all do) but it’s the ones that grow and change that stick with me.
I remember in one save, Bella and Mortimer Goth died while unplayed and Alexander (by this point a YA) became a vampire. I made Alexander into a really dark, traumatised villain and had him cause the death of his sister.
In another save I had a rich, elder woman called Alexis Devere, who was a snobby spellcaster. She founded a spellcaster club and would often turn up to lots and duplicate herself using magic. This would often glitch and cause the duplicates to become permanent townies so I’d have a whole horde of Alexis Devere’s (with different first names) showing up all over the place. Even though I didn’t play her much that unexpected twist made her stick out.
Back when I played TS3/2/1 I used to make a lot of characters based on people from TV shows so they weren’t as memorable because they were not original.