How does one write a Campaign?

My group has already run Waterdeep Dragon Heist and Stormkings thunder, unfortunately I haven't really found another adventure I like the concept of. Historically I have written my own games but I always lose the story around level 8 or so because it gets rather incoherent or the scale gets out of hand.

Does anyone have any tips for writing a long term adventure? I've done it multiple times before but always poorly imo.