A good adventure needs several things, one of which is not a good story, per se, because that will be determined by my PCs and I, but instead a well-thought out justification and design.
Contrast "Storm Lord's Keep" and "Lich Queen's Beloved", both from Dungeon magazine, the former being the first 3E Epic Adventure and the latter being the ultimate culmination of any Githyanki involvement in a game, featuring Epic elements.
The former is, as Psion describes of some modules, an adventure that 'looks the same'. Cross out Kobold Shaman, and writer in CLR11 Storm Giant, and you have the idea. Now, instead of a cave outside of town, it's a floating cloud castle with walls of force stairs and mithral golems...but it's still just an uninspired dungeon crawl.
"Lich Queen's Beloved", however, is a wonderful module, full of many uses. It is not just a module, it is a useful piece of campaign material. While it is a very flavorful dungeon, it has a full context with which to place it in, and many more ways for it to be utilized, other than a simple dungeon crawl. The inhanbitants are well developed, have reasons for being where they are, and the whole of the dungeon is designed with both logic and care. Exploring the Lich Queen's palace reveals a lot about the Lich Queen and her servants, and has a distinctive feel. The palace feels like her domain. Compare that with "Heart of Nightfang Spire", which has whole sections that don't make a lot of sense, and comes across as just plain silly at times.
The Storm Lord's Keep, on the other hand, feels like an excuse to use Force Dragons and Mithral Golems, as some sort of silly anteing-up to Epic. Encounters feel 'dropped in' and players don't really affect the environment much. The Lich Queen's fortress, quite simply, made sense. And the more you learned about the queen, the more you understood it. The Storm Lord, on other hand, is just some guy with a magic item that affects the weather and a beef with folks on the ground down below. Lich Queen's Beloved makes assumptions about high-level characters powers, understands them and challenges players to use them to the fullest. Storm Lord's Keep seems designed merely to thwart them, so that they'll stay in the maze and keep looking for the cheese.
Event-based modules are much more difficult to pull off well, becuase the writer needs to consider the PCs taking many different actions, and how they would effect the central plot. Further, a poorly written event-based module, such as "Standing Stone" can leave the players feeling frustrated, as if they really had no input on the story, because like a video-game or CRPG, the event was essentially 'scripted'. It would happen whenever they got there, no matter what they did.
The best kinds of adventures are ones where they give you something of a toolset to work with, allowing an individual DM to shape it to his will, if need be. The whole Hommlett section of RtToEE is fantastic, because Hommlett is such a well-realized place, and there is as much to do in the town as at the Moathouse. The characters almost write the adventure for you, and you know what might be happening at any given time, and how some people would react to different kinds of events. Compare this to "Standing Stone", where the town is just a mcguffin to get players out to one of a half-dozen other mcguffins, all designed to basically trick the players until the finale.
And ultimately, a module has to, IMHO, either allow enough space for a custom plot hook, or do more than simply assume that the PCs will come along for the ride "simply because they're heroes". It doesn't have to be much, you understand, but more than simply "come on, play along, even though it makes no sense in the context of your characters." That can be tough, sometimes, but most modules in Dungeon show that it's routinely easy to do. Simple things beyond "we'll pay you" and "hey, you're heroes, right?"...or leave me space to do it myself, and offer some suggestions.
Honestly, Dungeon really has spoiled the market for this, in some ways. And that's a good thing.
