This actually sounds pretty subjective. There is only one non-subjective answer I can think of:
Micromanage Early, Automate Late
Early in the game, you have few cities, few population. A couple of well used units can topple another civilization. A worker improving the right square and double your food income. But late in the game, you have lots of cities and units, all the important hexes are improved, you've explored everything already. In short, early in the game every decision affects a greater part of your civilization than the same kind of decision late in the game.
So, micromanage your units early in the game, when they matter a lot. Then, whenever you think they've become unimportant, automate.
Most of the other reasons to automate or not end up being subjective, or simply:
Do you want to win absolutely, and play on the highest difficulty possible? Chances are you should be micromanaging everything, checking on every city/unit every turn.
Do you like some parts of the game and not others? Perhaps you like war but not building? Automate whatever you don't like, and play what you do.
Do you enjoy quick games or watching a world evolve with only light input from you? Automate a lot, and click next turn to get to the fun stuff. Also maybe play on quick.
In short, it depends on how you want to play. As long as you're having fun, you're doing it right.
Can you rephrase your question at all to be less subjective, less abut how you specifically might want to play? Maybe, "which of the automation options tend to make good/bad decisions?" We can't tell you how much to care about automation, but we can tell you where you might want to care, if you decide to.