Great Spy: These guys are very useful. As has been said already, the great wall's early spy can do some serious damage and be very useful. If espionage is a big part of your game, I can see the appeal to a Scotland Yard or too. But I very often play with the espionage slider at 0% most of the time. Overall, I could play an entire game without a great spy and not really miss him. The same can not be said of the others.
Great Engineer: I used to love engineers. But the more I play the fewer wonders I tend to build, with the notable exception of my
current online game. Therefore I now find them more situational. I love them for rushing the UN as I'm usually trying to win the game early, thus building the UN before I have well developed production. But other than that I can either not build wonders, or build them manually with the help of chops, stone, marble, copper, etc.
Great Merchant: This was my vote. They're not as good at bulbing as Great Scientists and they don't build any cool buildings. But the 1
and the 6
as a settled great person is awesome. Also, trade missions are useful at every point in the game. Never do I hear myself saying, naaaah, I don't need that 2400 gold right now. Having a surplus in this game is so useful that I don't think it can be beat.
Great Scientist: My other favorite and the first great person I usually want to see. Academies are superb as is the ability to get from Civil Service to a completed Liberalism in ~15 turns. After Liberalism, I settle them and each one can net a solid 25
with representation and the proper multipliers in place.
Great Artist: Man, you guys don't give these guys any love. My favorite use for them, that I don't think anybody has mentioned yet, is culture bombing a recently captured city. The ability to capture an enemy's capital, skip the 10 turns of anarchy, have a BFC and beyond, and have it producing more military within one two turns of capture is huge in a war. If I think I will be fighting a war in the next ~50 turns, I save my GA for the war contributions. They are pretty situational though, there are indeed times when I get a GA and say crap.
Great Prophet: I can play games without these guys also. I usually settle them early in my capital for the nice hammer bonus. I kind of wish that the Great Prophet had the ability to spread a religion in ten cities with 100% success rate. That would be awesome. Too bad.