... what kind of applications it excels at where other techniques like DSP blocks or sprawling logic/LUTs don't work as well, are difficult to get to work right, or are grossly resource inefficient.
First of all I would rule out data processing. Say, you have a core running at 200 MHz and producing some work at the loop. I guess, such loop will be at least 20 cycles (especially with ARM architecture), which means loops are coming at 10 MHz. Let's say, you can do the same task in fabric at 300 MHz. How do you replicate such performance with cores? Install 30 cores and distribute the processing? It may end up to be much bigger job than doing it with FSM in fabric. But may be you're content with 10 MHz? Why would I need FPGA in the first place?
Housekeeping tasks? You certainly can do them with ARM core. Or without an ARM core. May be it's just me, but I don't care much how the housekeeping is done. Creating a counter in HDL is about as easy as in C.
IMHO, soft cores might be used for complex UI, complicated command interfaces, long slow and diverse calculations. The more complex and diverse is the task, the more useful is the core, because it saves lots of fabric. But also, the more complex tasks will require more memory. You have a choice of really small cores, such as Picoblaze, where you can add custom commands to the stock core and make things working nicely and efficiently. Or, you can design your own cores which have a limited set of operations (exactly what you need). Another choice is a big core, such as ARM or Microblaze. If you seriously need it, I think Zynq is much better choice, because it is implemented in hardware, runs much faster, has autonomous memory. You put the PS into the center and use PL as a slave which does all the fast processing tasks and is controlled by the CPU. It's easy to develop your apps in PS, easy to re-compile and re-run (without even touching PL), and you have plenty of resources to spare. Such approach will save you tons of time, effort, and will produce an efficient system.
I think there's some overlap between using Picoblaze/specialized cores and going Zynq. Soft ARM cores (or Microblaze for that matter) hardly can fit in between. Even if you want to get an ARM core because you're an ARM fan, Zynq is still ARM, so you've got to love it.