I consider this card somewhat under-rated, which might surprise people - I'm sure people would agree this card is incredibly powerful. The thing is, despite that fact, I very rarely see it used in decks. I think the reasons for that are on one hand that it's much more a multiplayer thing and on the other that people have a knee-jerk negative reaction to the Doomed keyword. Doomed 3 is a tricky prospect to sell people on I'll grant you, but for the effect it's well worth it. Well, one more point is that it works best if the damage is well spread so it's good for lots of direct damage, not for just taking bits of damage defending attacks.
So, problem one, multiplayer. Fairly obvious, this is a global healing effect, more players, more healing. Of course Doomed affects all players but you get better value for the resources. Also, with more players more damage will happen because you're revealing more cards, so you have more of a need for said healing. A solo game against even a quest which is pretty heavy on direct damage, you can get out a bunch of allies and have a comfortable buffer of hit points away from losing anyone, but get up to 4 players you have four times the characters in play and yet you end up having to kill them all over the place. The way quests(/encounter cards) scale into multiplayer is a whole separate subject for debate though. The fact is it does work this way, and given that, you need healing. If a quest really lays on the damage, more usual methods of healing may well fall behind, but this is a complete reset button. However much damage there is, it's all gone. 3 resources and 3 Doomed is a small price to pay in those circumstances. Furthermore, having that reset button available can save you putting more into continually healing. Problem 2, knee-jerk reaction to the Doomed, well I just said it's a small price to pay in the circumstances, and it should also be noted that this is an effect which you can't really get through other means, except perhaps for an extortionate number of resources - these being the two areas in which Doomed cards really shine.
The final point really covers the biggest aspect of how I feel this card really should be used. If your defender is just a bit too flimsy to keep tanking all the enemies so bits of damage get through, then cheaper repeatable healing is a better idea. But if you're playing a quest with lots of archery and/or direct damage treacheries and/or additional direct damage from other enemies, shadow effects and locations; where all this damage is under your control and you can keep taking it exactly as you like and no character will die unless you specifically choose to put that last damage token on them up to their hit points; then this card can absolutely blow quests out of the water. Managing that build-up of damage would usually be an ongoing struggle as it mounts up, but Waters of Nimrodel is the nuclear option, wiping the slate completely clean so you can start the whole process over again from a more powerful position than when you did it the first time. It's a truly amazing card in the right circumstances.