Tragic Hero can set up some powerful mega-turn endings, even just with Golds. If you're careful about when you play them during the game, you can build until the moment is just right and then pop them all on your last turn for a nice payout. In these cases, each of your remaining Tragic Heroes basically reads +3 Cards, +1 Buy, +$3, which is excellent for finishing off the Provinces or emptying a third pile. In the presence of more exotic treasures like Platinum or Horn of Plenty, Tragic Hero mega-turns are even more potent.
When used as your only draw in an engine, the trashing "drawback" can make playing with Tragic Hero tricky, but rarely unworkable. Cantrip villages keep your first Tragic Hero each turn safe, and non-drawing villages like Festival, Squire, and Conclave play very nicely (these make Tragic Hero work sort of like a draw-to-X). With its +Buy and payload generation, Tragic Hero also is a great source of supplemental draw alongside something else.
I feel like the biggest thing with Tragic Hero is: don't be afraid of trashing it or think it's a bad card because it might get trashed. While you obviously need to be careful not to just mindlessly play these and suddenly lose all your draw midgame, Tragic Hero's treasure gaining is absolutely a feature, not a bug. Yeah, it's no Margrave, but I mean come on, Margrave is crazy.