I agree that a lot of stars need to align for this to be worthwhile. You need good Copper trashing, but for some reason you still want a few Treasures in your deck, but your deck has to be built that you can't just draw them as part of an engine or whatever. But, at the same time, you need a way to hit $6 to actually buy Adventurer (or some other means of gaining it). So, uh, wtf?
Some rough thoughts: It's got a little bit of use in a HoP game, since it both finds the HoP and adds an extra unique card. And it means you don't actually have to hit $6, just 6 unique cards in play.
Does it have synergy with any other special Treasures? Maybe Relic, because then you're increasing your ability to attack on a regular basis. Potion, a little bit, including in Scrying Pool or Familiar games where you either want to get your Treasures out of the way where possible, or where you might have a lot of junk in your deck to dig through. Coin of the Realm? It's kind of like Scheme-ing your Villages, I guess, but it's not that great given you have to use one of those Actions to play it in the first place. Loan kinda sorta maybe but not.
So very broadly, I think that Adventurer is at its strongest when you're using it to find Treasures for purposes other than +Coins, and you lack the means of drawing those Treasures on a regular basis in any other way. Does that sound right? The only other case I could think of is Philosopher's Stone, since you don't reduce the size of your deck like you would by drawing it with lots of +Cards, but when do you ever run a PStone deck by trashing Copper?
EDIT: Also maaaaaaaaaybe in a situation where you use Scheme to top-deck the Adventurer as a proxy for top-decking your Treasures? But then you'd need a reliable way of playing Scheme, like King's Court, and if you've got KC/Scheme on the board then odds are good you've got a better payload than Adventurer.