Darkness over Daggerford is the most balanced in every area I feel. It has good story, atmosphere, roleplaying, exploration, combat, puzzles, and great sidequests. It's essentially a full game content wise.
Wyverns Crown of Cormyr and Pirates of the Sword Coast are also well balanced campaigns, but they fall short of Darkness over Daggerford in overall size and quality.
Mask of the Betrayer has excellent writing and atmosphere, but the gameplay is meh.
SoZ is the most ambitious campaign, but it's a mess. Deadfire is practically SoZ 3.0.
Neverwinter Nights 2 OC has some strong moments, but it's mostly mediocre.
Hordes of the Underdark is good. Shadows of the Udrentide is average. The NWN OC is terrible and not even worth playing.