Not sure it's good for anything anymore.

PvE it's used to cheese infinite sustain in solo content or as a healer that wants to do damage so slots Blood for Blood to not run into magicka issues.

In Trials it's a quick ticket to getting kicked out and/or lying face first in the dirt dead. Even if you do everything perfectly vampire is melee, and ZOS doesn't build these Trials for melee fighters.

In Dungeons you'll find yourself with the same issue as Trials unless you build super niche into high self healing. You'll probably still get kicked since you'll lose out on the healer buffs and be worse off for it.

PvP It's used for Undeath, Swarming Scion, and Elusive Mist. Sometimes for magDK's looking to cheese the infinite sustain system with Blood for Blood, but you'll quickly deny them that if you put them on the defensive. (Is one of those magDKs))

It's for roleplaying

Before Greymoor they used to be a decent choice for basically every build but now it has too many drawbacks to be enjoyable and it's more of a 'dedicate your whole character to vampirism' instead of a being a cool skill-line that can be mixed with others.

Vampire is a meme right with next patch.

It has some uses in both sections especially the skill that gives you massive Spell and Weapon Damage while draining your health with ever increasing cost.