Uh... most FF stories were never exactly what I call great. I guess, perhaps, "videogame great" or maybe "console RPG great", and even then, maybe 1 or 2 out of all the releases. That's not to say that I didn't love them, but it was more about the entire package and my love for the RPG genre itself.
I also think the age someone was when they played some of these older FF games factors into how much they think the story was "good" now when they reflect on it.
Like I got choked up at the end of FFX back when I was 15, but if 30 year old me played it for the first time today I may have just rolled my eyes