He's usually a whiny cartoonishly petty backstabbing asshole coolguy bitch autist (who becomes a ghost sometimes and continues being said petty backstabbing asshole) everyone keeps around for entertainment. The one you posted was from Armada they made him a lot more grounded and gave him reasons for betraying megatron beyond "i wanna be da leader!"
EXHIBIT A: ORIGINAL STARSCREAM
Literally every starscream ever pulls from this fucker. Even armada to some extent.
Unless you're talking comics then it's like a weird combo of this with mild elements from the Armada one for anything that's not marvel.
Most starscreams, ESPECIALLY g1 starscream follow a pattern of dying once megatron(sometimes called galvatron) has enough of his shit and just ices him. At which point he becomes an immortal ghost and starts possessing people, sometimes aoccasionally being briefly revived and then killed again. One of the first attempts at making a "mature" transformers thing unfortunately crossed over with the moeshit genre and resulted in a comic where g1 starscream's ghost possesses an anime girl and he immediately pisses himself because transformers don't have to pee or some shit like that. It's been forever since I witnessed that shit way back in the day when I was a lot younger and it was new, it's unimportant. I mean it's important in a sense because it's completely in line with how g1 starscream operates as a character. he pulls some shit and then almost IMMEDIATELY eats shit for it, even in weirder media tied to g1. Still though, not super important.
Anime girl starscream from edgy moeshit transformers as a bonus too.
By the way reddit people and youtube "transformers community" make like constant really cringe inducing screedy ass disavowements about how wrong and creepy this thing was and how "we're better now" as if they had any input in the creation of this, just a fun trivia.
WHAT IS IMPORTANT THOUGH is g1 starscream shows up eventually in beast wars and makes up a BS lie about defending everyone from unicron. This will be important later.
EXHIBIT B: ARMADA STARSCREAM
Armada (or micron legend if you're SOMEHOW FUCKING NOT FROM AN ENGLISH OR SPANISH SPEAKING COUNTRY) Was meant as a modernized more plot heavy reboot of g1. There's a lot of weird name mixups in the non-JP side of things, and the show's english dub is infamous for literally uses the unfinished footage at points for no apparent reason. This didn't stop hasbro and takara from going back to the original g1 continuity (see anime girl starcream above for one of the examples of that)
Starscream in Armada is less focused about just wanting to be the leader and more about proving himself. he constantly gets fucked over or used a LOT and it pisses him the fuck off. Somehow one of the first videos I found looking him up is literally just someone's apperent "favorite clip" which is just him being fucking mad as hell.
Anyways to get just how different he is, he briefly joined the fucking autobots and became friends with the human kids, one of them's in your OP image. SURPRISINGLY not a self insert art case, there ARE a lot of those with the weird ass starscream fangirls.
Now, REMEMBER THE EARLIER IMPORTANT THING? THE BS STORY ABOUT UNICRON G1 STARSCREAM TOLD?
They pulled from that BS story and made it an actual true thing for this reboot starscream's death.
It's kind of a shame how almost every starscream is just g1 with a half assed sprinkling of armada-y elements in there. Pure g1 dipshit jerk guy starscream is great and fine, Armada starscream is cool because he's not a dipshit and you can tell though his actions are kinda similar he has reasoning behind it beyond petty power grabs.
g1 starscream had ONE non dipshit moment that I'm fairly certain armada starscream pulled from to some extent, even without him and jetfire being tied closely like they were in g1's cartoon.
I may have rambled a little incoherently because I'm dead tired, but I think I made this starscream distinction clear. As for why women like him? Fuck I guess they just are horny for robot anime guys with boot looking legs and grey faces or something I dunno.