I don't entirely disagree, but I will point out that there are games which allow the narrative to unfold and develop as you go ??? and for some reason, Paradox is one of the developers who is providing that kind of experience.
In particular, EU IV and Stellaris are extremely good about providing the longform narrative real-time strategy experience. (In fact, they may be a little more longform than you were interested in.) Stellaris delivers that experience for me on a regular basis, and like most of Paradox's grand strategy offerings, the flow of DLC is steady but the upgrades usually come for free.
Graviteam Tactics' Mius Front and Tank Warfare: Tunisia 1943 aren't the most accessible of RTS designs but they do provide a really strong core of narrative experience to even a casual player. Also, they're pretty as Hell and provide a gratifying feeling of watching a lot of interacting pieces move under your command.
It's a shame that Planetary Annihilation didn't get a better reception, and I would absolutely agree with you that part of the problem is that there was too strong an emphasis on building for the competitive market with an eye to e-sports. The absence of a Supreme Commander-like single-player story experience is more than a little sad. Mechanically, and especially with the Titans expansion, it can certainly hold its own and then some next to its progenitor SC. It's also a really good game for that relaxed, LAN-party-friendly style of gameplay.
Dawn of War 3 was disappointing crap. And for somebody who really loved DoW2 that is a painful admission, but Relic really dropped the ball on the basic design principles of the game. I never thought that I would see the day that Relic was capable of dropping the ball but??? There you are.
My surprise insertion for this little bit would be to know to that one of the best RTS games in the last several years with narrative and competitive elements, single and multiplayer, was the Gearbox release of Homeworld Remastered. No, not when the game was actually released ??? it only became a decent version of the original a year later when they finally got the basics of fleet navigation mostly worked out, but if someone was looking to get into a fine example of a game in that middle space, I wouldn't have any hesitation in recommending Homeworld.
That the original came out in 1990 might be considered cheating.
I'm not sure anything can be done to "recapture the glory days of RTS." But there are a multitude of glorious days ahead of us, embracing more interesting grand strategy offerings, doing our best to avoid achingly bad sequels (Master of Orion, I'm looking at you), and just spending our time playing games.