I think from what we touched on regarding escorts we've pretty well boiled it down to the essentials:
Your NPC ally must either be really fucking smart (this is exceptionally hard to do,) damn near invulnerable, helpful, or something over which you have complete physical control. Alyx was some combination of the first three. It didn't necessarily make her bits enjoyable, but it did at least make them shockingly inoffensive compared to similar sequences in other games. The companion cube, if it even counts, was the last three. It was also a great example of a character that by virtue of never fucking speaking reduced the chance that you'd want to shoot it yourself.
The weird exception to all this has to be Yorda, who was autistic, made of wet toilet paper, and impossible to get to do anything but play with the fucking birds. Also, she talked, but was completely incomprehensible. There was a small but non-zero chance that what all that babble actually meant was "I apologize profusely for my inability to walk in a straight line like a non-retard. Your patience will be rewarded with sex." Which was somewhat more bearable than, say, a constant 18 kilocycle EEEEEEEEEEE sound, but it wasn't anything that made her more likable.
Then again, the Little Sister escort wasn't that bad, either. I think maybe it's a matter of understanding. If you're escorting what appears to be an empowered character-- say, a guy with a fucking machinegun and a flip attitude or a giant robot made of rusty I-beams-- you expect him to pull his weight and then some. If your charge is going to die if someone breathes on it too hard, you damn well better make it a little girl or a no-legged puppy to adequately, instantly, and constantly communicate just how much is going to be expected of you.
Better yet, make it an indestructible little girl with a bazooka or a wheelchair-bound psychic with forcefields and mind lazers. If we're going to be surprised, best to be pleasantly surprised.