This was a superb card for establishing an archetype - the archetype in question being Gandalf. Actually I'd debate whether Gandalf is exactly an archetype, but regardless, this card is the perfect enabler for whatever you want to do with Gandalf. It's certainly possible to play hero Gandalf without the Pipe, but it's hard to imagine why you would want to. Without the Pipe Gandalf is rather dependent on the whims of your random shuffle, helped along perhaps by Imladris Stargazer but still it's quite possible for his resources to end up being not so useful and out-of-sphere cards are a really risky idea in this case. With the Pipe everything works wonderfully. Card in your hand you can't otherwise play? Put it on top of the deck and let Gandalf play it. Card on top of your deck you want to play but you already played from top of deck this phase? Swap it into your hand then play it (ideally switching it with an event which you can then play in a subsequent phase). Want to spend all your resources but also hold one back for A Test of Will? Put AToW on top of your deck and you can play it with Gandalf's resource if need be. And so on. One of the greatest powers of Gandalf as a hero is the ability to have your deck function incredibly efficiently and careful use of the Wizard Pipe is a central part of that.
Beyond this it should be noted that it is possible though unusual to use the Wizard Pipe without hero Gandalf. Attached to OHaUH Gandalf or Radagast, it can still have some uses (these uses apply with hero Gandalf as well, but this way you can pass on his 14 threat if you want, albeit it'll be jankier). These uses are:
- Setting up Elrond/Vilya. Obviously very powerful.
- Getting draw from Expert Treasure-hunter. Potentially combined with Hidden Cache to generate resources at the same time. Powerful.
- Setting up Well-Equipped with a good attachment or Ered Luin Miner. Both potentially pretty good.
- Setting up Zigil Miner. Unlikely to be better than just using a Stargazer.
- Setting up any deck mining effect to discard a superfluous card rather than an unknown. Not worth the combo, but another option if you're already doing it.
- Setting up Flame of Anor with a high-cost card. Pretty powerful with any Istari other than Radagast.
There might be others but that's all I can think of. So these aren't terrible options in general, they're pretty good. It's just a lot of work to get them without hero Gandalf, and there may well be other routes to those effects requiring less expense or weird jank. But while it might be a bit much to go for these as ends in and of themselves, if you already have a reason to be using Hobbit Gandalf or Radagast you could certainly consider some Wizard Pipe shenanigans. Whether using hero Gandalf or not it should of course be noted that the Pipe can be fetched by a Master of the Forge, or even better by Bilbo Baggins.
Regardless if you use it in the standard context or something more unusual though, the simple fact is that the Wizard Pipe enables a lot of powerful other effects. It takes things which would otherwise be pretty thoroughly unpredictable and unreliable, and it lets you trigger those things consistently pretty much as and when you want them, only dependent on your card draw (which of course applies to basically everything). That's a level of enabling which cannot be undersold, and it's pretty incredible how much impact this simple 1-cost attachment can have.