Artillary almost saved a One City Challenge Game for me. I fell hopelessly behind in tech once we entered the industrial age, but had got to replaceable parts before the UN was built. I declared war on the Persians one turn before they built the UN. The largest civ, the Romans, were my MPP partner. It became a world war. My lone city never received a direct attack. Any enemy unit that got within range always got knocked down to 1 hp by my artillary and one of my allies invariably came in and finished it off. Unfortunately, I mismanaged my diplomacy enough for the war to become a 4 on 3 affair with the 4 being on the Persian side. Needless to say, when the vote came around after it became 4 on 3, the Persians held the vote and won 4 to 2 over the Romans (I abstained). If I would have managed my diplomacy a little bit better, the Persians would have never held a vote and I would have one via one city culture with artillary as my MVP.
I guess I have had good luck with artillary, but I am amazed by people who expect a 75% hit rate. If you attack with a unit in an evenly matched confrontation, you will lose 50% of the time (and by evenly matched - I mean after all modifying factors have been consisdered). When you lose, you lose the unit. If an artillary misses, it lives to fight another day. If you make the hit rate too high, artillary becomes more unbalancing than it already is. I wish the AI used it better - even with catapults the human has significant advantages because of the ability to make use of massive offensive bombardment - a feature the AI generally does not exploit.