IMO, they are best used when you need additional attacks on a tile, but can't afford to take up that space. I recently just finished a game where I wanted to move my artillery into firing range of a capital city, but the tiles were blocked. I was able to attack with cavalry then move back out of the way and move the artillery forward. I suppose you could argue that you could just wait the extra turn or two, but with so much gold per turn towards the end of the game it isn't difficult to keep a few around. But yeah, they tend to be more useful on the sidelines than fortifying positions up front. Also useful for sniping cities once artillery/bombers hit the field.