> no real usage has really taken off for "blockchain" technologies (and no, I am not counting the occasional pump and dump / virtual scarcity scheme as a real usage of the tech)

In the Bitcoin world, Ordinals have started to make waves as they are a creative use of unintended consequences. This will likely spur other innovations.

In the EVM world, DeFi is still used all the time. It lost a lot of steam from the last bull run, but trading, be your own bank, interest arbitrage, options, perps, all of that is still going on quite a bit. New sites are popping up all the time. LybraFinance LUSD/eUSD is one of the more recent ones.