Years ago, in a great solo Forgotten Realms campaign (I was the player), my character found a football-sized ruby in a treasure hoard. I thought it was the coolest thing since sliced bread — I mean, come on: Football. Sized Ruby. What’s not to love?
But I also remember it being a trickier prospect than I bargained on. How do you spend a ruby that big? Who could possibly afford to buy it from you? How carefully can you guard it?
My friend was an excellent GM, and I never felt cheated by the outcome (which, I think, was selling it for a lot less than it was worth — but still a lot of gold). With a different GM, though, I might have felt cheated — and that’s no fun.
So when is loot satisfying? When a) the PCs earn it, b) it’s got a high cool factor, c) it’s worth what it should be, and d) when the party can spend or use it easily. There are exceptions, of course, but if you line up those four factors when handing out goodies, you’ll be off to a good start.