I fall into the camp of "very little automation." Maybe because I like the details,
maybe because I just moved up a difficulty level.
In the very early game, I care about doing the right thing to the right tile. Roads to connect the resources, and to link up the cities. Mine the bonus grassland, and irrigate to spread the fresh water.
Once I revolt into republic or monarchy, I want to improve the tiles I will be working, and to mine those hills. I usually don't build quite enough (about 1 per city), so I hate losing them to barbs. I move them to spruce up newly captured cities.
In the Middle Ages, they get bored and I will often just fortify or sentry them.
I try to avoid the temptation of joining them to cities, cuz I will need them for railroading. Late middle ages, I will clear some jungle to avoid disease.
I use stacks of 3-5 for this (as CommandoBob suggests) so it goes faster.
Once I get steam, it's important to me to manually build the main trunk lines, from my core cities to seaports, so that troops can be shipped to the front easily.
When pollution starts appearing, I'm willing to use the clean up command (shift-D in C3C, and shift-P in vanilla, IIRC) If there is no pollution, I often stack them up next to a lake or mountain (think peaceful resort

) where it's easy to find them and wake all. I will keep slaves in one stack, and native workers in another.