Now that the narrative is completed, I would personally rate FFXIV's story as a good narrative, nothing ground-breaking. I really went into Shadowbringers after my friend told me how amazing FFXIV had become and expected to be blown away but I just never was.
The problem I personally have with the story is the world does not feel fantastical enough, especially compared to other Final Fantasy games and the whole concept of Aether came off as the writers trying to be overly clever and turn it into a "science" rather former concepts like the Lifestream of FFVIII or the Mist from FFIX.
I also felt the story was needlessly pompous in a lot of areas, especially in the use of characters like Urianger that speak in an old-world and somewhat pretentious way, making it rather difficult to understand the story.
I found it really difficult to see the Scions as anything other then "MMO NPCS" because they really did feel like they lacked agency, instead you were having to do fetch quests in the Crysterium and Old Shayalan because they told us to. The narrative could have certainly done with more meaningful options, rather then just slight dialogue changes however I do accept that this is not so much the fault of Square Enix but the problems with MMO's in general.
I just really wanted that moment were I was blown away and I just was not because I found the Ascians boring, especially when we found out more about them. There was no big plot reveal in Endwalker because Hydalean turned out to be exactly as she said and Fandaniel was so annoying that it made Zodiak rather lackluster. The less said about Zenos the better and Metieon is really under-developed and has a rather lackluster design.
The fan service, although cool to begin with became far too distracting, the whole concept of Eden at the end of Shadowbringers was a little too much for personally and I felt it detracted from FFVIII's legacy.
In summary: Compared to the masterpiece stories that were Final Fantasy IX and to a lesser extent Final Fantasy X (Both of which had me in tears at the end. I am still bittersweet about Vivi, 15 years later) and the masterful build up and end battle (and some would say confusing plot) of FFVIII, I would say that FFXIV would rank near the bottom of the other FF stories.
I just feel there can be a thing as "too much detail and worldbuilding" and the more realistic atmosphere to the game and old world dialect makes the game less accessible to players (If you want to call me an idiot or philistine for criticising the dialogue, please feel free).
The stories that are told are just boring compared to the story of FFVII, FFVIII and FFIX and for me, it really didn't have the same impact.
DISCLAIMER: I understand that I may be biased towards FFIX but I had an abusive childhood and FFIX literally stopped me from taking my own life so just a little context there.