there's 2 ways to approach mod design; Micro or Macro.
Micro - make a bunch of small things; tweaks to rules, units, buildings, civs, etc... Get your feet wet, get familiar with how things work...
Macro - Plan a nebulous mod - By that, write down an overview; "I want to make D&D type world that has Elves, Dwarves, Bugbears, and Flumphs as civilizations. I want three Unique Units for each, and 1 Unique Building."
And on this note, my advice would be "start small". Even if your ultimate goal is to get a massive total conversion made, start with something that you KNOW works (like the base game) and iteratively add and subtract, testing periodically.
So if you want to add the Elf civilization, then don't wipe out all of the others first; just add them, so you'll have Elf vs. Germany vs. India to test out the basics. Then add a few new buildings, then some new units, then start deleting non-fantasy stuff, and so on. For a while you'll have something that seems ridiculous, but at least it'll be a STABLE ridiculous.
For instance, my own mod (Crazy Spatz's Alpha Centauri mod) made this mistake. I decided to add 45 new techs, sixty new buildings and wonders, a few dozen new units, etc., and what happened?
It didn't work.
I'd changed so much that I had no easy way to tell what, exactly, broke it. I ended up having to create a copy of the mod with everything new stripped out, and then add one bit at a time. I'd add five new units, recompile, see if they loaded, then repeat. Eventually I got it all working, but that was a lot of wasted time.
As to the original questions, I'd say that there's no easy way to tell what will survive, but that it'll all depend on a few specific things:
A> How devoted the primary modder is to keeping up with it.
A team doesn't really help so much here, you really need one person whose vision keeps it all going. And if he decides that his time is better spent adding nude hacks to Fallout 3, well, you're hosed.
This becomes especially important when large patches and expansion packs come out. If the mod isn't compatible with them, or just doesn't take advantage of their new features, then people will move on to newer mods instead. Patches are important for this; if one of the key parts of your mod is that cities heal 2 HP per turn instead of 1HP, and suddenly Firaxis decides that the default value will be 3HP, your mod no longer does what you intended it to do.
B> How good the team is at keeping all the little details straight and balanced.
Balance is REALLY hard to get right, especially if you go too far from the core gameplay. Too many fantasy/sci-fi mods I've played have ended up feeling like a jumbled mess, where there's no real "flow" to the gameplay, just sort of a "here are a bunch of cool toys!" thing instead. And if you're looking at a large content mod, then there are a LOT of little things to keep straight; is that brand-new Gravtank unit too strong, do you have too many +research buildings, and so on.
C> Whether the fans decide that the design has strayed too far, or not far enough, from the core gameplay people like.
Add too many new features and you'll make a mod that only you and a couple other people would want to touch; many of the ideas I've seen on these boards (for religions and such) sound fantastic, but if you need a massive amount of documentation to learn the system, it's not something I think the average Civ player will ever bother with. Add too few new features and you get this "why bother?" feeling, especially if the mod is just a collection of a few new units or buildings. It's a fine line, and programming ability obviously helps for the latter part at least.
Just my opinions, of course.