I'm pretty sure it goes by whichever spy has the most subterfuge. If one has 6 and the other has 3 it will go bye the one with 6. When you attack a settlement with a spy in it the game automatically has the spy try to get them open. I'm not completely sure but I think if you siege a settlement and the spy fails to open them you could break the siege and try again. It's not worth the trouble though because you can take the settlement in 2 turns without the spy. If the spy successfully opens the gate you won't have to wait that extra turn to built battering rams/siege towers/etc.
Doesn't the Spy die or something if he fails; or, the attack fails?? I have some vague recollection....

I also use spies to see how strong an army or settlement is. I can figure out if my army is strong enough to take it or not. Even endgame though, it's nice to have a spy in the settlements you are going to take.
Oooohhh, bad Kauser, bad Kauser!

Just joking, once your more confident (perhaps you already are), you may enjoy a bit of *roleplaying*. That is makeup a set of rules to play by that add to the challenge. One might be to refrain from using Spies to ascertain the makeup of enemy AI stacks.

This way your Generalship should get better as you'll learn to bring a balanced army, and to WIN with a balanced army no matter what the AI brings (or anyone else for that matter).

Me, I always like being surprised; never know what the AI makeup is until the battlescreen.