The story is, from what i remember not that good due to the lack of advanced plot elements like tension, urgency, cohesion etc. You fall into the underground and your goal is to go back up but for that there is way to much dialogue and way too many characters that have nothing to do with you (All except Toriel) that exist just to pad the game with endless dialogue (that could be easily compressed) especially Alphys. 1-2 hours in and you will forget what was your purpose since your character (Frisk) has no character development or dialogue by design. No one else cares about your mission until almost the end, except Toriel which stops being plot relevant after the first area. If you were to remove the references to the underground/overworld you could have a totally different plot based entirely underground and the later parts like the core could be set up for a different goal.
Contrast this to lets say Cave Story which does this too but with a smaller relevance and for a limited time before remembering you what you're supposed to do through plot progression events.
Its structure is unfortunately made in such a way that if you end up liking it like i did, you will certainly enjoy it till the end. If you didn't, you will see the issues that you otherwise wouldn't see if you were imersed, which also happen to start being visible on the second playtrough. For example i remember how annoying Alphys is calling you every 2 steps in order to tell you something you will forget 5 minutes later, even though this wasn't a problem on my first playtrough.
As for the depth and appeal, when i played it as a kid in early 2016 i really felt that the story is unique and interesting but i didn't put much brain tought into since back then i was too dumb for anything story related and would pretty much skip all the talking and move straight into the gameplay if it was a different game, but not for this one since i really liked it for some reason. If i were to play it for the first time right now i would still like it but probably wouldn't go past 40% of it since i have much less patience now.