I think a lot of it might just come down to application. I haven't used all the spells, but I actually found spells like Death Siphon/Death Magic (mostly Death Siphon though as I liked the mana regen), and found Earthquake to be quite useful at CC, and was a spell I routinely had Wynne cast in my first playthrough. She also kept Spell Wisp on, but I agree that the Mechanics made me wonder if it was worth the mana cost. I also liked to combo drain life after vulnerability hex, and it works really well if you've gone Arcane Warrior and is a decent damage spell as blood mage (though the healing you get from it is mitigated by having Blood Magic active). Though it's usually my "other spells are on cool down" damage spell. The reason why I like it is because sure, I can drink a potion, but this lets me still hurt a target while healing myself.
The guy in my room has talked about Miasma a lot. He seems to like it. It's hard to say how effective it is due to combat mechanics not being obvious.
I agree that Shapeshifter was underwhelming. The only time I ever used it was when Morrigan's mana was dropped and I didn't want to use a mana potion. Even then I never got past Spider form. I don't have any real experience with the later abilities in that tree but I wouldn't think they'd be significantly better.
I think a big difference between us is our willingness to use potions, so I probably appreciated the spells that would regen mana and health a bit more. I typically found myself saving my health potions for Alistair, and in general did not have a whole lot of mana potions so I always saved those for bigger fights. I imagine Death Magic is good for an Arcane Warrior that is tanking, but I liked Death Siphon for draining up bodies to keep spells going with Morrigan.
I agree that Drain Mana does not seem like a useful spell (especially if it's not instant like entrerix says). If I'm out of mana I prefer to just staff attack while I regenerate. You'd have to drain a load of mana doing this but I suspect you probably don't.
I haven't really paid much attention to the rejuvenation spells, but they don't seem to rejuvenate very much at all the times I did look. I think stamina regen is being looked at in the upcoming patch.
I'll take a look at some of the ones I'm less familiar with if I get a chance tomorrow. I'll also see if I can talk with someone about some of these spells like Mana Drain and the regen/rejuv ones.