Warpstone Excavation makes perfect sense in particular builds or as a weapon against specific builds. But you are generally correct, most people will not want to play a card that forces them to corrupt a unit in a deck that is all about being super aggressive or super defensive.
I might though... Think about it this way. If I'm going to be playing a very faced paced deck I need to get as many cards into my hand, as many resources into my pool and as many units in my battlefield as I can. Trying to do that and defend an dbuild all three zones is going to slow me down. Period. If I can toss a Warpstone Excavation or two into my Quest Zone if I intended to only use it for draw and the best Quest x3, it doesn't matter if the unit comes into play contested, he still triggers a resource for my quest. It is a one turn delay before he can defend, and if I'm instead building up my BZ my opponent is going to be left in a position where they need to think about which of their zones I'm going to hammer, or leave one of their zones under defended.
Putting it in my KZ makes even more sense in general games because I want more control over how many card I want draw and I want the ability to sacrifice or kill off defenders if necessary in the QZ to keep from decking myself. With these "free" support cards to give me resources, where there is little in the game to punish you for pulling in a lot of resources the only drawback is again putting a unit there and it being corrupted for a turn. Again using this card allows me to gain resources without having to invest heavily in doing so, which means my QZ and BZ can be built up much stronger, especially than my opponent who must stick his own units in all 3 zones just for protection let alone his trying to keep up with me and my draw and resources.
In a grinding control deck, meant to when by attrition, the cards are useful also. I can just keep them in my hand until such a time when a zone is burning (and if your opponent doesn't suck, eventually one of your zones will burn) and then lay however many of these you have in your hand down since you won't need to play new units there. Keeps your power in that zone up with no disadvantage.
Of course depending on your build you may find that you can build up your QZ of KZ to your satisfaction from a defensive standpoint just fine. If you get a unit or combination of cards that lets you heal or cancel damage then one or two decent HP charcaters can defend the zone by themselves. In this case Warpstone Excavation is just an extra card to draw or resource to pull with no meaningful disadvantage unless you are already getting screwed... but the extra draw/pull should help you prevent that from happening.
Summary - it isn't for the weak of heart, but the card definitely has its uses and certain types of players will make very good use of a card most people would never bother including in their deck.