Played Storm Kings Thunder to completion and ran Dragon Heist to completion. I've played through a chunk of CoS (loved it), Hoard, Princes, and DotMM. Currently in the Hoard campaign actually - just joined a new group recently. Very different experience - only two players and one DM. Been a ton of fun actually.
Dragon Heist requires a few things - a LOT of player buy in for one. Also, I think people forget that you're supposed to blow through this thing in about 6-8 sessions. It's really, really short. It's not meant as a campaign, and I think people try to play it like one. It's more just a primer for Waterdeep.
Now, that being said, when you start adding in lots of stuff from DM's Guild, you can easily turn this into a full campaign. And Waterdeep really is a very cool place. But, again, you need players who actually give a toss about setting. If you're players are all goal/task oriented and just want to resolve the plot, then, nope, this module is going to suck. That's I think, why the whole factions thing just falls so flat. No one really cares about the factions, mostly because the factions don't really connect to anything. Yay, you're a harper now. Ok, do these harper tasks. Great, now, let's get back to the actual adventure and we'll never mention the fact that you're a harper again.