I think there's definitely room for good cosmic stories if they're done right, but they
always need a lot of buildup. We should never be fighting big cosmic-level threats from the start of a Saga, or they'll lose all their impact and turn the setting into an absurd power-scaling mess, where nothing actually feels meaningful. Legion, for example, did it right- we've dealt with minor incursions from them from Vanilla and BC, and had their invasion teased throughout MoP and WoD before we faced them in full- and even then, only went to their domain at the end. The Void and Titan arcs are on the right track too- they're entities that we've known about for a long time, and we'll have Dragonflight, TWW, and possibly part of Midnight as immediate buildup before facing them.
That kind of enemy only really fits as the climax for a Saga. Take a hypothetical Light threat, for example. I think it could be great if, say, we spent a couple expansions with heavy Arathi influence (maybe one EK revamp expansion where they initially seem to come as allies before conflict breaks out, then another where we go to Avaloren to strike back), gradually learning about some Cosmic-level Light allies they have. Who we would face in the final expansion of that Saga, or even just the final tiers. But just diving straight into conflict with said Cosmic threat, without time to learn about them first or connect them to us, just doesn't work.
Dumbing it down by forcing everything into six categories with too-similar Pantheons was also an issue that didn't really work for some forces (Demons and the Void absolutely shouldn't have a unified pantheon or be filling a crucial role for the cosmos) and cheapened some entities that didn't fit into any one group before retcons (Elune, especially, who
did have ties to Life before seemingly being forced into that pantheon, but was also a prominent Light force who created the Naaru). Those forces existing, and
some of them having structured pantheons, is fine, but it shouldn't be forced on all of them. Demons (in their natural state) shouldn't be unified (in fact I'd love to see an expansion about powerful demonic warlords fighting for supremacy, maybe with us aiding one who wants to leave us alone?). The Void Lords should all have their own dark agendas rather than working together (maybe Dimensius wants to just devour everything while others want to corrupt our world like the Titans believed?) And Life, too, should be wild, primal, and chaotic.
Fortunately, they seem to be moving away from that extreme homogenization lately. The
Arathi book in particular dismisses it as a discredited theory, so perhaps there is room again for cosmic entities to not fit cleanly into generic boxes. And maybe we'll actually see a proper payoff to all the mysteries about Elune after all.