Any level. Although easier level games get boring more quickly because you can pretty plainly see how you are going to win by the time you are in the mid-game.
I do think the late game is more fun on deity and higher levels in general because it's just so much more difficult to know for sure how it is going to end. You might think you are closing in on a victory when a hostile army shows up on your doorstep and changes the math.
Regardless, I just have more fun exploring the map, finding and securing the best expansion sites and trying to hit all my marks for setting up a great empire. It's so satisfying when you pull it off just like you planned, particularly if you have to troubleshoot some sort of hurdle.
I mentioned difficulty level because, in my experience, a lack of a conceivable threat, or a remotely competitive opposition, is the source of apathy for most players. Most of us play at a difficulty level where we're going to win the vast majority of the time, most of which is also because it's deity and we can't crank it up anymore. But even so, at the start of the game at any level at or above Prince, the player is behind the AI and at that moment, there is a perceived vulnerability and problem to be solved. This is what most strategy games break down to: problem-solving, and as fans of this game that's something that we like to do.
But for experienced and capable players, levels between King and Immortal present the thrill of starting vulnerable and needing to catch up, coupled with the safety-net of insurance on the time we're going to invest in the game: the AI at that level is NOT going to beat us, it can't. Deity in an entirely different game, where the thrill of initial vulnerability is markedly increased, but the safety-net disappears because I maintain (subjectively) that there are some situations where with this civ, this map, these neighbors, you're going to lose the game.
If you play at a level below your ability, the benefit is that you can try things that won't work on your level (ex. piety starts) or experiment with alternative strategies. The downside is that you still play as proficiently as you can, and you put yourself in a position where winning the game is a foregone conclusion and you're just going through the motions to achieve victory. There may still be situations to read and respond to, but all the problem-solving for that game is done. If this is accomplished by the medieval or renaissance era, then the time spent in that game breaks down to 5% setting up victory and 95% turn-clicking to realize that victory.
But yes, a lot of it also has to do with the game mechanics of the late game. I'm currently deleting a Denmark small continents game where victory is a foregone conclusion. I missed the window for berserker dominance, but just loaded the game at turn 170ish and all opponents are renaissance entering industrial, I oxford-slinged radio and have an embarked carpet of 8 rifles, 4 gatlings and 6 cannons, escorted by 5 frigates and 3 privateers. They're going island hopping against deity-sized hordes of pikes and knights, with a few new muskets as well. And that's the first of 5 AI that I'll have to grind through. It will be epic, it will be successful, and it will be tedious to the point that I simply don't want to do it.