Most important features of spies:
1) As mentioned, unit info, including exact numbers and types. This is crucial for planning an over all war.
2) investigating cities, key for tactics, and finding out which wonders are being built and warning you if someone is working on something you need to build first. Also since you can't an embassy while at war, the spy allows you to do this during war time.
3) Sabotaging production of said wonders, though this would be much more useful in the early stages of the game when the AI has more equal production. I find this rare but sometimes used in higher level games when the AI can keep up with your production.
Lesser used and often too expensive features:
1) As mentioned, stealing tech is useful, but only if you have scads of money and aren't already kicking the AIs ass at tech. Again useful at higher levels of difficulty.
2) Troop positions. I find starting at Regent even this can be very important. The larger the map the more space there is to search and its so much easier to just steal some plans. If you are ever wiping out a civ, but when you check their unit numbers you see a settler, before you kill the civ immediately get troop info and find where the settler is so you can squash it. The most annoying thing is to kill all the cities of a civ, have a rogue settler get away and not be able to find it because you can't spy on it after its cities are gone, and not being able to end the war because he won't talk to you. Downright kills a democracy
Stuff I'm still confused about:
1) Exposing a spy seems risky, but also odd. The AI already knows, as it is designed to keep things interesting, what units you have and where. It doesn't need a spy to get these things. At the same time, the AI never seems to have enough money to commit any acts that are listed upon you, especially since so many players run democracy. The AI would get more tech by trading with other AIs than it would from a spy, for example, so how does a spy work for the AI?
2) I've never had a city flip on me due to propoganda, despite all the threads discussing it. It's like they made one of the most powerful features in civ2 nearly useless in civ3.