While it is an amazing project, I can't wonder about the appeal of civ games themselves. To me they are a mess, the mid to late game itself is chaotic chore of micromanaging large number of units.
To me, the earlier games come with less complexity but with more micromanagement. I played SMAC again some time ago, and it's fun early to mid game, but towards the late game the sheer number of cities and units is just overwhelming. I think Civ 2 was in many respects similar although it's been a long time since I played that.
The greater relative cost and lower number of both cities and units means that to me there seem to be a smaller number of things to micromanage in Civ 5. Units embarking themselves means you don't have to herd them into transports.
The lack of unit stacking and the inability to move any units together automatically (even the ones that do stack) does cause quite a bit of micromanagement, though, so that's true.