Hobbit Pipe is a card I've felt for a while is under-rated. The big reason for this I think is because it was released alongside Smoke Rings, and because that card didn't look so good on release (it's better now though still a more niche option), a lot of people tarred the Pipe with the same brush. Which is really unfair if you give it a moment's thought. Smoke Rings is dependent on Pipes, but the Hobbit Pipe is not dependent on Smoke Rings.
So to start with, this card is not for every deck. But then very few cards are. You need Hobbits to attach to - although this can include ally Bilbo, who also fetches the Pipe out of your deck. The more significant restriction though is that you need to have threat reduction in the form of events. So if you're getting your threat reduction from Gandalf, Aragorn, Galadriel, Galadriel's Handmaiden or Merry then the Pipe will do nothing for you. Equally many decks may simply not bother with threat reduction, instead working under the assumption that they'll just win the quest before they hit 50. However, if you are in a deck which requires threat reduction and gets it from events such as Elrond's Counsel, The Galadhrim's Greeting, and the like, then the Hobbit Pipe is a great inclusion. The one remaining downside I haven't brought up is that of sequence - you need to draw the Pipe before you draw the threat reduction for it to work, but having Bilbo to fetch the Pipes and simply having numerous threat reduction events gives you decent odds for this.
Consider then - the Hobbit Pipe costs 0, and will cause you to draw a card every time you reduce your threat with an event. Those Elrond's Counsels and Galadhrim's Greetings you wanted to play anyway, but now they've become card draw as well as their other function, and all it cost you is including the Pipe in your deck with a hero to play it on. Or maybe even without a suitable hero and you put it on Bilbo after he fetches it - since he's 2-cost for 2 and 2 hit points including him is not an unreasonable prospect either. Consider as a comparison the fact that people will include the 0-cost We Are Not Idle just to thin their deck; in this light, the Hobbit Pipe seems pretty clearly an upgrade - as soon as you play some threat reduction the Pipe has effectively worked as a copy of We Are Not Idle. Every subsequent threat reduction event is giving you a profit of card draw. If you have at least 6 threat reduction events in your deck (3 Counsels and 3 Greetings) then that's some pretty good draw over the course of the game. If you have more then that probably means you're specifically building around the Pipes, but that's not a bad move in any case because card draw is king, and having that threat reduction means that while you're at it you can also be handily paying for any effects which require you to raise your threat (such as those featured on some of the Hobbit heroes) or just reducing your threat to very low levels allowing you to ignore a lot of combat in favour of a questing/support focus and also to benefit from powerful Secrecy cards.
All in all, the Hobbit Pipe is a very nice option for card draw to just splash into a deck which happens to include threat reduction and Hobbits, or it can become the focus and draw engine of a dedicated Hobbit/threat reduction deck. Both options have much to recommend them.