As with all other questions related to quality there is no single answer to this. It depends quite a lot on the DM and the players, the theme of the campaign/setting a.s.o.
It is easier to point out the elements that makes for a bad adventure, IMHO these include:
Inconsistency: This is the...