I didn't realize it was accepted to be great, I thought that it was accepted as a scapegoat for people who want to be cool for hating popular things, where legions of imaginary, rabid fans extol its virtues somewhere unseen so pretentious people can mock them.
It came out in the mid 90's, where it had some cool new ideas attached to horrendous, grindy gameplay. A lot of the story comes across as a bit tired and cliche in retrospect, and its easy to forget the sense of wonder you once had with how much games have improved and evolved over the years (Yes, games have improved. I said it). But dammit, you find yourself loving the characters. I can not tell you how many times I have seen cynical, anti-emotional nerds outright admit, with near pride, that they cried when Aeris died. Can anyone name a game that has moved more people to tears? Hell, to this day I hear those first 3 notes of her theme and feel a little sad. If you don't get even a tiny bit emotional when young Aeris comforts her adopted mother after she senses that her husband died in the war, or when you realize that Aeris Christ-like death is what redeemed the world, then you must have a cold, cold heart. The rest of it, fine, its a process of the evolution of the medium of games and its not going to be anything outlandish, but the attachment to characters is what makes it amazing.
(Except Cait Sith. He is terrible no matter how you slice it)