I'll share my success/failure here.
I'm curious if we are all looking at the same problem. I find that once I reach late game, there is little chance of me losing - it might be trying and take awhile, but taking city after city from the AI is almost guaranteed. Thus, late game is the least fun part, a mop-up exercise.
I acknowledge, of course, that this is a 4X game recurring problem, not just a Legendary Heroes problem.
So, my goal is to give all AI players a huge boost late game, when I can handle it. Just giving them a dozen dragons would do the trick, but I was thinking some custom-made units might be more interesting...