I actually thought for a couple of hours about it, and arrived at the conclusion that it's totally impossible for me to answer the question. I've played so many totally different mods for so many totally different games that there simply isn't any yardstick that could be used to measure and compare all of them.
As an example: Some of the great mods for Civ4 include:
- "A new Dawn", an aggregate mod that adds tons and tons of features to an already very complex base game
- "Fall from Heaven", basically a whole new game
- BUG mod, a UI mod that is much smaller in scope than each of the two above, but that's incredibly useful. I immediately miss it when I'm playing a mod that doesn't have it integrated
- Better BtS AI, a mod that adds no content at all, but considerably improves the AI.
How could one determine which of these mods is the "best" one? A fair comparison simply isn't impossible, they are simply too different.
And these are only mods for _one_ game. Now let's just add one other game, say, Morrowind. This will give us at least a dozen of _additional_ categories: new quests, new items, new creatures, essential bugfixes, useful tweaks, texture and mesh improvements, new gameplay mechanics, player homes, total conversions, new sounds, new landmasses ... and this list isn't even exhaustive.
So, at least for me, asking for the "best mod" is similar to asking questions like "Who's better - Beethoven, Philip K. Dick, or Salvador Dali?". I can't answer that one either. :)