I would think a GREAT quest mod has a great story and great quest with challenging dungeons, like Lost Spires. More than anything, it is about an overall immersive story.
All quest boil down to:
go to location A, do task B, come back, get reward and next quest.
The difference is how they go about it, how they depart from Oblivion visuals, how it fits into the mods big picture.
I think I actually disagree with everything here, even that a great quest mod has a great quest - why just one? Why not a set of independent small quests that add up to a bigger picture?
It doesn't require a dungeon at all to be great, or require dungeons to be challenging.
A "story" usually implies a linear sequence that the author has predetermined, which spoils most mods for me. I'm playing an interactive game, not reading a book, and I'd like to be the one in control. I'd have preferred the Guild quest lines to have offered a lot more of their individual quests in parallel, instead of the linear progression they all had. At least you could do a quest from each one at the same time, or the whole game would have been boring.