I use espionage in perhaps 30% of my games. Usually, I can complete a game without training one spy.
I usually build spies if I notice a lot of espionage activity happening to me. Just one improvement destroyed is not too bad, but if it is happening frequently, I begin to focus on my espionage. I use it as a result of reaction, not necessarily because it is within my plan.
I mostly use espionage defensively, ensuring that major points in my empire have some sort of "spy border checks", especially between mountain passes.
The main aggressive use of a spy is the city revolt, which lowers city defences.
In a city with 100% defence, causing a city revolt reduces the siege from ten turns to simply one turn. The trick is that the assault has to succeed the first time, though.